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
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)
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 │
└──────────┴──────────┴────────────┘
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
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 = ...)
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 NULLandUNIQUEconstraints -
NULLwith 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
- Install:
pip install fromscratch-pydb, then runpydb - Docs, with every concept explained simply: https://robcox2910.github.io/pydb/
- Code: https://github.com/robcox2910/pydb
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.
How this was made: I designed PyDB and directed every part of it, but most of the code was written with Claude Code, under strict test-driven development, and this article was drafted with Claude’s help too. Every output shown above comes from actually running it.
Top comments (2)
The textbook index comparison is the part that makes this click. One concrete tip for a beginner trying this: run EXPLAIN on the same query before you create the index, then again right after. Seeing the plan change from a full table scan to an index lookup makes the idea stick in a way that reading about it does not. It also helps to try the same test on a column with no index, so you can see when the database still has to check every row.
Thanks, that’s exactly the moment that made it click for my kids too: same question, a completely different plan. Great idea about the unindexed column. With only name indexed, PyDB still scans everything for the type:
The index only helps the questions it was built for, just like the back of a textbook only lists the words someone chose to put there. Thanks for reading!