Stupid Database

This is an exercise in building a very minimal (and very stupid) in-memory SQL-like database for educational purposes.

None of this code is to be considered usable in a real project. There are no safeguards, security, checks, or anything that resembles production-ready code. And it's written this way to be easily understandable for a very beginner programmer.

The goal is to demystify the SQL syntax like:

SELECT name FROM users WHERE age > 30

To an equivalent javascript call like:

select('name', from('users', { where: 'users.age > 30' }))

And the idea is to have a "mental model" of what SQL means in a more general programming language

You can run the example with:

node test

REPL

You can open an interactive REPL to run SQL command directly by running:

./repl.js

It's a node REPL and you can send SQL commands like this:

stupid sql > select name from users where users.city = 'Gotham'

REPL with Transaction Log

This is another experimental version of a persistency layer based on a simple append-only transaction log.

This will decorate the insert, delete and update functions with special transaction-enabled versions.

Transactions will be saved in the 'transact.log' file.

Run: ./replTransact.mjs

This will replay existing transactions and save new operations to the transaction log.

Client/Server

There is also a demonstration of the most minimal bare-bones "SQL-like" database. You can start this server at port 4000:

./stupid-server.mjs

And connect to it with this client:

./stupid-client.mjs

It will open an interactive prompt so you can send the SQL commands

AVL Tree based Index

Just to show how an index is built and used (in a normal SQL server, it's obviously a B+Tree, but as this is a stupid in-memory hash, a simple AVL Tree should suffice).

We can compare the times for the equivalente of a 'table scan' filtering every single element in the table or using the index.

./bench_index.mjs

Spoilers: on my i9-9900K, with NVME, running inside WSL2, this is the result:

` ❯ ./bench_index.mjs from('users', { where: 'users.city === "Wakanda"'}).length took 145 ms normal select: 524288

getFromIds(users,getFromIndex('users', 'city', 'Wakanda')).length took 20 ms indexed select: 524288 `

The AVL indexed version can go from anywhere around 5 to 10 times faster, obviously.

ANTLR4

Just out of fun, I did a very small and very naive implementation of a SQL parser using Antlr4.

You can run very basic SQL against the stupid database. Just add the commands in the test.sql file and run doing:

./stupid.mjs

The parser is a stripped down version copied from the SQLite3 parser.

Again, this is for educational purposes only.

---

Copyright (C) Fabio Akita, 2022 This project should not be used out of context, without the permission of the author