SQL dialect

Arkeion speaks a practical dialect of SQL, parsed by a hand-written recursive-descent lexer and parser — zero dependencies, errors that point at an exact position, and a grammar that is small enough to read in an afternoon. There is no cost-based optimiser: a deterministic, rule-based planner chooses between a full scan and a point lookup by rowid, and pushes predicates down into joins. The same rules always produce the same plan, which matters more for an auditable engine than chasing the last few percent of a benchmark.

Every statement travels the same short pipeline:

Types

SQL type	Value	Notes
INTEGER	Integer(i64)	INTEGER PRIMARY KEY ⇒ alias for the rowid (SQLite-style)
REAL	Real(f64)
TEXT	Text(String)	UTF-8 validated
BLOB	Blob(Vec<u8>)	x'…' literals
BOOLEAN	Bool(bool)	TRUE / FALSE
—	Null

Column constraints: PRIMARY KEY, NOT NULL, DEFAULT <literal>, UNIQUE, CHECK (expr), and REFERENCES (foreign keys). CHECK text is stored in the catalogue (schema v7) and re-parsed when evaluated.

Statements

-- DDL
CREATE TABLE [IF NOT EXISTS] t (col TYPE [constraints], … [, table_constraint …]);
  -- column-level: PRIMARY KEY | NOT NULL | DEFAULT v | UNIQUE | CHECK (expr) | REFERENCES parent […]
  -- table-level:  UNIQUE (c…) | CHECK (expr) | FOREIGN KEY (c…) REFERENCES parent (p…) […]
CREATE TABLE [IF NOT EXISTS] t AS SELECT …;        -- CTAS: column names + types from the query
DROP TABLE [IF EXISTS] t;
ALTER TABLE t ADD [COLUMN] col TYPE [DEFAULT v] [NOT NULL];  -- appended; never rewrites rows
ALTER TABLE t MOVE COLUMN col {FIRST | BEFORE x | AFTER x};  -- logical reorder (presentation only)
ALTER TABLE t REORDER COLUMNS (col, …);                     -- fix the whole logical order
ALTER TABLE t RENAME [COLUMN] old TO new;                   -- metadata only
ALTER TABLE t DROP [COLUMN] col;                            -- logical DROP (tombstone); no rewrite
CREATE VIEW [IF NOT EXISTS] v AS <select>;                  -- a stored, named SELECT
DROP VIEW [IF EXISTS] v;
CREATE TRIGGER [IF NOT EXISTS] tr {BEFORE|AFTER|INSTEAD OF} {INSERT|UPDATE|DELETE} ON t
  [FOR EACH {ROW|STATEMENT}] BEGIN <dml>; … END;            -- body with OLD./NEW.
DROP TRIGGER [IF EXISTS] tr;

-- DML
INSERT INTO t [(cols)] {VALUES (expr, …)[, …] | SELECT …}
  [ON CONFLICT [(cols)] DO {NOTHING | UPDATE SET col = expr [, …] [WHERE expr]}]
  [RETURNING list | *];
UPDATE t SET col = expr [, …] [WHERE expr] [RETURNING list | *];
DELETE FROM t [WHERE expr] [RETURNING list | *];

-- Query
[WITH [RECURSIVE] cte AS (SELECT …) [, …]]        -- CTEs; RECURSIVE supported
SELECT [DISTINCT] list | *
  [FROM {table | (SELECT …)} [alias]              -- FROM is optional; a subquery here is a derived table
  [{INNER|LEFT} JOIN src2 ON expr]]               -- nested-loop, predicate pushdown
  [WHERE expr]
  [GROUP BY e1, … [HAVING cond]]
  [{UNION [ALL] | INTERSECT | EXCEPT} SELECT …]    -- set operators; ALL keeps duplicates
  [ORDER BY {col | expr | alias | ordinal} [ASC|DESC] [, …]]
  [LIMIT n [OFFSET m]]
  [AS OF {VERSION n | TIMESTAMP 'rfc3339'}];       -- arkeion extension

-- Transactions
BEGIN; COMMIT; ROLLBACK;
SAVEPOINT s;  ROLLBACK TO [SAVEPOINT] s;  RELEASE [SAVEPOINT] s;   -- inside a BEGIN

Queries

Joins. INNER and LEFT joins, evaluated nested-loop with predicate pushdown (a WHERE that constrains the inner side is applied as the rows are produced, not after).

Set operators. UNION (deduplicates), UNION ALL (keeps duplicates), INTERSECT (rows on both sides), and EXCEPT (rows in the accumulated left not present on the right). They combine left-associatively; ORDER BY after a set operation sorts by output column or ordinal.

