Building a database — nyx-db

What is nyx-db?

nyx-db is an embedded SQL database engine written entirely in Nyx. It accepts SQL commands over the RESP protocol (just like nyx-kv), so you can use redis-cli as the client. Think of it as a mini SQLite accessible over the network.

Architecture

SQL string ──► Lexer ──► Parser ──► AST ──► Executor ──► Storage
                                                            │
                                                         ┌──┴──┐
                                                         │.db  │
                                                         │file │
                                                         └─────┘

The pipeline has four stages:

1. Lexer — tokenizes the SQL string into keywords, identifiers, literals, and operators.
2. Parser — recursive descent parser that builds an AST (abstract syntax tree).
3. Executor — walks the AST and performs the requested operation.
4. Storage — rows are stored in-memory in Maps, with background persistence to a .db file.

Connecting

nyx-db listens on port 6382 by default:

$ redis-cli -p 6382
127.0.0.1:6382> PING
PONG

Creating tables

127.0.0.1:6382> SQL CREATE TABLE users (id INT PRIMARY KEY, name TEXT, age INT)
OK

Supported types: INT, TEXT, REAL. The PRIMARY KEY constraint ensures uniqueness.

Inserting data

127.0.0.1:6382> SQL INSERT INTO users VALUES (1, 'Alice', 30)
OK
127.0.0.1:6382> SQL INSERT INTO users VALUES (2, 'Bob', 25)
OK
127.0.0.1:6382> SQL INSERT INTO users VALUES (3, 'Charlie', 35)
OK

Querying data

127.0.0.1:6382> SQL SELECT * FROM users
1) "id=1, name=Alice, age=30"
2) "id=2, name=Bob, age=25"
3) "id=3, name=Charlie, age=35"

Select specific columns with a WHERE clause:

127.0.0.1:6382> SQL SELECT name, age FROM users WHERE age > 28
1) "name=Alice, age=30"
2) "name=Charlie, age=35"

Ordering and limiting results:

127.0.0.1:6382> SQL SELECT * FROM users ORDER BY age DESC LIMIT 2
1) "id=3, name=Charlie, age=35"
2) "id=1, name=Alice, age=30"

Aggregate functions:

127.0.0.1:6382> SQL SELECT COUNT(*) FROM users
(integer) 3

Updating and deleting

127.0.0.1:6382> SQL UPDATE users SET age = 31 WHERE name = 'Alice'
OK (1 row updated)

127.0.0.1:6382> SQL DELETE FROM users WHERE age < 30
OK (1 row deleted)

Indexes

Create an index to speed up queries on frequently-searched columns:

127.0.0.1:6382> SQL CREATE INDEX idx_age ON users (age)
OK

Transactions

Group multiple operations into an atomic transaction:

127.0.0.1:6382> SQL BEGIN
OK
127.0.0.1:6382> SQL INSERT INTO users VALUES (4, 'Diana', 28)
OK
127.0.0.1:6382> SQL INSERT INTO users VALUES (5, 'Eve', 22)
OK
127.0.0.1:6382> SQL COMMIT
OK

Use ROLLBACK to undo all changes since BEGIN.

Meta commands

Persistence

nyx-db persists data to a .db file. A background saver thread writes snapshots periodically, and a shutdown handler ensures a final save on SIGTERM. On restart, all tables and rows are restored from disk.

$ ./nyx-db --data mydata.db --port 6383

The SQL parser

The parser is a classic recursive descent parser. It reads tokens one at a time and builds AST nodes. For example, parsing SELECT * FROM users WHERE age > 25 produces:

["SELECT",                    ← node type
  ["*"],                      ← columns
  "users",                    ← table name
  [">", "age", 25],           ← WHERE expression
  [],                         ← ORDER BY (empty)
  -1,                         ← LIMIT (none)
  0,                          ← OFFSET
  []]                         ← JOIN (empty)

WHERE expressions support AND, OR, and comparison operators (=, !=, <, >, <=, >=) with proper precedence.

Summary

• nyx-db is an SQL database written in Nyx, accessible via redis-cli.
• Supports CREATE TABLE, INSERT, SELECT (with WHERE, ORDER BY, LIMIT), UPDATE, DELETE.
• Types: INT, TEXT, REAL.
• Transactions with BEGIN / COMMIT / ROLLBACK.
• Persistence via background snapshots and SIGTERM handler.
• The SQL parser is a recursive descent parser that builds an AST.