Understanding sqlite

A from-the-source book on SQLite internals

A self-paced, hands-on study guide that turns the sqlite/sqlite repository into something you genuinely understand — not just SELECT from.

---

Who this book is for (you)

You've shipped SQLite a hundred times without ever opening it. It's the database inside your Android and iOS apps (under Room, under Core Data / GRDB), the local-first store that rides along inside your Tauri desktop builds, and the embeddable C engine that fits the edge boxes you cut your teeth on. It is, by some measure, the most-deployed software module on Earth — and to most engineers it's a black box that turns SQL strings into rows.

This book opens the box. By the end you'll be able to take a SQL statement, predict the bytecode SQLite compiles it into, point to the exact .c file and line where each layer does its work, and explain why a query is fast or slow by reading the plan instead of guessing.

Two things make this fit you exactly:

• You already know C and the edge. SQLite is ~250k lines of disciplined, portable ANSI C — opaque handles, function-pointer vtables, goto-based cleanup, zero-malloc paths, SQLITE_OMIT_* size knobs. That's your QCS605/Hexagon dialect. We lean on it instead of re-teaching it.
• You already use it everywhere. Every "Connect to your past" sidebar bridges a layer back to something you actually ship: Room/Core Data on mobile, tauri-plugin-sql in your desktop app, and the difference between an in-process engine and a Postgres socket.

---

How this book works

• Grounded in real source. Every claim is cited to an exact file:line in a local clone pinned to tag version-3.53.2 (commit ccc132c5be20). Verify any citation yourself:

  cd ../sqlite && git log -1 --format='%H %d'    # ccc132c5be20… (tag: version-3.53.2)
  sed -n '846p' src/vdbe.c                        # int sqlite3VdbeExec(

• Every chapter has the same shape:

  1. Why it matters — the problem this layer solves.
  2. Mental model + an ASCII diagram — a picture to hold in your head.
  3. A guided source read — exact file:line citations into ../sqlite/src/.
  4. A lab — concrete commands (mostly sqlite3 + EXPLAIN) with an expected observation.
  5. A checkpoint — questions to prove you got it.
  6. 🔌 Connect to your past — the layer mapped to your mobile / Tauri / edge / web work.

• Read with the source open. Keep ../sqlite beside this book. SQLite ships as a single amalgamated sqlite3.c, but the real source is src/*.c — that's what we cite.

⚠️ A grounding quirk worth knowing up front: a few "source" files aren't in src/ at all — parse.c, opcodes.h, opcodes.c, and sqlite3.h are generated during the build (by Lemon, the mkopcode* scripts, and from sqlite.h.in). We cite the true source: parse.y, the opcode comment blocks in vdbe.c, and sqlite.h.in.

---

The curriculum

#	Chapter	What you'll be able to do	Priority
00	Environment & C-for-SQLite Refresher	Build the CLI, run EXPLAIN, and read SQLite's C idioms fluently	Foundation
01	Mental Model & Repo Map	Name the 8 layers and trace one SELECT through all of them	Foundation
02	Connection Lifecycle & Prepare	Walk open → prepare → step → column → finalize → close in source	Foundation
03	The VDBE — the Bytecode Machine	Read EXPLAIN output and trace any opcode into the interpreter	★ Flagship
04	The Parser & Code Generator	Follow SQL text → tokens → parse tree → opcodes	Core
05	The Query Planner	Explain index/join choices from EXPLAIN QUERY PLAN	Core
06	B-tree & the Record Format	Decode a page and a record by hand from the file bytes	Core
07	Pager, WAL & the VFS	Explain atomic commit, WAL vs rollback, and the VFS seam	Application
08	Capstone: Extending & Staying Current	Ship an extension + virtual table; read a real check-in unaided	Mastery

Depth is weighted toward Chapter 3. The VDBE is SQLite's load-bearing abstraction — the seam between the compiler (00–02, 04–05) and the storage engine (06–07). Everything else is "what feeds the machine" or "what the machine drives." Spend the most time there.

---

Suggested pace

• Week 1 — Chapters 00–02. Get a green build, internalize the layer map, walk the lifecycle.
• Week 2 — Chapter 03 (the flagship). Slow down. Do all four EXPLAIN case studies.
• Week 3 — Chapters 04–05. The front end and the planner; you now know what produces the bytecode.
• Week 4 — Chapters 06–07. Down to the disk: B-tree, records, pager, WAL, VFS.
• Week 5 — Chapter 08. Extend it, then read a real upstream change with no help.

Depth over speed. A chapter you can teach beats three you skimmed.

---

Progress checklist

• 00 — sqlite3 built; EXPLAIN SELECT 1; prints an opcode table (labs/lab0-baseline.md filled).
• 01 — You can sketch the 8 layers from memory and name a file for each.
• 02 — You can point to where sqlite3_step resumes the VM (vdbeapi.c).
• 03 — You traced a SELECT, an INSERT, an indexed lookup, and a transaction to opcodes.
• 04 — You found the grammar rule for SELECT in parse.y and the codegen for INSERT.
• 05 — You read an EXPLAIN QUERY PLAN and explained the index choice.
• 06 — You decoded the 100-byte file header and one record's serial types by hand.
• 07 — You watched a -wal file appear and explained checkpointing.
• 08 — You compiled a loadable extension and registered a virtual table.

---

Layout

learning/
├── sqlite/                     # the PINNED clone (version-3.53.2) — read-only, for citations
└── sqlite-book/
    ├── README.md               # you are here
    ├── 00..08-*.md             # 9 chapters; 03 is the ★ flagship
    ├── labs/                   # lab note templates + drop-in SQL
    ├── reference/glossary.md   # terms by category + a "Key files" source index
    └── site/                   # the built HTML site (run site_src/build_site.py)

Start with Chapter 0.

---

_{A RumitX publication · rumitx.com · Edge AI, human-centric design.}