SQLite that persists without hoarding RAM 🗄️

The objective

Robust SQLite — demand-paged, with the extensions we actually want — running inside a plain .html file opened at file://. No server, no network fetch, no SharedArrayBuffer, and therefore none of the COOP/COEP headers a local file can't send. Double-click the file and you have a real database.

Three things have to hold at once, and most browser-SQLite setups give up one of them:

• Demand-paged — the pager faults in individual 4 KB pages as queries touch them, so a database far larger than RAM works and a write doesn't rewrite the file.
• Extensions — full-text search, CRDT merge, vectors, geometry. Since SQLITE_OMIT_LOAD_EXTENSION is set in these builds, nothing loads at runtime: whatever you want has to be compiled into the binary.
• Verified in a browser at file:// — not merely under Node, which is where the most expensive bug in this post hid.

Ordinary SQLite is demand-paged

A file-backed SQLite database is not loaded into memory. Its pager reads and writes individual pages (4 KB by default) as queries touch them, keeping only a small cache in RAM (PRAGMA cache_size). A 50 GB database runs in a few megabytes; only a :memory: database lives entirely in RAM. That on-demand paging is the whole reason SQLite scales down to a wristwatch and up to a warehouse.

The browser broke that — sql.js hands it back as one big array

The browser gave WebAssembly no random-access file API that SQLite's pager could drive, so sql.js fakes the file as a single Uint8Array on the WASM heap. Simple, and fine for small data — but a 20 MB database is 20 MB of RAM, load times scale with the whole file, and writes mean re-serialising and re-storing the lot. You've traded SQLite's best trick for convenience.

A VFS puts it back — in about four lines

SQLite has a seam for exactly this: the VFS (virtual filesystem). Give it one backed by IndexedDB and the pager's block reads and writes land there, one page at a time. wa-sqlite ships that VFS — IDBBatchAtomicVFS:

import * as SQLite from '@vlcn.io/wa-sqlite';
import SQLiteESMFactory from '@vlcn.io/wa-sqlite/dist/crsqlite.mjs';
import { IDBBatchAtomicVFS } from '@vlcn.io/wa-sqlite/src/examples/IDBBatchAtomicVFS.js';

const module  = await SQLiteESMFactory({ wasmBinary });          // inlined wasm → no fetch
const sqlite3 = SQLite.Factory(module);
sqlite3.vfs_register(new IDBBatchAtomicVFS('mydb'), true);        // IndexedDB is the "disk"
const db = await sqlite3.open_v2('demo.db');                      // demand-paged from here

Each SQLite page becomes one IndexedDB object — a FileBlock keyed [path, offset, version], with batch-atomic commits. Open DevTools → Application → IndexedDB in the demo and you can watch the pages appear.

Why it runs from file://

Two properties, and they're the ones most persistent-SQLite options miss:

• The VFS is Asyncify-based, so it needs no SharedArrayBuffer — and therefore none of the COOP/COEP cross-origin-isolation headers a static host (or a local file) can't send. This is why absurd-sql and threaded-OPFS builds don't work here.
• The wasm is base64-inlined at build time, so opening the engine costs zero network fetches. Double-click the HTML file; it just runs.

Which build: FTS5 and CRDT come for free

The snippet above imports @vlcn.io/wa-sqlite rather than plain wa-sqlite, and it's worth being precise about why, because the naming invites a wrong conclusion. vlcn's package is a fork of wa-sqlite that statically links cr-sqlite. It ships the same IDBBatchAtomicVFS — byte-identical but for one commented-out import — so choosing it costs nothing in paging. Demand paging comes from the VFS, not from the build. What the fork adds is two things you'd otherwise compile yourself:

Upstream wa-sqlite has neither FTS5 nor CRDT — PRAGMA compile_options lists no ENABLE_FTS5, and CREATE VIRTUAL TABLE … USING fts5 fails with no such module: fts5. The fork has both. Since SQLITE_OMIT_LOAD_EXTENSION is set in both, nothing can be loaded at runtime: whatever you want has to be in the binary, which is exactly why picking the right prebuilt binary saves you a toolchain.

