MarsmoduleDB — a SQLite-compatible Rust database engine and VDD proof project

MarsmoduleDB is an experimental, independent SQLite-compatible embedded database engine in pure Rust: no C, no FFI, #![forbid(unsafe_code)] throughout, byte-compatible file I/O, and a cargo-friendly embedding API. The published crate is named sqlite-rust.

It is also a public proof project for agent-team based software development with the Marsmodule VDD Plugin workflow. The project is intentionally real rather than demo-sized: storage, SQL execution, compatibility tests, fuzzing, documentation, and review evidence are all developed against a demanding database system.

Why this project exists

MarsmoduleDB has three goals:

1. Build a useful, freely licensed SQLite-compatible embedded database engine in pure Rust.
2. Validate agent-team based development on a serious, long-running software system rather than on a toy example.
3. Publish transparent engineering evidence — compatibility tests, fuzzing, architecture notes, and limitations — so the development method can be judged by real artifacts.

Project status at a glance

Area	Current status
Core storage	SQLite-compatible page/header/record handling, B-tree reads, multi-level INSERT growth, overflow pages, freelist reuse, rollback journal recovery
SQL execution	SELECT, joins, aggregates, subqueries, CTEs, window functions, scalar/math/date-time functions, type affinity, collation support
DML and DDL	INSERT, UPDATE, DELETE, CREATE/DROP TABLE, CREATE/DROP INDEX, CREATE/DROP VIEW, CREATE/DROP TRIGGER (BEFORE / AFTER / INSTEAD OF, FOR EACH ROW), ALTER TABLE ADD COLUMN / RENAME TO / RENAME COLUMN / DROP COLUMN, constraints (CHECK, UNIQUE/PRIMARY KEY, FOREIGN KEY via PRAGMA foreign_keys), conflict handling, UPSERT
Compatibility testing	Writes are differential-tested against /usr/bin/sqlite3 3.45.1 and verified with PRAGMA integrity_check
Fuzzing	cargo-fuzz targets cover SQL parsing and SQLite-compatible byte decoding; crashes become stable regression tests
Production readiness	Experimental. Evaluate compatibility, durability, concurrency, and security requirements before production use
VDD proof value	Real-world validation target for the Marsmodule VDD Plugin agent-team workflow

Status

A pure-Rust, binary-compatible port of the SQLite file format — no C, no FFI, #![forbid(unsafe_code)] throughout. Every write is differential-tested against the real /usr/bin/sqlite3 3.45.1 and verified with PRAGMA integrity_check. Current capabilities:

File format & storage

• Page-level I/O (Pager, LRU page cache); byte-compatible database header & records
• B-tree leaf + interior read; ordered multi-leaf table scan
• Multi-level B-tree growth on INSERT (arbitrary depth/size via append-only spine balancing)
• Overflow pages for oversized TEXT/BLOB; freelist page reuse
• Rollback journal with crash recovery — BEGIN / COMMIT / ROLLBACK transactions
• Nested transactions via savepoints — SAVEPOINT <name>, RELEASE [SAVEPOINT] <name>, and ROLLBACK [TRANSACTION] TO [SAVEPOINT] <name>, behaviour-matched to the real sqlite3 3.45.1 oracle. A SAVEPOINT outside a transaction implicitly begins one; RELEASE of that outermost implicit savepoint commits, while RELEASE of the outermost under an explicit BEGIN leaves the transaction open. ROLLBACK TO <name> undoes post-savepoint work (truncating pages appended since), preserves pre-savepoint work, and keeps the target active for a repeat rollback. Savepoints nest and may reuse names — RELEASE/ROLLBACK TO target the innermost match and pop every savepoint inside it; names are ASCII case-insensitive; COMMIT/plain ROLLBACK clear the whole stack; a missing name is a typed no such savepoint: <name> error that writes nothing. The stack is in-memory and orthogonal to the on-disk rollback journal, so savepoints are not crash-durable (a crash rolls the whole transaction back) — matching real SQLite (REQ-080)

SQL — query (SELECT)

• Projection; WHERE with boolean three-valued logic (AND / OR / NOT / parens)
• ORDER BY (multi-key, ASC/DESC); LIMIT / OFFSET
• GROUP BY / HAVING; aggregates (COUNT / SUM / MIN / MAX / AVG / group_concat)
• INNER JOIN and LEFT [OUTER] JOIN; FROM/JOIN table aliases (FROM t [AS] x, JOIN u [AS] y) and self-joins
• Scalar subqueries; IN / NOT IN over a literal list or a subquery
• Derived tables — a subquery in FROM/JOIN (FROM (SELECT …) [AS] x), with an optional alias
• Non-recursive and recursive CTEs (WITH [RECURSIVE] … AS (SELECT …) SELECT …)
• Window functions — ROW_NUMBER(), RANK(), DENSE_RANK(), and aggregate window functions (COUNT/SUM/MIN/MAX/AVG) with OVER ([PARTITION BY …] [ORDER BY …]) and the default frame; explicit frames, LAG/LEAD/NTILE, named windows, FILTER deferred
• rowid / _rowid_ / oid pseudo-column projection — the implicit rowid is selectable in a SELECT column list (previously resolvable only in WHERE / ORDER BY) on any real base table, returning the row's stored rowid rather than a scan position, behaviour-matched to /usr/bin/sqlite3 3.45.1. Works bare (SELECT rowid, v FROM t), aliased (_rowid_, oid), inside a projected expression (SELECT rowid + 1 FROM t), qualified per side in a JOIN (a.rowid, b.rowid — each side's own rowid, disambiguated), and combined with WHERE / ORDER BY in the same query. On a table declared with an INTEGER PRIMARY KEY, rowid and the aliased column return the identical value (byte-for-byte). A real column literally named rowid / _rowid_ / oid shadows the pseudo-column (oracle parity). WHERE rowid … is newly enabled on plain (non-INTEGER PRIMARY KEY) tables and carries INTEGER affinity — a TEXT literal on the other side of the comparison is coerced numerically (WHERE rowid > '1' behaves as > 1). Limitations: a bare (unqualified) rowid across a JOIN of two real-table sources is rejected — qualify it with the table name/alias; rowid on a VIEW / CTE / derived-table / FROM-less / vec_each source is unsupported (only real base tables carry a rowid); and rowid is not projectable in an aggregate / GROUP BY query — ORDER BY's pre-existing scan-position rowid resolution is a separate, unchanged mechanism (REQ-086)

SQL — DML & DDL

• INSERT — multi-row, column DEFAULT, INTEGER PRIMARY KEY rowid alias

• INSERT INTO t [(col[, …])] SELECT … — a SELECT query as the row source instead of a VALUES list, behaviour-matched to /usr/bin/sqlite3 3.45.1 (REQ-089). Any SELECT form this engine supports may be the source: a plain projection, WHERE-filtered, JOINs, scalar/IN subqueries, and WITH [RECURSIVE] … CTEs (the source is the full compound-SELECT surface, so UNION/INTERSECT/EXCEPT also compose). The optional column list targets specific columns (omitted columns take their DEFAULT); with no list the projection fills the table's columns left-to-right. INSERT OR IGNORE … SELECT … and INSERT OR REPLACE … SELECT … compose — the conflict action is applied per source row exactly as for a VALUES insert. A self-referential INSERT INTO t SELECT … FROM t … reads a stable pre-write snapshot: the SELECT is materialized to completion before any row is written, so it never sees or loops on its own freshly-inserted rows (oracle-matched). Whole-statement atomicity is inherited from the multi-row VALUES path — a constraint violation (NOT NULL, UNIQUE/CHECK, FK, or an index-page-full condition) partway through rejects the entire statement and writes no rows (no partial insert; BUG-005 / REQ-044 guarantees apply unchanged, since materialization makes the row count concrete before the first write). Non-Goal: a trailing ON CONFLICT … DO UPDATE upsert tail combined with a SELECT source is out of scope. One documented edge difference (not a bug): a 0-row SELECT source combined with a projection/target column-count mismatch is a silent no-op (changes() = 0) rather than the oracle's prepare-time arity error — the engine's empty-result short-circuit runs before its arity check; a non-empty mismatched SELECT is a typed ExecError as expected

• UPDATE and DELETE with incremental index maintenance

• UPDATE relocates the rowid when the SET clause assigns a new value to an INTEGER PRIMARY KEY (rowid-alias) column — the row's b-tree cell key is physically moved to the new rowid (e.g. UPDATE t SET id = 999 WHERE id = 2), behaviour-matched to /usr/bin/sqlite3 3.45.1, including mid-gap targets that append-only INSERT cannot reach. Secondary indexes and FK parent-side enforcement follow the moved rowid; a relocation onto a rowid already in use is rejected with a typed UNIQUE constraint failed error (exit 19) and the file is left byte-unchanged. A non-ipk UPDATE, or an ipk UPDATE that does not change the key's value, keeps the existing byte-for-byte in-place write path. The new value must be a literal or a scalar (SELECT …); a SET id = <column arithmetic> form (e.g. SET id = id + 1000) is a separate, unchanged SET-expression limitation and is not part of this behaviour (BUG-003)

