Sqlite

SQLite

Production patterns for SQLite as a primary datastore on servers, mobile devices, and inside CLI tools. Defaults below assume SQLite 3.45+ (Debian stable, macOS 14, Ubuntu 24.04). Version-gated features are flagged.

When SQLite is the right call

SQLite is the best choice when:

• Single-node OLTP/OLAP up to ~1 TB with mostly read-heavy or modest write traffic (single-digit thousand writes/sec).
• You want zero operational overhead — no daemon, no network, one file plus a -wal + -shm sidecar.
• You want fork-safe local state in a CLI, agent, mobile app, Electron shell, or edge worker.
• Read replicas are acceptable for HA (Litestream, Turso, D1).

Skip SQLite when:

• You need multi-writer scale-out (Postgres / CockroachDB / D1 with replication caveats).
• The DB lives on NFS/SMB/Dropbox/iCloud Drive — WAL locking is unsafe across those filesystems.
• You need per-row access control at the engine level.

Open every connection with this preamble

Most "SQLite is slow" reports are an un-tuned default config. Run this on every connection (PRAGMAs reset per handle):

PRAGMA journal_mode = WAL;          -- many readers + one writer, no block
PRAGMA synchronous = NORMAL;        -- safe with WAL; ~10x faster than FULL
PRAGMA busy_timeout = 5000;         -- ms; let SQLite handle lock contention
PRAGMA foreign_keys = ON;           -- off by default for back-compat
PRAGMA temp_store = MEMORY;         -- keep tmp tables/indexes off disk
PRAGMA cache_size = -64000;         -- 64 MiB page cache (negative = KiB)
PRAGMA mmap_size = 134217728;       -- 128 MiB mmap window for reads

Run PRAGMA optimize; before closing long-lived connections (or every few hours on a server) to refresh stats and let the planner rebuild affected indexes — cheap if nothing changed.

Full PRAGMA reference, safety implications, and pool sizing: references/pragmas-and-connection.md.

The writer-concurrency rule

SQLite allows many concurrent readers but exactly one writer at a time, even with WAL. The canonical server pattern is:

• One writer connection (serialize writes through it).
• N reader connections (pool sized to CPU count).
• All writes start with BEGIN IMMEDIATE (acquires RESERVED lock up front) — never plain BEGIN, which is DEFERRED and causes upgrade deadlocks when two readers both try to become writers.
• Retry on SQLITE_BUSY outside the transaction with jittered backoff. busy_timeout handles short waits; long-tail contention needs explicit retry.

def write(conn, fn, *, max_retries=5):
    for attempt in range(max_retries):
        try:
            conn.execute("BEGIN IMMEDIATE")
            fn(conn)
            conn.execute("COMMIT")
            return
        except sqlite3.OperationalError as e:
            conn.execute("ROLLBACK")
            if "locked" not in str(e) or attempt == max_retries - 1:
                raise
            time.sleep((2 ** attempt) * 0.05 + random.random() * 0.05)

Frameworks that bake this in: GRDB (DatabasePool), better-sqlite3 (single sync connection), Rails 7.1+ (separate writer/reader pools).

Schema design

• Use STRICT tables (3.37+) — type enforcement that catches bugs the default affinity hides.
• Prefer INTEGER PRIMARY KEY (rowid alias, lookup is O(1)) over AUTOINCREMENT unless you specifically need monotonic-never- reused IDs.
• WITHOUT ROWID helps narrow composite PKs without large blobs; hurts wide tables.
• Use CHECK constraints liberally; SQLite enforces them cheaply.
• Store time as either ISO-8601 text (TEXT) or unix seconds (INTEGER) — pick one project-wide. Never Julian real-numbers.

CREATE TABLE posts (
    id          INTEGER PRIMARY KEY,
    author_id   INTEGER NOT NULL REFERENCES users(id),
    title       TEXT    NOT NULL,
    body        TEXT    NOT NULL,
    created_at  INTEGER NOT NULL DEFAULT (unixepoch()),
    deleted_at  INTEGER,
    CHECK (length(title) BETWEEN 1 AND 200)
) STRICT;

Indexes

Three index shapes that solve 90% of slow queries:

Column order rule: equality → range → sort. A query that filters WHERE author_id = ? AND created_at > ? benefits from (author_id, created_at), not the reverse.

Always inspect plans with EXPLAIN QUERY PLAN. Look for SEARCH … USING INDEX (good) vs SCAN (bad on large tables).

Deep dive: references/indexes-and-queries.md.

Pagination: keyset, not OFFSET

LIMIT N OFFSET M is O(N+M) — SQLite still scans the skipped rows. Use keyset / seek pagination with a composite cursor that includes the PK as tiebreaker:

