DATABASES

Quire

A page-oriented SQL database in Go, from the pager up to the query planner.

Go, B+trees, SQL

kvdb exec

> CREATE TABLE books (id INT64, title STRING, author STRING, PRIMARY KEY (id));
table books created (0.24 ms)
> CREATE INDEX by_author ON books (author);
index on books (author) created (0.05 ms)
> INSERT INTO books VALUES (1, 'Red Rising', 'Pierce Brown'), (2, 'Frankenstein', 'Mary Shelley'), (3, 'Golden Son', 'Pierce Brown');
3 row(s) written (0.02 ms)
> SELECT title FROM books WHERE author = 'Pierce Brown';
| Red Rising |
| Golden Son |
2 row(s) via index scan on (author) (0.01 ms)

A real run on a fresh build. The planner picked the author index without being told to.

Problem

A database has to keep data safe if the process dies mid-write, and it has to find rows without reading the whole file. Quire does both with a B+tree stored on fixed-size pages.

What I built

Quire stores INT64 and STRING rows, a primary key and secondary indexes in one file of 4096-byte pages. It accepts CREATE TABLE, CREATE INDEX, INSERT, UPSERT, SELECT with WHERE, LIKE, ORDER BY, LIMIT and aggregates, plus UPDATE, DELETE, EXPLAIN and BEGIN / COMMIT / ROLLBACK.

It runs as a shell, a one-shot exec command, or an HTTP server with a web console that shows tables, indexes and the page map.

How

SQL textShell, exec or HTTP
Lexer and parserStatements into a syntax tree
PlannerPrimary-key lookup, index scan or table scan
ExecutorFilters, sorts and aggregates
Tables and indexesOrder-preserving keys
B+treeSplits on insert, merges on delete
PagerCopy-on-write 4 KB pages, freelist, fsync on commit

Keys are encoded so that byte order matches value order: a 4-byte prefix, sign-flipped big-endian INT64, and escaped strings. That lets one B+tree serve range scans for every table and index.

Writes never overwrite live pages. A commit appends new pages, calls fsync, then rewrites page 0, the master page that points at the current root. Readers see the last committed snapshot.

LIKE 'prefix%' becomes index bounds. When ORDER BY doesn't match the scan order, the executor sorts in memory.

Results

The repo has 30 Go tests across the B+tree, pager, SQL and HTTP layers.
Each shell entry or exec script commits as one transaction, and a failed statement rolls the batch back.
Not done yet: no JOIN, subqueries, GROUP BY or OR, no NULL, and one writer at a time.

NEXT: Space Invaders →