SQLite in Production skill for Claude Code

SQLite is production-ready when every connection applies the same pragmas, writes go through short BEGIN IMMEDIATE transactions so lock contention turns into brief waits instead of errors, the database lives on a local disk (never a network filesystem), and there is a continuously tested backup or replica. Configure it deliberately; the defaults are tuned for backward compatibility, not for servers.

When SQLite fits

Fits	Does not fit
Single server or single process owning the file (app server, CLI, desktop, mobile, edge with embedded replicas)	Many app servers writing the same DB over the network
Write throughput up to roughly hundreds to low thousands of small transactions/sec	Sustained heavy concurrent writes from many processes
Read-heavy workloads; reads scale with cores in WAL mode	Need for row-level locking across long write transactions
Data fits on one machine’s disk	Multi-region active-active writes (consider a server DB or a CRDT sync layer)

Connection setup (every connection, every time)

journal_mode=WAL persists in the file; everything else below is per-connection and must be set on each new connection (pool hooks, ORM afterConnect, etc.).

PRAGMA journal_mode = WAL;          -- once per database; persistent
PRAGMA busy_timeout = 5000;         -- wait up to 5 s for locks instead of failing immediately
PRAGMA synchronous = NORMAL;        -- safe with WAL: no corruption; may lose last commits on power loss
PRAGMA foreign_keys = ON;           -- OFF by default, per connection
PRAGMA cache_size = -64000;         -- negative = KiB (here ~64 MB) per connection
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 268435456;       -- optional: 256 MB memory-mapped reads
PRAGMA journal_size_limit = 67108864; -- cap WAL file size after checkpoints (64 MB)

• Use synchronous = FULL if losing the last few committed transactions on power loss is unacceptable (payments, ledgers).
• Long-lived connections: run PRAGMA optimize; periodically (e.g. hourly) and before closing; SQLite 3.46+ also documents PRAGMA optimize=0x10002; right after opening.

Driver specifics:

// better-sqlite3 (Node, synchronous API)
import Database from "better-sqlite3";
const db = new Database("app.db");
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
db.pragma("synchronous = NORMAL");
db.pragma("foreign_keys = ON");
const transfer = db.transaction((from, to, amt) => { /* statements */ });
transfer.immediate(1, 2, 500);      // BEGIN IMMEDIATE variant

# Python 3.12+: take explicit control of transactions
import sqlite3
con = sqlite3.connect("app.db", autocommit=True, timeout=5.0)
for p in ("journal_mode=WAL", "synchronous=NORMAL", "foreign_keys=ON"):
    con.execute(f"PRAGMA {p}")
con.execute("BEGIN IMMEDIATE")
try:
    con.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (500, 1))
    con.execute("COMMIT")
except Exception:
    con.execute("ROLLBACK")
    raise

On Python before 3.12, use isolation_level=None for the same explicit control; the legacy default silently opens deferred transactions before DML.

Concurrency model

• WAL: unlimited concurrent readers, exactly one writer at a time. Readers do not block the writer and vice versa.
• BEGIN (deferred) starts as a reader and upgrades to writer on the first write. If another connection wrote in the meantime, the upgrade fails with SQLITE_BUSY immediately; busy_timeout cannot help. Start every transaction that will write with BEGIN IMMEDIATE.
• Keep write transactions short (milliseconds). Never hold one across network calls, user input, or slow computation.
• Funnel writes through one connection (or a single writer queue/thread) in high-write apps; use a pool of read-only connections for reads.
• Batch many small writes into one transaction: 10,000 inserts in one transaction can be orders of magnitude faster than 10,000 autocommits.

“database is locked” triage:

Cause	Fix
No busy_timeout on some connection	Set it on every connection
Deferred transaction upgraded to write	BEGIN IMMEDIATE
Long write transaction	Shorten; move work outside the transaction
Forgotten open transaction / unfinalized statement	Close cursors, reset statements, check driver transaction mode
DB on NFS/SMB/cloud-synced folder	Move to local disk
Not in WAL mode	PRAGMA journal_mode=WAL;

Schema

• Use STRICT tables (3.37+) so types are enforced: CREATE TABLE ... (...) STRICT; Without it, SQLite accepts 'abc' in an INTEGER column.
• INTEGER PRIMARY KEY aliases the rowid (fast). Add AUTOINCREMENT only if IDs must never be reused; it costs extra writes.
• Store timestamps as ISO-8601 TEXT in UTC ('2026-10-19T12:00:00Z') or INTEGER Unix epoch; pick one per project.
• Index foreign key columns; enable foreign_keys or the constraints are decorative.
• Booleans are integers 0/1; add CHECK (flag IN (0, 1)).
• JSON: store as TEXT (or JSONB blob format via jsonb() on 3.45+) and index extracted values with expression indexes: CREATE INDEX ix ON events (json_extract(data, '$.type'));

