SQLite file format · from scratch

A database engine that speaks real SQLite, byte for byte.

SclJet is a SQLite-compatible storage engine written entirely in ScalaScript — 22 modules, no C, no bundled library. It reads and writes ordinary .db files, and the reference sqlite3 reads them back, runs PRAGMA integrity_check, and writes into them. The proof isn't a claim; it's a diff.

● reads real SQLite files ● writes valid SQLite files ● JDBC driver + façade ● runs on interpreter · JS · JVM · native

offset 0 — every SclJet file opens with these 16 bytesthe SQLite magic header

␠

␀

The round trip

Two engines, one file, in both directions. A file written by the reference sqlite3 is queried by SclJet and comes back byte-identical to the oracle; a file written by SclJet is opened by sqlite3, passes its integrity check, and is written into.

→ sqlite3 writes · SclJet reads

# SELECT · WHERE on the rowid · GROUP BY + SUM · ORDER BY LIMIT
1|ann|eng|100        ✓
7|bob|sales|250      ✓
9|cat|eng|300        ✓
eng|2|400            ✓
bob|250   ·   cat, bob

✓ diff vs sqlite3 — byte-identical

← SclJet writes · sqlite3 reads

PRAGMA integrity_check  →  ok
SELECT id,item,qty,rowid
  1|bolt|480|1       (UPDATE applied)
  7|nut|1200|7       (IPK 7 == rowid 7)
INSERT ... 99,'screw'  →  sqlite3 writes our file

✓ a valid SQLite database, accepted end‑to‑end

A first taste

Create a database, add a row, read it back. Imports are [names](module) markdown links, and a connection is immutable — every write returns a new image, threaded forward.

scljet-hello.ssc

[SqlInteger, SqlText, buildTableDatabase](std/scljet/index.ssc)
[jdbcOpen, jdbcExecuteUpdate, jdbcExecuteQuery,
 rsNext, rsHasRow, rsGetLong, rsGetString](std/scljet/jdbc.ssc)

// each write returns a new image, threaded forward
def run(c: JdbcConnection, sql: String): JdbcConnection =
  jdbcExecuteUpdate(c, sql) match
    case Right(u) => u.conn

// create a database, add a book, read them back
buildTableDatabase(512, 1, 1, "books",
  "CREATE TABLE books(id INTEGER PRIMARY KEY, title TEXT)",
  List(List(SqlInteger(1L), SqlText("SICP")))) match
  case Right(image) =>
    val c = run(jdbcOpen(image),
      "INSERT INTO books VALUES (2, 'TAPL')")
    jdbcExecuteQuery(c,
      "SELECT id, title FROM books ORDER BY id") match
      case Right(rs) =>
        var r = rsNext(rs)
        while rsHasRow(r) do
          println(rsGetLong(r,1).toString + " · " + rsGetString(r,2))
          r = rsNext(r)

$ ssc run scljet-hello.ssc

1 · SICP
2 · TAPL

$ ssc run scljet-file.ssc # interop

SclJet wrote books.db
sqlite3 integrity_check  →  ok
sqlite3 reads: 1 · SICP · 2 · TAPL
sqlite3 adds a row  →  now 3 books

Real output, byte-identical on the interpreter, JS, and the default ssc run. The full example adds an UPDATE and a bound ? parameter; the interop demo (scljet-file.ssc) writes a real .db and lets the reference sqlite3 read it, check it, and write into it.

Every value has an address

The engine is the foundation; the idea it carries is addressing. An address is the link between a logical location and a physical one — and the standing question is always the same: what does this bit mean, here?

Take an INTEGER PRIMARY KEY. Real SQLite stores nothing for it in the record — the value lives in the rowid. So the two halves of the address genuinely disagree, and only the link between them is correct:

emp/7/id

logical — meaning

the value, taken from the row's rowid

physical — the bytes

0 bytes

the record field is NULL; nothing is stored here

both are true at once. read → INTEGER 7 · fromRowid=true · stable=true — reporting the physical bit as the logical value is the exact bug this model exists to prevent.

address

a name for one leaf — table/rowid/column, or a path into a document

type · value

the format's own type where known; Raw(n) — n bits — where not. Never a guess.

one protocol

read address → (type, value); write is the same triple, applied

Twenty-two modules, bytes up to SQL

Each layer is a pure, target-neutral .ssc module. Semantics are defined once; the interpreter, JS, JVM, and the native VM are translators, not reinterpretations.

Front doors

jdbc · typedsql · address

SQL

sql — lexer · SELECT/INSERT/UPDATE/DELETE/CREATE · joins · aggregates · subqueries

Mutation

write · mutate · journal · wal · freelist

Schema & read

schema · readonly · btree · pager · page

Codec

record · header · values · bytes

VFS

vfs · memory-vfs · jvm-vfs — abstract file, in-memory image, real host files

What works today — and what doesn't yet

Capability	Status	Detail
Read real SQLite files	solid	byte-identical to sqlite3 3.51.0 across SELECT, WHERE, GROUP BY, aggregates, ORDER BY, LIMIT, joins, indexes
Write valid SQLite files	solid	INSERT / UPDATE / DELETE / CREATE — sqlite3 opens the result and passes `integrity_check`
JDBC	solid	a real `java.sql.Driver` for `jdbc:scljet:` plus a portable façade; cross-checked value-for-value against Xerial sqlite-jdbc
Durable host-file writes	solid	crash-atomic (write-temp → fsync → atomic rename → fsync dir) and single-writer via a cross-process lock
Addressing	solid	read & write by `table/rowid/column` for SQLite; read for JSON documents over a lossless tree
Backends	solid	interpreter, JS, the JVM driver, and the default `ssc run` native command
WAL & rollback journal	in progress	codecs and transaction primitives exist; not yet the default write path
A few engine write edges	in progress	`UPDATE` of an IPK column, `NULL` in an `INSERT … VALUES` list — tracked, honest, open
Documents beyond JSON	in progress	YAML / XML / Markdown addressing, and remote references — same model, a different resolver

By the numbers

13,631

lines of pure ScalaScript

modules, bytes up to SQL

conformance cases, locked on int & js

0×

bundled C, or SQLite library