SQL Engine

A full relational SQL engine mounted alongside the document engine in the same process. It owns entirely separate files — a collection name and a table name never collide. Off by default; zero cost when unused. For the internals — the life of a query, the transaction model, crash-atomic checkpoints, instant ALTER — read How the SQL Engine Works.

Enable it

# Server: set OXIDB_SQL=1
OXIDB_SQL=1 ./oxidb-server

# SQL data lives under <OXIDB_DATA>/sql (override with OXIDB_SQL_DATA)

Send SQL over the wire

Every request is length-prefixed JSON. A SQL request carries "engine": "sql":

{ "engine": "sql", "cmd": "sql", "sql": "SELECT * FROM users WHERE age > ?", "params": [18] }

params binds ? / $N placeholders left-to-right. The reply is {ok, data:[ ...one result per statement... ]}.

DDL — tables, types, constraints

CREATE TABLE users (
  id     INT PRIMARY KEY AUTO_INCREMENT,
  name   TEXT NOT NULL,
  email  TEXT UNIQUE,
  age    INT DEFAULT 0,
  joined TIMESTAMP,
  price  DECIMAL(10,2)
);

CREATE INDEX idx_age ON users (age);
CREATE UNIQUE INDEX idx_email ON users (email);

Types: INT, DOUBLE, DECIMAL(p,s) (exact), TEXT, BLOB, BOOL, TIMESTAMP (epoch-ms, ISO-8601 auto-detected).

Instant, online ALTER TABLE

Adding or dropping a column is O(1) — metadata-only, no row rewrite, no downtime. Works on a 500M-row live table without blocking. A later checkpoint reclaims a dropped column's space.

ALTER TABLE users ADD COLUMN score INT DEFAULT 0;   -- instant
ALTER TABLE users DROP COLUMN score;                 -- instant
ALTER TABLE users RENAME COLUMN name TO full_name;

DML

INSERT INTO users (name, email, age) VALUES ('Ada', 'ada@x.io', 36);
INSERT INTO users (name, age) VALUES ('Bob', 25), ('Eve', 30);   -- multi-row

UPDATE users SET age = age + 1 WHERE name = 'Ada';
DELETE FROM users WHERE age < 18;

INSERT INTO users (name) VALUES ('Kim') RETURNING id, name;       -- RETURNING

SELECT — joins, aggregates, windows

-- INNER / LEFT / RIGHT / FULL / CROSS / LATERAL joins
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- GROUP BY + HAVING
SELECT age, COUNT(*), AVG(price)
FROM users GROUP BY age HAVING COUNT(*) > 1;

-- Window functions
SELECT name, age,
       ROW_NUMBER() OVER (ORDER BY age DESC) AS rank,
       AVG(age) OVER () AS avg_age
FROM users;

-- DISTINCT ON (argmax), aggregate DISTINCT, LIMIT/OFFSET
SELECT DISTINCT ON (age) name, age FROM users ORDER BY age, name;

CTEs, recursion & set operations

WITH adults AS (SELECT * FROM users WHERE age >= 18)
SELECT COUNT(*) FROM adults;

WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

SELECT id FROM a UNION SELECT id FROM b;      -- also EXCEPT / INTERSECT (+ ALL)

Transactions

Per-engine transactions over one connection (session). Buffered until commit.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK; SAVEPOINT / ROLLBACK TO also supported

Row locking — SELECT ... FOR UPDATE

Real pessimistic row locks: the matched rows are locked until the transaction commits or rolls back, and a concurrent UPDATE/DELETE/FOR UPDATE on them blocks until then (up to OXIDB_SQL_LOCK_TIMEOUT_MS, default 5000 — also how a deadlock resolves, as a timeout error on one side). Plain UPDATE/DELETE lock their rows too, so two concurrent read-modify-write transactions serialize instead of losing one write.

BEGIN;
SELECT * FROM products WHERE id = 42 FOR UPDATE;  -- row 42 is now ours
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;                                           -- lock released, waiters proceed

Only a plain single-table SELECT can lock rows; FOR UPDATE on a join, aggregate, DISTINCT, set operation, view or derived table is refused with a clear error — never accepted without taking the lock. FOR SHARE/NOWAIT/SKIP LOCKED are likewise refused. In a cluster, SELECT ... FOR UPDATE classifies as a write, so oxipool never routes it to a replica where the lock would be meaningless.

Sequences

CREATE SEQUENCE order_seq START WITH 1000;
SELECT NEXT VALUE FOR order_seq;               -- EF Core HiLo keys

Stored procedures

Two languages: zero-toolchain SQL-text bodies and compiled Cobra bytecode run by an in-server VM. Both CALL inside the caller's transaction and replicate under Raft.

CREATE PROCEDURE give_raise(pct INT) AS BEGIN
  UPDATE users SET salary = salary + salary * pct / 100;
END;
CALL give_raise(5);

Full guide — SQL-text vs Cobra, the run(db, …) API, compiling, result shaping, and replication safety: Stored Procedures.

EF Core & ADO.NET (.NET)

A full EF Core provider passes all 3832 official EF Core relational specification tests and beats PostgreSQL across the EF Core benchmark. Migrations, scaffolding, LINQ, and ExecuteUpdate/ExecuteDelete all work.

// EF Core
options.UseOxiDb("Host=127.0.0.1;Port=4444");
var adults = db.Users.Where(u => u.Age >= 18).OrderBy(u => u.Name).ToList();
db.Database.Migrate();

// ADO.NET / Dapper
using var conn = new OxiDbConnection("Host=127.0.0.1;Port=4444");
var rows = conn.Query<User>("SELECT * FROM users WHERE age > @a", new { a = 18 });
dotnet add package OxiDb.EntityFrameworkCore
dotnet add package OxiDb.Data          # ADO.NET, Dapper-ready

Backup & restore

The SQL engine has its own consistent, low-lock online backup — the archive compresses with the engine lock released, so queries and writes keep running. Admin-only.

{ "engine": "sql", "cmd": "backup",  "path": "/backups/sql.tar.gz" }
{ "engine": "sql", "cmd": "restore", "archive": "/backups/sql.tar.gz", "target": "/data/restored" }

Durability

Crash-atomic checkpoints via a MANIFEST + generations: each checkpoint writes a whole new generation and promotes it with a single atomic rename, so catalog and snapshots can never disagree after a crash. A CRC'd WAL bridges the rest. Node-local (not Raft-replicated) in v1.

Report Issue