rsqlite design

rsqlite is a reactive query engine for SQLite: you register a query, receive an atom, and the atom always derefs to the query's current result. Writes flow through the engine; SQLite's preupdate hook turns them into invalidations; only the registered queries whose dependencies actually changed re-execute, and only result atoms whose rows really changed are written.

The architecture is posh's (for Datascript) transplanted onto SQLite:

posh / Datascript	rsqlite
tx-report datoms	preupdate-hook events, materialized per row
q-analyze reload patterns	instaparse SELECT grammar + EXPLAIN QUERY PLAN
datom-matcher	pure table/column intersection (match)
listener re-run + not=	post-commit flush + result diff before delivery
ratom / reaction	plain atom per query (Reagent derefs it for free)

Everything below describes what ships; the namespace docstrings carry the same information next to the code.

The pipeline

      exec! / transact!                     rsqlite.cljc (API sugar)
            |
      backend/SqliteBackend                backend.cljc (the seam)
        |          |
   backend/jvm   backend/wasm              one per platform
        |          |
   preupdate hook --------+                capi.clj / preupdate.clj
            |             |                (JVM); backend/wasm.cljs
      :pending events  rollback hook       does both jobs inline
            |             |                on ClojureScript
            |         discard (+ savepoint restore)
            |
    statement left autocommit?
            |
      schedule flush  ---------------->  flush-safe! (serialized
            |                              per engine; off-writer)
            v
      flush!  (engine.cljc)
        1. resolve column names for touched tables (off-writer)
        2. coalesce events per table
        3. match against registered dependency profiles
        4. re-execute affected queries (reused prepared statements)
        5. diff rows; reset! only changed result atoms
        6. drain until :pending is empty

Change capture

The preupdate hook fires per changed row, on the thread executing the write, with access to the row's old and new values. Inside the callback almost everything is forbidden: sqlite3_value handles die on return, and the connection must not be touched (asking sqlite anything means preparing a statement). So the callback does the minimum and returns: read the column count, convert old/new values positionally, hand one cheap map to the engine, which swaps it into :pending. The writer is never blocked.

{:op :insert|:update|:delete
 :table "person"
 :rowid-old 4 :rowid-new 4        ; nil when undefined (WITHOUT ROWID)
 :old [4 "Ann" 33]                ; absent on insert
 :new [4 "Bea" 33]}               ; absent on delete

Column names are resolved later, off the writer: the flush prepares SELECT * FROM t LIMIT 0 against a per-engine cache, refreshed when a row's width stops matching the cached names.

A rollback hook discards :pending on full ROLLBACK. Savepoints checkpoint it: SAVEPOINT s snapshots, ROLLBACK TO s restores (the savepoint stays active), RELEASE s merges its events back for the enclosing commit - because ROLLBACK TO fires no rollback hook, and undid events must not survive as phantom invalidations.

Dependency extraction

At registration, each query's dependency profile is derived from two sources with complementary failure modes:

• EXPLAIN QUERY PLAN (sqlite's own planner) is the sound floor for tables - sqlite cannot plan to read a table it will not read - and needs no grammar, but reports no columns.
• The instaparse SELECT grammar (sql.cljc, modelled on parse.y with deliberately loose expression interiors) contributes column granularity and star resolution through FROM aliases, subqueries and CTE bodies.

{:tables          #{"person"}                  ; EQP floor ∪ grammar
 :table-columns   {"person" #{"name" "age"}}   ; bound refs, exact
 :columns         #{"x"}                       ; bare refs in multi-table
 :wildcard-tables #{"person"}}                 ; select * through FROM

Column references bind to their table when qualified (u.age, users.name, resolved through the scope's FROM aliases, CTE qualifiers to their body's tables) or when their scope reads a single table - the common query shape, exact for free. Bare references in all-real-table multi-table scopes resolve against the schema at registration: a name declared by exactly one in-scope table binds there (SQLite rejects ambiguous references at prepare, so unique ownership is the correct reading for anything that runs). Scopes with cte/subquery outputs of unknown shape, and genuinely ambiguous names, stay unbound and match any queried table: over-trigger only, never a miss. CTE names subtract only where lexically visible, so a CTE shadowing a real table name in a sibling scope cannot hide that table.

A statement the grammar cannot parse degrades to table-granularity (the wildcard set becomes every planned table) instead of re-running on every write. :all survives only for statements that would not prepare anyway.

Matching

match.cljc is pure set logic, cheap enough to run anywhere:

• :all deps always re-run;
• a table not in deps.tables never re-runs;
• inserted/deleted rows always re-run queries on the table - a new row appears in results regardless of which columns it set;
• coarse events (unknown columns, nil changed-cols) re-run everything on the table;
• wildcard tables re-run on any change;
• otherwise only when changed columns intersect the profile.

Events are coalesced per table first, so a 500-statement transaction touching two tables produces at most two invalidation sets.

Where matching runs was measured, not assumed: matching is cheap per event but proportional to registry size, and it could run on the writer thread (right after each statement) instead of in the flush. Benchmarked on the JVM backend with 60 registered queries (single-statement writes and 10-statement transactions): in-writer matching raised write-path latency 2.5-2.7x (p50 ~20us to ~55us, p99 ~50us to ~170us) and cost transactions 25% per statement, while the closed loop (write + recompute) showed no gain - re-execution outweighs matching ~20x there. So matching stays in the off-writer flush: the writer's cost remains the sqlite statement itself, and the matching work lands on the thread already doing the expensive part. (The measurement harness lived on a since-removed experiment branch; the numbers are recorded here because they justify this placement.)

Evaluation and delivery

Flushes are scheduled when a statement leaves the connection in autocommit - the commit boundary, not any hook (sqlite's commit_hook fires before finalization and cannot be trusted for committed-state reads). On the JVM they run on a single background daemon thread per engine; on JS on a microtask. Every flush takes a per-engine lock (JVM): an inline sync! and a scheduled flush serialize instead of racing for the same prepared statements - a race that once produced duplicated garbage rows from a count(*).

Subscribers therefore only ever observe committed state, a rolled-back transaction publishes nothing, and intermediate database states need not be reproduced: after mutations stop and evaluation succeeds, every active subscription reflects the latest committed state (eventual convergence).

Re-execution rebinds and re-steps the cached prepared statement. Rows are compared with = against the last published result; reset! happens only on change, so watchers - and Reagent - stay quiet for no-op recomputations. A failing evaluation keeps the last published result, records the error on the query entry, and retries on the next flush (no rescheduling loop: a permanently broken query costs nothing until the next write).

flush! refuses re-entry from watchers - they must write via exec!, whose own flush drains afterwards. Writes inside watchers are fine; recursive flushing is not.

Backends

backend.cljc defines the seam: exec, prepare/run/reset/finalize, event subscription, column names, EQP tables, autocommit, close.

• backend/jvm over the official org.sqlite.jni binding (SQLite 3.54.1, built by script/native-setup.sh with CMake + Ninja, mingw-w64 gcc on Windows). capi.clj is the only JNI interop: typed binds, rows as maps keyed by column name, result codes to ex-info with sqlite3_errmsg. preupdate.clj materializes hook callbacks; natives.clj extracts the bundled library from the jar into a content-addressed cache (XDG_CACHE_HOME/rsqlite-natives, LOCALAPPDATA on Windows) and loads it through a one-method NativeShim class compiled into the jar - modern JVMs snapshot java.library.path at startup, so neither loadLibrary nor System/load from dynamically compiled code can reach a jar-bundled library.
• backend/wasm over @sqlite.org/sqlite-wasm's capi + oo1 layer. The consumer initializes the module (their build toolchain's import; once per app) and passes it to connect. Values convert to JS (BigInt beyond 2^53 loses precision - the JVM keeps full longs).

One engine owns one connection (hooks observe only their own connection); engines are not pools. All writes go through the engine - the posh contract.

Invariants

1. No uncommitted visibility.
2. No rollback publication (savepoint-aware).
3. No lost invalidations: events arriving during a flush cause another flush (the drain loop); no locking deadlocks.
4. Deduplicated evaluation per transaction.
5. SQLite is the source of truth; queues and notifications are transient coordination.
6. Eventual convergence after mutations stop.
7. The writer thread is never blocked by recomputation.

Boundaries by design

• Virtual tables are invisible to invalidation: SQLite's own hooks do not fire for them, and wiring e.g. FTS shadow tables into the change feed would couple the engine to every vtab's shadow layout. Queries over virtual tables simply do not re-run.
• sqlite_sequence and other system tables are invisible too - internal AUTOINCREMENT bookkeeping must not invalidate user queries, so this is the correct behavior, not a gap.
• Bare column references in multi-table scopes match any queried table (bound references are exact; see Matching).
• DDL re-derives every profile and re-runs every query on the next flush - with or without pending row events - rather than trying to diff schemas.
• intel macOS has no bundled native and is not a supported platform; Apple Silicon, linux x64/aarch64 and Windows x64 ship bundled.

Building and releasing

script/native-setup.sh pins SQLite 3.54.1 (sha3-verified downloads), patches exactly one thing into the binding (CApi's static System.loadLibrary becomes the shim described above), and builds libsqlite3-jni.{so,dylib,dll} uniformly with CMake + Ninja, bootstrapping the single ninja binary itself where missing. clojure -T:build ci embeds the binding classes and every platform's native into one jar (resources/NOTICE records provenance; SQLite is public domain).

CI runs the JVM suite on linux x64/aarch64, Apple Silicon and Windows, plus the CLJS suite under node - concurrency groups cancel superseded runs, and push CI runs on main only. Releases run from the release branch, whose tip must carry an exact vX.Y.Z tag; the matrix rebuilds the natives, lays them into the jar, re-runs both suites, and publishes to Clojars and the GitHub Release.