SQLite internals

SQLite keeps a whole database in one file of fixed-size pages, and the format is documented to the byte. This tool runs real SQLite — compiled to WebAssembly by sql.js — and, after every statement, reads the database file back and decodes it: which page is the root of which table, how a leaf page packs its cells from the end backwards, how a record encodes its columns, where a big blob continues on overflow pages, which pages a DELETE put on the freelist. It also shows the program SQLite compiled your statement into (EXPLAIN) and the plan it chose (EXPLAIN QUERY PLAN).

For the general idea of B+trees against LSM trees, animated, see storage engines. For a server database with concurrent sessions, see the Postgres playground; for the column-store side of the story, the DuckDB analytics lab.

How to use it

Console. Type SQL and press Ctrl/⌘+Enter to run everything (or the selection), Ctrl/⌘+Shift+Enter for the statement at the cursor. Bytecode and Query plan explain the statement at the cursor. Results show as a grid; EXPLAIN output is drawn annotated and EXPLAIN QUERY PLAN as a tree. Under the results a line says which pages the statement wrote and how the file and the freelist changed; the panel beside the editor is a small map of the file with those pages outlined.

The database starts as a small sample (authors and 500 books, with an index on title) using 1 KiB pages, so that a few hundred rows already make a multi-level b-tree. New empty starts again with the page size you choose; Open a .db file… loads one of your own SQLite files from disk (read in the browser, not uploaded) so you can look inside it; Download .db saves the current file.

Pages. The file, decoded:

the key facts and the 100-byte header field by field (offset, size, value, meaning), with the fields the last statement changed highlighted;
a map of every page, coloured by type — table interior and leaf, index interior and leaf, overflow, freelist trunk and leaf, pointer map — with a strip showing how full each page is and the pages the last statement wrote outlined;
the anatomy of the page you pick: its header (type, first freeblock, cell count, start of the cell content area, fragmented bytes, right-most child), the cell pointer array, a strip of the page’s bytes by region (header, pointers, unallocated space, cells, freeblocks), every cell (left child, rowid, payload size, how much is stored locally, the first overflow page) and its record decoded column by column with each value’s serial type, and a coloured hex dump;
overflow pages with their next-page pointer, freelist trunks with the leaf pages they list, and pointer-map entries;
every table and index with its root page, depth and page counts.

B-tree. A diagram of the b-tree of any table or index: each page with its number, kind, cell count, first and last key and how full it is. Click a page to open it. Wide levels are cut to twelve children with a "… n more" marker.

Bytecode. Explain a statement without running it: the query plan as a tree (with SCAN, SEARCH, COVERING INDEX, TEMP B-TREE explained) and the VDBE program with a description of each instruction filled in with its operands, loops indented, jump targets shown (hover to highlight the target), and cursors mapped back to the table or index whose root page they open.

Labs. Eleven guided labs, each starting from an empty database (running a step replays the ones before it, so you can start anywhere), with the relevant view drawn under each step:

Rowid tables — the table b-tree, records, an INTEGER PRIMARY KEY stored as NULL, rowid reuse;
Rowid vs WITHOUT ROWID — the hidden autoindex of a TEXT key against a clustered table;
From SCAN to SEARCH — what an index changes in the plan and in the bytecode;
Covering indexes — no table lookup, count(*) on the smallest index, partial and expression indexes;
Page splits and fill — the root splitting, appending against random keys, VACUUM;
Overflow pages — the local part of a big record and its chain;
Freelist, DELETE and VACUUM — why the file does not shrink, reuse, incremental auto-vacuum and pointer-map pages;
Journal modes — rollback journal against WAL, and what the header records;
Type affinity quirks — declared types as hints, "FLOATING POINT" being INTEGER, mixed-type sorting, variable-size integers;
STRICT tables — type errors and the TypeCheck opcode;
Reading VDBE bytecode — Init and Transaction, scans, Count, sorters, writes and aggregates.

History keeps the statements you ran in this browser. Share link copies a link with the console text and the lab you are on.

How the page view works

sql.js keeps the database in a file in the WebAssembly in-memory file system. After each batch of statements the tool asks for the file’s bytes (db.export()) and decodes them with its own reader of the file format — the same reader that is tested against files written by SQLite itself, page for page against SQLite’s dbstat table. It compares the bytes with the previous copy to find the pages that changed.

db.export() closes and reopens the database, so TEMP tables and per-connection settings such as PRAGMA foreign_keys do not survive a statement. Inside an explicit transaction the page view waits for COMMIT or ROLLBACK.
In WAL mode, closing the database checkpoints the WAL, so the file you see has the changes folded in; the WAL file itself is not shown. Locking and crash recovery between connections cannot be shown in one page; the journal lab explains them.
sql.js is built without dbstat, the bytecode() table function, FTS5 and the explanatory comments in EXPLAIN output; the descriptions here come from the tool, after the opcode documentation.

The engine

sql.js 1.14.2 (MIT licence) is SQLite 3.49 compiled to WebAssembly, loaded from jsDelivr at a pinned version: about 0.35 MB compressed. It runs on the page itself (it is synchronous), which is fine for databases up to a few tens of megabytes.

Privacy

Everything runs in this browser. The network is only used to fetch sql.js from cdn.jsdelivr.net. A database file you open is read from your disk by the page and never uploaded; the database lives in memory and is gone when you leave the page unless you download it. The console text, the lab you are on and the history are kept in this browser’s local storage.