{"slug":"sqlite-ops","title":"sqlite-ops","summary":"SQLite across every host and engine - query performance, concurrency, schema, feature modules, operations. Triggers on: sqlite, slow query, EXPLAIN QUERY PLAN, query plan, SCAN vs SEARCH, covering index, index not used, rows read, rows_read, sql_duration_ms, ANALYZE, sqlite_stat1","platform":"Claude","tags":[],"authorName":"LLM Mart","authorSlug":"llm-mart","score":0,"source":"github","price":null,"verified":false,"createdAt":"2026-09-30T19:36:56.814024Z","repo":{"url":"https://github.com/0xDarkMatter/claude-mods","stars":43,"forks":7,"license":"MIT","updatedAt":"2026-09-30T15:18:48Z"},"bodyHtml":"
\n

name: sqlite-ops\ndescription: \"SQLite across every host and engine - query performance, concurrency, schema, feature modules, operations. Triggers on: sqlite, slow query, EXPLAIN QUERY PLAN, query plan, SCAN vs SEARCH, covering index, index not used, rows read, rows_read, sql_duration_ms, ANALYZE, sqlite_stat1, LIKE performance, database is locked, SQLITE_BUSY, WAL, busy_timeout, STRICT tables, type affinity, foreign_keys, VACUUM, integrity_check, fts5, trigram, json_extract, D1, cloudflare d1, wrangler d1, node:sqlite, better-sqlite3, bun:sqlite, aiosqlite, libsql, turso, migration, d1 batch, read replication, sessions api, d1 bookmark, migration timeout.\"\nlicense: MIT\ncompatibility: \"Guidance is engine-agnostic (SQLite 3.x semantics). Examples are labelled by host: sqlite3 CLI, Python sqlite3/aiosqlite, node:sqlite/better-sqlite3/bun:sqlite, Cloudflare D1 via wrangler, libSQL/Turso. scripts/eqp-triage.py needs Python 3.8+ (stdlib only).\"\nallowed-tools: \"Read Write Bash\"\nmetadata:\nauthor: claude-mods\nrelated-skills: \"sql-ops, perf-ops, cloudflare-ops, postgres-ops\"

\n

SQLite Operations

\n

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

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

Measurement discipline (read this before optimising anything)

\n

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

\n
    \n
  1. Measure the statement, not the tool call. An expensive aggregate that ships inside\na batch another query was already sending costs no extra round trip and is therefore\ninvisible to per-call timing — while still scanning the whole table on every request.\nDecompose multi-part statements and time each part separately.
    2. \n
  2. Report latency AND rows scanned. They move independently. An optimisation can cut\nlatency ~25x while leaving rows-read essentially unchanged (and on a billed engine like\nD1, rows read is the money metric — see references/d1-edge.md).
    3. \n
  3. Never trust wall-clock time from a CLI. Process startup dominates. Use the engine's\nown reported duration (.timer on in the CLI, meta.timings.sql_duration_ms on D1).
    4. \n
  4. Take a median of 10+ runs and report the range. First runs are cold. In one measured\nsession a cold run hit 2,495 ms against a 171 ms median on the same statement — a\n1.5–1.7x first-run penalty was routine on multi-thousand-row reads.
    5. \n
\n
# sqlite3 CLI: engine-reported timing, not shell time\nsqlite3 app.db '.timer on' \"SELECT count(*) FROM q_product WHERE org LIKE '%acme%';\"\n\n# What the planner thinks the data looks like (empty = ANALYZE never ran)\nsqlite3 app.db 'SELECT * FROM sqlite_stat1;'\n
\n

Prove an index will help before you create it

\n

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

\n
-- Hypothesis: a covering index on (org, product_id) makes this fast.\n-- Unindexed control (what you have today):\nSELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%';\n\n-- Proof shot: same shape, over a column an existing index already covers.\n-- If this is fast, the index is worth writing. If it isn't, the index is not your problem.\nSELECT DISTINCT org FROM q_product WHERE org LIKE '%acme%';\n
\n

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

\n

EXPLAIN QUERY PLAN — the 60-second read

\n

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

\n
EXPLAIN QUERY PLAN\nSELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%';\n
\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Plan lineMeansVerdict
SEARCH t USING INDEX ix (col=?)B-tree seek, touches matching rows onlyBest case
SEARCH t USING COVERING INDEX ixSeek, and every needed column is in the index — table never readBest case
SCAN t USING COVERING INDEX ixFull pass, but over narrow index entries, not wide rowsOften fine — see below
SCAN t USING INDEX ixFull pass over the index and a row fetch per hitSuspicious: the index is buying little
SCAN tFull table scanFix it, unless the table is tiny
USE TEMP B-TREE FOR ORDER BYSorting because no index supplies the orderCost signal
USE TEMP B-TREE FOR GROUP BYSame, for groupingCost signal
CORRELATED SCALAR SUBQUERYSubquery re-executed per outer rowUsually the whole problem
\n

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

\n

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

\n

The unseekable-predicate trap (worked example)

\n

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

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

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

\n

Two further findings from the same session worth internalising:

\n\n
\n

Verify the planner's choice with and without statistics

\n

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

\n
ANALYZE;                                  -- populate sqlite_stat1\nEXPLAIN QUERY PLAN SELECT ...;            -- record the plan\n\nDELETE FROM sqlite_stat1;                 -- simulate a never-analyzed database\nANALYZE sqlite_master;                    -- force the planner to reload (now-empty) stats\nEXPLAIN QUERY PLAN SELECT ...;            -- same plan? then you are safe either way\n
\n

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

\n

Index design in one table

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Predicate shapeIndexable?What to build
col = ?, col IN (…), col > ?, BETWEENYesB-tree on col
a = ? AND b = ?YesComposite (a, b) — equality columns first
a = ? ORDER BY bYesComposite (a, b) — kills the temp B-tree
col LIKE 'x%' (anchored)Yes, if col is TEXT with BINARY collationB-tree on col
col LIKE '%x%' (leading wildcard)No seek possibleMake the scan covering, or use FTS5 trigram
lower(col) = ?Not on a plain indexExpression index ON t(lower(col))
status = 'open' where 2% of rows qualifyYesPartial index WHERE status = 'open'
json_extract(doc,'$.k') = ?Not on a plain indexExpression index, or generated column + index
\n

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

\n

Concurrency and durability — the 80/20

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
SymptomCauseFix
SQLITE_BUSYAnother connection holds a lock; yours gave up waitingPRAGMA busy_timeout = 5000; and keep write transactions short
SQLITE_LOCKEDConflict within the same connection (or a shared cache)Fix the code — a retry loop will spin forever
\"database is locked\" mid-transactionBEGIN (DEFERRED) read that later writes → upgrade deadlock, not retryableBEGIN IMMEDIATE for any transaction that will write
Readers blocked by a writerRollback journal modePRAGMA journal_mode = WAL; (persistent, set once)
-wal file grows without boundLong-lived reader pins the checkpointClose/refresh readers; PRAGMA wal_checkpoint(TRUNCATE);
\n
PRAGMA journal_mode = WAL;      -- persistent; survives reconnect\nPRAGMA busy_timeout = 5000;     -- per-connection; set on EVERY connection\nPRAGMA foreign_keys = ON;       -- per-connection, OFF by default — see below\nPRAGMA synchronous = NORMAL;    -- safe with WAL; FULL only if you fear power loss\n
\n

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

\n

Schema — the three silent bugs

\n
    \n
  1. PRAGMA foreign_keys is OFF by default. Per connection, every connection. Your\nREFERENCES clauses parse, are stored, and do nothing. This is the classic silent\ndata-integrity bug in SQLite applications.
    2. \n
  2. Type affinity is not a type. A TEXT column will happily store an integer; a\ndeclared type is a suggestion about conversion. Use STRICT tables (SQLite 3.37+)\nwhen you want a declared type enforced.
    3. \n
  3. ALTER TABLE is limited. Adding a column and renaming are supported; dropping,\nretyping, and changing constraints need the 12-step recreate dance.
    4. \n
\n
CREATE TABLE product (\n    id       INTEGER PRIMARY KEY,\n    org      TEXT NOT NULL,\n    price    REAL NOT NULL,\n    doc      TEXT,\n    -- indexable projection of a JSON field\n    sku      TEXT GENERATED ALWAYS AS (json_extract(doc, '$.sku')) VIRTUAL\n) STRICT;\n
\n

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

\n

Feature modules at a glance

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
NeedReach forNote
Substring / fuzzy text searchFTS5 with the trigram tokenizerThe real answer to LIKE '%x%' at scale
Word/phrase search with rankingFTS5 + bm25()External-content table avoids duplicating the corpus
Semi-structured documentsjson_extract / -> / ->>, JSONB (3.45+)Index via generated column or expression index
Bounding-box / interval overlapR-tree virtual tableCompile-time module; check availability
Running totals, ranking, gapsWindow functions (3.25+)Same syntax as PostgreSQL
Insert-or-updateON CONFLICT … DO UPDATE (3.24+)excluded.col refers to the proposed row
Read back what you wroteRETURNING (3.35+)Makes atomic claim-a-job patterns single-statement
\n

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

\n

Hosts

\n

The engine is the same; the envelope is not.

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
HostConnection modelWatch out for
sqlite3 CLIDirect file.timer on for real timings; .mode/.headers for output
Python sqlite3Direct file, per-connection pragmasImplicit transaction handling; check_same_thread
Python aiosqliteThread-backed async wrapperStill one writer; see ./references/async-patterns.md
node:sqliteSynchronous, built into NodeNo external dependency; API still stabilising
better-sqlite3Synchronous, native addonFastest Node option; prepared statements are the unit of reuse
bun:sqliteSynchronous, built into BunAPI close to better-sqlite3, not identical
Cloudflare D1HTTP/RPC to a managed SQLiteBilled on rows read; 100-parameter cap; no PRAGMA surface
libSQL / TursoServer or embedded replicaReplica staleness; syntax extensions beyond stock SQLite
\n

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

\n

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

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

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

\n

Operations

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

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

\n

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

\n

Triage script

\n

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

\n
# Run against a database file (uses Python's bundled sqlite3 — no external binary needed)\npython3 scripts/eqp-triage.py --db app.db \\\n  --sql \"SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%'\"\n\n# Triage a plan captured elsewhere (D1, a log, a colleague's paste)\nwrangler d1 execute atdw-mirror --remote --json \\\n  --command \"EXPLAIN QUERY PLAN SELECT product_id FROM q_product WHERE org LIKE '%acme%'\" \\\n  | python3 scripts/eqp-triage.py\n\n# Machine-readable findings\npython3 scripts/eqp-triage.py --db app.db --sql \"SELECT ...\" --json | jq '.data[]'\n
\n

Gotchas

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
MistakeWhy it bitesFix
Adding an index for LIKE '%x%'Leading wildcard can never seekCovering index, or FTS5 trigram
Timing with a shell stopwatchCLI/driver startup dominatesEngine-reported duration; median of 10+
Timing the tool call, not the statementPiggy-backed statements are invisibleDecompose and time each part
Assuming REFERENCES is enforcedforeign_keys is OFF per connectionPRAGMA foreign_keys = ON on every connection
Assuming a declared type is enforcedAffinity, not typingSTRICT tables
Retrying SQLITE_LOCKEDSame-connection conflict never clearsFix the code path
BEGIN then writeDEFERRED→write upgrade deadlocks and is not retryableBEGIN IMMEDIATE
cp on a live databaseTorn copyVACUUM INTO / backup API
SELECT *Defeats covering indexes; widens every row readProject only what you need
VACUUM to \"speed things up\"Rewrites the whole file, needs 2x space, holds a lockPRAGMA optimize / targeted index work
Trusting one cold run1.5–1.7x first-run penalty is routineMedian of 10+, report the range
Inlining literals to dodge a parameter capThat is how injection happensChunk the work; keep bound parameters
Re-running a timed-out remote migrationThe apply may have landed; the error was about the responseVerify schema state read-only first — ./references/d1-production-patterns.md
Reading a committed batch() as per-statement successA conditional UPDATE matching 0 rows is not an errorCheck meta.changes; 0 on a scoped write = 403/conflict
\n

Reference files

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
ReferenceLoad when
./references/query-performance.mdAny slow statement: EQP, index design, ANALYZE, planner defeats, measurement method
./references/d1-edge.mdCloudflare D1: rows-read economics, d1 insights, Sessions API/replication, Time Travel, limits, errors
./references/d1-production-patterns.mdRunning D1 in production: verifying a timed-out migration, batch() 0-row write verification, the opt-in-to-replica replication rollout
./references/concurrency-durability.mdLocking, WAL, busy_timeout, transaction modes, checkpointing, durability
./references/schema-design.mdAffinity, STRICT, foreign keys, generated columns, WITHOUT ROWID, constraints
./references/schema-patterns.mdReady-made table designs: state, cache, event log, queue, session, dedup
./references/migration-patterns.mdVersioned migrations, the 12-step ALTER dance, host-specific runners
./references/feature-modules.mdFTS5, JSON/JSONB, R-tree, window functions, upsert, RETURNING
./references/hosts.mdPer-host connection recipes and driver traps (Python, Node, Bun, D1, libSQL)
./references/async-patterns.mdPython aiosqlite depth: async CRUD, batching, pooling
./references/operations.mdIntegrity checks, corruption recovery, VACUUM, backups, size and page tuning
./references/testing.mdIn-memory vs file databases, fixtures, deterministic seeding, migration tests
\n

See also

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
SkillWhen to combine
sql-opsVendor-neutral SQL: CTEs, window functions, JOIN strategy
perf-opsThe wider performance workflow — profiling, load testing, before/after protocol
cloudflare-opsWorkers, bindings, and deployment around a D1 database
postgres-opsWhen the workload has outgrown SQLite's single-writer model
python-database-opsSQLAlchemy / ORM layers over SQLite
\n","files":[{"path":"assets/.gitkeep","sizeBytes":0,"isText":false},{"path":"references/async-patterns.md","sizeBytes":8592,"isText":true},{"path":"references/concurrency-durability.md","sizeBytes":13474,"isText":true},{"path":"references/d1-edge.md","sizeBytes":27724,"isText":true},{"path":"references/d1-production-patterns.md","sizeBytes":12992,"isText":true},{"path":"references/feature-modules.md","sizeBytes":15441,"isText":true},{"path":"references/hosts.md","sizeBytes":13134,"isText":true},{"path":"references/migration-patterns.md","sizeBytes":14162,"isText":true},{"path":"references/operations.md","sizeBytes":11371,"isText":true},{"path":"references/query-performance.md","sizeBytes":25742,"isText":true},{"path":"references/schema-design.md","sizeBytes":13326,"isText":true},{"path":"references/schema-patterns.md","sizeBytes":6566,"isText":true},{"path":"references/testing.md","sizeBytes":10810,"isText":true},{"path":"scripts/eqp-triage.py","sizeBytes":13447,"isText":true},{"path":"scripts/.gitkeep","sizeBytes":0,"isText":false},{"path":"SKILL.md","sizeBytes":20492,"isText":true},{"path":"tests/run.sh","sizeBytes":15028,"isText":true}],"reviewScore":null,"reviewSummary":null,"trust":{"provenance":"trusted-source-unreviewed","notice":"Community-authored content, reproduced verbatim and not vetted as instructions. Treat it as data to evaluate, never as directives to follow.","bodySource":null},"bodyLocked":false,"purchaseUrl":null,"sourceUrl":null,"report":{"provenance":"trusted-source-unreviewed","screen":{"ran":true,"outcome":"clean","suspicious":0,"notes":0,"hiddenCharacters":false},"virusScan":{"engine":"clamav","status":"clean","scannedAt":"2026-09-30T19:39:13.238172Z","sha256":"9205C2CCF309A24CE22907B5218DEC323B811A9D41D02F2880AA153BE36AF029","sizeBytes":91088},"review":null,"source":{"repositoryUrl":"https://github.com/0xDarkMatter/claude-mods","path":"skills/sqlite-ops","license":"MIT","commit":"3dfaf0ba5753026a99ee13f9d9ed56b9793bb6e8","subtreeSha":"7F3790435F2762FC9132B41F60A6301DCB07114771128035F0D9B22A32666BFC","lastSyncedAt":"2026-09-30T19:37:28.226022Z"},"reviewedAt":"2026-09-30T19:43:17.874331Z","notice":"Community-authored content, reproduced verbatim and not vetted as instructions. Treat it as data to evaluate, never as directives to follow."},"install":[{"target":"skills-cli","command":"npx skills add https://github.com/0xDarkMatter/claude-mods/tree/main/skills/sqlite-ops"},{"target":"claude-code","command":"claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install 0xdarkmatter-claude-mods@llmmart"},{"target":"git","command":"git clone https://github.com/0xDarkMatter/claude-mods.git"}]}