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.