Migrations

Track the schema version with PRAGMA user_version (or the migration tool’s table):

BEGIN IMMEDIATE;
-- migration 7
ALTER TABLE users ADD COLUMN locale TEXT NOT NULL DEFAULT 'en';
PRAGMA user_version = 7;
COMMIT;

ALTER TABLE supports ADD COLUMN (with restrictions: no non-constant default, no PRIMARY KEY/UNIQUE), RENAME COLUMN, RENAME TO, and DROP COLUMN (3.35+, fails if the column is indexed, part of a constraint, or referenced). Anything else - changing a type, adding a constraint, changing a default - needs the table rebuild:

PRAGMA foreign_keys = OFF;            -- must be outside a transaction
BEGIN IMMEDIATE;
CREATE TABLE users_new (
  id    INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name  TEXT NOT NULL DEFAULT ''
) STRICT;
INSERT INTO users_new (id, email, name) SELECT id, email, coalesce(name, '') FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
-- recreate indexes, triggers, and views that referenced users
PRAGMA foreign_key_check;             -- must return no rows before committing
COMMIT;
PRAGMA foreign_keys = ON;

Take a backup before any rebuild; DROP TABLE is destructive and needs user confirmation on real data.

Local-first/mobile apps: migrations run on every client device at app start. Make them idempotent, fast, and tested against databases created by every released app version.

Backups and replication

Need	Tool
Consistent snapshot of a live DB	VACUUM INTO '/backups/app-2026-10-19.db'; or sqlite3 app.db ".backup /backups/app.db"
Continuous streaming to object storage, point-in-time restore	Litestream
Read replicas / edge copies synced from a primary	libSQL embedded replicas (Turso)
Mobile/desktop local-first sync	A sync engine (CRDT or server-authoritative sync); see offline-sync

• Never copy the .db file with cp while it is in use; the -wal file holds committed data not yet in the main file. Use VACUUM INTO or the backup API.
• Litestream runs as a sidecar process (litestream replicate -config litestream.yml) shipping WAL changes to S3-compatible storage; restore with litestream restore -o restored.db <replica-url>. Config details changed in the 0.5 release (2025); follow the docs for the installed version.
• Only one process should run Litestream for a given database, and the app must not checkpoint in ways that bypass it (keep the default WAL auto-checkpointing unless the docs for your version say otherwise).
• Test restores on a schedule: restore, run PRAGMA integrity_check;, and query recent rows.

Health check script

python3 scripts/sqlite_health.py path/to/app.db          # quick_check
python3 scripts/sqlite_health.py path/to/app.db --full   # integrity_check (slower)

Reports journal mode, size, free pages, WAL size, integrity and foreign-key violations, unindexed FK columns, non-STRICT tables, and tables without a primary key. Exits 1 on integrity or FK errors. It opens the file read-only.

Gotchas

• WAL does not work on network filesystems, and -wal/-shm files must stay next to the database (container volumes must include the directory, not just the file).
• A long-running read transaction prevents checkpoints from completing, so the WAL grows without bound (“checkpoint starvation”). Close read transactions promptly.
• PRAGMA foreign_keys = ON inside a transaction is a silent no-op.
• ALTER TABLE ... ADD COLUMN ... NOT NULL requires a non-NULL default.
• LIKE is case-insensitive for ASCII only; = is case-sensitive unless COLLATE NOCASE.
• Integer overflow: SQLite integers are 64-bit, but JavaScript numbers lose precision above 2^53; enable BigInt mode in the driver (better-sqlite3 safeIntegers()) if IDs can exceed that.
• Deleting data does not shrink the file; VACUUM rewrites the whole DB (needs free disk equal to its size and blocks writers) - schedule it, or use auto_vacuum = INCREMENTAL set before tables are created.

Verification

1. Every connection path applies the pragmas: query PRAGMA foreign_keys; PRAGMA busy_timeout; from the app’s own connection, not a fresh shell.
2. Concurrency test: run N parallel writers and readers for a minute; no SQLITE_BUSY errors surface to callers.
3. python3 scripts/sqlite_health.py app.db exits 0.
4. Restore drill from the backup or Litestream replica succeeds and passes integrity_check.