Is it still paged with all that on top? Measure it

Easy to assert, so the repo asserts it in a test instead. Subclass the VFS, count every xRead the pager issues, then close and reopen the database so the page cache is cold and every read has to come back out of IndexedDB:

234 pages in file, FTS5 query faulted in 17

A full-text search against a 234-page database touches 17 pages — about 7% of the file — with a CRR and an FTS5 index live in the same file. That's the whole claim in one line, and it's a test, not a paragraph.

Four things that bite

Most of the value in the repo is these four, each of which cost real debugging time.

1. One connection cannot run two statements at once. This is the expensive one. A wa-sqlite connection is a single Asyncify state machine. Overlap two queries on it — an innocent Promise.all, or a debounced search box landing mid-refresh — and the await points inside step() interleave, desynchronising SQLite's pager. It takes duplicate read locks, then fails a hot-journal check against a journal that was never there, and the wasm aborts with memory access out of bounds. The symptom looks like VFS corruption and has nothing whatsoever to do with the VFS. The fix is to funnel every statement through one promise queue, which the engine does now, so callers can be as concurrent as they like.

2. cr-sqlite rejects NOT NULL without a DEFAULT. A replicated table needs a default on every column — so replicas on different schema versions can still trade rows — and a non-nullable primary key. body TEXT NOT NULL fails; add DEFAULT '' and it's fine.

3. An FTS5 table can't itself be a CRR. crsql_as_crr refuses it, on the nullable rowid primary key. So the index is a derived local artifact: the content table replicates, and triggers maintain the FTS index on top of it. The good news, and it wasn't obvious enough to assume: those triggers do fire for rows that arrive through a merge, so the index stays correct without a rebuild. That's asserted in the test suite too.

4. No autoincrement ids under CRDT. Two replicas offline, both picking max(id)+1, collide and merge into a single row. Use random ids.

Two directions of "demand-paged"

Same idea, two targets — pick by whether you're reading or writing, local or remote:

The second is how you'd serve a large static .sqlite knowledge base without making every visitor download it whole — the query pulls a few kilobytes of pages, not the file.

The step up: encrypted & access-controlled

The demo stops at convergence: two replicas merge, and any changeset is as trustworthy as the last. Wanting it encrypted at rest, or wanting to constrain who may read and write what, is the same VFS with more on top. localRbac wraps this exact IDBBatchAtomicVFS as an EncryptedIDBVFS — transparent per-page AES — and adds per-resource encryption keys, signatures verified at merge, and Shamir threshold custody over the root secret. Start here for the mechanism; go there for the full apparatus.

What we have now: one binary, every extension

The published @vlcn.io/wa-sqlite has FTS5 and CRDT but no vectors and no spatial support — rtree, geopoly and vec0 are all absent, confirmed by probing the binary rather than reading a Makefile. Getting them means building from source, so that is now its own repo, producing four variants so you can pay only for what you use:

Geometry costs about 60 KB and vectors about 170 KB — both cheap against the 1.77 MB floor. But that floor becomes roughly 2.7 MB once base64-inlined into an HTML file, so take the smallest variant that does the job.

Registration hangs off SQLITE_EXTRA_INIT, which names exactly one symbol — and cr-sqlite already claims it with core_init(). Rather than patch a submodule, a small file is concatenated after it and chains: run cr-sqlite's initializer, then register everything else. R*Tree and Geopoly need nothing there at all; they live in the SQLite amalgamation and are enabled purely by -D flags, which is exactly what makes them the sane spatial choice for this target.

On geometry specifically, the useful answer is narrower than “use SpatiaLite”. Geopoly ships inside the amalgamation with zero external dependencies and gives real indexed polygon predicates — geopoly_contains_point, geopoly_within, geopoly_overlap, geopoly_area — on top of R*Tree, taking GeoJSON-style coordinate arrays that pair well with JSON1. What it isn't: no projections, no geodesy, polygons only, no ST_* names. SpatiaLite gives you all of that and needs GEOS and PROJ to do it — a serious wasm porting project that would add megabytes to a binary whose whole point is arriving as one double-clickable file. If a project truly needs OGC semantics and reprojection, that argues for a server, not for this build.

