Backend · Databases
SQLite in production: WAL, backups and where it stops
SQLite is a good database for a single-node service: no extra process, no network hop, and one file you can reason about. It stays good in production if you change a few defaults and respect a few limits.
Turn on WAL, and set the rest on purpose
By default SQLite uses a rollback journal, where a writer blocks readers. In write-ahead logging (WAL) mode, readers and the single writer work at the same time, which is what most web workloads want.
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;The journal mode is stored in the database file, so it sticks. The other three are per connection and must be set every time you open one. With synchronous = NORMAL in WAL mode, a power loss can lose the latest committed transactions but will not corrupt the file. Use FULL if you cannot accept that. The busy_timeout makes a blocked connection wait instead of failing at once with SQLITE_BUSY.
Keep write transactions short
There is exactly one writer at a time. A long write transaction, or a transaction that starts as a read and then tries to upgrade to a write, will end in SQLITE_BUSY. Start transactions that you know will write with BEGIN IMMEDIATE so the lock is taken up front, and do slow work such as HTTP calls or password hashing outside the transaction.
Watch the WAL file
A checkpoint moves content from the WAL back into the main database file. SQLite does this automatically after roughly a thousand pages, but a long-running read transaction can prevent a checkpoint from finishing. The symptom is a -wal file that keeps growing. Find the long reads and shorten them. During a quiet window you can also force a checkpoint:
PRAGMA wal_checkpoint(TRUNCATE);Back up with the database, not around it
Copying the file while the application runs can give you a torn copy, and in WAL mode the -wal and -shm files matter too. Use the online backup instead:
sqlite3 app.db ".backup '/backups/app-latest.db'"
sqlite3 /backups/app-latest.db "PRAGMA integrity_check;"VACUUM INTO also produces a consistent, compacted copy. Whichever you pick, restore a backup somewhere else on a schedule. A backup you have never restored is a hope, not a backup. If you need a smaller recovery window, a tool that streams the WAL to object storage can cut it to seconds.
Where it stops
- One host. WAL mode relies on shared memory, so every process that opens the database must run on the same machine. Do not put the file on a network file system.
- One writer. Write throughput is plenty for most services, but measure it with your own workload before you commit to it.
- Several application servers writing. At that point you want a client/server database, and the earlier you plan the move, the cheaper it is.