DEV Community

Rob Cox
Rob Cox

Posted on AI-assisted

I built a database from scratch to show my kids how databases work

#python #learning #beginners #database

Databases feel like magic. You type SELECT * FROM cards WHERE name = 'Pikachu' and an answer comes back, instantly, from millions of rows. I've been coding for 30+ years and I still couldn't have explained to my kids what actually happens in between.

So I built one. PyDB is a small relational database written from scratch in Python, with every idea explained in plain English. It's not meant to replace Postgres. It's meant to be read.

You can try it in one line:

pip install fromscratch-pydb
pydb
Enter fullscreen mode Exit fullscreen mode

Start with a table

A table is just a grid: columns across the top, one row per thing. Let's make some trading cards.

pydb> CREATE TABLE cards (name TEXT NOT NULL, type TEXT, power INTEGER)
pydb> INSERT INTO cards VALUES ('Pikachu', 'Electric', 55)
pydb> INSERT INTO cards VALUES ('Raichu', 'Electric', 90)
pydb> INSERT INTO cards VALUES ('Charmander', 'Fire', 52)
Enter fullscreen mode Exit fullscreen mode

Behind that one line of SQL there's a whole pipeline. A tokenizer chops the text into words (SELECT, cards, 'Pikachu'), a parser turns those words into a structure the program understands, and an executor goes and fetches the rows. Each of those is its own small, readable Python file.

Grouping works too:

pydb> SELECT type, COUNT(*), AVG(power) FROM cards GROUP BY type
┌──────────┬──────────┬────────────┐
│ type     │ COUNT(*) │ AVG(power) │
├──────────┼──────────┼────────────┤
│ Electric │        2 │       72.5 │
│ Fire     │        1 │       52.0 │
└──────────┴──────────┴────────────┘
Enter fullscreen mode Exit fullscreen mode

The back of the textbook

Here's the question that got my kids interested: if a table has a million rows, how does the database find Pikachu without reading all of them?

Ask the database how it plans to find him:

pydb> EXPLAIN SELECT * FROM cards WHERE name = 'Pikachu'
Full table scan on cards
Enter fullscreen mode Exit fullscreen mode

A full table scan means checking every row, one by one. Fine for three cards, painful for a million.

Now think about a school textbook. You don't read the whole book to find "Fossils". You flip to the index at the back, see "page 55", and jump straight there. A database index is exactly that:

pydb> CREATE INDEX idx_name ON cards (name)
pydb> EXPLAIN SELECT * FROM cards WHERE name = 'Pikachu'
Index lookup on idx_name (name = ...)
Enter fullscreen mode Exit fullscreen mode

Same question, completely different plan. The index in PyDB is a B-tree, which I explain as a magical filing cabinet: each drawer holds a few cards in alphabetical order, and when a drawer gets too full it splits in two and puts a label on the cabinet saying which drawer to open. Because it always stays balanced, you reach any card in a handful of steps, even with millions of them.

No take-backs (with a safety net)

The idea my kids liked most was transactions. Imagine trading cards with a friend: "I'll give you my Pikachu if you give me your Charmander." Both halves have to happen, or neither does. You don't want to hand over Pikachu and have your friend run off.

A transaction groups several changes and promises all of them happen, or none do. If something goes wrong halfway, the database rolls back as if nothing happened. In PyDB, every change in a transaction writes a little note saying how to undo itself, so rolling back means reading the notes backwards.

Turn on the journal (Database(path, journal=True)) and it goes one step further: every commit is written to disk before it counts. Pull the plug mid-write, reopen the database, and it replays the journal. Your committed work is still there, and half-finished work leaves no trace.

What's in there

Everything below is real, working code you can step through:

  • SQL: SELECT, INSERT, UPDATE, DELETE, CREATE/DROP TABLE
  • JOINs, GROUP BY / HAVING, subqueries and views
  • B-tree indexes and a query planner (EXPLAIN)
  • PRIMARY KEY, NOT NULL and UNIQUE constraints
  • NULL with proper three-valued logic (TRUE / FALSE / UNKNOWN)
  • Transactions with commit and rollback
  • A crash-safe journal with checkpoints

It's also tested the way I'd test production code: test-first, strictly typed, and with property-based tests (using Hypothesis) that throw hundreds of random inputs at it looking for a rule it breaks. The docs include a page walking through one of those properties and the bug it catches.

What building it taught me

The biggest surprise was how much of a "real" database is ordinary code. There's no single clever trick. It's a stack of small, understandable ideas, each solving one problem, and once you've built them yourself the magic turns into engineering. That's exactly what I wanted my kids to see.

Try it, read it, break it

PyDB is one of 18 projects in a series called From scratch in Python. There's also an operating system, a programming language, git, a search engine, a tiny GPT and more, all tied together by PyStack. If you want the lot: pip install fromscratch-pystack. The series home is https://robcox2910.github.io/

I'd love to hear what's confusing, what's missing, or which system you'd want built next.

Top comments (0)