A database GUI, in the same one file

The most convincing demonstration of a claim like this is an application you'd otherwise expect to need a server. So: SQLite Studio — a database GUI in a single index.html. Schema tree, SQL editor with completion fed from the live schema, results grid, CSV export, and a tab for ordinary CRUD. Create a database, create tables, query them, edit rows.

Nothing about it is served. Save the page and open it from file:// and it behaves identically, because the wasm is inlined and the pages are in IndexedDB. The completion is schema-aware rather than a keyword list — after each statement it re-introspects, so SELECT offers your tables and notes. offers that table's columns.

Two details it forced, both consequences of things above. A single-column primary key or rowid is needed to address a row for editing, so views and WITHOUT ROWID tables without one are read-only and say so. And because Studio refreshes the schema immediately after running a user query, it would trip the two-statements-at-once abort constantly — every statement goes through one queue.

Gotchas that cost real time

Building this against a current toolchain (Emscripten 6, Rust nightly) failed six times before it worked, and none of the failures were self-explanatory. Recording them because they will recur:

• cr-sqlite main does not compile for wasm. It dropped use core::alloc::Layout; while still using Layout under #[cfg(target_family = "wasm")]. Build from the v0.16.3 tag.
• SQLite 3.45 builds fine and then fails at runtime. cr-sqlite v0.16.3 compiles against it, but crsql_as_crr() then dies with out of memory. 3.44 works — and is what the published package ships.
• WASM_BIGINT defaults on in Emscripten 6. int64 returns arrive as BigInt, but wa-sqlite's own sqlite-api.js still reads them the legalized way (lo32 + getTempRet0()) and multiplies — so it throws Cannot mix BigInt and other types on the first integer column read. Every count(*), rowid and json_extract fails. Link with -s WASM_BIGINT=0.
• Heap views are no longer auto-attached. Module.HEAPU8/HEAPU32 must be exported explicitly, or the API dies with Cannot read properties of undefined (reading 'buffer'). And it can't be fixed with a command-line -s: the Makefile places extra flags before the @file ones, so the file always wins.
• bindgen can't find stdarg.h. cr-sqlite's Rust layer runs bindgen, which uses its own libclang and doesn't inherit emcc's include paths.
• The repo layout is undocumented. wa-sqlite contains a symlink, crsql -> ../cr-sqlite/core, so the two repos must be siblings. Neither is a submodule of the other, so nothing enforces the pairing — which is the whole reason to pin.

Dependencies, and what would force a rebuild

This stack is a pinned pairing, not a set of libraries that float forward independently. Worth knowing which moving parts would make us re-evaluate:

The guard against all of it is the same: a probe that executes SQL against the produced binary rather than trusting compile flags, plus a test asserting each variant contains exactly its declared extensions and nothing else. A flag that was set but had no effect cannot fool that.

References

• rheophile10/wa-sqlite-idb-demo — the demo: TypeScript, demand-paged, FTS5 + CRDT (source & live)
• SQLite Studio · source — a database GUI in one index.html: schema tree, SQL editor, CSV export, CRUD
• rheophile10/sqlite-wasm-build — per-extension builds from pinned source: vec0, rtree, geopoly
• wa-sqlite — async WASM SQLite + pluggable VFS toolkit
• vlcn-io/wa-sqlite — the fork used here: same VFS, plus FTS5 and cr-sqlite
• Geopoly · R*Tree — dependency-free spatial support, if you compile it in
• sqlite-vec — vector search; single-file C, so wasm-friendly
• sql.js-httpvfs — demand-paging a remote static DB over HTTP range requests
• SQLite VFS · sql.js — the in-memory approach this is the counterpoint to
• localRbac — the encrypted, CRDT-flavoured step up (per-page AES + cr-sqlite over this VFS)