SQLite in Production: 5 Mistakes That Corrupt Your Database

I have corrupted a SQLite database in production. More than once. Each time the pattern was the same: the database was fine — the human process around it was the problem. Here are the five mistakes that actually bite, and the discipline that catches them.

Mistake 1: Letting a second SQLite implementation touch a live database

The error that started it: database disk image is malformed (11) on a database the app had been happily using. The cause was not the app. A maintenance script opened the same WAL-mode database with a different SQLite build, wrote to it, and the two implementations stepped on each other.

Rule: one live database, one SQLite implementation. If your app uses a specific driver (I use the pure-Go modernc.org/sqlite), do not point a Python sqlite3, a different CLI, or another driver at the same live file. Stop the service, then use whatever tool on a copy.

Mistake 2: Backing up only the .db file in WAL mode

In WAL mode, the real database is the .db file plus the -wal file holding un-checkpointed writes. Copying just .db while a -wal exists silently drops every write that has not been checkpointed yet. The backup looks fine; it is missing your most recent data.

Rule: back up .db, -wal, and -shm together — or take a consistent snapshot with VACUUM INTO, which reads everything safely in one pass.

Mistake 3: Never testing a restore

A backup you have never restored is not a backup; it is a folder of bytes. The first time you discover your restore procedure does not work should not be during an incident.

Rule: restore into a scratch directory on a schedule. If you cannot bring the data back in ten minutes with your documented steps, fix the steps until you can.

Mistake 4: Treating PRAGMA integrity_check as optional

SQLite's integrity_check is cheap on small databases and it catches structural corruption early. But remember what it does not catch: corruption inside row payload bytes. In my own testing, garbled bytes inside a text column returned ok from integrity_check — the structure was fine, the content was wrong. Checksums of the content are your responsibility.

Rule: run integrity_check on a schedule, but do not mistake it for a guarantee. It validates structure, not data.

Mistake 5: Deploying or replacing files carelessly

On Windows, replacing a database across volumes fails with WinError 17 (ERROR_NOT_SAME_DEVICE) — copy to the destination volume first, then replace on the same volume. And never move or delete a database while the service is running. File-replacement mistakes are how "I just deployed" becomes "the database is malformed."

Rule: stop the service, copy to the same volume, operate on a copy, verify, then swap. Same discipline as a database upgrade, because it is one.

The discipline that prevents all five

1. One driver per live database.
2. Back up all three WAL files (or use VACUUM INTO).
3. Test restores on a schedule.
4. Run integrity_check regularly — and understand its limits.
5. Stop, copy to the same volume, work on a copy.

None of these are exciting. All five would have saved me an incident. If you want to size a SQLite database before you build it, the estimator below is a start — but the discipline above is what keeps it from turning into a recovery story.