How to protect SQLite database from corruption

Question

I'm trying to figure out which is the safest strategy for protecting my (file-based) SQLite database from corruption (in this case, I'm working with Adobe Air, but this could apply to any webkit browser that uses SQLite, including Mobile Safari).

I'm thinking about creating a database connection, keeping it around for only maybe 5 or 10 seconds and then closing it if it hasn't been used during that period. My thinking is that should the machine crash or the application exit abnormally, chances are good that the file will already be closed and thus less likely to get corrupted. But I know that the more often you open and close a filebased DB, the more likely it is you'll have a critical error.

I'm sure I'm over-thinking this, but for my application it's critical that in the event of a system crash, the application can recover cleanly and quickly and that means I have to try to protect the DB as much as I can.

Does anyone know which strategy is likely to be safer?

Answer 1

At the end of this document

File Locking And Concurrency In SQLite Version 3

There are a section named "6.0 How To Corrupt Your Database Files" that discuss corruption hipotetical curroptions problems in sqlite. "Things that can can go wrong".

Answer 2

First do NOT use journal_mode=MEMORY or =OFF.

We can use these commands to reduce the probability of a corruption:

1. PRAGMA synchronous=FULL or PRAGMA synchronous=EXTRA
2. PRAGMA fullfsync=ON (only works on Mac OS X)

But they come with a cost of making the transactions slower. And even with them the db can become corrupted due to other causes like failure in the storage device or in the memory so we must be prepared to the problem.

We can make regular backups, but it has 2 disadvantages:

1. It copies the entire db file on each backup
2. We loose all the transactions that were made after the last backup

I have a customer that was used to make his backup on a pen-drive and the pen-drive was always kept plugged to the computer until a lightning came and destroyed the computer, including the pen-drive. All the data was lost. So the backup must be kept separated from the main computer.

A better alternative is to use replication. With it each transaction executed on the main db is replicated to the replicas.

For SQLite we can use litereplica. It supports Point-in-Time Recovery and I suggest to use it with replication because if some data is accidentally deleted from the main db it will be replicated. With PITR we can restore the db to a previous point in time.

Another important suggestion is to keep the replica in a separate device, and apart from each other. At least not in the same building.

Running PRAGMA integrity_check command once in a while is a good practice due to the fact that SQLite does not do it automatically and it continues writing to the db in some kinds of corruptions.

And if your db uses foreign keys you can do the same with PRAGMA foreign_key_check.