Lessons and the learning game

Build a database from scratch

Write SolDB, the history store of a Mars weather station, in plain Python on a Raspberry Pi, and run it on a live sensor.

The course

Build a database from scratch is an advanced Coding course in seven lessons. The first six run on a Raspberry Pi 4 and the boss on a Raspberry Pi 5 with an MCP3008, whose knob is the station's thermometer. Each step is checked against what the Pi prints.

  1. Records on disk: readings encoded as text lines and decoded back, fixed-size records you can reach with one seek, binary records with struct, and what this Pi does with files between Runs.
  2. Append-only log: a key-value store whose only write is a line on the end. Updates are new records, deletes are tombstones, every read scans the file, and the log keeps every value a key ever had.
  3. An index: a dict from each key to the byte offset of its newest record (the Bitcask design), rebuilt from the log at startup, and the dead records updates leave behind.
  4. Queries: a full scan with a filter, range queries with sorted keys and bisect, why zero padding makes text sort like numbers, aggregates, and a tiny query language over the Serial Monitor.
  5. Compaction: copy the live records to a new file and swap it in with os.replace, prove a crash halfway loses nothing, and compact automatically when the log doubles.
  6. Crash safety: a torn last record that breaks the store, recovery that trusts only records ending in a newline, CRC-32 checksums that catch a flipped bit, commit markers that make a batch all or nothing, and what fsync adds on a real disk.
  7. Boss: a sensor history store: SolDB on live data.

The finale

The boss logs eight readings from the knob, half a second apart, into SolDB with checksums. Turn the knob down while it logs (a cold front over Jezero crater), then talk to the store over the Serial Monitor:

store> stats
8 readings: min -76.5, max -39.9, mean -53.6
store> crash
power cut mid-write! rebooting...
recovery: dropped 9 bytes
8 readings still here
store> keep 5
kept 5 readings, 105 bytes

crash tears a record mid-write and reopens the store, and keep is a retention policy built on compaction. The last step adds a feature of your own: a range command, a second channel, batches or binary records.

What the Pi's engine does differently

The Pi runs MicroPython 1.29, not CPython, and its files live in the browser. For this course that shows in a few places:

  • A file the program writes lasts only while it runs: every Run starts with the project's tabs and nothing else. So a crash and a reboot are acted out inside one Run, by writing part of a record and reopening.
  • There is no sqlite3, which is the point: you build the engine.
  • Files have no truncate() and os has no fsync. Recovery writes the good part to a new file and renames it over the old one.
  • A dict does not keep the order keys went in, so every query that needs an order sorts.
  • Floats print with every digit: float("-65.4") shows as -65.40000000000001, so the store formats temperatures with :.1f.

Everything else (os.replace, binascii.crc32, struct, bisect, binary seek) works as it does on a real Pi, so SolDB runs there unchanged and keeps its file on the SD card.