Subqueries — scalar (SELECT …), x [NOT] IN (SELECT …), and [NOT] EXISTS (SELECT …). Correlated subqueries are supported: when the inner query references a column of the outer row, it is re-evaluated per outer row against that row’s values. A scalar subquery returning more than one row is an error; zero rows is NULL.

Derived tables — a (SELECT …) in the FROM clause, optionally aliased, is treated as a table and can be joined and filtered like any other source.

CTEs (WITH n AS (SELECT …)) — named, materialised tables visible to the query that follows; each sees the earlier ones and shadows a real table of the same name. WITH RECURSIVE runs a recursive CTE: a base case, then a step that references the CTE itself, iterated until it produces no new rows (UNION deduplicates the frontier, UNION ALL keeps every row).

Views (CREATE VIEW v AS SELECT …) — like a CTE but persistent: the SELECT text is stored in the catalogue and materialised on read, so it always reflects current data. A view over another view works, and view names never collide with tables. A view’s own definition is versioned, so an AS OF from before it was created does not see it.

Aggregates — COUNT(*), COUNT(col), SUM, AVG, MIN, MAX, GROUP_CONCAT(x[, sep]), each supporting DISTINCT (COUNT(DISTINCT x), …). SELECT DISTINCT deduplicates the projected rows. MIN/MAX also have a scalar form with ≥2 arguments (MIN(a, b, …)); with one argument they are aggregates, as in SQLite.

GROUP BY / HAVING — group by the value of expressions (usually columns) and emit one row per group, folding the projection’s aggregates over each group. A non-aggregated projection column must appear in GROUP BY. HAVING filters the aggregated groups.

Window functions — func(args) OVER ([PARTITION BY …] [ORDER BY …]) in the SELECT list, computed over the filtered set before the outer ORDER BY/LIMIT. Available: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(k), LAG, LEAD, FIRST_VALUE, LAST_VALUE, and SUM/COUNT/AVG/MIN/MAX as window aggregates. Explicit frames ROWS BETWEEN start AND end (physical ROWS, not RANGE) with UNBOUNDED {PRECEDING|FOLLOWING}, N {PRECEDING|FOLLOWING}, and CURRENT ROW.

SELECT without FROM evaluates constant expressions against a single implicit row (SELECT 1 + 1, SELECT UPPER('hi') AS g). A constant WHERE can filter it out. Without a table, the projection rejects columns, *, aggregates, and clauses that need rows (JOIN/GROUP BY/HAVING/ORDER BY/AS OF).

Writes

INSERT / UPSERT — INSERT … ON CONFLICT [(cols)] DO {NOTHING | UPDATE SET …}. If a row collides with the PK or a UNIQUE index it is skipped (DO NOTHING) or the existing row is updated (DO UPDATE). In DO UPDATE, SET/WHERE see the existing row and excluded.col = the proposed row; a false WHERE discards that update.

RETURNING — INSERT/UPDATE/DELETE … RETURNING list | * returns the affected rows (inserted rows, updated rows with their NEW values, or deleted rows) instead of only a count. Run it through query; via execute the write still happens but the rows are discarded.

Foreign keys — col REFERENCES parent[(col)] or table-level FOREIGN KEY (a, b) REFERENCES parent (x, y) (composite), with [ON DELETE action] [ON UPDATE action] and action ∈ {RESTRICT | CASCADE | SET NULL}. The referenced columns must be the parent’s PK or covered by a UNIQUE index. Checked on INSERT/UPDATE; the action fires on parent DELETE/UPDATE. Self-references (trees) are fine.

Triggers — {BEFORE|AFTER|INSTEAD OF} {INSERT|UPDATE|DELETE}, FOR EACH {ROW|STATEMENT}, body of INSERT/UPDATE/DELETE. FOR EACH ROW (default) fires once per affected row with OLD./NEW. bound; FOR EACH STATEMENT fires once. INSTEAD OF (views only) makes a view writable by translating the write to the base tables. Re-entrant writes are guarded against runaway recursion — the basis for tamper-evident audit logs.

Logical schema changes — MOVE COLUMN / REORDER COLUMNS change only the presentation order (* expansion and positional INSERT); RENAME COLUMN changes only the name; DROP COLUMN tombstones the column (it stops appearing in * and by name) but freezes its physical bytes. All are O(1) and never rewrite rows, so time-travel stays intact — an AS OF from before the change sees the schema of its era, because the catalogue is versioned in the same b-tree as the data. Dead bytes are reclaimed by vacuum.

