Yesterday I found this and ported it to Python: https://github.com/tekknolagi/db.py

It's ~150LOC but doesn't have aggregates (yet)

Did you use the same data? I couldn't find it in the original.

What do you mean same data?

I mean the data / tables used. The tables used in the original.

I made up some stuff for tests and copied some tables from their demos

It’s like a ray tracer: every programmer should write a toy sql engine!

A very long time ago I started writing a game in a weekend a for Ludum dare competition and ended up writing a toy sql engine of my own https://williame.github.io/post/59997353762.html

I still get the urge to make a proper one one day. With the sql features that I wish sql engines had. Hmmm. Need-sniping myself now… :)

Every programmer should also write a CPU and computer out of NAND gates: https://www.nand2tetris.org

I got you! https://github.com/chunky/sqlraytracer

Great toy but it's a bit misleading not to touch on the query planner. All real SQL implementations will plan how to most efficiently run a query so the order of operations can be wildly different to expected based on the data and indexes available. This kind of tutorial makes it look like joins are very inefficient whereas a real database may make them highly efficient for a given query.

I had fun over the last day implementing this two ways in Elixir: https://github.com/halostatue/sqltoy_elixir.

I hoped this had a toy implementation of sql indices, but it looks like that's not the case: it just sorts the whole table on the fly on an ORDER BY.

I mean that basically in an implementation of indexes. Move the on-the-fly sorts to behind a command, then change the insert/delete code to maintain them, and voila - indexes.

Aren't indexes a static, pre-calculated way to avoid in-memory sorting on the fly?

I love projects like this that shed light on and demystify and otherwise mysterious process. Thanks for sharing!

Great way to learn!

Would be great if it was literally any other language other than js

It would be very easy to follow along with a transliteration into any dynamic language language with halfway decent support for functional programming.

Why is that? There doesn't seem to be anything particularly esoteric in the implementation.

Do it yourself: https://github.com/tekknolagi/db.py