Interactive architecture map of the world's most deployed database engine — a serverless, zero-configuration, self-contained SQL database that replaces fopen(), not PostgreSQL.
SQLite was born from a practical frustration: Richard Hipp was building battleship software for the US Navy and kept having the Informix database server crash. Rather than fix the server, he asked: "Why can't I pull data directly off the disk drive?" The result is a serverless, zero-configuration, self-contained database engine that runs in-process.
SQLite competes with custom file formats, not client/server databases. Reading data from SQLite is faster than reading individual files from the filesystem.
The entire database — schema, tables, indexes, metadata — lives in one cross-platform binary file. No directory structures, no server processes.
Hipp built his own parser generator (Lemon), VCS (Fossil), printf(), and malloc wrappers. No external dependencies beyond the C compiler.
111+ C source files concatenated into a single sqlite3.c file (~8.4 MB). Compilers produce 5-10% faster code from one translation unit.
"Your enemy is complexity." Hipp developed SQLite independently of academic database theory, discovering convergences with PostgreSQL's architecture organically. He learned B-trees directly from Knuth's Art of Computer Programming.
SQL processing flows through a clean pipeline of seven layers. Each layer communicates only with its immediate neighbors. The top three implement sqlite3_prepare_v2() (compilation), while the bottom four implement sqlite3_step() (execution).
graph TD
SQL["SQL Text Input"]
TOK["Tokenizer
tokenize.c"]
PAR["Parser
parse.y + Lemon"]
CG["Code Generator
select.c, where.c, ..."]
VM["VDBE Bytecode VM
vdbe.c"]
BT["B-Tree Engine
btree.c"]
PG["Page Cache / Pager
pager.c, wal.c"]
VFS["OS Interface / VFS
os_unix.c, os_win.c"]
DISK["Disk Storage"]
SQL --> TOK
TOK --> PAR
PAR --> CG
CG --> VM
VM --> BT
BT --> PG
PG --> VFS
VFS --> DISK
style SQL fill:#F5F2EC,stroke:#B8B0A0,color:#1C1B18
style TOK fill:#3B6FC2,stroke:#2A5299,color:#FAFAF8
style PAR fill:#6B4C91,stroke:#553A75,color:#FAFAF8
style CG fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style VM fill:#C23B3B,stroke:#A02E2E,color:#FAFAF8
style BT fill:#3B8C5A,stroke:#2D6B44,color:#FAFAF8
style PG fill:#C9A84C,stroke:#A88A3A,color:#1C1B18
style VFS fill:#8A8070,stroke:#6B6358,color:#FAFAF8
style DISK fill:#E5DFD3,stroke:#B8B0A0,color:#1C1B18
sqlite3_prepare_v2() drives the top three layers (Tokenizer, Parser, Code Generator) to compile SQL text into a sqlite3_stmt containing VDBE bytecode. sqlite3_step() then executes that bytecode through the bottom four layers, one result row at a time.
The frontend transforms SQL text into VDBE bytecode through three stages: tokenization, parsing, and code generation. The code generator is by far the most complex component.
graph LR
subgraph Tokenizer
T1["Hand-coded Lexer"]
T2["Tokens"]
T1 --> T2
end
subgraph Parser
P1["Lemon LALR-1"]
P2["Parse Tree"]
P1 --> P2
end
subgraph CodeGen["Code Generator"]
CG1["Query Planner
where.c"]
CG2["Statement Handlers
select/insert/update/delete"]
CG3["Expression Engine
expr.c"]
CG4["sqlite3_stmt
Bytecode"]
CG1 --> CG4
CG2 --> CG4
CG3 --> CG4
end
T2 --> P1
P2 --> CG1
P2 --> CG2
P2 --> CG3
style T1 fill:#3B6FC2,stroke:#2A5299,color:#FAFAF8
style T2 fill:#A0BDE8,stroke:#3B6FC2,color:#1C1B18
style P1 fill:#6B4C91,stroke:#553A75,color:#FAFAF8
style P2 fill:#C4B0DC,stroke:#6B4C91,color:#1C1B18
style CG1 fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style CG2 fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style CG3 fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style CG4 fill:#E8C0A0,stroke:#D4793B,color:#1C1B18
Unlike most designs, the tokenizer calls the parser (not vice versa). This inversion makes it naturally thread-safe and faster.
Hipp's custom LALR(1) parser generator. Reentrant, thread-safe, includes non-terminal destructors to prevent memory leaks.
Evaluates millions of possible algorithm combinations in where.c to find the optimal execution plan for each query.
| File | Purpose |
|---|---|
select.c | SELECT statement code generation |
insert.c | INSERT statement code generation |
update.c | UPDATE statement code generation |
delete.c | DELETE statement code generation |
where.c | WHERE clause optimization and query planner |
expr.c | Expression evaluation code generation |
build.c | CREATE / DROP statements |
auth.c | Authorization callbacks via sqlite3_set_authorizer() |
The VDBE is a register-based bytecode virtual machine (changed from stack-based in SQLite 3.0). Each instruction has an opcode plus up to five operands (P1-P5). It can be traced with PRAGMA vdbe_trace=ON.
graph TD
subgraph Input
STMT["sqlite3_stmt
Bytecode Program"]
end
subgraph VDBE["VDBE Core"]
DECODE["Opcode Decoder"]
REG["Register File
strings, ints, floats, BLOBs"]
CURSOR["Cursor Manager
OpenRead / OpenWrite"]
FUNC["SQL Functions
func.c, date.c"]
end
subgraph Output
ROW["Result Row"]
TXN["Transaction Control
Commit / Rollback"]
end
STMT --> DECODE
DECODE --> REG
DECODE --> CURSOR
DECODE --> FUNC
REG --> ROW
CURSOR --> TXN
style STMT fill:#E8C0A0,stroke:#D4793B,color:#1C1B18
style DECODE fill:#C23B3B,stroke:#A02E2E,color:#FAFAF8
style REG fill:#C23B3B,stroke:#A02E2E,color:#FAFAF8
style CURSOR fill:#C23B3B,stroke:#A02E2E,color:#FAFAF8
style FUNC fill:#C23B3B,stroke:#A02E2E,color:#FAFAF8
style ROW fill:#A0D4B3,stroke:#3B8C5A,color:#1C1B18
style TXN fill:#E8D9A0,stroke:#C9A84C,color:#1C1B18
| Opcode | Category | Purpose |
|---|---|---|
OpenRead / OpenWrite | Cursor | Open a cursor on a B-tree for reading or writing |
Column | Data | Extract a column value from the current row |
Rewind / Next | Iteration | Position cursor at start; advance to next row |
MakeRecord | Construction | Build a database record from register values |
PutIntKey | Storage | Store a record in the B-tree keyed by integer |
Transaction / Commit | Control | Begin and commit a database transaction |
UPDATE and DELETE use a two-pass approach: the first pass identifies target rows (storing keys in a temporary list), the second pass modifies them. This is necessary because modifying a B-tree during traversal would invalidate cursor positions.
All data lives on disk in B-tree structures. SQLite uses two B-tree variants: table B-trees (keyed by 64-bit rowid, data in leaves) and index B-trees (arbitrary keys, no data). One B-tree per table, one per index, all in the same file.
graph TD
subgraph TableBTree["Table B-Tree (rowid keyed)"]
TI1["Interior Node
rowid + child pointers"]
TL1["Leaf: rowid=1
Row Data"]
TL2["Leaf: rowid=2
Row Data"]
TL3["Leaf: rowid=3
Row Data"]
TI1 --> TL1
TI1 --> TL2
TI1 --> TL3
end
subgraph IndexBTree["Index B-Tree (key only)"]
II1["Interior Node
keys + child pointers"]
IL1["Leaf: key=alice"]
IL2["Leaf: key=bob"]
IL3["Leaf: key=carol"]
II1 --> IL1
II1 --> IL2
II1 --> IL3
end
style TI1 fill:#3B8C5A,stroke:#2D6B44,color:#FAFAF8
style TL1 fill:#A0D4B3,stroke:#3B8C5A,color:#1C1B18
style TL2 fill:#A0D4B3,stroke:#3B8C5A,color:#1C1B18
style TL3 fill:#A0D4B3,stroke:#3B8C5A,color:#1C1B18
style II1 fill:#C9A84C,stroke:#A88A3A,color:#1C1B18
style IL1 fill:#E8D9A0,stroke:#C9A84C,color:#1C1B18
style IL2 fill:#E8D9A0,stroke:#C9A84C,color:#1C1B18
style IL3 fill:#E8D9A0,stroke:#C9A84C,color:#1C1B18
The schema itself is stored in sqlite_schema (page 1), a table B-tree containing the CREATE statements for all tables, indexes, views, and triggers. This bootstraps the entire database structure from a single root page.
The pager views the database as an array of uniform-sized pages (default 4096 bytes, configurable 512–65536). It handles reading, writing, and caching pages in memory, and manages atomic commit and rollback — this is where ACID guarantees are enforced.
graph LR
subgraph BTreeLayer["B-Tree Requests"]
REQ["Page Read/Write
Requests"]
end
subgraph PagerCore["Pager (pager.c)"]
CACHE["Page Cache
pcache.c"]
LOCK["Lock Manager"]
JOURNAL["Journal Writer"]
end
subgraph Storage["Disk Files"]
DB["Database File
.db"]
WAL["WAL File
-wal"]
JRNL["Journal File
-journal"]
end
REQ --> CACHE
CACHE --> DB
CACHE --> LOCK
JOURNAL --> WAL
JOURNAL --> JRNL
LOCK --> DB
style REQ fill:#3B8C5A,stroke:#2D6B44,color:#FAFAF8
style CACHE fill:#C9A84C,stroke:#A88A3A,color:#1C1B18
style LOCK fill:#C9A84C,stroke:#A88A3A,color:#1C1B18
style JOURNAL fill:#C9A84C,stroke:#A88A3A,color:#1C1B18
style DB fill:#E5DFD3,stroke:#B8B0A0,color:#1C1B18
style WAL fill:#E5DFD3,stroke:#B8B0A0,color:#1C1B18
style JRNL fill:#E5DFD3,stroke:#B8B0A0,color:#1C1B18
The VFS abstraction layer isolates all platform-specific I/O behind three objects. Porting SQLite to a new OS means writing a new VFS implementation. Custom VFS enables SQLite on embedded devices without filesystems, in-memory databases, and encrypted databases.
graph TD
subgraph VFSLayer["VFS Abstraction"]
VFSOBJ["sqlite3_vfs
open, delete, exists, randomness"]
FILEOBJ["sqlite3_file
open file handle"]
IOOBJ["sqlite3_io_methods
xRead, xWrite, xSync, xLock"]
VFSOBJ --> FILEOBJ
FILEOBJ --> IOOBJ
end
subgraph Implementations
UNIX["unix
POSIX advisory locks"]
WIN["win32
LockFile / LockFileEx"]
SHIM["VFS Shim
vfstrace, quota, multiplex"]
end
IOOBJ --> UNIX
IOOBJ --> WIN
IOOBJ --> SHIM
style VFSOBJ fill:#8A8070,stroke:#6B6358,color:#FAFAF8
style FILEOBJ fill:#8A8070,stroke:#6B6358,color:#FAFAF8
style IOOBJ fill:#8A8070,stroke:#6B6358,color:#FAFAF8
style UNIX fill:#A0BDE8,stroke:#3B6FC2,color:#1C1B18
style WIN fill:#A0BDE8,stroke:#3B6FC2,color:#1C1B18
style SHIM fill:#C4B0DC,stroke:#6B4C91,color:#1C1B18
VFS shims are thin wrappers that intercept calls and delegate to an underlying VFS. This allows layered functionality without modifying the core.
Logs all VFS calls for debugging and performance analysis.
Enforces file size limits. Used by Firefox to manage IndexedDB storage.
Splits large databases across multiple files to work around filesystem size limits.
Appends a database to an executable, enabling self-contained data-bundled applications.
The entire database is one binary file with a 100-byte header starting with the magic string "SQLite format 3\000". The file format has been stable since 2004 (version 3.0.0) and is guaranteed compatible through at least 2050.
| Offset | Size | Field |
|---|---|---|
| 0 | 16 | Magic string: "SQLite format 3\000" |
| 16 | 2 | Page size (bytes) |
| 18-19 | 2 | File format version (1=rollback, 2=WAL) |
| 24 | 4 | File change counter |
| 28 | 4 | Database size in pages |
| 40 | 4 | Schema cookie (incremented on schema change) |
| 56 | 4 | Text encoding (1=UTF-8, 2=UTF-16LE, 3=UTF-16BE) |
| 96 | 4 | SQLite version that created the DB |
graph LR
subgraph PageTypes["Database File Pages"]
P1["Page 1
Header + sqlite_schema"]
TBL["0x0D Table Leaf
rowid + data"]
TBI["0x05 Table Interior
rowid + children"]
IDL["0x0A Index Leaf
keys only"]
IDI["0x02 Index Interior
keys + children"]
OVF["Overflow Pages
large cell data"]
FREE["Freelist Pages
unused tracking"]
end
style P1 fill:#C23B3B,stroke:#A02E2E,color:#FAFAF8
style TBL fill:#3B8C5A,stroke:#2D6B44,color:#FAFAF8
style TBI fill:#A0D4B3,stroke:#3B8C5A,color:#1C1B18
style IDL fill:#C9A84C,stroke:#A88A3A,color:#1C1B18
style IDI fill:#E8D9A0,stroke:#C9A84C,color:#1C1B18
style OVF fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style FREE fill:#E5DFD3,stroke:#B8B0A0,color:#1C1B18
Records use variable-length integers (varints, 1-9 bytes) and serial type codes: 0=NULL, 1-6=integers (1-8 bytes), 7=IEEE 754 float, 8-9=constants 0/1. For BLOBs: (N-12)/2 bytes where N≥12 and even. For text: (N-13)/2 bytes where N≥13 and odd.
SQLite uses a five-state file locking protocol for multi-process concurrency. Lock conflicts return SQLITE_BUSY immediately with no blocking waits.
graph LR
UL["UNLOCKED
Default state"]
SH["SHARED
Multiple readers"]
RS["RESERVED
One writer planning"]
PN["PENDING
Writer waiting"]
EX["EXCLUSIVE
Writer active"]
UL --> SH
SH --> RS
RS --> PN
PN --> EX
style UL fill:#E5DFD3,stroke:#B8B0A0,color:#1C1B18
style SH fill:#3B8C5A,stroke:#2D6B44,color:#FAFAF8
style RS fill:#C9A84C,stroke:#A88A3A,color:#1C1B18
style PN fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style EX fill:#C23B3B,stroke:#A02E2E,color:#FAFAF8
| State | Who Holds It | What It Means |
|---|---|---|
| UNLOCKED | Nobody | Default state; no reads or writes |
| SHARED | Multiple readers | Can read; prevents writers from committing |
| RESERVED | One writer (planning) | Intends to write; other SHARED locks still allowed |
| PENDING | One writer (waiting) | No new shared locks allowed; existing readers finish |
| EXCLUSIVE | One writer (active) | Actually writing to disk; no other locks of any kind |
The PENDING state prevents writer starvation. Without it, a continuous stream of readers acquiring SHARED locks could starve a waiting writer indefinitely. PENDING blocks new readers while letting existing ones finish.
SQLite offers two journal modes for crash recovery and atomic commit. The rollback journal (default) copies original pages before modifying them, while WAL mode appends changes to a separate log file.
graph TD
subgraph Rollback["Rollback Journal (default)"]
RW1["Original page copied
to journal file"]
RW2["Changes written
to database file"]
RW3["Commit = delete
journal file"]
RW1 --> RW2 --> RW3
end
subgraph WALMode["Write-Ahead Log (WAL)"]
WW1["Original pages
preserved in DB"]
WW2["Changes appended
to WAL file"]
WW3["Commit = append
commit record"]
WW1 --> WW2 --> WW3
end
style RW1 fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style RW2 fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style RW3 fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style WW1 fill:#3B6FC2,stroke:#2A5299,color:#FAFAF8
style WW2 fill:#3B6FC2,stroke:#2A5299,color:#FAFAF8
style WW3 fill:#3B6FC2,stroke:#2A5299,color:#FAFAF8
Readers never block writers, and writers never block readers. Each reader gets a consistent snapshot via "end marks."
Data is written only once (to WAL), not twice as in rollback mode. All writes are sequential appends.
Sequential WAL appends require fewer costly disk synchronization calls compared to random-access journal writes.
| File | Purpose | Details |
|---|---|---|
-wal | WAL file | Sequential log of modified pages in "frames" |
-shm | WAL-index | Memory-mapped shared-memory file for fast lookups; rarely exceeds 32 KB |
All processes must be on the same host (shared memory requirement). WAL does not work over network filesystems. Mostly-read workloads are ~1-2% slower than rollback journal. Large transactions (>100 MB) can be slower.
SQLite supports run-time loadable extensions via shared libraries. Extensions are disabled by default for security and must be explicitly enabled. All notable extensions are compiled into the amalgamation but gated behind -DSQLITE_ENABLE_* flags.
graph TD
subgraph Loading["Extension Loading"]
API["sqlite3_load_extension()"]
SQL["SELECT load_extension()"]
AUTO["sqlite3_auto_extension()"]
end
subgraph Platform["Platform Abstraction"]
DL["Unix: dlopen / dlsym"]
WIN["Windows: LoadLibrary"]
VFS2["VFS: xDlOpen / xDlSym"]
end
subgraph Extensions["Built-in Extensions"]
FTS["FTS5
Full-Text Search"]
RTREE["R-Tree
Spatial Index"]
JSON["JSON1
JSON Functions"]
RBU["RBU
Bulk Update"]
end
API --> VFS2
SQL --> VFS2
AUTO --> VFS2
VFS2 --> DL
VFS2 --> WIN
style API fill:#3B6FC2,stroke:#2A5299,color:#FAFAF8
style SQL fill:#3B6FC2,stroke:#2A5299,color:#FAFAF8
style AUTO fill:#3B6FC2,stroke:#2A5299,color:#FAFAF8
style DL fill:#8A8070,stroke:#6B6358,color:#FAFAF8
style WIN fill:#8A8070,stroke:#6B6358,color:#FAFAF8
style VFS2 fill:#8A8070,stroke:#6B6358,color:#FAFAF8
style FTS fill:#6B4C91,stroke:#553A75,color:#FAFAF8
style RTREE fill:#3B8C5A,stroke:#2D6B44,color:#FAFAF8
style JSON fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style RBU fill:#C9A84C,stroke:#A88A3A,color:#1C1B18
Segmented inverted index architecture with pluggable tokenizers (unicode61, ascii, porter, trigram). Built-in BM25 ranking. Supports phrase matching, proximity queries, and boolean operators.
R*-Tree spatial indexing for 1-5 dimensional range queries. Used in geospatial, CAD, and time-interval applications. Stores coordinates as 32-bit floats.
SQL functions for JSON data: json_extract(), json_insert(), json_set(), json_remove(). Table-valued functions json_each() and json_tree() for iteration. Internal JSONB binary format.
SESSION tracks changesets between database snapshots. RBU (Resumable Bulk Update) enables efficient offline bulk modifications that can be paused and resumed.
SQLite has one of the most rigorous testing regimes of any software project: 155.8K lines of source code vs ~92 million lines of test code — a 590:1 test-to-code ratio. The project achieves 100% MC/DC branch coverage, adopted from aviation industry standard DO-178B.
graph TD
subgraph Harnesses["Test Harnesses"]
TCL["TCL Tests
51,445 cases
Public domain"]
TH3["TH3
~2.4M parameterized
Proprietary"]
SQLT["SQL Logic Test
7.2M queries
Cross-DB"]
FUZZ["dbsqlfuzz
~500M cases/day
Structure-aware"]
end
subgraph Techniques["Anomaly Testing"]
OOM["OOM Testing
malloc failure injection"]
IOE["I/O Error Testing
custom VFS failures"]
CRASH["Crash Testing
power loss simulation"]
MUT["Mutation Testing
assembly branch flipping"]
end
style TCL fill:#3B6FC2,stroke:#2A5299,color:#FAFAF8
style TH3 fill:#6B4C91,stroke:#553A75,color:#FAFAF8
style SQLT fill:#3B8C5A,stroke:#2D6B44,color:#FAFAF8
style FUZZ fill:#C23B3B,stroke:#A02E2E,color:#FAFAF8
style OOM fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style IOE fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style CRASH fill:#D4793B,stroke:#B8632C,color:#FAFAF8
style MUT fill:#D4793B,stroke:#B8632C,color:#FAFAF8
| Harness | Type | Cases | Purpose |
|---|---|---|---|
| TCL Tests | Public domain | 51,445 | Primary development tests (1,390 files, 23.2 MB) |
| TH3 | Proprietary C | ~2.4M | 100% MC/DC branch coverage; embedded platform testing |
| SQL Logic Test | Cross-DB | 7.2M queries | Compares results against PostgreSQL, MySQL, SQL Server, Oracle |
| dbsqlfuzz | Proprietary fuzzer | ~500M/day | Mutates SQL and database files simultaneously (16 cores) |
"Once we got to 100% MC/DC coverage, we stopped getting bug reports from Android." — Richard Hipp. The release process includes a ~200-item manual checklist and minimum 3 days of testing across multiple platforms (big/little endian, 32/64-bit, ARM/Intel).