• Scalar and IN subqueries in UPDATE … WHERE, UPDATE … SET, and DELETE … WHERE — both non-correlated and correlated, behaviour-matched to /usr/bin/sqlite3 3.45.1 (REQ-087). A WHERE clause accepts a scalar subquery in a comparison operand (DELETE FROM t WHERE amount > (SELECT avg(amount) FROM t)) and an IN / NOT IN subquery (UPDATE t SET flag = 1 WHERE id IN (SELECT id FROM other)); a SET right-hand side accepts a scalar subquery (SET total = (SELECT sum(x) FROM lines WHERE lines.t_id = t.id)) alongside the existing literal / ?-parameter forms — an arbitrary SET expression (arithmetic, functions, column refs) remains unsupported. Correlation is synthesised by qualified-outer-column substitution: a reference qualified with the target table's name (e.g. t.id when updating/deleting t) binds to the current outer row's value before the inner subquery runs. A bare (unqualified) outer-column reference is out of scope — it resolves only against the inner source and surfaces the usual UnknownColumn if absent, never a silent outer bind (SQLite's inner-first fallthrough is not reproduced). A subquery that reads the same table being modified sees the pre-modification snapshot: the matching-row set is computed once, up front, before any page is written. A scalar subquery follows strict cardinality — 0 rows → NULL, exactly 1 → its value, more than 1 → a typed ExecError, a deliberate divergence from the oracle (which silently picks the first row). A subquery-free UPDATE / DELETE is byte-for-byte the previous path; a malformed subquery is a typed ExecError, never a panic (REQ-087)

• CREATE TABLE / DROP TABLE; CREATE [UNIQUE] INDEX / DROP INDEX

• CREATE VIEW / DROP VIEW; ALTER TABLE ADD COLUMN / RENAME TO / RENAME [COLUMN] … TO … / DROP [COLUMN] …

• CREATE TRIGGER / DROP TRIGGER — BEFORE, AFTER, and INSTEAD OF timing, FOR EACH ROW triggers on INSERT / UPDATE [OF col[, …]] / DELETE, behaviour-matched to /usr/bin/sqlite3 3.45.1. CREATE TRIGGER [IF NOT EXISTS] name {BEFORE|AFTER|INSTEAD OF} {INSERT|UPDATE [OF col[, …]]|DELETE} ON {table|view} [WHEN condition] BEGIN stmt[; stmt…] END registers the trigger in sqlite_schema (type='trigger', rootpage=0, stored SQL verbatim from the name token with the trailing ; stripped, including the timing keyword) and fires it once per affected row, inside the triggering statement's implicit transaction. OLD.col / NEW.col bind to the pre/post-image of the affected row (NEW only for INSERT, OLD only for DELETE, both for UPDATE); the optional WHEN condition is evaluated per row (identically for all three timings) and the body runs only when it is true. A trigger body statement is an INSERT / UPDATE / DELETE whose VALUES/SET right-hand sides are literals or OLD/NEW column references (no arithmetic in VALUES/SET); full expressions are available in the WHEN clause and in a body statement's own WHERE. Multiple triggers on the same table/event fire in reverse creation order (last created fires first), interleaved per row. recursive_triggers is effectively OFF: a trigger will not re-fire itself (direct or indirect self-recursion via the same trigger name is suppressed, so a self-inserting trigger terminates), while cross-table / cross-event trigger chains fire normally. UPDATE OF col fires iff a listed column appears in the SET list, regardless of whether its value changes. Only physically-written rows fire (an OR IGNORE-skipped row does not fire); a trigger-body error rolls back the whole triggering statement. CREATE TRIGGER IF NOT EXISTS on an existing name is a no-op, and DROP TRIGGER IF EXISTS on a missing name is a no-op; a bare duplicate CREATE or a bare DROP of a missing trigger is a typed ExecError.

  Timing semantics (REQ-085, completing the surface REQ-084 opened with AFTER):

  • BEFORE (tables only) fires per affected row before that row's physical write, with OLD/NEW bound to the pre-write image. Firing interleaves with the write per row: for each row all BEFORE triggers fire, then the row is written, then all AFTER triggers fire, then the next row. On a BEFORE INSERT the NEW rowid/INTEGER PRIMARY KEY slot is the explicit value when supplied, else a stable -1 sentinel (the real rowid — max+1 — is not yet assigned at BEFORE time). A BEFORE body that raises a typed error (this engine has no RAISE(); any body-DML error suffices) aborts the row's write and rolls the whole outer statement back, leaving the file byte-unchanged. SQLite cannot modify NEW in a trigger, so BEFORE is observe-or-abort only.
  • AFTER (tables only) fires per affected row after that row's physical write — the timing REQ-084 introduced, unchanged.
  • INSTEAD OF (views only) makes an otherwise non-updatable view's INSERT / UPDATE / DELETE succeed by running the trigger body as the entire effect: no physical write happens to the view or its base table, and changes() for the outer view DML is 0. INSTEAD OF INSERT binds NEW to the inserted tuple; INSTEAD OF UPDATE fires once per view row the WHERE selects with OLD = the view row and NEW = OLD merged with the SET list (an unset column keeps its OLD value); INSTEAD OF DELETE fires once per matched view row with OLD bound. Row materialization for UPDATE/DELETE reuses the view's own definition, so the view's own WHERE/JOIN filtering applies before the trigger fires. A view whose event has no matching INSTEAD OF trigger still rejects that DML as non-updatable.

  Placement is gated by timing × target kind: BEFORE/AFTER attach only to tables and INSTEAD OF only to views — attaching the wrong timing to the wrong target type is a typed ExecError at CREATE time. DROP TABLE does not cascade-remove a table's triggers — see Known limitations (REQ-084, REQ-085)

• CREATE VIRTUAL TABLE … USING vec0(embedding <type>[N]) — native, pure-Rust vec0 vector store behaviour-matched to the real sqlite-vec 0.1.9 extension, supporting three element types (aliases in parentheses): float/f32 → little-endian IEEE-754 f32[N], N·4 bytes; int8/i8 → two's-complement i8[N], N·1 bytes; bit → a packed bit vector, N/8 bytes stored verbatim. Vectors persist in a backing rowid table; INSERT, DELETE (including the reindex form DELETE FROM v WHERE rowid IN (SELECT … )), and rowid/vector read-back all match the oracle. Input forms per type: float32 and int8 accept JSON '[…]' text or a raw BLOB (int8 elements are range-checked to -128..=127); bit accepts a BLOB only (TEXT/JSON is a typed error). A malformed, out-of-range, wrong-length, NULL, or dimension-mismatched value is a typed ExecError, never a panic

• vec0 KNN search — SELECT rowid, distance FROM v WHERE embedding MATCH '[…]' AND k = ? returns the k nearest vectors ranked ascending by the column type's default metric, surfaced as a distance column, behaviour-matched to sqlite-vec 0.1.9: float/f32 and int8/i8 use Euclidean (L2) distance (the float32 L2 kernel uses fused multiply-add for bit-exact parity; the int8 kernel accumulates exact integer squares); bit uses Hamming distance (population count of the XOR). The query vector binds as the representation the column expects (float32/int8: JSON '[…]', a BLOB, or a ? parameter; bit: a BLOB or ?); the constraint may be k = ? or LIMIT; a MATCH with neither is a typed error. Supports the rowid join … FROM v JOIN chunks c ON c.id = v.rowid ORDER BY v.distance. A dimension mismatch or malformed query vector is a typed ExecError, never a panic

• vec0 distance_metric= column option — CREATE VIRTUAL TABLE v USING vec0(embedding <type>[N] distance_metric=L1|cosine|L2) selects a non-default KNN metric for float/f32 and int8/i8 columns, behaviour-matched to sqlite-vec 0.1.9: L1 (Manhattan) and cosine join the default L2. The metric keyword is case-insensitive (l1, COSINE); an omitted clause keeps L2 (every pre-existing vec0 table is unchanged). The metric is recovered from the stored CREATE text on reopen — no separate persisted column. bit columns are Hamming-only: any distance_metric= clause on a bit column, and any unknown metric keyword, is a typed ExecError, never a panic (REQ-070)

• vec0 metadata columns — CREATE VIRTUAL TABLE v USING vec0(embedding <type>[N], meta_col <TEXT|INTEGER|FLOAT>) declares auxiliary metadata columns alongside the vector column, behaviour-matched to sqlite-vec 0.1.9: strict-typed INSERT rejects NULL, omitted, or type-mismatched metadata values with a typed ExecError; KNN search filters candidate rows on metadata predicates before ranking and truncating to k, never after (REQ-071)

• Vector Phase 1 — complete. The end-to-end vec0 index → search → reindex → search workload of the real consumer (mm-nav) converges with the sqlite-vec 0.1.9 oracle on MarsmoduleDB: schema create, scale index in one transaction, KNN join search, and reindex-by-IN (subquery) delete all match the oracle bit-for-bit (REQ-067)

• vec_* scalar functions — the seventeen sqlite-vec 0.1.9 vector utility functions, usable anywhere a scalar expression is (no vec0 table required), behaviour-matched to the oracle: vec_distance_l2(a, b) / vec_distance_cosine(a, b) (Euclidean / cosine distance, FMA-contracted for bit-exact float parity), vec_distance_l1(a, b) (L1/Manhattan distance — float32 returns a real, and, when both arguments are direct vec_int8(...) calls, int8 returns an integer, matching the oracle's sqlite3_result_int/_double split), vec_distance_hamming(a, b) (Hamming distance — population count of XOR — for bit vectors only, i.e. both arguments direct vec_bit(...) calls; any other element-type pair is a typed error), vec_f32(v) (JSON '[…]' or f32 BLOB → canonical little-endian f32[N] BLOB), vec_int8(v) (JSON '[…]' int array, range-checked -128..=127, or a raw BLOB → dimensionless i8[N] BLOB), vec_bit(v) (a packed-bit BLOB only, JSON is a typed error → dimensionless bit BLOB), vec_to_json(v) (→ six-decimal […] JSON text, C printf %f format), vec_length(v) (element count), vec_type(v) (→ 'float32'/'int8'/'bit' — dispatches on a result-subtype tag that survives only a direct constructor-call argument, e.g. vec_type(vec_int8(v)) → 'int8'; any other expression, including a stored-then-read value, reports 'float32'), vec_normalize(v) (L2-unit-normalize a float32 vector; FMA-contracted magnitude accumulation + f64 sqrt, oracle-exact — a zero-norm input yields the IEEE quiet-NaN bytes 0x7FC00000 per element, never an error; non-float32 input is a typed error), vec_slice(v, start, end) (the [start, end) sub-vector, subtype-preserved across float32/int8/bit; bit slices additionally require start/end divisible by 8; out-of-range, inverted, empty, or non-byte-aligned bounds are a typed error), vec_add(a, b) / vec_sub(a, b) (elementwise add/subtract on same-length, same-subtype float32 or int8 vectors; int8 uses two's-complement wrap, not saturation, e.g. 127 + 1 → -128; dimension mismatch, subtype mismatch, or bit-vector input is a typed error), vec_quantize_int8(v, 'unit') (quantize a float32 vector to int8 via the oracle's fixed unit-range scale step, computed in f32 then re-widened to f64 — arity 2, the 2nd argument must be the literal mode string 'unit' case-insensitive; non-float32 input or any other mode string is a typed error), vec_quantize_binary(v) (sign-threshold-quantize a float32 or int8 vector to a packed-bit vector, strict v[i] > 0, LSB-first; bit input is a typed error; a length not divisible by 8 is a typed error — no padding), and vec_version() (→ 'v0.1.9'). A NULL, malformed, dimension-mismatched, element-type-mismatched, or wrong-arity argument is a typed ExecError, never a panic (REQ-068, REQ-072, REQ-073, REQ-074, REQ-075, REQ-076)

• vec_each(v) table-valued function — the first table-valued sqlite-vec 0.1.9 function, used in a FROM/JOIN clause (not as a scalar), behaviour-matched to the oracle: SELECT * FROM vec_each(vec_f32('[…]')) or SELECT labels.name, ve.value FROM labels JOIN vec_each(vec_f32('[7,8,9]')) AS ve ON labels.idx = ve.rowid expands a vector into one row per element with a single output column value — float32 → REAL (the stored f32 widened to f64, i.e. the numeric value, not the source text, e.g. 0.1 → 0.100000001490116), int8 → signed INTEGER (-128..127, one row per byte), bit → INTEGER 0/1, one row per bit, MSB-first (8 rows per stored byte). Rows are returned in element-index order; the 0-based element index is available as the rowid pseudo-column when referenced explicitly (e.g. ve.rowid) but is excluded from SELECT *. The argument is a constant scalar expression evaluated once (a correlated/LATERAL argument referencing an outer row is out of scope); an unknown table-valued function name, or a NULL / non-vector / wrong-type / malformed argument, is a typed ExecError, never a panic (REQ-077)

• CHECK constraints — column-level and table-level, enforced pre-write on INSERT/UPDATE (NULL/Unknown passes; only FALSE rejects; typed CheckConstraintViolation)

• UNIQUE and PRIMARY KEY column/table constraints — automatically create sqlite_autoindex_<table>_<n> backing indexes and enforce uniqueness (NULL keys exempt; duplicates rejected with typed UniqueConstraintViolation)

• FOREIGN KEY constraints — table-level FOREIGN KEY (col[, …]) REFERENCES parent(col[, …]) and the inline col … REFERENCES parent[(col)] shorthand (omitted parent columns reference the parent's PRIMARY KEY); composite (multi-column) keys supported. Enforcement is gated by PRAGMA foreign_keys (default OFF): with it ON, a child INSERT/UPDATE whose non-NULL key tuple has no matching parent row — and a parent DELETE/UPDATE that would orphan a referencing child — are rejected with a typed ForeignKeyConstraintViolation (NO ACTION semantics; no cascade). MATCH SIMPLE: any NULL member of the key tuple exempts the row from the check; a multi-row INSERT is checked against its statement-final state (a self-referential batch succeeds regardless of row order) (REQ-078). Parent-side enforcement is uniform across every UPDATE/DELETE shape — a subquery-driven statement (DELETE FROM parent WHERE id IN (SELECT …), UPDATE parent SET id = (SELECT …) WHERE …) that would orphan a referencing child is rejected identically to its non-subquery equivalent, evaluated against the pre-write snapshot (REQ-088)

• INSERT conflict resolution — INSERT OR IGNORE (skip constraint-violating rows) and INSERT OR REPLACE (delete conflicting rows then insert); OR ABORT is the default

• UPSERT — INSERT … ON CONFLICT [(target)] DO NOTHING and DO UPDATE SET col = excluded.col [, …] [WHERE …] (excluded.* refers to the would-be-inserted row)

Expressions & types

• Arithmetic operators (+ - * / %, unary ±); CASE WHEN
• LIKE / GLOB pattern matching (with ESCAPE)
• [NOT] BETWEEN range predicate — expr BETWEEN low AND high is exact sugar for expr >= low AND expr <= high (and expr NOT BETWEEN low AND high for NOT(expr >= low AND expr <= high)). It desugars at parse time to those existing comparison nodes, so it inherits their comparison-affinity and three-valued-NULL semantics for free. NULL follows Kleene three-valued logic on the expansion: any NULL operand yields NULL unless the other comparison is already definitively FALSE — e.g. 0 BETWEEN 1 AND NULL → 0/FALSE (because 0 >= 1 is FALSE and FALSE absorbs), matching the oracle rather than a naive "any NULL ⇒ NULL". Usable wherever an expression is valid — WHERE, CHECK constraints, CASE WHEN, and as a computed SELECT column (SELECT x BETWEEN 1 AND 10 FROM t, projecting 1 / 0 / NULL). HAVING divergence (disclosed): positive HAVING … BETWEEN … works, but HAVING … NOT BETWEEN … is a typed ParseError — HAVING has never supported a negated predicate for any operator (its expression node carries no NOT), whereas the oracle accepts it. A narrow, known divergence, surfaced as a typed error, not silently mis-evaluated (REQ-090)
• Blob literals (x'HH..'/X'HH..') and '' string-escape collapse (doubled quotes in single-quoted strings decode to one quote at tokenize time)
• Scalar + math functions (COALESCE, LENGTH, SUBSTR, TRIM, ABS, ROUND, printf/format, last_insert_rowid(), …); CAST
• Date/time scalar functions — date, time, datetime, julianday, and strftime, behaviour-matched to /usr/bin/sqlite3 3.45.1, built on a pure-std integer Julian-Day carrier (no runtime dependency, no unsafe). They accept the SQLite time-value forms — YYYY-MM-DD, YYYY-MM-DD HH:MM[:SS[.fff]] (space or T separator), a bare HH:MM[:SS[.fff]] (date defaults to 2000-01-01), a numeric Julian Day (date(2460375) → 2024-03-05), and 'now' — with an omitted time-value defaulting to 'now'. The variadic modifier list applies left-to-right: ±N[.f] days|hours|minutes|seconds|months|years (singular or plural lexeme), start of month|year|day, weekday N (0=Sun..6=Sat, advances forward to the next such weekday), and localtime/utc. Month/year arithmetic overflow-normalizes the day-of-month rather than clamping (date('2024-01-31','+1 month') → 2024-03-02). strftime supports the substitution set %Y %m %d %H %M %S %s %J %f %w %W %j %%. Every malformed input — a bad time-value, an out-of-range field, an unknown modifier, an out-of-scope specifier, a Julian Day pushed outside [0, 5373484.5), or a NULL argument anywhere — yields SQL NULL, never an ExecError and never a panic (a deliberate divergence from the printf-family error path). localtime/utc are UTC-identity transforms — see Known limitations (REQ-083)
• Type affinity (store / compare / read-time); COLLATE (BINARY / NOCASE / RTRIM)

Embedding API

• Connection::open / execute / query; Row::get::<T>() typed reads
• Connection::last_insert_rowid() — rowid of the most recent successful INSERT on the connection (0 before any insert); also the SQL function last_insert_rowid()
• PRAGMA foreign_keys; reads the connection's foreign-key-enforcement flag (default 0/OFF); PRAGMA foreign_keys = ON|OFF|1|0; sets it (case-insensitive). The flag is connection-scoped and is never persisted in the database file — a fresh connection always reads OFF (REQ-078)
• ? bound parameters — typed literals, injection-safe by construction (OWASP A03)

Compatibility & testing

• Every write differential-tested against the real /usr/bin/sqlite3 3.45.1
• PRAGMA integrity_check oracle on written files; zero mocks

Known limitations

• INSERT is append-only — no ordered insertion; an explicit rowid ≤ current max is rejected
• No WAL / concurrency
• Triggers cover BEFORE / AFTER / INSTEAD OF timing, FOR EACH ROW only. DROP TABLE does not cascade-remove a table's triggers (the oracle does) — dropping a table with triggers leaves orphaned sqlite_schema trigger rows; a DROP TABLE cascade is a later REQ. Statement-level (FOR EACH STATEMENT) triggers, the RAISE() function in a WHEN/body clause (this engine has no RAISE(); a BEFORE abort is expressed through any typed body-DML error), and a body statement other than INSERT/UPDATE/DELETE (e.g. SELECT) are out of scope. A BEFORE INSERT trigger observing an auto-assigned rowid via NEW.rowid on a plain (non-INTEGER PRIMARY KEY) rowid table is not supported (the engine has no bare-rowid alias binding); observe the sentinel through the declared INTEGER PRIMARY KEY column instead
• No general constraint enforcement beyond UNIQUE index probing, NOT NULL, CHECK constraint enforcement, UNIQUE/PRIMARY KEY column constraint enforcement, and FOREIGN KEY enforcement gated by PRAGMA foreign_keys
• Foreign keys enforce NO ACTION only — ON DELETE/ON UPDATE cascade/set-null actions, foreign_key_check/foreign_key_list, and deferred constraints are out of scope
• Collation-aware indexes out of scope (index keys stay BINARY)
• Date/time localtime/utc are UTC-identity transforms — the zero-runtime-dependency, #![forbid(unsafe_code)] constraint forecloses a real timezone database (chrono/chrono-tz/libc), so a non-UTC local offset is not applied. The differential harness pins TZ=UTC, where the oracle's localtime == utc == identity too. The strftime long-tail specifiers (%e %I %p %P %R %T %F %u %k %l), the unixepoch modifier, and the CURRENT_DATE/CURRENT_TIME/CURRENT_TIMESTAMP keyword forms are deferred (a documented limitation, not a bug); an out-of-scope specifier makes the whole strftime result NULL

See CHANGELOG.md for the full version history.

Crate layout

Cargo.toml                          # workspace manifest
crates/sqlite-rust/
  Cargo.toml
  src/
    lib.rs                          # #![forbid(unsafe_code)], re-exports
    value.rs                        # Value: the shared decoded-column entity (zero deps; consumed by btree, schema, eval, sql)
    collation.rs                    # Collation::{Binary,Nocase,Rtrim} + collate(a,b,c) -> Ordering (BINARY byte compare, NOCASE ASCII-only fold, RTRIM trailing-space strip); Collation::from_name case-insensitive resolver
    error.rs                        # Error, PageError, HeaderError, CellError, RecordError, SchemaError, TokenizeError, ParseError, ExecError
    schema.rs                       # read_schema → HashMap<String, u32>; read_table_defs → HashMap<String, TableDef> (rootpage + CREATE TABLE sql)
    eval/
      mod.rs                        # execute: SelectStmt + Pager → Vec<Row>; dispatches to aggregate path (has_aggregate || !group_by.is_empty()), join path (!joins.is_empty()), or non-aggregate single-table path; re-exports execute_insert, execute_delete, execute_update
      aggregate.rs                  # aggregate path: validate → collect+WHERE → group → fold (COUNT/SUM/MIN/MAX/AVG/group_concat) → HAVING filter → sort → LIMIT → project
      join.rs                       # join path: JoinSchema cross-table resolver (qualified/ambiguous) → nested-loop combine (INNER + LEFT-OUTER NULL-fill) → WHERE → ORDER BY → LIMIT → project over combined rows
      order.rs                      # value_order / value_order_collated (SQLite storage-class total order, COLLATE-aware text branch) + sort_rows (stable multi-key ORDER BY pass)
      predicate.rs                  # WHERE evaluation: operand interpretation, value comparison matrix, COLLATE selection rule (explicit > column > BINARY)
      insert.rs                     # execute_insert: InsertStmt + Pager → i64 rowid; SQLite record + cell encoding, leaf-page mutation; root-type dispatch
      split.rs                      # split_leaf_root: depth-1 leaf-page split — interior root + two leaf children
      delete.rs                     # execute_delete: DeleteStmt + Pager → usize; depth-0/1 = full-page / K-C-N rebuild via build_leaf; depth≥2 = rebuild-by-reinsert (scan survivors → free non-root pages to freelist → re-insert ascending); frees overflow chains via free_overflow_chain
      update.rs                     # execute_update: UpdateStmt + Pager → usize; a non-ipk UPDATE re-encodes matched rows under their original rowid, keeps unmatched verbatim, and rebuilds affected leaves via build_leaf (any tree depth); when SET changes an INTEGER PRIMARY KEY alias column's value the row's b-tree cell key is physically relocated to the new rowid via a whole-table rebuild (secondary indexes follow the moved rowid; a target rowid already in use is rejected with a typed error); frees/reallocates overflow chains
      overflow.rs                   # write_overflow_chain, free_overflow_chain, encode_leaf_cell_maybe_overflow — write/free paths for overflow pages (eval layer)
      subquery.rs                   # materialise-once subquery resolver (ADR-024): resolve_where, ResolvedWhere, eval_scalar, in_membership
      compound.rs                   # execute_compound: compound SELECT eval (UNION/UNION ALL/INTERSECT/EXCEPT); iterative left-assoc fold; sort_and_dedup for DISTINCT ops; NULL=NULL set-context equality (ADR-REQ-049)
      cte.rs                        # CTE resolution: Ctx scope threading, materialize (named derived table), cycle guard (CircularReference) + depth backstop (ASVS V5); iterative fixpoint for recursive terms (materialize_recursive, safety cap RecursiveCteLimit, ASVS V5)
      function.rs                   # per-row scalar evaluator: COALESCE/IFNULL (lazy), LENGTH/UPPER/LOWER/SUBSTR/TRIM/LTRIM/RTRIM/TYPEOF (eager), CAST (affinity coercion); SUBSTR bounds-checked (ASVS V5 fail-safe); ABS/ROUND/scalar MAX/MIN/RANDOM (ADR-026): math scalar dispatch, checked_abs overflow guard, round_half_away, std-only PRNG; INSTR, REPLACE, HEX, QUOTE, CHAR, UNICODE, NULLIF, printf/format (SQLite-pinned format-string scanner, shared escape_quotes helper)
      trigger.rs                    # CREATE/DROP TRIGGER executors + BEFORE/AFTER/INSTEAD OF firing (REQ-084, REQ-085): execute_create_trigger/execute_drop_trigger (verbatim sqlite_schema storage incl. timing keyword, timing×target-kind placement check, mirrors CREATE/DROP VIEW); TriggerRuntime (active-trigger-name stack = recursive_triggers=OFF guard, MAX_TRIGGER_DEPTH backstop); per-row firing hook threaded into the INSERT/UPDATE/DELETE cores fires BEFORE on the pre-write image then AFTER on the post-write image, reverse-creation-order, inside the implicit txn; INSTEAD OF view-dispatch runs the body as the whole effect (changes()=0, no physical write); OLD/NEW token→?-param rewrite feeding the existing parse_*_with_params bound-Value path
    format/
      constants.rs                  # MAGIC, HEADER_SIZE, EMPTY_LEAF_HEADER
      header.rs                     # DatabaseHeader: parse / validate / serialize
    pager/
      io.rs                         # PageIo: open / read_page / write_page
      cache.rs                      # PageCache: LRU, disk_reads() counter
    sql/
      mod.rs                        # re-exports: tokenize, Token, TokenKind, Keyword, parse, parse_insert, parse_delete, parse_update, BinOp, Expr, Literal, SelectColumn, SelectStmt, OrderByTerm, Direction, AggFunc, AggArg, agg_output_name, HavingExpr, HavingOperand, Subquery, InsertStmt, DeleteStmt, UpdateStmt, CompoundOp, CompoundSelect, parse_compound, parse_compound_with_params
      token.rs                      # Token, TokenKind, Keyword types (Keyword includes Insert/Into/Values/Delete/Update/Set)
      tokenizer.rs                  # tokenize: single-pass char_indices scanner
      ast.rs                        # SelectStmt (with order_by, group_by, joins), JoinSpec, JoinKind, SelectColumn (Star/Expr/Aggregate), AggFunc, AggArg, agg_output_name, OrderByTerm, Direction, Expr, Literal, BinOp, InsertStmt, DeleteStmt, UpdateStmt, CompoundOp, CompoundSelect types
      parser.rs                     # parse: &[Token] → Result<SelectStmt, ParseError>; parse_insert: &[Token] → Result<InsertStmt, ParseError>; parse_delete: &[Token] → Result<DeleteStmt, ParseError>; parse_update: &[Token] → Result<UpdateStmt, ParseError>; parse_compound: &[Token] → Result<CompoundSelect, ParseError>; parse_compound_with_params: &[Token], params → Result<CompoundSelect, ParseError>
    btree/
      mod.rs                        # re-exports: Value (from crate::value), Row, LeafPageReader, TableScan, InteriorPageReader, collect_leaf_pages, read_varint
      varint.rs                     # read_varint: 1–9 byte SQLite varint decoder
      record.rs                     # Row, decode_record: serial-type → Rust value (Value lives in crate::value)
      leaf.rs                       # LeafPageReader: 0x0D page buffer → Vec<Row>
      interior.rs                   # InteriorPageReader: 0x05 page buffer → child page numbers (pure, no I/O)
      scan.rs                       # TableScan: ordered multi-leaf iteration via Pager; collect_leaf_pages; scan_from_root
      overflow.rs                   # local_payload_len (spec-exact X/M/K formula), read_overflow_chain — read path for overflow pages (btree layer)
  tests/
    req_001_page_io.rs              # E2E integration tests (page I/O)
    req_002_btree_leaf.rs           # E2E + unit tests (btree leaf reader)
    req_003_btree_interior.rs       # E2E + adversarial tests (interior traversal)
    req_004_schema_reader.rs        # E2E + adversarial tests (schema reader)
    req_025_scalar_functions.rs     # E2E tests (scalar SQL functions: COALESCE/IFNULL/LENGTH/UPPER/LOWER/SUBSTR/TRIM/TYPEOF/CAST)
    req_026_math_scalar_functions.rs # E2E tests (math scalar functions: ABS/ROUND/MAX/MIN scalar/RANDOM)
    req_059_scalar_functions.rs     # E2E tests (extended scalar functions: INSTR/REPLACE/HEX/QUOTE/CHAR/UNICODE/NULLIF)
    req_063_printf_format.rs        # E2E differential tests (printf/format: conversions, flags/width/precision, %q/%Q/%w, float fidelity) vs sqlite3 oracle
    req_060_sql_literals.rs         # E2E differential tests (blob literals, '' escape collapse, QUOTE round-trip)
    req_064_last_insert_rowid.rs    # E2E differential tests (last_insert_rowid API + SQL fn: auto/explicit/REPLACE/UPSERT physical-insert gating) vs sqlite3 oracle
    req_084_create_drop_trigger_after.rs # E2E differential tests (CREATE/DROP TRIGGER AFTER: schema round-trip, OLD/NEW binding, WHEN guard, reverse-order firing, recursive_triggers=OFF, body-error rollback) vs sqlite3 oracle
    req_085_before_instead_of_trigger_timing.rs # E2E differential tests (CREATE TRIGGER BEFORE + INSTEAD OF timing: per-row pre-write firing + interleave, BEFORE abort byte-unchanged, INSTEAD OF view-DML success + changes()=0, timing×target-kind placement, WHEN) vs sqlite3 oracle
    req_089_insert_select.rs        # E2E differential tests (INSERT … SELECT: plain/WHERE/JOIN/subquery/CTE sources, optional column list, OR IGNORE/REPLACE composition, self-referential pre-write snapshot, whole-statement atomicity, 0-row no-op vs arity mismatch) vs sqlite3 oracle
    req_090_between_predicate.rs    # E2E differential tests ([NOT] BETWEEN … AND …: parse + range filter, precedence vs AND/OR/NOT, NULL three-valued logic incl. FALSE-absorbing bounds, all contexts WHERE/CHECK/CASE WHEN/projection, HAVING positive vs NOT-BETWEEN parse-error divergence) vs sqlite3 oracle
    bug_003_update_ipk_relocation.rs # E2E differential tests (UPDATE relocates the b-tree cell key when SET changes an INTEGER PRIMARY KEY value: literal/scalar-subquery target, mid-gap relocation, collision rejected byte-unchanged, secondary-index + FK parent-side follow) vs sqlite3 oracle
    bug_003_adversarial_probes.rs   # E2E adversarial probes for the ipk-relocation path (edge cases around the whole-table rebuild, collision, and non-relocating fast path) vs sqlite3 oracle
    fixtures/                       # real sqlite3-created .db files (btree_test.db, btree_multi.db, btree_deep.db, btree_schema.db)

Build

cargo build -p sqlite-rust

Test

cargo test -p sqlite-rust --test req_001_page_io
cargo test -p sqlite-rust --test req_002_btree_leaf
cargo test -p sqlite-rust --test req_003_btree_interior
cargo test -p sqlite-rust --test req_004_schema_reader
cargo test -p sqlite-rust --test req_005_sql_tokenizer
cargo test -p sqlite-rust --test req_006_sql_parser
cargo test -p sqlite-rust --test req_007_select_executor
cargo test -p sqlite-rust --test req_008_insert_parser
cargo test -p sqlite-rust --test req_009_insert_executor
cargo test -p sqlite-rust --test req_010_leaf_page_split
cargo test -p sqlite-rust --test req_011_delete_executor
cargo test -p sqlite-rust --test req_012_update_executor
cargo test -p sqlite-rust --test req_013_order_by
cargo test -p sqlite-rust --test req_014_aggregates
cargo test -p sqlite-rust --test req_015_join
cargo test -p sqlite-rust --test req_016_multipage_dml
cargo test -p sqlite-rust --test req_017_overflow
cargo test -p sqlite-rust --test req_018_create_table
cargo test -p sqlite-rust --test req_019_drop_table
cargo test -p sqlite-rust --test req_020_transactions
cargo test -p sqlite-rust --test req_021_limit_offset
cargo test -p sqlite-rust --test req_022_having
cargo test -p sqlite-rust --test req_023_embedding_api
cargo test -p sqlite-rust --test req_024_subquery
cargo test -p sqlite-rust --test req_025_scalar_functions
cargo test -p sqlite-rust --test req_026_math_scalar_functions
cargo test -p sqlite-rust --test req_027_case_when
cargo test -p sqlite-rust --test req_028_like_glob
cargo test -p sqlite-rust --test req_059_scalar_functions
cargo test -p sqlite-rust --test req_063_printf_format
cargo test -p sqlite-rust --test req_060_sql_literals
cargo test -p sqlite-rust --test req_064_last_insert_rowid
cargo test -p sqlite-rust --test req_065_vec0_storage
cargo test -p sqlite-rust --test req_066_vec0_knn
cargo test -p sqlite-rust --test req_067_mm_nav_convergence
cargo test -p sqlite-rust --test req_068_vec_scalar_functions
cargo test -p sqlite-rust --test req_069_vec0_int8_bit_types
cargo test -p sqlite-rust --test req_070_vec0_distance_metric
cargo test -p sqlite-rust --test req_071_vec0_metadata_columns
cargo test -p sqlite-rust --test req_072_vec_distance_l1_scalar
cargo test -p sqlite-rust --test req_073_vec_int8_bit_scalar
cargo test -p sqlite-rust --test req_074_vec_distance_l1_int8_hamming
cargo test -p sqlite-rust --test req_075_vec_arithmetic_scalar
cargo test -p sqlite-rust --test req_076_vec_quantize_scalar_functions
cargo test -p sqlite-rust --test req_077_vec_each_table_function
cargo test -p sqlite-rust --test req_078_foreign_key_constraints
cargo test -p sqlite-rust --test req_080_savepoints
cargo test -p sqlite-rust --test req_029_create_index
cargo test -p sqlite-rust --test req_030_create_view
cargo test -p sqlite-rust --test req_031_index_maintenance
cargo test -p sqlite-rust --test req_032_type_affinity
cargo test -p sqlite-rust --test req_033_drop_index
cargo test -p sqlite-rust --test req_034_drop_view
cargo test -p sqlite-rust --test req_035_collate
cargo test -p sqlite-rust --test req_038_boolean_where
cargo test -p sqlite-rust --test req_039_in_list
cargo test -p sqlite-rust --test req_040_alter_table_add_column
cargo test -p sqlite-rust --test req_041_insert_default
cargo test -p sqlite-rust --test req_045_arithmetic
cargo test -p sqlite-rust --test req_048_fuzz_regressions
cargo test -p sqlite-rust --test req_049_compound_select
cargo test -p sqlite-rust --test req_050_cte_with
cargo test -p sqlite-rust --test req_051_recursive_cte
cargo test -p sqlite-rust --test req_052_table_aliases
cargo test -p sqlite-rust --test req_053_from_subquery
cargo test -p sqlite-rust --test req_082_alter_table_rename_drop_column
cargo test -p sqlite-rust --test req_083_date_time_functions
cargo test -p sqlite-rust --test req_084_create_drop_trigger_after
cargo test -p sqlite-rust --test req_085_before_instead_of_trigger_timing
cargo test -p sqlite-rust --test req_086_rowid_select_projection
cargo test -p sqlite-rust --test req_087_scalar_subquery_dml
cargo test -p sqlite-rust --test req_088_fk_parent_enforcement_dml_subquery
cargo test -p sqlite-rust --test req_089_insert_select
cargo test -p sqlite-rust --test req_090_between_predicate
cargo test -p sqlite-rust --test bug_003_update_ipk_relocation
cargo test -p sqlite-rust --test bug_003_adversarial_probes
cargo test -p sqlite-rust --test mvp_sqlite_compat
cargo test -p sqlite-rust --test phase0_smoke_perf -- --nocapture

Per-phase differential smoke + performance test

Each build phase has its own differential smoke test that runs every feature implemented up to that phase against the real /usr/bin/sqlite3 3.45.1 oracle and, in the same run, measures performance. For each workload the test prints a Rust-vs-sqlite3 ratio (rust_median / sqlite3_median) and flags any operation more than 10 % slower. Phase 0 is phase0_smoke_perf; later phases reuse the shared harness in tests/support/.

Correctness is a hard gate (a result mismatch or failed integrity_check fails the test). The performance signal is report-only for now — the ≤10 %-slower target is measured and reported but is not enforced until the Performance phase, and is never pursued at the expense of compatibility. The reported ratio includes a measured sqlite3 process-startup baseline so the fairness of the comparison is transparent (run with -- --nocapture to see the table).

Fuzzing

MarsmoduleDB ingests two untrusted input surfaces — SQL strings and SQLite-compatible database bytes — and the product promise is memory safety: every input must produce either Ok or a typed Err, never a panic, hang, or unbounded allocation. A cargo-fuzz lane (fuzz/) covers the four highest-risk pure parse/decode paths.

The fuzz crate is excluded from the workspace (exclude = ["fuzz"]): the normal stable lane above (build, test, clippy, fmt) never compiles it and never requires nightly. Running the fuzzer needs the nightly toolchain plus cargo-fuzz:

rustup toolchain install nightly
cargo install cargo-fuzz

Build all targets, then run a short smoke (30 s) against each:

cargo +nightly fuzz build
cargo +nightly fuzz run sql_tokenize_parse -- -max_total_time=30
cargo +nightly fuzz run db_header_parse    -- -max_total_time=30
cargo +nightly fuzz run btree_leaf_read    -- -max_total_time=30
cargo +nightly fuzz run record_decode      -- -max_total_time=30

Each target applies an explicit input bound (64 KiB SQL cap; fixed 100-byte header; ≤ 65536-byte page; claimed-length-vs-remaining check before any allocation) and accepts both Ok and a typed Err — only a panic/hang/overflow fails the run.

The same adversarial inputs are replayed on stable by cargo test -p sqlite-rust --test req_048_fuzz_regressions, which is the non-recurrence proof that runs in the normal CI lane; any crash a campaign discovers is reduced into that battery.

Cadence: the fuzz campaign runs at each phase end (alongside the per-phase differential smoke + performance gate) — not on every CI run and not on every REQ.

Lint & format

cargo clippy -p sqlite-rust --all-targets -- -D warnings
cargo fmt --all --check

Usage

The recommended entry point for an embedding consumer is Connection — open a real .db file, then execute DML/DDL or query a SELECT. Parameters are bound with ? placeholders and supplied as a &[Value]; a bound value is a typed literal, never concatenated into the SQL, so injection is impossible by construction (OWASP A03):

use std::path::Path;
use sqlite_rust::{Connection, Value};

let mut conn = Connection::open(Path::new("my.db"))?;

// Parameterized INSERT — `?` placeholders bind the &[Value] in order.
let affected = conn.execute(
    "INSERT INTO users(id, name) VALUES (?, ?)",
    &[Value::Integer(1), Value::Text("alice".into())],
)?; // == 1

// Parameterized SELECT — `Rows` is an iterator of `Row`; `get::<T>` is typed.
let rows = conn.query("SELECT id, name FROM users WHERE id = ?", &[Value::Integer(1)])?;

// Scalar functions — usable in projection, WHERE, ORDER BY.
let rows = conn.query(
    "SELECT UPPER(name), LENGTH(name), COALESCE(note, 'none') FROM t",
    &[],
)?;
for row in rows {
    let id: i64 = row.get(0)?;
    let name: String = row.get(1)?;
    // a NULL column reads back as None via get::<Option<T>>
    println!("{id} {name}");
}

// Math scalar functions — ABS, ROUND, scalar MAX/MIN, RANDOM.
// ABS: NULL→NULL; INTEGER stays INTEGER (overflow → ExecError); TEXT/BLOB coerced to numeric.
// ROUND: always returns REAL; half-away-from-zero; default digits=0.
// MAX(a,b)/MIN(a,b): 2-arg scalar form; NULL if any arg is NULL.
// RANDOM(): non-deterministic full-range i64; type verified via TYPEOF.
let rows = conn.query(
    "SELECT ABS(val), ROUND(val, 2), MAX(val, 0.0), RANDOM() FROM t WHERE ABS(val) > 5 ORDER BY ABS(val)",
    &[],
)?;

// printf / format — SQLite format-string formatting (format() is a byte-identical
// alias). Conversions %d %i %s %x %X %o %f %e %E %g %G %c %q %Q %w %%, with
// flags/width/precision (e.g. %05d, %-8.2f, %.3s, %*d). Pinned to sqlite3 output,
// not C printf (%c is the first char of the arg-as-text; missing arg = NULL).
let rows = conn.query(
    "SELECT printf('%s=%05d', name, id), format('%.2f', val) FROM t",
    &[],
)?;

// Date/time scalar functions — date / time / datetime / julianday / strftime,
// behaviour-matched to sqlite3 3.45.1 on a pure-std Julian-Day carrier. The
// time-value is a date/datetime string, a bare time, a numeric Julian Day, or
// 'now' (an omitted time-value defaults to 'now'); modifiers fold left-to-right.
// Month/year arithmetic overflow-normalizes (Jan-31 +1 month -> Mar-02, not
// Feb-29). Any malformed input, or a Julian Day pushed outside [0, 5373484.5),
// returns SQL NULL — never an error, never a panic.
let rows = conn.query(
    "SELECT date('2024-01-31', '+1 month'),          -- '2024-03-02'
            datetime('2024-03-05 14:30:00', 'start of day'), -- '2024-03-05 00:00:00'
            strftime('%Y-W%W', '2024-03-05'),        -- '2024-W10'
            julianday('2024-03-05')                  -- 2460374.5 (REAL Julian Day)
     FROM t",
    &[],
)?;

// CASE WHEN conditional expressions — searched form (condition per branch) and
// simple form (subject evaluated once, compared to each WHEN value).
// NULL condition → false; NULL subject never matches NULL WHEN; lazy short-circuit.
// Usable in SELECT projection, WHERE, ORDER BY, and as function arguments.
let rows = conn.query(
    "SELECT id,
            CASE WHEN score >= 90 THEN 'A'
                 WHEN score >= 80 THEN 'B'
                 WHEN score >= 70 THEN 'C'
                 ELSE 'F'
            END AS grade
     FROM students
     ORDER BY CASE WHEN score IS NULL THEN 1 ELSE 0 END, score DESC",
    &[],
)?;

// LIKE / GLOB string pattern matching — value-producing infix operators.
// LIKE: case-insensitive ASCII, `%` = any run, `_` = one char, optional ESCAPE.
// GLOB: case-sensitive, `*` = any run, `?` = one char, `[a-z]`/`[^0-9]` classes.
// NOT LIKE / NOT GLOB negate; a NULL operand yields NULL (not 0). Usable in
// SELECT projection, WHERE, and CASE WHEN. Bound `?` patterns stay inert data.
let rows = conn.query(
    "SELECT name FROM files
     WHERE name LIKE '%.txt' AND name NOT GLOB '[._]*'",
    &[],
)?;
let escaped = conn.query(
    "SELECT 'sale_price' LIKE 'sale\\_price' ESCAPE '\\' FROM t",
    &[],
)?;

// Boolean WHERE — OR, unary NOT, and parenthesised groups, with SQLite
// precedence (OR < AND < NOT < comparison) and three-valued NULL logic
// (a NULL operand is Unknown; NOT (v = 2) excludes NULL v). Drives
// SELECT / UPDATE / DELETE. A pure AND-chain stays byte-identical.
let picked = conn.query(
    "SELECT a, b, c FROM t
     WHERE (a = 1 OR b = 2) AND NOT (c = 9)
     ORDER BY a, b, c",
    &[],
)?;

// COLLATE — choose the text comparison/sort rule. BINARY (default) is byte
// compare; NOCASE is case-insensitive over ASCII A-Z; RTRIM ignores trailing
// spaces. An explicit operand `COLLATE` overrides a column's declared collation;
// a column declared `name TEXT COLLATE NOCASE` applies NOCASE implicitly.
let by_name = conn.query(
    "SELECT name FROM users
     WHERE name = 'foo' COLLATE NOCASE
     ORDER BY name COLLATE NOCASE",
    &[],
)?;

// CREATE INDEX — builds an on-disk index B-tree (0x0A leaf, depth-1) over a
// table, back-filling every existing row in key order. UNIQUE enforces key
// uniqueness (NULL keys exempt) on back-fill and on later INSERTs; a colliding
// INSERT returns ExecError::UniqueConstraintViolation with the table unchanged.
// IF NOT EXISTS on an existing index is a no-op. The file passes the real
// sqlite3 `PRAGMA integrity_check`. UPDATE/DELETE on an indexed table now
// maintain every index incrementally (REQ-031): a DELETE drops each deleted
// row's index entries; an UPDATE moves the entries of indexes whose key changed
// (UNIQUE recheck before any write) and skips unchanged-key indexes.
conn.execute("CREATE INDEX idx_email ON users (email)", &[])?;
conn.execute("CREATE UNIQUE INDEX idx_user_dept ON users (user_id, dept)", &[])?;

// DROP INDEX — the inverse of CREATE INDEX (REQ-033): removes the index's
// type='index' sqlite_master row by rebuilding page 1's schema leaf, frees its
// single 0x0A leaf root page to the SQLite freelist, and bumps the file change
// counter + schema cookie like every schema change — leaving the table and any
// sibling indexes untouched. IF EXISTS on an absent index is a byte-unchanged
// no-op; a missing index without it returns ExecError::UnknownIndex with the
// file unchanged. An interior-root (multi-page) index is out of scope and
// returns ExecError::NotSupported before any write. Post-drop files pass the
// real sqlite3 `PRAGMA integrity_check`.
conn.execute("DROP INDEX idx_email", &[])?;
conn.execute("DROP INDEX IF EXISTS maybe_missing", &[])?;

// CREATE VIEW — stores a named SELECT in sqlite_master (type='view', rootpage=0,
// the CREATE VIEW text verbatim). The view owns no b-tree; querying it expands
// the stored SELECT at execution time ("view = derived table"), then applies the
// outer query's WHERE / ORDER BY / LIMIT / projection. An optional rename list
// renames the output columns. IF NOT EXISTS on an existing view is a no-op; a
// duplicate without it returns ExecError::ViewAlreadyExists with the file
// unchanged. A view over a missing base table is accepted and fails only at
// query time (ExecError::UnknownTable); a self-referential view returns
// ExecError::ViewRecursion rather than overflowing the stack. The file is
// byte-identical to real sqlite3's and passes `PRAGMA integrity_check`.
conn.execute("CREATE VIEW active_users AS SELECT name, email FROM users", &[])?;
conn.execute("CREATE VIEW dept_names (label) AS SELECT dept FROM users", &[])?;
let rows = conn.query("SELECT email FROM active_users WHERE name = ?", &[name])?;

// DROP VIEW — the inverse of CREATE VIEW (REQ-034): removes the view's
// type='view' sqlite_master row by rebuilding page 1's schema leaf, and bumps
// the file change counter + schema cookie like every schema change. A view owns
// no B-tree (rootpage=0), so — unlike DROP TABLE/INDEX — no page is freed. Tables
// and indexes survive intact. IF EXISTS on an absent view is a byte-unchanged
// no-op; a missing view without it returns ExecError::UnknownView with the file
// unchanged. Post-drop files pass the real sqlite3 `PRAGMA integrity_check`.
conn.execute("DROP VIEW active_users", &[])?;
conn.execute("DROP VIEW IF EXISTS maybe_missing", &[])?;

// CREATE TRIGGER / DROP TRIGGER — BEFORE / AFTER / INSTEAD OF, FOR EACH ROW triggers
// (REQ-084, REQ-085). A trigger is stored in sqlite_schema (type='trigger',
// rootpage=0, the CREATE TRIGGER text verbatim from the name token with the trailing
// ';' stripped, incl. the timing keyword) and fires once per affected row — inside
// the triggering statement's implicit transaction. OLD.col / NEW.col bind to the
// pre/post-image of the row (NEW only for INSERT, OLD only for DELETE, both for
// UPDATE); the optional WHEN condition is checked per row (identically for all three
// timings) and the body runs only when it is true. A body statement is an
// INSERT/UPDATE/DELETE whose VALUES/SET are literals or OLD/NEW references (no
// arithmetic there); full expressions live in WHEN and in a body statement's own WHERE.
conn.execute("CREATE TABLE audit(action TEXT, old_price INTEGER, new_price INTEGER)", &[])?;
conn.execute(
    "CREATE TRIGGER price_audit AFTER UPDATE OF price ON products \
     WHEN NEW.price <> OLD.price \
     BEGIN INSERT INTO audit VALUES ('update', OLD.price, NEW.price); END",
    &[],
)?;
// BEFORE (tables only): fires per row BEFORE that row's physical write, with OLD/NEW
// bound to the pre-write image; firing interleaves with the write per row (all BEFORE,
// write, all AFTER, next row). On a BEFORE INSERT the auto-assigned rowid/ipk slot is
// the -1 sentinel (the real rowid is not yet assigned); a typed body error aborts the
// row's write and rolls the whole statement back (this engine has no RAISE() — any
// body-DML error suffices). SQLite cannot modify NEW in a trigger (observe-or-abort).
conn.execute(
    "CREATE TRIGGER stock_guard BEFORE INSERT ON products \
     WHEN NEW.qty < 0 \
     BEGIN INSERT INTO reject_log VALUES (NEW.sku); END",
    &[],
)?;
// INSTEAD OF (views only): makes an otherwise non-updatable view's DML succeed by
// running the body as the ENTIRE effect — no physical write to the view or its base,
// and changes() for the outer view DML is 0. INSTEAD OF INSERT binds NEW to the tuple;
// INSTEAD OF UPDATE fires per view row the WHERE selects with OLD = the view row and
// NEW = OLD merged with the SET list (an unset column keeps its OLD value); INSTEAD OF
// DELETE fires per matched view row with OLD bound.
conn.execute("CREATE VIEW active_products AS SELECT sku, qty FROM products WHERE qty > 0", &[])?;
conn.execute(
    "CREATE TRIGGER av_ins INSTEAD OF INSERT ON active_products \
     BEGIN INSERT INTO products(sku, qty) VALUES (NEW.sku, NEW.qty); END",
    &[],
)?;
// Placement is timing×target-kind gated: BEFORE/AFTER attach only to tables, INSTEAD
// OF only to views — the wrong combination is a typed ExecError at CREATE time.
// Multiple triggers on the same table/event fire in REVERSE creation order (last
// created fires first), interleaved per row. UPDATE OF price fires iff `price` is in
// the SET list, regardless of whether the value changes. Only physically-written
// rows fire (an OR IGNORE-skipped row does not); a trigger-body error rolls back the
// whole triggering statement. recursive_triggers is effectively OFF — a trigger will
// not re-fire itself, so a self-inserting trigger terminates, while cross-table
// chains fire normally. DROP TABLE does not cascade-remove triggers (see Known
// limitations).
conn.execute("CREATE TRIGGER IF NOT EXISTS price_audit AFTER UPDATE ON products BEGIN INSERT INTO audit VALUES ('noop', 0, 0); END", &[])?; // existing name → no-op
conn.execute("DROP TRIGGER price_audit", &[])?;
conn.execute("DROP TRIGGER IF EXISTS maybe_missing", &[])?; // missing name → no-op

// CREATE VIRTUAL TABLE … USING vec0 — the native, pure-Rust vec0 vector store
// (REQ-065, phase 1). The vtab's own sqlite_master row mirrors sqlite-vec
// (type='table', rootpage=0, verbatim CREATE VIRTUAL TABLE text); the f32[N]
// vectors live in a backing rowid table "<vtab>__vchunks" created through the
// ordinary CREATE TABLE path, so integrity_check and close/reopen come for free.
// A vector is supplied as a JSON array text ('[…]') or a little-endian f32 BLOB;
// both store the identical canonical N·4-byte little-endian f32[N] BLOB, and a
// SELECT reads it back byte-for-byte. Dimension mismatch, malformed JSON, a BLOB
// whose byte length is not a multiple of 4, an unknown module, or a non-float[N]
// column are typed errors (ExecError::VirtualTable / ParseError) — never a panic.
conn.execute("CREATE VIRTUAL TABLE vec_chunks USING vec0(embedding float[4])", &[])?;
conn.execute("INSERT INTO vec_chunks(rowid, embedding) VALUES (1, '[1,2,3,4]')", &[])?;
let rows = conn.query("SELECT embedding FROM vec_chunks", &[])?; // -> the f32[4] BLOB
// vec0 has no conflict-replace (matching sqlite-vec 0.1.9): INSERT OR REPLACE /
// OR IGNORE / a duplicate INSERT on an existing rowid all error; replace a vector
// with DELETE then INSERT.
conn.execute("DELETE FROM vec_chunks WHERE rowid = 1", &[])?;

// ALTER TABLE ADD COLUMN — the first schema-mutation DDL (REQ-040). Splices the
// new column into the verbatim stored CREATE TABLE sql (re-encoding only that one
// sqlite_master row, every other cell verbatim) and bumps the change counter +
// schema cookie. No existing data page is rewritten: a row stored before the ALTER
// is narrower than the new schema, so its missing trailing column is filled at READ
// time with the column's DEFAULT (or NULL) — composing with read-affinity (a REAL
// DEFAULT 1 reads back as 1.0). COLUMN is optional. PRIMARY KEY / UNIQUE, a
// NOT NULL column without a non-NULL DEFAULT, and a duplicate column name are all
// rejected before any write (file byte-unchanged); a missing table returns
// ExecError::UnknownTable. Files stay byte-faithful — real sqlite3 reads the spliced
// schema and passes `PRAGMA integrity_check`.
conn.execute("ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 0", &[])?;
conn.execute("ALTER TABLE users ADD nickname TEXT", &[])?; // COLUMN keyword optional

// ALTER TABLE RENAME TO / RENAME COLUMN / DROP COLUMN (REQ-082) complete the
// four-form ALTER grammar on top of ADD COLUMN. The stored CREATE sql is rewritten
// by a token-aware identifier walk (re-tokenise, substitute only the identifier
// tokens that reference the target name, copy every other byte verbatim — never a
// naive string replace), matching sqlite3 3.45.1 byte-for-byte. RENAME TO also
// rewrites dependent index/view sql and index tbl_name (new table name emitted
// double-quoted); RENAME COLUMN rewrites the column def, inline and table-level
// CHECK exprs, and dependent index sql. DROP COLUMN removes the column's def
// segment and rewrites every data row (shrink-only: a narrower record never
// overflows, so no page split/rebalance; rowid preserved), verified on multi-page
// and overflow-row tables. All rejects pre-validate before any write (file
// byte-unchanged): RENAME TO onto an existing table/index name; RENAME COLUMN of a
// missing column or onto an existing name; DROP COLUMN of a PK / column-level
// UNIQUE / the last remaining column / a nonexistent column, or of a column still
// referenced by a remaining table-level UNIQUE/CHECK/FK or an index. COLUMN is
// optional in both RENAME COLUMN and DROP COLUMN.
conn.execute("CREATE TABLE t(a INTEGER, b TEXT, c INTEGER)", &[])?;
conn.execute("ALTER TABLE t RENAME TO t2", &[])?;
conn.execute("ALTER TABLE t2 RENAME COLUMN b TO bb", &[])?; // COLUMN optional
conn.execute("ALTER TABLE t2 DROP COLUMN c", &[])?; // COLUMN optional
// Out of scope (documented, deferred): renaming/dropping a column that another
// table's FOREIGN KEY references (the oracle propagates the rename into the child
// table's REFERENCES clause), and generated columns (no `AS (expr)` parser support).
// See docs/architecture/ADR-REQ-082-alter-table-rename-drop-column.md.

// INSERT honors column DEFAULT for omitted columns (REQ-041). An INSERT that omits
// a column stores that column's DEFAULT constant (integer/real/text/NULL) rather
// than always NULL; the injected default receives store-time affinity for free
// (REAL DEFAULT 1 stored as 1.0). An omitted column with no DEFAULT stores NULL; an
// omitted NOT NULL column with no usable (non-NULL) default returns a typed
// ExecError::NotNullConstraintViolation with the file byte-unchanged. So after the
// ALTER above, INSERT INTO users(name) VALUES('x') stores age=0 (the DEFAULT), and a
// new INSERT now agrees with the read-time pad applied to pre-ALTER rows.
conn.execute("INSERT INTO users(name) VALUES ('x')", &[])?; // age defaults to 0

use sqlite_rust::{Pager, read_schema};

let mut pager = Pager::open("my.db")?;
// Returns HashMap<String, u32>: table name → root page number
let schema = read_schema(&mut pager)?;
let root = schema["users"];

use sqlite_rust::{tokenize, Token, TokenKind, Keyword};

// Returns Vec<Token> or TokenizeError
let tokens = tokenize("SELECT * FROM users")?;
// tokens[0] == Token { kind: TokenKind::Keyword(Keyword::Select), lexeme: "SELECT".to_string() }

use sqlite_rust::{parse, SelectStmt, ParseError};

// Returns SelectStmt or ParseError
let tokens = tokenize("SELECT * FROM users")?;
let stmt = parse(&tokens)?;
// stmt.table == "users"; stmt.columns == [SelectColumn::Star]

use sqlite_rust::{execute, parse, tokenize, ExecError, Pager};

// Evaluate a parsed SELECT against a real database → Vec<Row> (or ExecError)
let mut pager = Pager::open_readonly("my.db")?;
let stmt = parse(&tokenize("SELECT name FROM users WHERE id = 3 LIMIT 1")?)?;
let rows = execute(&stmt, &mut pager)?;
// rows[0].values() == [Value::Text("carol".to_string())]

use sqlite_rust::{execute, parse, tokenize, Pager};

// ORDER BY: rows are sorted before LIMIT/projection are applied
let mut pager = Pager::open_readonly("my.db")?;
let stmt = parse(&tokenize("SELECT * FROM users ORDER BY name ASC, id DESC LIMIT 3")?)?;
let rows = execute(&stmt, &mut pager)?;
// rows are sorted primarily by name ascending, ties broken by id descending,
// matching the row sequence the real sqlite3 CLI returns for the same query

use sqlite_rust::{execute, parse, tokenize, Pager};

// Aggregate functions with GROUP BY: groups rows, computes COUNT/SUM/MIN/MAX/AVG per group.
// Results are cross-checked row-for-row against the real sqlite3 CLI.
let mut pager = Pager::open_readonly("scores.db")?;
let stmt = parse(&tokenize(
    "SELECT name, COUNT(*), SUM(score) FROM scores GROUP BY name ORDER BY SUM(score) DESC"
)?)?;
let rows = execute(&stmt, &mut pager)?;
// One row per distinct name, ordered by SUM(score) descending.
// COUNT(*) returns 0 for empty tables; SUM/AVG/MIN/MAX return NULL for all-NULL groups.
// Bare non-aggregate column without GROUP BY returns Err(ExecError::InvalidQuery).

use sqlite_rust::{execute, parse, tokenize, Pager};

// JOIN: combines two tables row-by-row (nested loop). Qualified `table.column`
// references resolve across both tables; an unqualified name shared by both is
// Err(ExecError::AmbiguousColumn). LEFT JOIN keeps every left row, NULL-filling
// the right columns when nothing matches. Cross-checked against the real sqlite3 CLI.
let mut pager = Pager::open_readonly("company.db")?;
let stmt = parse(&tokenize(
    "SELECT employees.name, departments.name FROM employees \
     LEFT OUTER JOIN departments ON employees.dept_id = departments.id \
     ORDER BY employees.name"
)?)?;
let rows = execute(&stmt, &mut pager)?;
// stmt.joins == vec![JoinSpec { kind: JoinKind::LeftOuter, table: "departments", alias: None, on: ... }]
// Bare JOIN == INNER JOIN; an unknown join table returns Err(ExecError::UnknownTable).

// FROM/JOIN table aliases (`FROM t [AS] x`, `JOIN u [AS] y`) name each source; an
// alias shadows the table name, so the original name (`employees.name` above) then
// resolves to Err(ExecError::UnknownTable). Aliasing one table twice expresses a
// self-join — two distinct relations. Cross-checked against the real sqlite3 CLI.
let stmt = parse(&tokenize(
    "SELECT e.name, m.name FROM employees AS e \
     JOIN employees AS m ON e.mgr_id = m.id ORDER BY e.name"
)?)?;
let rows = execute(&stmt, &mut pager)?;

use sqlite_rust::{parse_insert, tokenize, InsertStmt, Value};

// Returns InsertStmt or ParseError; values are interpreted into Value at parse time.
// The Insert/Into/Values keywords are new Keyword variants this REQ.
let stmt = parse_insert(&tokenize("INSERT INTO users (id, name) VALUES (1, 'alice')")?)?;
// stmt.table == "users"
// stmt.columns == Some(vec!["id".to_string(), "name".to_string()])
// stmt.values  == vec![Value::Integer(1), Value::Text("alice".to_string())]

use sqlite_rust::{execute_insert, parse_insert, tokenize, ExecError, Pager};

// Encode a parsed INSERT's row and append it to the table's leaf page → the
// assigned auto-rowid (1 for an empty table, else max(rowid) + 1). The page is
// written through the Pager, producing bytes identical to what sqlite3 writes.
let mut pager = Pager::open("my.db")?;
let stmt = parse_insert(&tokenize("INSERT INTO users VALUES (4, 'newbie', 99, 1.25)")?)?;
let rowid = execute_insert(&stmt, &mut pager)?; // == 4

// Scope: a full leaf triggers a depth-1 B-tree split (REQ-010) — the insert
// succeeds and grows the file by two pages. Only an interior-root table whose
// rightmost leaf is full returns Err(ExecError::PageFull). An unknown table is
// ExecError::UnknownTable.

use sqlite_rust::{parse_delete, tokenize, DeleteStmt};

// Returns DeleteStmt or ParseError
let stmt = parse_delete(&tokenize("DELETE FROM users WHERE id = 3")?)?;
// stmt.table == "users"; stmt.where_clause == Some(Expr::BinOp { ... })

use sqlite_rust::{execute_delete, parse_delete, tokenize, ExecError, Pager};

// Remove matching rows → count of deleted rows (any tree depth: single-leaf, depth-1 interior-root, or multi-level depth≥2).
let mut pager = Pager::open("my.db")?;
let stmt = parse_delete(&tokenize("DELETE FROM users WHERE id = 3")?)?;
let deleted = execute_delete(&stmt, &mut pager)?; // == 1

use sqlite_rust::{parse_update, tokenize, UpdateStmt, Value};

// Returns UpdateStmt or ParseError; SET RHS values are interpreted into Value at parse time.
let stmt = parse_update(&tokenize("UPDATE users SET name = 'bob' WHERE id = 2")?)?;
// stmt.table == "users"
// stmt.assignments == vec![("name".to_string(), Value::Text("bob".to_string()))]
// stmt.where_clause == Some(Expr::BinOp { ... })

use sqlite_rust::{execute_update, parse_update, tokenize, ExecError, Pager};

// Modify matching rows → count of updated rows (any tree depth: single-leaf, depth-1 interior-root, or multi-level depth≥2).
// Matched rows are re-encoded under their original rowids (unless the SET assigns a new value to an
// INTEGER PRIMARY KEY column, which relocates the row's b-tree cell key to the new rowid); unmatched rows are kept verbatim.
let mut pager = Pager::open("my.db")?;
let stmt = parse_update(&tokenize("UPDATE users SET name = 'robert' WHERE id = 2")?)?;
let updated = execute_update(&stmt, &mut pager)?; // == 1

Requirements

• Rust stable 1.96.0+
• sqlite3 CLI (3.45.1+) — required for E2E integrity-check tests

Contribution Policy

MarsmoduleDB is public open source software, but it is not an open contribution project in the usual "send a pull request" sense. Unsolicited implementation pull requests are not accepted.

Bug reports, SQLite compatibility findings, reduced fuzzing reproducers, and documentation corrections are welcome. See CONTRIBUTING.md for the full policy.

Funding

MarsmoduleDB is developed with an agent-team workflow powered by the Marsmodule VDD Plugin. This gives the project strong specification, review, testing, and compatibility discipline, but it also has real compute and token costs.

If MarsmoduleDB is useful to you or your organization, consider sponsoring its development through GitHub Sponsors. Sponsorship helps fund SQLite compatibility work, fuzz testing, security hardening, documentation, and long-running validation against real SQLite behavior.

License and Warranty

MarsmoduleDB is licensed under the Apache License 2.0. See LICENSE for the full legal terms and NOTICE for attribution notices.

MarsmoduleDB is provided on an "AS IS" basis, without warranties or conditions of any kind. Users are responsible for evaluating whether it is appropriate for their use case, including production use, data durability, compatibility, and security requirements.

MarsmoduleDB is an independent SQLite-compatible Rust database engine. It is not affiliated with, sponsored by, or endorsed by the SQLite project. SQLite is a trademark of Hipp, Wyrick & Company, Inc.