> SQLite: what your AI agent should know — from The Handover, the-handover.org/services/sqlite
> © Captivated Ltd — free to use in your own work, not to redistribute as a collection. the-handover.org/licence

# SQLite

What we tell an AI agent working on a project that uses SQLite: 2 rules, 0 learned the hard way.

Build a full rootnote set for a SQLite project: https://the-handover.org/build?areas=working-agreement,database&database=sqlite

SQLite published no file for agents when last checked; this page is the only agent-facing guidance for it we know of.

### SQLite: back up through SQLite, never by copying the file

> Reported by Scott Spence, "SQLite Corruption with fs.copyFile() in WAL Mode" — scottspence.com/posts/sqlite-corruption-fs-copyfile-issue; SQLite, "How To Corrupt An SQLite Database File — Backup or restore while a transaction is active" — www.sqlite.org/howtocorrupt.html · distilled by Leon Mallett (captivated.online) with Claude Code · reviewed 2026-10-25

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.

### SQLite: foreign keys are off by default, and there is one writer at a time

> General guidance · Leon Mallett (captivated.online) with Claude Code · reviewed 2026-10-19

- **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.

Also applies to every database choice, so not repeated here: An instruction is not a control: keep production out of the agent's reach, and check recovery yourself; Never run a migration or a write against production that has not run against a copy.