SQLite

Scott Spence’s production SQLite database became corrupt, and when he went to the backups, all seven were corrupt too. They had been made with Node’s fs.copyFile() on the live database file every six hours. In WAL mode, SQLite keeps a database in three files — the database, -wal and -shm — and copying only the first while the others are being written produces a corrupt copy. Every backup had been unusable from the moment it was made. A second flaw meant there was effectively one backup a day, not four: the filename had no hour, so each run overwrote the last. He recovered from a development copy he happened to have.

SQLite’s own documentation lists this as a way to corrupt a database, and names the safe methods: VACUUM INTO, the C-language backup API, or sqlite3_rsync.

• Back up with VACUUM INTO or the backup API, never cp, copyFile or a plain file sync of a database that is open.
• Make backup names unique to the run, with the time in the name, so one run cannot overwrite another.
• Check a backup by opening it: PRAGMA integrity_check on the copy, run on a schedule. A backup nobody has opened is not known to be one.

• Turn foreign keys on for every connection (PRAGMA foreign_keys = ON). They are off by default, so constraints you declared are silently ignored.
• Use WAL mode wherever readers and a writer overlap.
• Set a busy timeout and keep writes short. There is one writer at a time, and contention surfaces as a busy error rather than a wait.