SQLite in Production

Pathrule3 Rules • 2 Memories

SQLite is a transactional database embedded in a process, not a development-only toy, but its file locking, connection model, journal behavior, durability options, and migration constraints differ sharply from a client-server database. This pattern constrains transaction length, connection configuration, and migration safety while recording WAL checkpoint, backup, and deployment ownership decisions. It complements PostgreSQL and generic transaction patterns by focusing on a single database file, multiple process access, filesystem guarantees, and the operational consequences of SQLite's writer serialization.

Suggested path map

Pathrule places each piece on the matching path, so your assistant only sees it where it belongs. This is the scoping you get on import; you can adjust it in your workspace.

/ workspace root

src/

db/

Keep write transactions short and deterministic

Initialize every connection with the same database contract

WAL improves reader concurrency, not writer concurrency

migrations/

Make SQLite migrations resumable and verified

ops/

backups/

A SQLite backup is a consistent database snapshot

Rules

Keep write transactions short and deterministic/src/dbhighstrictCompute and validate before BEGIN, perform only required database work inside, and never wait on remote I/O while holding the writer.

1	SQLite serializes writers. A transaction that waits on HTTP, user input, a queue, or slow computation blocks unrelated writes even when WAL mode allows readers to continue.
2
3	- Load external inputs and perform remote calls before opening the write transaction, then revalidate database-dependent assumptions once inside.
4	- Use one explicit transaction for the complete local invariant and commit or roll back on every path; do not scatter auto-committed statements across a multi-row transition.
5	- Apply a bounded busy strategy and surface exhaustion as a retryable capacity failure instead of spinning indefinitely or returning a misleading generic error.
6	- Measure transaction duration and lock waits in the application because query timing alone does not reveal time spent waiting for the writer.
7
8	See /src/db for the adjacent decision or procedure that completes this constraint.

Initialize every connection with the same database contract/src/dbhighstrictApply foreign keys, busy handling, journal expectations, and durability settings when a connection opens, then verify them.

1	SQLite settings can be connection-specific or database-persistent with nuanced behavior. Assuming one setup call configured every future connection leads to missing foreign-key enforcement or inconsistent contention handling.
2
3	- Enable and verify foreign-key enforcement for every connection before executing application statements.
4	- Centralize connection creation and apply the approved busy timeout, synchronous policy, and other required settings from one adapter.
5	- Treat journal-mode changes as deployment operations and verify the effective mode; do not toggle modes per request or let two processes disagree about setup.
6	- Reject database files on unsupported filesystems or sharing arrangements where required locking and durability guarantees are not reliable.
7
8	See /ops/backups for the adjacent decision or procedure that completes this constraint.

Make SQLite migrations resumable and verified/migrationshighstrictApply ordered schema changes with a recorded version, transaction strategy, compatibility check, and integrity evidence.

1	SQLite migrations often rebuild tables to change constraints or column behavior. An interrupted copy or rename sequence can leave a schema that looks present but has lost indexes, triggers, foreign keys, or data.
2
3	- Record each migration exactly once and fail startup on an unknown or partially applied version instead of guessing the next schema.
4	- Use a transaction where the operation supports it, and stage table rebuilds with explicit column mapping, row-count checks, index recreation, and foreign-key validation.
5	- Back up or checkpoint according to the deployment contract before an irreversible migration, and define how the prior application version behaves with the new schema.
6	- Run integrity and foreign-key checks after migration and before accepting normal writes; preserve failure details for recovery rather than continuing on a suspect file.
7
8	See /ops/backups for the adjacent decision or procedure that completes this constraint.

Memories

WAL improves reader concurrency, not writer concurrency/src/dbUse WAL when its filesystem and workload fit, monitor checkpoint progress, and retain the single-writer capacity model.

1	Write-ahead logging lets readers continue while a writer appends, but commits still serialize and the WAL must eventually be checkpointed into the main database. A long reader can prevent checkpoint progress and allow the WAL to grow.
2
3	- Choose WAL only where all participating processes share a supported local filesystem and use compatible SQLite implementations.
4	- Monitor WAL size, checkpoint results, write latency, and long-running readers so growth is detected before disk pressure becomes an outage.
5	- Schedule or trigger checkpoints through the database API with awareness of active readers; do not delete WAL or shared-memory files manually.
6	- Scale write-heavy workloads by shortening and batching transactions first, then move to a client-server database when serialized writer capacity no longer fits.
7
8	See /ops/backups for the rule or workflow that puts this decision into practice.

A SQLite backup is a consistent database snapshot/ops/backupsUse the backup API or a coordinated snapshot procedure and restore-test the complete result instead of copying an active main file blindly.

1	The visible database file may not contain the newest committed pages while journaling is active. A filesystem copy taken without coordination can omit state or capture files from different moments.
2
3	- Prefer the SQLite backup mechanism or a storage snapshot whose consistency guarantees cover the database and active journal state.
4	- Record application version, schema version, database checksum, encryption context, and snapshot time with the backup artifact.
5	- Restore into an isolated location, run integrity and foreign-key checks, then exercise critical reads and a controlled write before declaring the backup usable.
6	- Protect backups as production data and ensure retention, encryption, and deletion policies cover copies outside the application's ordinary data directory.
7
8	See /migrations for the rule or workflow that puts this decision into practice.

Why this pattern

AI agents often keep write transactions open across network calls, assume WAL permits unlimited concurrent writers, copy a live database file without a safe backup method, or open connections with inconsistent pragmas.

Built for Teams using SQLite in desktop, mobile, edge, embedded, or modest server workloads.

Keeps your assistant from:

Holding the single writer lock during slow application work
Allowing each connection to use different safety settings
Shipping a migration that cannot recover from interruption
Backing up only the main file while journal state is active

View as markdown

License: Apache-2.0
Version: 1.0.0
Updated: 2026-08-25