package granary_repl

granary

A pure-OCaml SQL engine — a concept port of SQLite targeting MirageOS unikernels. No C stubs, a fresh on-disk file format, single-writer / multi-reader MVCC with snapshot isolation, and strict typing (not SQLite's manifest typing).

The engine covers a large slice of the SQL surface: CRUD, JOINs, aggregates, subqueries and correlated subqueries, CTEs and recursive CTEs, window functions, views, triggers (BEFORE / AFTER / INSTEAD OF), foreign keys with CASCADE / SET NULL / SET DEFAULT and DEFERRABLE checks, UPSERT, FTS5 with BM25 and snippet(), generated columns, partial / expression indexes, SAVEPOINTs, a WAL with crash recovery, VACUUM, overflow pages, and WITHOUT ROWID tables.

Encryption at rest

Opt-in, page-level AES-256-GCM encryption (#84). Pass a 32-byte raw key to open_file / open_file_wal (or the lower-level Store.open_block*) and the database is created — or reopened — encrypted; omit the key and the database is plaintext, exactly as before (encryption is off by default).

let key = (* 32 raw bytes from your secrets manager / boot config *) in
Granary_unix.Store.open_file ~key ~path:"app.db" ()

• Fixed at creation. Whether a database is encrypted is a per-file property chosen when it is first created and cannot be toggled later. Reopening an encrypted database without the key fails cleanly (Encryption_key_required); a wrong key is rejected by a header canary (Encryption_key_mismatch); a key supplied for a plaintext database is refused (Not_encrypted).
• What is protected: every data page of the main DB and every page payload in the WAL, both on disk and (for the WAL) on the replication wire. Each page carries its own random nonce + GCM authentication tag (a 32-byte reserved tail), so tampering is detected cryptographically.
• What leaks (accepted): the plaintext header pages expose structural metadata (page geometry, root/txn ids, schema version); WAL frame headers expose page-ids and the commit pattern. Page contents are never exposed.
• App owns the key and entropy. The library takes raw key material, not a passphrase (no in-engine KDF, no salt in the file). The application must seed mirage-crypto-rng at boot (the Unix backend uses Mirage_crypto_rng_unix.use_default ()); the core library never seeds, to stay Mirage-clean.

Durability modes (PRAGMA synchronous)

granary supports a per-deployment durability setting analogous to SQLite's synchronous, gating only the WAL group-commit fsync. CoW shadow-paging, snapshot isolation, rollback, and crash recovery are unaffected — only when commits are fsynced changes.

Configuration (database-wide):

• PRAGMA synchronous = full | batched | off
• PRAGMA wal_batch_commits = N (default 256) — the batched commit-count threshold
• PRAGMA wal_batch_interval_ms = T (default 100) — the batched time threshold (milliseconds)
• Open-option: Db.open_block ?durability:(Granary_store.Store.Batched { commits; interval_ms }) (also Full / Off)
• The getters (PRAGMA synchronous, PRAGMA wal_batch_commits, PRAGMA wal_batch_interval_ms) read the current values back.

As-of time travel (#266)

Read the database as it existed at any past commit, identified by transaction id or wall-clock timestamp. Feature is off by default — zero overhead unless enabled.

Enabling

Pass ~as_of_history:true when opening the database. On the Unix layer this writes a <path>.aslog sidecar file containing a CRC32-verified, fixed-width record per commit (txn id, wall-clock ms, root page):

(* plain-file open *)
let* db = Granary_unix.open_file ~as_of_history:true ~path:"app.db" () in

(* WAL open *)
let* db = Granary_unix.open_file_wal ~as_of_history:true ~path:"app.db" () in

For the lower-level Store.open_block / Store.open_block_wal, supply a ~history:History.sink (the injected log backend) and a ~now clock in addition to ~as_of_history:true.

Retention

Pages reachable from historical roots are only retained while a floor is set. Without a floor an open historical reader still pins its own snapshot, but pages from released snapshots can be reclaimed.

(* Retain every root from txn_id onwards — pages stay queryable. *)
Store.history_pin t ~txn_id;

(* Read the current retention floor (None = unset). *)
Store.history_floor t;

(* Release the floor; reclamation of superseded pages resumes. *)
Store.history_release t;

(* Inspect the full commit log (ascending txn order). *)
let* records = Store.history_log t in

The same wrappers are available at the Db layer (Db.history_pin, Db.history_floor, Db.history_release, Db.history_log).

Reading

(* Store level — raw snapshot *)
let* ro = Store.ro_begin_as_of t (`Txn txn_id) in  (* or `Ts ms *)
(* ... get/cursor_open/etc. ... *)
let* () = Store.ro_end ro in

(* SQL level — lazy result stream *)
let* stream = Db.query_as_of db (`Txn txn_id) "SELECT …" in

ro_begin_as_of / query_as_of return the committed root with the largest txn id (for `Txn) or timestamp (for `Ts) that is ≤ the target.

Errors

Limitations

• Requires an active retention floor. As-of reads serve only what history_pin protects: with no floor set, every as-of target returns History_pruned. Pinning is not retroactive — pin before the writes you want to retain across.
• Unencrypted databases only (the sidecar log is always plaintext; encrypted DB support is future work).
• Schema is read at HEAD. DDL executed after the target snapshot may misinterpret older rows (column types, table layout). Keep schema stable across the query horizon, or re-open with a matching schema version.
• Dense whole-DB retention grows the file under write-heavy workloads: every historical root's page tree is pinned while the floor is set. Per-table / sparse retention and a GC sweep are future work; #266 remains open for those.
• In-memory and Mirage backends: history is non-durable (in-memory sink only, lost on restart). Durable history requires the Unix file sink.
• `Ts (timestamp) resolution assumes a monotonic, non-decreasing wall clock (guaranteed under the single-writer model); a backwards clock adjustment could make a `Ts target resolve to a slightly different commit. `Txn resolution is always exact.
• Main database only. As-of applies to the main store; a query routed to an ATTACHed database is rejected (as-of queries are not supported against attached databases).

Building & cross-platform support

The engine is 100% OCaml with no C stubs, and the on-disk format is explicitly byte-ordered (big-endian page headers and index keys, little-endian float64 in rows), so builds are architecture-neutral. Both linux/amd64 and linux/arm64 are supported and verified (#157); darwin/arm64 works for local dev. The ocaml/opam base image in Containerfile is published multi-arch, so the same Containerfile builds on either host.

# native build (host architecture)
podman build -t granary-dev -f Containerfile .

# explicit per-arch builds — these build natively on a matching host and under
# qemu-user-static emulation on a foreign host (e.g. arm64 on an x86 box):
podman build --platform=linux/amd64 -t granary-dev:amd64 -f Containerfile .
podman build --platform=linux/arm64 -t granary-dev:arm64 -f Containerfile .

To build or run a foreign-architecture image on an x86 host, register the qemu binfmt handlers once (Debian/Ubuntu):

sudo apt-get install -y qemu-user-static binfmt-support

Then build and test exactly as on the host architecture:

podman run --rm --platform=linux/arm64 -v "$(pwd):/workspace:z" -w /workspace \
  granary-dev:arm64 dune runtest

Sample MirageOS unikernel

A minimal, in-tree sample unikernel under mirage/ runs the engine over a Mirage_block device in WAL mode (the amd64 baseline for the aarch64 audit, #403). It builds and runs on the unix target and builds for the hvt (Solo5) target. The mirage CLI is not in granary-dev; see mirage/README.md for the dedicated build image (Containerfile.mirage) and the build/run commands.

Benchmarks

In-process benchmarks against reference C SQLite 3.45.1 (same dataset, prepared statements both sides, WAL, fsync-per-commit, matched page cache), separating the CPU term from the I/O term via cpu/wall per run. Full method and tables: docs/benchmarks/2026-06-07-bench-222-results.md (also on the project wiki, on the maintainer's private development instance).

Current NVMe baseline (1a73da0, after #228 PK B-tree seek, #229 O(n) bulk insert, and the T4/T5 read-path work) — granary is now within single-digit multiples of C SQLite on most workloads:

That is a large improvement over the 2026-06-02 pre-fix baseline (358b2b9), where the same NVMe workloads were ~7,700× (point lookup, an O(n) full scan), ~200× (scan), and ~5,300× (batch insert, an O(n²) path) slower. The point-lookup and bulk-insert complexity bugs are gone; the only large remaining gap is batch insert (~44×), a constant-factor copy-on-write write-amplification cost (#230 / #231).

AES-256-GCM encryption-at-rest now adds only ~20% (or within noise) to cache-resident reads — the frame-cache (T4) caches decrypted pages, down from the ~2× (≈ +100%) of the pre-fix run. Writes are barely affected.

Verdict: reads are CPU-bound (cpu/wall ≈ 1.0), single-row writes are fsync/I-O-bound (cpu/wall 0.5–0.6). With the O(n) read path and O(n²) insert path fixed, 4 of 5 plaintext workloads are within the #231 "10× of SQLite" goal; the read-side multicore epic (#156) remains gated on closing the batch-insert constant factor first.

Reproduce: scripts/bench222.sh (builds the bench image and runs the suite; cross-host steps in the results doc).

AI authorship

This codebase is entirely AI-written. Per the avsm/ocaml-ai-disclosure proposal — which aligns its vocabulary with the W3C AI Content Disclosure levels (none / ai-assisted / ai-generated / autonomous) — granary's disclosure level is:

ai-generated

Authorship model. A human (the repository owner) sets the scope, picks which issues to work on, decides architectural trade-offs, and signs off on the result. An AI agent writes all of the code, tests, documentation, and commit messages. The primary model is Claude Opus (Anthropic), with Claude Sonnet occasionally used for cheaper mechanical work.

A handful of commits — multi-phase autonomous-loop work — drift toward autonomous, but autonomous would overstate how hands-off the human is at the design and scoping layer, so ai-generated is the honest level for the project as a whole.

The same disclosure is published in the package's opam metadata:

x-ai-disclosure: "ai-generated"
x-ai-model:      "claude-opus-4-7"
x-ai-provider:   "Anthropic"