SQLite in Production skill for Codex

SQLite serves real production traffic when it runs on one host’s local disk, in WAL mode, with every connection configured the same way, writes kept short and serialized, and continuous off-host backups. Done well means no database is locked errors under load, a measured restore path, and an honest answer to “does this need a server database instead?”

Does SQLite fit?

Situation	Verdict
One app server (or one primary process), local SSD	Good fit
Read-heavy, writes up to hundreds per second, each short	Good fit
Several app servers writing to the same database	No. Use Postgres, or an edge/replicated variant ($libsql-turso, $cloudflare-d1)
Database file on NFS, SMB, EFS or other network storage	No. Locking and WAL shared memory break; corruption risk
Long write transactions (bulk jobs) mixed with latency-sensitive writes	Only with batching; every write waits for the single writer
Need for row-level permissions, many DB users, or remote SQL access	No

Before changing anything

1. Read AGENTS.md and find where connections are opened (ORM config, a db.py, a Go sql.Open). Every code path that opens the file must apply the same pragmas, including background jobs, migrations and one-off scripts.
2. Run the health check on a copy or on the live file (it opens read-only):

python3 scripts/sqlite_health.py data/app.db          # quick_check
python3 scripts/sqlite_health.py data/app.db --full   # integrity_check, slower

It reports journal mode, size, free pages, WAL size, non-STRICT tables, unindexed foreign keys and FK violations, and exits 1 on integrity or FK failures.

Connection setup

Persistent (stored in the file, set once): journal_mode=WAL, page_size, auto_vacuum. Per-connection (must be set every time a connection opens): everything else.

PRAGMA journal_mode = WAL;        -- persistent; readers no longer block the writer
PRAGMA busy_timeout = 5000;       -- wait up to 5 s for a lock instead of failing
PRAGMA synchronous = NORMAL;      -- safe with WAL; FULL only if every commit must survive power loss
PRAGMA foreign_keys = ON;         -- OFF by default, per connection
PRAGMA cache_size = -64000;       -- ~64 MB page cache (negative = KiB)
PRAGMA temp_store = MEMORY;

Run PRAGMA optimize; periodically or when long-lived connections close (it is cheap and updates statistics only where useful).

Framework hooks:

• Rails 8: ships production-ready SQLite defaults (WAL, busy handling, immediate transactions). Check config/database.yml for timeout and any pragmas: overrides.
• Django 5.1+: OPTIONS = {"transaction_mode": "IMMEDIATE", "init_command": "PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA busy_timeout=5000;"} (verify init_command handles multiple statements on your version, or use a connection_created signal).
• Python sqlite3: sqlite3.connect(path, timeout=5.0, isolation_level="IMMEDIATE") makes implicit transactions start with BEGIN IMMEDIATE.
• Go: the driver DSN carries pragmas (syntax differs between modernc.org/sqlite and mattn/go-sqlite3; check its README). Use a separate *sql.DB for writes with SetMaxOpenConns(1), and a larger pool for reads.
• Node: better-sqlite3 is synchronous and fast; call db.pragma('journal_mode = WAL') and friends right after opening.

Writes and “database is locked”

SQLite allows one writer at a time. With WAL, readers never block the writer and the writer never blocks readers. Locked errors come from:

Cause	Fix
No busy_timeout, so contention fails instantly	Set it on every connection
Deferred transaction reads, then tries to write after another writer committed (SQLITE_BUSY even with a timeout)	Start write transactions with BEGIN IMMEDIATE
Long write transactions (bulk import, network calls inside a transaction)	Batch into short transactions (1k-10k rows), never hold a transaction across I/O
Many goroutines/threads/processes writing at once	Funnel writes through one connection or a queue
A forgotten open transaction in a REPL or admin tool	Find and close it; fuser/lsof on the file shows holders

Bulk inserts: wrap thousands of inserts in one transaction and reuse a prepared statement. Single-row autocommit inserts are 100x slower because each commit syncs.

Checkpoints and file growth

• The WAL is merged back into the main file by checkpoints (auto at ~1000 pages). A reader that never finishes (a long report, a leaked read transaction) prevents the WAL from resetting, and it grows without bound. Keep reads short; monitor WAL size.
• PRAGMA journal_size_limit = 67108864; caps the WAL size left on disk after checkpoints.
• Deleted data leaves free pages; the file does not shrink. VACUUM rebuilds it but needs exclusive access and up to 2x disk space: schedule it with the user’s approval.

Schema and types

• Use STRICT tables (SQLite 3.37+) so a text value cannot land in an INTEGER column: CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL) STRICT;
• INTEGER PRIMARY KEY aliases the rowid (fast). Add AUTOINCREMENT only if ids must never be reused.
• Store timestamps as ISO-8601 text in UTC or integer Unix epochs; pick one per database.
• Index foreign key columns yourself.
• ALTER TABLE supports add column, rename column/table and DROP COLUMN (3.35+). Other changes (types, constraints) need the documented rebuild: create new table, copy, drop old, rename, recreate indexes and triggers, inside one transaction with PRAGMA foreign_keys=OFF set before it and PRAGMA foreign_key_check after.
• Track schema version with PRAGMA user_version if the project has no migration tool.

Backups and replication

Never copy the database file with cp while the app runs: the WAL holds committed data and the copy can be inconsistent.

sqlite3 data/app.db ".backup '/backups/app-$(date -u +%Y%m%dT%H%M%SZ).db'"
sqlite3 data/app.db "VACUUM INTO '/backups/app-compact.db'"     # compacted copy

Python: src.backup(sqlite3.connect(dest)). Verify every backup with python3 scripts/sqlite_health.py <backup> --full.

Continuous replication with Litestream streams WAL changes to S3-compatible storage (needs network and credentials). Minimal /etc/litestream.yml:

dbs:
  - path: /data/app.db
    replicas:
      - url: s3://acme-backups/app

litestream replicate -config /etc/litestream.yml
litestream restore -config /etc/litestream.yml -o /tmp/restore.db /data/app.db

Litestream 0.5 changed its storage format; check the installed version’s docs for config keys and do not mix versions on one replica path. Run a restore drill and the health script on the result before calling backups done. Restoring over the live database file is destructive: stop the app, keep the old file, and confirm with the user first.

Gotchas

• Docker: mount the database directory as a volume on local disk, include the -wal and -shm files’ directory, and run one container that writes.
• foreign_keys and busy_timeout silently revert to defaults on any connection that did not set them, such as an admin shell or a cron script.
• Opening the same file through two different SQLite libraries in one process (two drivers) can break POSIX locks; use one.
• Time-based DEFAULT CURRENT_TIMESTAMP is UTC text without a zone suffix; document it.

Verification

• Health script: WAL mode, quick_check ok, no FK violations
• Every connection path sets busy_timeout, foreign_keys, synchronous
• Writers use BEGIN IMMEDIATE and short transactions; load test shows no locked errors
• Backups taken with .backup/VACUUM INTO or Litestream; a restore was tested
• Database on local disk, single writing host

Output template

## SQLite production review: <app>
**Fit:** <yes/no and why: hosts, write rate, storage>
**Pragmas:** <persistent and per-connection, where set in code>
**Write path:** <transaction mode, batching, writer pool>
**Backups:** <method, frequency, destination, last restore test>
**Health check:** <script output summary>
**Risks:** <growth, long readers, migration constraints>