sqlite-plan-diff

sqlite-plan-diff is a small TypeScript CLI for comparing SQLite query plans semantically, not just with plain text diffing.

It runs EXPLAIN QUERY PLAN, normalizes plan nodes, and reports meaningful changes such as:

• SCAN -> SEARCH
• index added/removed/changed
• covering index gained/lost
• temporary b-tree introduced/removed
• major subtree/join shape changes

Install

From npm (recommended)

npx sqlite-plan-diff --help

Or install globally:

npm install -g sqlite-plan-diff

From source

pnpm install
pnpm build

If better-sqlite3 native bindings are blocked on first install (pnpm security prompt), run:

pnpm approve-builds

Then approve better-sqlite3.

Run locally during development:

pnpm dev -- --help

Usage

explain

sqlite-plan-diff explain app.db "select * from users where email = ?"
sqlite-plan-diff explain app.db "select * from users where email = ?" --json

diff

sqlite-plan-diff diff app.db \
  --before "select * from users where name = 'Alice'" \
  --after "select * from users where email = '[email protected]'"

sqlite-plan-diff diff app.db \
  --before "select * from users where name = ?" --before-param "Alice" \
  --after "select * from users where email = ?" --after-param "[email protected]" \
  --json

sqlite-plan-diff diff app.db \
  --before "select * from users where name = 'Alice'" \
  --after "select * from users where email = '[email protected]'" \
  --format markdown

whatif

sqlite-plan-diff whatif app.db \
  --query "select * from orders where customer_id = 42 order by created_at desc" \
  --index "create index idx_orders_customer_created on orders(customer_id, created_at desc)"

whatif works by cloning the DB file to a temporary location, applying the DDL there, and then diffing baseline vs hypothetical plans.

Demo (Before/After)

Using the included fixture database:

sqlite-plan-diff whatif test/fixtures/app.db \
  --query "select * from orders where customer_id = 42 order by created_at desc" \
  --index "create index idx_orders_customer_created on orders(customer_id, created_at desc)"

Expected semantic changes include:

• scan_to_search for orders
• index_added (idx_orders_customer_created)
• temp_btree_removed (ORDER BY)

Typical output:

Semantic Diff
- [scan_to_search] Table "orders" improved from SCAN to SEARCH.
- [index_added] Table "orders" now uses index "idx_orders_customer_created".
- [temp_btree_removed] Temporary B-tree removed (ORDER BY).

Before Plan
SCAN | table=orders
TEMP_BTREE | reason=ORDER BY

After Plan
SEARCH | table=orders | index=idx_orders_customer_created | where=customer_id=?

Output Formats

All commands support terminal output by default, plus:

• --json
• --format markdown

--json output includes:

• raw EQP rows
• normalized plan (op, table, index, covering, whereTerms, tempReason, children)
• semantic diff changes for diff and whatif

--format markdown is intended for PR comments and CI summaries.

Use in CI

Example GitHub Actions step that runs a plan diff check:

name: Query Plan Check

on: [pull_request]

jobs:
  plan-diff:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: pnpm/action-setup@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 22
          cache: pnpm
      - run: pnpm install --frozen-lockfile
      - run: pnpm build
      - run: node dist/cli.js diff test/fixtures/app.db --before "select * from users where name = 'Alice'" --after "select * from users where email = '[email protected]'"

Limitations

• Planner comparison tool only: it does not benchmark runtime.
• Query plans can differ across SQLite versions and dataset statistics.
• Parameter values can affect plan choice.
• Use this tool to detect likely plan improvements/regressions, then confirm with real timing on representative data.

Development

pnpm test
pnpm typecheck
pnpm build

TODO

• Add parser fixtures from multiple SQLite versions to harden normalization.
• Add a small benchmark script to pair semantic diffs with measured query timings.

Project Layout

• src/cli.ts
• src/commands/explain.ts
• src/commands/diff.ts
• src/commands/whatif.ts
• src/sqlite/connect.ts
• src/sqlite/eqp.ts
• src/parser/normalizePlan.ts
• src/diff/semanticDiff.ts
• src/diff/renderTerminal.ts
• src/diff/renderMarkdown.ts
• src/diff/renderJson.ts
• src/sandbox/cloneDb.ts
• test/