san_db — a tiny database, built block by block

01 Playground

Write a statement, end it with ;, and press Run (or Ctrl/⌘ + Enter). Nothing leaves your browser: the database lives in memory and resets when you reload.

query.sql Loading engine…

02 Docs

Here's what the engine does and how to talk to it.

Create a table

Columns are int64 or bytes. Every table needs a primary key, which can span several columns. Each index (…) adds a secondary index.

create table emp (
  id int64,
  name bytes,
  dept bytes,
  salary int64,
  index (dept, salary),
  primary key (id),
);

Write rows

insert adds new keys only. replace updates rows that already exist. upsert does either.

insert into emp (id, name, dept, salary)
  values (1, 'alice', 'eng', 120),
         (2, 'bob', 'ops', 95);
upsert into emp (id, name, dept, salary)
  values (2, 'bob', 'eng', 99);
update emp set salary = salary + 5
  index by dept = 'eng';
delete from emp index by id = 2;

Query rows

Conditions come in two kinds. INDEX BY picks an index and sets the scan range and its direction. FILTER checks each row without using an index. LIMIT x, y skips x rows and keeps y.

select name, salary * 12 as yearly
  from emp
  index by (dept, salary) >= ('eng', 100)
  filter not name = 'bob'
  limit 10;

INDEX BY rules

a = v is a point query, or a prefix match on a longer index.
a > v or a >= v scans upward and is open-ended.
a < v or a <= v scans downward.
a > v1 and a < v2 is a closed range.
The columns must match the start of the primary key or of an index, in order.

Expressions

Operators, from tightest to loosest binding:

-a
a * b   a / b   a % b
a + b   a - b         ('+' joins bytes)
=  !=  <>  <  <=  >  >=
not a
a and b
a or b
(a, b, …)             tuple

Run it locally

The CLI stores everything in one file on disk and survives restarts.

git clone https://github.com/lendrik-kumar/beas_db
cd beas_db
go run ./cmd/cli mydb.db

Rebuild this site's engine with ./scripts/build-web.sh.

Under the hood

Query languageA recursive-descent parser turns statements into calls on the layers below.
Tables & indexesRows are encoded so that comparing the bytes gives the right sort order. Secondary indexes are kept up to date on every write.
TransactionsRead-only snapshots run alongside a single writer. A commit is atomic: fsync the pages, then switch the master page.
Free listEach freed page is tagged with a version number. A page is reused only once no reader can still see it.
B+tree on pages4 KB copy-on-write nodes, mmapped from a single file.