Expressions

expr    := or
or      := and ( OR and )*
and     := not ( AND not )*
not     := [NOT] cmp
cmp     := add ( ( = | != | <> | < | <= | > | >= | LIKE | IS [NOT] NULL ) add )?
add     := mul ( ( + | - ) mul )*
mul     := unary ( ( * | / | % ) unary )*
unary   := [-] primary
primary := literal | column | table.column | ?N | :name | ( expr ) | function(args)

• Parameters: positional ?1, ?2, … or named :name (repeatable); not mixed in one statement.
• LIKE with % / _, case-sensitive.
• Comparing different types is a type error, not silent coercion (surprising behaviour is treated as a bug) — except INTEGER ↔ REAL, which is promoted.
• Division / modulo by zero → NULL (SQLite compat). A NaN result (e.g. inf - inf) normalises to NULL; ±inf is preserved. Integer overflow is an error (+/-/*, and i64::MIN / -1).
• Operators/forms: || (concatenation), CAST(x AS type), CASE WHEN … THEN … [ELSE …] END, x [NOT] BETWEEN a AND b.

Scalar functions (case-insensitive; NULL propagates unless noted):

• Text: UPPER, LOWER, LENGTH/CHAR_LENGTH, TRIM/LTRIM/RTRIM, SUBSTR/SUBSTRING, REPLACE, INSTR, REVERSE, HEX, CONCAT, CONCAT_WS, LPAD/RPAD, UNICODE, CHAR, QUOTE, PRINTF/FORMAT, GLOB.
• Numeric: ABS, ROUND, CEIL/CEILING, FLOOR, TRUNC, SQRT, POW/POWER, MOD, SIGN, EXP, LN, LOG/LOG10/LOG2/LOG(base, x), SIN/COS/TAN, ASIN/ACOS/ATAN/ATAN2, PI(), RADIANS/DEGREES, RANDOM (non-deterministic).
• Conditional / NULL: COALESCE, IFNULL, NULLIF, TYPEOF, IIF(c, a, b).
• Date / time: NOW, DATE, TIME, DATETIME, STRFTIME, JULIANDAY, UNIXEPOCH. The time integer is epoch milliseconds UTC — the same unit as audit timestamps.
• JSON (JSON1-style, pure-Rust): JSON, JSON_VALID, JSON_TYPE, JSON_EXTRACT, JSON_ARRAY_LENGTH, JSON_OBJECT, JSON_ARRAY, JSON_QUOTE, plus json -> key (result as JSON) and json ->> key (result as SQL value).

The AS OF extension

AS OF is an Arkeion extension that closes a SELECT (after every other clause). It pins the whole statement to one historical snapshot — there is no per-table mixing.

SELECT total, status FROM invoices WHERE id = 7 AS OF VERSION 1042;
SELECT * FROM invoices AS OF TIMESTAMP '2026-05-01T00:00:00Z';

• AS OF VERSION n — exact; a VersionNotFound error if n was compacted away.
• AS OF TIMESTAMP t — resolves to the highest version whose commit timestamp ≤ t. The timestamp is informative; the version is the authority.
• SELECT only. There is no writing to the past — that is what branches are for.

The executor is generic over its data source, so it fixes the snapshot once and evaluates everything against it: joins, subqueries (including correlated), CTEs (including recursive), set operators, aggregates, views, and the catalogue (columns, order, FKs are versioned in the same b-tree). The one exception is the clock: now() / date('now') return the execution time — AS OF rolls back the data, not the statement’s wall clock.

Identifiers

Unquoted identifiers start with a letter or _ and continue with letters, digits, or _; Unicode letters count (café, 名前), as in SQLite. Double quotes ("…", with "" for a literal quote) allow reserved words or spaces/symbols: SELECT "select", "my col" FROM t. Single quotes are always a text string, never an identifier.

Deliberately out of scope

Excluded	Why
Physical ALTER TABLE (type change, row-rewriting DROP)	would break time-travel without a per-row epoch; the logical ADD/MOVE/REORDER/RENAME/DROP cover the common cases
Instant space reclaim on DROP COLUMN	the logical drop leaves dead bytes; vacuum reclaims them in a rewrite
Cost-based optimiser (statistics)	replaced by a deterministic rule-based planner — index-vs-scan and predicate pushdown — so plans are reproducible and auditable

Codegen

The catalogue serialises to a stable JSON representation via Database::schema(). External code generators consume that, not the SQL text: the codegen contract is the catalogue, not the statement string.