SQLite reliablity on SD card

How reliable is it on an SD card.

I think SQLite supports raw read /write. Does GHI support it.
Is it better than having the data base on a file system like SD card
Because FAT could get corrupted.

@ microt - I’ve used Sqlite on SD extensively with the premium boards. As long as you’re sure to flush the file system regularly you shouldn’t run into any issues.

There is some bloat involved on the file but I can’t imagine that being too much of an issue either so long as you’re not doing copious amounts of inserts and deletes.

1 Like

Thanks @ Skewworks

Can you give me an idea of how big of a SD card I would need.
If I record 10 integers every minute with current date and time.
Will it be a mess/slow to query and get the oldest record if the DB has been running for a year.

@ Skewworks - Have you ever had issues with corruption if power is removed while data is being written to the SD? I am just now starting to use SQLite on the G120 with SD card.

I have so many issues with using SD cards. Everything is fine until you put 2 or 3 gigs of data on it and weird stuff starts to happen. I have spent weeks trying to find out what the exact conditions required to cause problems are, but to no avail. There is clearly a bug in the filesystem handler somewhere.

Data loss in the event of a power failure during write on any device is expected behavior. You’ll also want to be very sure to call flush. If you don’t your data is not guaranteed to be there even in ideal conditions.

very possible. I’ve not used quite that level of data. If you want to PM me details of what “weird behavior” constitutes I could try to replicate it when I have time. I believe I own every premium GHI board and a few non-premium ones.

I’ve generated a new thread. https://www.ghielectronics.com/community/forum/topic?id=16524

The G120 has 2.87 MB Flash

is there any way I can reserve 1MB and use it for SQLite

@ microt - no

@ Skewworks - I guess I should have asked the question a little better. Can the FAT file system or SQLite recover from the corruption that could occur if power is removed during a write operation? I’m not as concerned about losing the last write. I’m more worried about an unusable device in the field or losing all the data.

I am in the middle of developing a G120 based application using SD card and SQLite. I am trying to determine if I need to come up with a UPS type system to give device time to shut down cleanly after a power fail.

Recovering would be affected by the amount of corruption. I’ve only see an SD card get corrupted a couple of times in use with my boards and in all cases using a PC to scan & repair it worked.

I’m not sure what you’re using this for or how vital the data is so I can’t advise you for or against backup power.

if it’s important, put a supercap on it and flush when you see the main power lost…