-- next page (created_at, id) < (?, ?)
SELECT id, created_at, title
FROM posts
WHERE deleted_at IS NULL
  AND (created_at, id) < (:cursor_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Encode the cursor as base64(created_at || ":" || id) and hand it back to the client. Reverse the comparison and sort order for the previous page.

Patterns and the bidirectional cursor: references/pagination-and-queues.md.

Job queue in one table

UPDATE … RETURNING (3.35+) lets you atomically claim the next job without a row lock or SELECT FOR UPDATE:

CREATE TABLE jobs (
    id           INTEGER PRIMARY KEY,
    queue        TEXT    NOT NULL DEFAULT 'default',
    payload      TEXT    NOT NULL,            -- JSON
    status       TEXT    NOT NULL DEFAULT 'queued'
                         CHECK (status IN ('queued','running','done','failed')),
    priority     INTEGER NOT NULL DEFAULT 0,
    run_after    INTEGER NOT NULL DEFAULT (unixepoch()),
    attempts     INTEGER NOT NULL DEFAULT 0,
    max_attempts INTEGER NOT NULL DEFAULT 5,
    leased_at    INTEGER,
    last_error   TEXT
) STRICT;

CREATE INDEX jobs_claim_idx ON jobs(queue, status, run_after, priority DESC, id)
    WHERE status = 'queued';

-- atomically claim one job
UPDATE jobs
   SET status = 'running',
       leased_at = unixepoch(),
       attempts = attempts + 1
 WHERE id = (
     SELECT id FROM jobs
      WHERE queue = ?1
        AND status = 'queued'
        AND run_after <= unixepoch()
      ORDER BY priority DESC, id
      LIMIT 1
   )
RETURNING id, payload;

A sweeper requeues leases older than the timeout. A dead-letter move happens when attempts >= max_attempts. Full implementation: references/pagination-and-queues.md#job-queue-in-one-table.

Full-text + vector search

FTS5 ships with SQLite; sqlite-vec adds vector kNN. Combine them with reciprocal-rank fusion for hybrid search.

-- external-content FTS5: index stays in sync via triggers
CREATE VIRTUAL TABLE posts_fts USING fts5(
    title, body, content='posts', content_rowid='id',
    tokenize='unicode61 remove_diacritics 2'
);

CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
    INSERT INTO posts_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;
CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;
CREATE TRIGGER posts_au AFTER UPDATE ON posts BEGIN
    INSERT INTO posts_fts(posts_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO posts_fts(rowid, title, body)
    VALUES (new.id, new.title, new.body);
END;

-- ranked search
SELECT p.id, p.title, snippet(posts_fts, 1, '<b>', '</b>', '…', 10) AS hit
FROM posts_fts JOIN posts p ON p.id = posts_fts.rowid
WHERE posts_fts MATCH ?
ORDER BY bm25(posts_fts);

For substring (not just prefix) matches, use the trigram tokenizer. Hybrid retrieval and the sqlite-vec schema: references/fts5-and-vector-search.md.

JSON

json_extract, ->, and ->> (3.38+) let you treat columns as documents:

SELECT id, payload ->> '$.user.email' AS email
FROM events
WHERE payload ->> '$.kind' = 'signup';

-- index a hot JSON path
CREATE INDEX events_kind_idx
    ON events (payload ->> '$.kind');

Promote anything you query often to a real column with a generated column. Treat JSON as a schemaless escape hatch, not a default.

Backups and replication

Never cp a live WAL database — it loses the -wal and -shm state. Use VACUUM INTO, the backup API, or Litestream.

Full replication walkthrough: references/extensions-and-replication.md.

Migrations

SQLite does not support most ALTER TABLE operations natively (rename column with deps, drop CHECK/FK, change type). The canonical workaround is the 12-step rewrite documented at https://sqlite.org/lang_altertable.html#otheralter:

PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE posts_new ( … );           -- target shape
INSERT INTO posts_new SELECT … FROM posts;
DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;
-- recreate indexes, triggers, views referring to posts
PRAGMA foreign_key_check;               -- verify before commit
COMMIT;
PRAGMA foreign_keys = ON;

Use a real migrator: goose, atlas, alembic, sqlx-migrate, dbmate, or sqlite-utils. Pair with the go-sql skill for the sqlc + goose flow against an SQLite engine.

Driver picks

Stack-specific tuning, pool sizing, and bundle/build notes: references/platforms.md.

Useful extensions

• sqlean — uuid, stats, math, crypto, fileio, regexp, text; the "missing stdlib" for SQLite.
• sqlite-vec — vector kNN (cosine, L2, dot); successor to sqlite-vss.
• R*Tree — spatial indexing; ships with SQLite.
• spellfix1 — typo-tolerant suggestion; ships with SQLite.
• STMT virtual table — lists currently-prepared statements; ships with SQLite.

Load via sqlite3 -cmd ".load ./vec0" or the driver API (better-sqlite3 loadExtension, Python enable_load_extension, Go sqlite3_load_extension). You must build/install the extension binary for your platform.

Index of extensions and Litestream/Turso config: references/extensions-and-replication.md.

Gotchas

• INSERT OR REPLACE deletes the old row, cascading FKs. Prefer INSERT … ON CONFLICT(col) DO UPDATE SET … (upsert).
• foreign_keys = ON is per-connection. Easy to miss on a reader; enforce in the connection preamble.
• WAL sidecars (-wal, -shm) must travel with the DB. Don't ship just the .db file.
• cp of a live WAL DB is corrupt. Use VACUUM INTO or the backup API.
• Type affinity without STRICT. A TEXT 'abc' will happily land in an INTEGER column. STRICT tables reject it.
• Date/time format. Pick ISO-8601 text or unix seconds for the whole project. Mixing breaks indexes and comparisons.
• sqlite_sequence quirks. AUTOINCREMENT writes to that shared table on every insert — it's a contention hotspot. Drop AUTOINCREMENT unless you specifically need it.
• UPDATE without WHERE. Same risk as any SQL; the CLI's .bail on and .changes on help during interactive work.
• Multithreading mode. Check PRAGMA compile_options for THREADSAFE=1 (full) or =2 (multi-thread, no shared cache). Single-thread builds will deadlock under any concurrency.

Observability and debugging

When to load a sibling skill