Sqlite Ops

SQLite Operations

SQLite is one engine with many hosts. The SQL semantics, query planner, and pragmas are the same whether you reach it through the sqlite3 CLI, Python, node:sqlite, better-sqlite3, Bun, Cloudflare D1, or libSQL/Turso — what differs is the driver surface and the operational envelope (who owns the file, what a "connection" costs, whether you can even run PRAGMA). Reason about the engine first; then check the host section for the traps that differ.

Where does the problem live?
│
├─ A statement is slow, or scans too much
│  └─ EXPLAIN QUERY PLAN first, always → references/query-performance.md
│
├─ "database is locked" / SQLITE_BUSY / writers blocking readers
│  └─ WAL + busy_timeout + BEGIN IMMEDIATE → references/concurrency-durability.md
│
├─ Wrong data got in, or a constraint didn't fire
│  └─ Type affinity, STRICT, foreign_keys=OFF → references/schema-design.md
│
├─ Search / JSON / geo / analytics feature question
│  └─ FTS5, JSON, R-tree, window fns → references/feature-modules.md
│
├─ Running on a managed/edge engine (D1, Turso)
│  └─ references/d1-edge.md + references/hosts.md
│
└─ Corruption, size, backup, VACUUM
   └─ references/operations.md

Measurement discipline (read this before optimising anything)

Most SQLite "optimisations" are unmeasured. Four rules, in order of how often they are broken:

1. Measure the statement, not the tool call. An expensive aggregate that ships inside a batch another query was already sending costs no extra round trip and is therefore invisible to per-call timing — while still scanning the whole table on every request. Decompose multi-part statements and time each part separately.
2. Report latency AND rows scanned. They move independently. An optimisation can cut latency ~25x while leaving rows-read essentially unchanged (and on a billed engine like D1, rows read is the money metric — see references/d1-edge.md).
3. Never trust wall-clock time from a CLI. Process startup dominates. Use the engine's own reported duration (.timer on in the CLI, meta.timings.sql_duration_ms on D1).
4. Take a median of 10+ runs and report the range. First runs are cold. In one measured session a cold run hit 2,495 ms against a 171 ms median on the same statement — a 1.5–1.7x first-run penalty was routine on multi-thousand-row reads.

# sqlite3 CLI: engine-reported timing, not shell time
sqlite3 app.db '.timer on' "SELECT count(*) FROM q_product WHERE org LIKE '%acme%';"

# What the planner thinks the data looks like (empty = ANALYZE never ran)
sqlite3 app.db 'SELECT * FROM sqlite_stat1;'

Prove an index will help before you create it

The highest-leverage trick in this skill, and the one that keeps schema work inside a deploy gate: run the identical statement shape against a column an existing index already covers. Same table, same row count, same predicate shape — only the column changes. The difference is your projected payoff, measured on live production data with zero schema writes.

-- Hypothesis: a covering index on (org, product_id) makes this fast.
-- Unindexed control (what you have today):
SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%';

-- Proof shot: same shape, over a column an existing index already covers.
-- If this is fast, the index is worth writing. If it isn't, the index is not your problem.
SELECT DISTINCT org FROM q_product WHERE org LIKE '%acme%';

In the worked example below the proof shot returned 6.75 ms against a 171.83 ms control — enough to justify the index without touching production schema.

EXPLAIN QUERY PLAN — the 60-second read

EXPLAIN QUERY PLAN (EQP) is the first command for any slow statement. It is cheap, safe, read-only, and available on every host that lets you run arbitrary SQL.

EXPLAIN QUERY PLAN
SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%';

The distinction that matters most: SCAN … USING COVERING INDEX is not a failure. A covering scan reads narrow index entries instead of paging in wide rows, which is exactly how you make an unseekable predicate fast.

Deep dive: ./references/query-performance.md — index design, column order, partial and expression indexes, ANALYZE/sqlite_stat1, and the full catalogue of planner defeats.

The unseekable-predicate trap (worked example)

A leading-wildcard LIKE '%x%' can never use a B-tree — SQLite optimises LIKE only for an anchored prefix ('x%'). So a plain index on that column changes nothing, people observe no improvement, and conclude "indexing didn't help here". The index wasn't wrong; the shape was. The fix is to make the scan covering, so the unavoidable full pass reads narrow index entries instead of wide rows.

-- Column order is load-bearing: FILTERED column first, PROJECTED column second.
CREATE INDEX q_product_org_product ON q_product(org, product_id);

Worked example — one database, not a constant. Measured 2026-08-04 against a live Cloudflare D1 (atdw-mirror, region OC, colo SYD), 12 runs each, median of server-side sql_duration_ms; 73-column table, 58k rows. Before: SCAN q_product USING INDEX q_product_org, 171.83 ms, 60,736 rows read. The identical statement shape over an already-covered column: 6.75 ms, 58,433 rows read. ~25x faster with rows-read essentially unchanged — proof that the win came from row width, not from touching fewer rows. Your table's numbers will differ; the shape of the result is what transfers.

Two further findings from the same session worth internalising:

