sqlite

sqlite — a pure-Mojo SQLite-format engine (read + SELECT + write)

Reads and writes real SQLite 3 database files — no FFI, no libsqlite3 linked. It implements the on-disk file format (varints, the record/serial-type codec, table B-trees, the sqlite_master schema), a small SQL SELECT engine, and a writer that emits databases the real sqlite3 opens cleanly.

This is a format-compatible subset, not a reimplementation of SQLite. The goal is interop: read DBs the real engine wrote, and write DBs it can read. Every phase is verified against the system libsqlite3 3.45 via Python sqlite3 as an independent oracle.

Modules

Module	What it is
value.mojo	Value — the five storage classes (NULL/INT/REAL/TEXT/BLOB) + typed accessors.
format.mojo	On-disk primitives: varints (1–9 byte), the record/serial-type codec (encode_record/decode_record), and the 100-byte DB header (parse_header).
pager.mojo	Pager — fixed-size page access over the file (1-based page numbers).
btree.mojo	walk_table — table B-tree traversal (leaf 0x0D + interior 0x05), in rowid order, with overflow-page reassembly.
db.mojo	Database — open, table_names, columns, read_table, schema_sql; resolves sqlite_master + the INTEGER PRIMARY KEY rowid-alias.
sql.mojo	Tokenizer + recursive-descent parser → a SELECT AST.
query.mojo	execute_select(db, sql) -> ResultSet — WHERE (AND/OR/parens, = != < <= > >=), ORDER BY, LIMIT, projection.
writer.mojo	DbWriter — create_table + insert + save; emits a valid SQLite file (header, sqlite_master, table B-trees with leaf-splitting under an interior root).

Example

from sqlite.db import Database
from sqlite.query import execute_select
from sqlite.writer import DbWriter
from sqlite.value import Value

# write a database the real sqlite3 can open
var w = DbWriter.create()
var cols = List[String](); cols.append("a"); cols.append("b")
w.create_table("t", "CREATE TABLE t(a INTEGER, b TEXT)", cols)
var row = List[Value](); row.append(Value.integer(1)); row.append(Value.text("hi"))
w.insert("t", row)
w.save("out.db")

# read it back + query
var db = Database.open("out.db")
var rs = execute_select(db, "SELECT a, b FROM t WHERE a > 0 ORDER BY a LIMIT 10")

Verified (real libsqlite3 3.45 via Python sqlite3, measured — not asserted)

# from the repo root (-I .); fixtures are generated by the *_fixtures.py
pixi run mojo run -I . sqlite/tests/format_test.mojo                                  # 25/25 primitives
python3 sqlite/tests/reader_fixtures.py && pixi run mojo run -I . sqlite/tests/reader_test.mojo   # 41/41
python3 sqlite/tests/query_fixtures.py  && pixi run mojo run -I . sqlite/tests/query_test.mojo    # 7/7 vs Python
pixi run mojo run -I . sqlite/tests/writer_test.mojo && python3 sqlite/tests/writer_check.py      # writer + sqlite3 oracle

• format 25/25 — varint vectors + round-trip, record codec (all types incl. signed ints + serial-8/9), header parsed from a real Python-sqlite3 DB.
• reader 41/41 — reads a small mixed-type table, a 2000-row table spanning an interior page + 11 leaves, an overflow row (5000-byte text + 6000-byte blob reassembled byte-exact), and the schema — all matching Python SELECT.
• query 7/7 — SELECT/WHERE(AND/OR/parens)/ORDER BY/LIMIT/projection results match Python sqlite3 cell-for-cell.
• writer — Mojo-written DBs open in real sqlite3 with PRAGMA integrity_check = ok, SELECT * returns the exact inserted rows, and a 500-row table writes a genuine interior-page B-tree (verified by page-byte inspection + .dump). Round-trips through this lib's own reader too.

Scope / limitations (honest)

This is an interop subset, deliberately not full SQLite:

• Reader: table B-trees only (no index B-trees); reads the main DB file directly (a DB with an un-checkpointed WAL won't reflect WAL contents); no freelist traversal; assumes reserved-bytes-per-page = 0 (the sqlite3 default).
• SQL: read-only SELECT with single-table FROM, column/* projection, WHERE (column-vs-literal/column, AND/OR/parens), single-column ORDER BY, LIMIT. No joins, aggregates/GROUP BY, functions, subqueries, IN/LIKE/BETWEEN/ IS NULL, DISTINCT, or arithmetic select items.
• Writer: CREATE TABLE + INSERT only (no DELETE/UPDATE, indexes, or WAL); rows must fit a page (no overflow-page writing); one interior B-tree level (hundreds of thousands of rows, not unbounded); sqlite_master is single-leaf.
• For a production database, FFI to libsqlite3 (the real engine) is the right choice; this lib is for reading/writing SQLite files in pure Mojo where format interop — not a full SQL engine — is what's needed.