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 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%';

Plan line	Means	Verdict
SEARCH t USING INDEX ix (col=?)	B-tree seek, touches matching rows only	Best case
SEARCH t USING COVERING INDEX ix	Seek, and every needed column is in the index — table never read	Best case
SCAN t USING COVERING INDEX ix	Full pass, but over narrow index entries, not wide rows	Often fine — see below
SCAN t USING INDEX ix	Full pass over the index and a row fetch per hit	Suspicious: the index is buying little
SCAN t	Full table scan	Fix it, unless the table is tiny
USE TEMP B-TREE FOR ORDER BY	Sorting because no index supplies the order	Cost signal
USE TEMP B-TREE FOR GROUP BY	Same, for grouping	Cost signal
CORRELATED SCALAR SUBQUERY	Subquery re-executed per outer row	Usually the whole problem

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)

The measured D1 case behind this advice (why a plain index on LIKE '%x%' changes nothing, the fix, before/after numbers) and the with/without-sqlite_stat1 planner check: references/covering-index-worked-example.md.

Index design in one table

Predicate shape	Indexable?	What to build
col = ?, col IN (…), col > ?, BETWEEN	Yes	B-tree on col
a = ? AND b = ?	Yes	Composite (a, b) — equality columns first
a = ? ORDER BY b	Yes	Composite (a, b) — kills the temp B-tree
col LIKE 'x%' (anchored)	Yes, if col is TEXT with BINARY collation	B-tree on col
col LIKE '%x%' (leading wildcard)	No seek possible	Make the scan covering, or use FTS5 trigram
lower(col) = ?	Not on a plain index	Expression index ON t(lower(col))
status = 'open' where 2% of rows qualify	Yes	Partial index WHERE status = 'open'
json_extract(doc,'$.k') = ?	Not on a plain index	Expression index, or generated column + index

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

Symptom	Cause	Fix
SQLITE_BUSY	Another connection holds a lock; yours gave up waiting	PRAGMA busy_timeout = 5000; and keep write transactions short
SQLITE_LOCKED	Conflict within the same connection (or a shared cache)	Fix the code — a retry loop will spin forever
"database is locked" mid-transaction	BEGIN (DEFERRED) read that later writes → upgrade deadlock, not retryable	BEGIN IMMEDIATE for any transaction that will write
Readers blocked by a writer	Rollback journal mode	PRAGMA journal_mode = WAL; (persistent, set once)
-wal file grows without bound	Long-lived reader pins the checkpoint	Close/refresh readers; PRAGMA wal_checkpoint(TRUNCATE);

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

Need	Reach for	Note
Substring / fuzzy text search	FTS5 with the trigram tokenizer	The real answer to LIKE '%x%' at scale
Word/phrase search with ranking	FTS5 + bm25()	External-content table avoids duplicating the corpus
Semi-structured documents	json_extract / -> / ->>, JSONB (3.45+)	Index via generated column or expression index
Bounding-box / interval overlap	R-tree virtual table	Compile-time module; check availability
Running totals, ranking, gaps	Window functions (3.25+)	Same syntax as PostgreSQL
Insert-or-update	ON CONFLICT … DO UPDATE (3.24+)	excluded.col refers to the proposed row
Read back what you wrote	RETURNING (3.35+)	Makes atomic claim-a-job patterns single-statement

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

Hosts

The engine is the same; the envelope is not.

Per-host connection model and traps: Hosts at a glance. On D1, cost is rows read, bound parameters cap at 100, and there is no PRAGMA surface.

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%'"

# More: triage a plan piped from D1 or a log, and --json output -> references/eqp-triage.md

Gotchas

Mistake	Why it bites	Fix
Adding an index for LIKE '%x%'	Leading wildcard can never seek	Covering index, or FTS5 trigram
Timing with a shell stopwatch	CLI/driver startup dominates	Engine-reported duration; median of 10+
Timing the tool call, not the statement	Piggy-backed statements are invisible	Decompose and time each part
Assuming REFERENCES is enforced	foreign_keys is OFF per connection	PRAGMA foreign_keys = ON on every connection
Assuming a declared type is enforced	Affinity, not typing	STRICT tables
Retrying SQLITE_LOCKED	Same-connection conflict never clears	Fix the code path
BEGIN then write	DEFERRED→write upgrade deadlocks and is not retryable	BEGIN IMMEDIATE
cp on a live database	Torn copy	VACUUM INTO / backup API
SELECT *	Defeats covering indexes; widens every row read	Project only what you need
VACUUM to "speed things up"	Rewrites the whole file, needs 2x space, holds a lock	PRAGMA optimize / targeted index work
Trusting one cold run	1.5–1.7x first-run penalty is routine	Median of 10+, report the range
Inlining literals to dodge a parameter cap	That is how injection happens	Chunk the work; keep bound parameters
Re-running a timed-out remote migration	The apply may have landed; the error was about the response	Verify schema state read-only first — ./references/d1-production-patterns.md
Reading a committed batch() as per-statement success	A conditional UPDATE matching 0 rows is not an error	Check meta.changes; 0 on a scoped write = 403/conflict

Reference files

Reference	Load when
./references/query-performance.md	Any slow statement: EQP, index design, ANALYZE, planner defeats, measurement method
./references/d1-edge.md	Cloudflare D1: rows-read economics, d1 insights, Sessions API/replication, Time Travel, limits, errors
./references/d1-production-patterns.md	Running D1 in production: verifying a timed-out migration, batch() 0-row write verification, the opt-in-to-replica replication rollout
./references/concurrency-durability.md	Locking, WAL, busy_timeout, transaction modes, checkpointing, durability
./references/schema-design.md	Affinity, STRICT, foreign keys, generated columns, WITHOUT ROWID, constraints
./references/schema-patterns.md	Ready-made table designs: state, cache, event log, queue, session, dedup
./references/migration-patterns.md	Versioned migrations, the 12-step ALTER dance, host-specific runners
./references/feature-modules.md	FTS5, JSON/JSONB, R-tree, window functions, upsert, RETURNING
./references/hosts.md	Per-host connection recipes and driver traps (Python, Node, Bun, D1, libSQL)
./references/async-patterns.md	Python aiosqlite depth: async CRUD, batching, pooling
./references/operations.md	Integrity checks, corruption recovery, VACUUM, backups, size and page tuning
./references/testing.md	In-memory vs file databases, fixtures, deterministic seeding, migration tests
./references/covering-index-worked-example.md	The measured D1 covering-index case behind the advice above; the with/without-statistics planner check
./references/eqp-triage.md	More eqp-triage.py invocations: a plan piped from D1 or a log, --json output

See also

Skill	When to combine
sql-ops	Vendor-neutral SQL: CTEs, window functions, JOIN strategy
perf-ops	The wider performance workflow — profiling, load testing, before/after protocol
cloudflare-ops	Workers, bindings, and deployment around a D1 database
postgres-ops	When the workload has outgrown SQLite's single-writer model
python-database-ops	SQLAlchemy / ORM layers over SQLite