• Once the covering index existed, SQLite dropped the GROUP BY temp B-tree by itself. A hand-rewrite to avoid the grouping measured 5.99 ms vs 5.85 ms — noise. Don't hand-optimise around a temp B-tree until you have re-read the plan post-index.
• An unindexed MAX() riding inside a batch another query was already sending cost 28.09 ms and 58,432 rows scanned on every response across four tools, while the statement without it cost 0.17 ms / 2 rows. The same MAX() over an indexed column: 0.17 ms / 1 row. It never showed up in per-query timing because it added no round trip.

Verify the planner's choice with and without statistics

A covering index may only be chosen once ANALYZE has populated sqlite_stat1 — and many hosted engines never run ANALYZE for you. Test both states before you rely on it:

ANALYZE;                                  -- populate sqlite_stat1
EXPLAIN QUERY PLAN SELECT ...;            -- record the plan

DELETE FROM sqlite_stat1;                 -- simulate a never-analyzed database
ANALYZE sqlite_master;                    -- force the planner to reload (now-empty) stats
EXPLAIN QUERY PLAN SELECT ...;            -- same plan? then you are safe either way

In the worked example the covering index was chosen in both states — verified, not assumed. Do the same check rather than inheriting that result.

Index design in one table

Rules that repay themselves: put the filtered column first and the projected column second in a covering index; index the column, never a function of it (unless it is an expression index); and every index you add taxes every write — audit before adding.

Concurrency and durability — the 80/20

PRAGMA journal_mode = WAL;      -- persistent; survives reconnect
PRAGMA busy_timeout = 5000;     -- per-connection; set on EVERY connection
PRAGMA foreign_keys = ON;       -- per-connection, OFF by default — see below
PRAGMA synchronous = NORMAL;    -- safe with WAL; FULL only if you fear power loss

Deep dive: ./references/concurrency-durability.md — WAL internals, the DEFERRED-upgrade deadlock, synchronous levels, checkpoint starvation, multi-process access.

Schema — the three silent bugs

1. PRAGMA foreign_keys is OFF by default. Per connection, every connection. Your REFERENCES clauses parse, are stored, and do nothing. This is the classic silent data-integrity bug in SQLite applications.
2. Type affinity is not a type. A TEXT column will happily store an integer; a declared type is a suggestion about conversion. Use STRICT tables (SQLite 3.37+) when you want a declared type enforced.
3. ALTER TABLE is limited. Adding a column and renaming are supported; dropping, retyping, and changing constraints need the 12-step recreate dance.

CREATE TABLE product (
    id       INTEGER PRIMARY KEY,
    org      TEXT NOT NULL,
    price    REAL NOT NULL,
    doc      TEXT,
    -- indexable projection of a JSON field
    sku      TEXT GENERATED ALWAYS AS (json_extract(doc, '$.sku')) VIRTUAL
) STRICT;

Deep dive: ./references/schema-design.md (affinity, STRICT, generated columns, WITHOUT ROWID, constraints) and ./references/migration-patterns.md (the 12-step ALTER dance, versioned migration runners).

Feature modules at a glance

Deep dive: ./references/feature-modules.md.

Hosts

The engine is the same; the envelope is not.

Deep dive: ./references/hosts.md for per-host connection recipes and traps.

On D1 specifically, three platform features have no stock-SQLite equivalent and are the most commonly missed:

wrangler d1 insights <db> --sort-type=sum --sort-by=reads --limit=10   # rank REAL queries by cost
wrangler d1 time-travel info <db>                                      # 30-day point-in-time restore point
# Sessions API (env.DB.withSession(bookmark)) - read replicas, sequential consistency

./references/d1-edge.md covers those plus the rows-read economics, the verified limits table, the error catalogue, and import/export. For the production incident patterns — a timed-out migrations apply --remote that landed anyway, batch() treating a 0-row scoped UPDATE as success, and the opt-in-to-replica rollout shape for read replication — see ./references/d1-production-patterns.md.

Operations

sqlite3 app.db 'PRAGMA quick_check;'        # fast structural check
sqlite3 app.db 'PRAGMA integrity_check;'    # full check — slow on big DBs
sqlite3 app.db "VACUUM INTO 'backup.db';"   # consistent backup, no downtime, defragmented
sqlite3 app.db '.dump' > backup.sql         # portable text backup
sqlite3 app.db 'PRAGMA optimize;'           # run before closing a long-lived connection

Never copy a live database file with cp while a writer is active — use VACUUM INTO, the backup API, or .dump.

Deep dive: ./references/operations.md — corruption causes and recovery, VACUUM vs VACUUM INTO, page/cache sizing, size analysis.

Triage script

scripts/eqp-triage.py reads an EXPLAIN QUERY PLAN result — either by running the statement against a database, or from piped plan text — and classifies each line by severity with a fix hint. Exits 10 when it finds something (the domain signal), 0 when the plan is clean.

# Run against a database file (uses Python's bundled sqlite3 — no external binary needed)
python3 scripts/eqp-triage.py --db app.db \
  --sql "SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%'"

# Triage a plan captured elsewhere (D1, a log, a colleague's paste)
wrangler d1 execute atdw-mirror --remote --json \
  --command "EXPLAIN QUERY PLAN SELECT product_id FROM q_product WHERE org LIKE '%acme%'" \
  | python3 scripts/eqp-triage.py

# Machine-readable findings
python3 scripts/eqp-triage.py --db app.db --sql "SELECT ..." --json | jq '.data[]'

Gotchas

Reference files

See also