SQLite notes from the trenches

Half the small services I run use SQLite, and every time I mention that, someone assumes I am trading correctness for convenience. These notes are the counterargument, collected from production use.

WAL mode plus a busy timeout handles the concurrency pattern that actually occurs at this scale: one writer, several readers, occasional bursts. The failure mode is a clean SQLITE_BUSY you retry, not a mystery.

PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=5000;
PRAGMA foreign_keys=ON;

Backups are the real superpower: the database is one file, so the backup pipeline treats it like every other file, and snapshot consistency comes from the filesystem. Restoring is copying a file.

When I do reach for Postgres: multiple writers contending per-row, or when another team needs to connect with tools that assume a server. Neither happens in my garage. Right tool, honestly applied, is boring and correct.