Writing an embedded SQL database in C++20

When you use PostgreSQL or SQLite from web code, CREATE INDEX feels like flipping a config switch. Writing SQLite Clone (dbx) in roughly 10,000 lines of dependency-free C++20 gave me a much clearer respect for what happens between a query string and an SSD block.

Inside dbx, there is no parser generator and no external storage library. Every query walks through code you can step through in a debugger:

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, age INTEGER);
CREATE INDEX users_email ON users(email);
EXPLAIN SELECT id, email FROM users WHERE email = 'alice@example.com';

Where I spent most of the debugging time

Packing variable-length TEXT rows into fixed-size slotted disk pages took far more care than writing the SQL lexer and parser. Free space fragments quickly when rows are inserted out of order, and the pager has to keep dirty-page flushes consistent while letting :memory: databases run through the exact same interface.

B-Tree splits were the second humbling part. Splitting a full leaf page pushes a separator key into its parent, which can split an interior node all the way up to a new root page. Once you add secondary indexes like users_email, every INSERT has to keep both the table B-Tree and the index B-Tree in step inside a transaction.

The best decision I made early on was adding .pages, .btree <table>, and .validate commands to the interactive REPL. Whenever a stress test broke key ordering across a split, printing the raw page tree made the bug obvious right away. That work also shaped how I write SQL schemas and read-only database queries in tools like Automatic Report Generator (more on learning by building).


Linked mentions

You might also enjoy