Sqlite database management best practices

[Mike Strout]

We are creating a process that writes IN* and GT1 segments for all incoming SIU messages on an incoming scheduling feed. We expect to see a few hundred rows per day added to this DB and we will save these rows for about 60 working days, which means the DB will contain about 12,000 rows.

I think there are some great arguments for using a Sqlite DB rather than adding the complexity of connecting via ODBC to an MSSql DB. Before I finalize my plans, I would like to hear what people with mature Sqlite solutions do to protect that data from loss or corruption.

For example, what do you do for backups of the data?

Do you do anything special permissions-wise on the Sqlite files or directory in which they live?

Are there any special strategies you employ to prevent corruption?

Topic

[James Cobane]

Hi Mike,

We have an instance of a simple SQLite database that we use that contains a list of self-pay patients that is updated daily.  We use this to bounce other transactions against to filter for one of our interfaces.  We don’t do anything special in terms of backup; it is within the file system that currently gets backed up, so it gets backed up as part of that.  Other than that, the only thing we do is a monthly prune and delete any records that are older than 90 days.  This isn’t “mission critical”, so we aren’t really doing anything in addition to the daily backup.

Jim Cobane

Henry Ford Health

[Jeff Dinsmore]

Like Mike, we rely on daily file system backup to protect our SQLite databases.

We also run our engines on VMWare, so there’s pretty good protection against hardware failure.

In my dozen or so years experience with SQLite on Linux and Windows – both virtual and physical – I can recall only one instance of a corrupt DB file.

Jeff Dinsmore
Chesapeake Regional Healthcare

[Author]

Replies