SQLite in the browser that reads pages on demand 🗄️

The objective

The objective is robust SQLite that runs in a plain .html file that you open at file://. It is demand-paged, and it has the extensions that we want. It uses no server, no network fetch, and no SharedArrayBuffer. Thus the COOP/COEP headers are not necessary, and a local file cannot send them. Double-click the file, and you have a real database.

Three properties must be true at the same time. Most browser-SQLite setups lose one of them:

• Demand-paged. The pager reads single 4 KB pages when a query touches them. Thus a database that is much larger than the RAM can operate, and a write does not rewrite the file.
• Extensions. Full-text search, CRDT merge, vectors, and geometry. These builds set SQLITE_OMIT_LOAD_EXTENSION, so nothing loads at runtime. Each extension that you want must be in the compiled binary.
• Tested in a browser at file://. A test under Node only is not sufficient. Node hid the most expensive bug in this post.

Ordinary SQLite is demand-paged

SQLite does not load a file-backed database into memory. Its pager reads and writes single pages (4 KB by default) when a query touches them. The pager keeps only a small cache in RAM (PRAGMA cache_size). A 50 GB database runs in a few megabytes. Only a :memory: database is fully in RAM. This on-demand paging is the reason that SQLite operates on a very small device and also on a very large database.

sql.js holds the full file in one array

The browser gave WebAssembly no random-access file API that the pager of SQLite can drive. Thus sql.js keeps the file as one Uint8Array on the WASM heap. This is simple, and it is satisfactory for small data. But a 20 MB database uses 20 MB of RAM, and the load time increases with the size of the full file. A write serialises the full file again and stores it again. You exchange the best feature of SQLite for convenience.

A VFS puts it back, in approximately four lines

SQLite has an interface for exactly this: the VFS (virtual filesystem). Give SQLite a VFS that stores its blocks in IndexedDB. Then the block reads and writes of the pager go there, one page at a time. wa-sqlite includes 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 with the key [path, offset, version]. The commits are batch-atomic. Open DevTools → Application → IndexedDB in the demo, and you can see the pages appear. Figure 1 compares the two models.

Why it runs from file://

Two properties make this possible. Most persistent-SQLite options do not have them:

• The VFS uses Asyncify, so it does not use SharedArrayBuffer. Thus the COOP/COEP cross-origin-isolation headers are not necessary. A static host or a local file cannot send those headers. This is the reason that absurd-sql and threaded-OPFS builds do not operate here.
• The build inlines the wasm as base64. Thus the engine opens with zero network fetches. Double-click the HTML file, and it runs.

The build to use: it includes FTS5 and CRDT

The code above imports @vlcn.io/wa-sqlite, not plain wa-sqlite. The reason is important, because the names can cause a wrong conclusion. The vlcn package is a fork of wa-sqlite, and it statically links cr-sqlite. It includes the same IDBBatchAtomicVFS. The two copies are byte-identical, except for one commented-out import.

Thus the choice of the fork costs nothing in paging. Demand paging comes from the VFS, not from the build. The fork adds two items. Without the fork, you must compile them yourself:

Upstream wa-sqlite has no FTS5 and no CRDT. Its PRAGMA compile_options lists no ENABLE_FTS5, and CREATE VIRTUAL TABLE … USING fts5 fails with no such module: fts5. The fork has both. Both packages set SQLITE_OMIT_LOAD_EXTENSION, so nothing can load at runtime. Each extension that you want must be in the binary. This is the reason that the correct prebuilt binary makes a toolchain unnecessary.

Is it still paged with the extensions? Measure it

It is easy to assert this. Thus the repo asserts it in a test. The test does these steps:

1. Subclass the VFS.
2. Count each xRead that the pager issues.
3. Close the database, then open it again. Now the page cache is cold, and each read must come from IndexedDB.

234 pages in file, FTS5 query faulted in 17

A full-text search on a 234-page database touches 17 pages. That is approximately 7% of the file. A CRR and an FTS5 index are live in the same file. That is the full claim in one line, and it is a test, not a paragraph.

Four problems that cost time

Most of the value in the repo is these four items. Each one cost real debug time.

1. One connection cannot run two statements at the same time. This is the expensive one. A wa-sqlite connection is one Asyncify state machine. If two queries overlap on it, the await points in step() interleave, and the pager of SQLite loses synchronisation. A simple Promise.all, or a debounced search box in the middle of a refresh, causes that overlap. The pager takes duplicate read locks, then fails a hot-journal check on a journal that never existed.

Then the wasm aborts with memory access out of bounds. The symptom looks the same as VFS corruption, but it has no relation to the VFS. The fix is one promise queue for every statement. The engine does that now, so the callers can be as concurrent as they want.

2. cr-sqlite rejects NOT NULL without a DEFAULT. A replicated table must have a default on each column, so replicas on different schema versions can still exchange rows. It must also have a non-nullable primary key. body TEXT NOT NULL fails. Add DEFAULT '', and it passes.

3. An FTS5 table cannot be a CRR. crsql_as_crr rejects it, because of the nullable primary key on rowid, so the index is a derived local artifact. The content table replicates, and triggers maintain the FTS index on top of it. One result was not obvious, so we did not assume it: those triggers do fire for rows that arrive through a merge. Thus the index stays correct without a rebuild, and the test suite asserts that also. Figure 2 shows the two paths into the index.

4. No autoincrement ids under CRDT. Two offline replicas can each select max(id)+1. The two rows collide and merge into one row. Use random ids.

Two directions of "demand-paged"

The idea is the same, with two targets. Select by your task: a read or a write, local or remote:

The second is the way to serve a large static knowledge base in a .sqlite file. No visitor downloads the full file. The query gets a few kilobytes of pages, not the file.

The step up: encrypted and access-controlled

The demo stops at convergence: two replicas merge, and each changeset is as trustworthy as the last. Encryption at rest, or a limit on who can read and write what, is the same VFS with more on top. localRbac wraps this exact IDBBatchAtomicVFS as an EncryptedIDBVFS, with transparent per-page AES. It also adds per-resource encryption keys, signatures that the merge checks, and Shamir threshold custody of the root secret. Start here for the mechanism. Go there for the full system.

What we have now: one binary, every extension

The published @vlcn.io/wa-sqlite has FTS5 and CRDT, but it has no vectors and no spatial support. rtree, geopoly, and vec0 are all absent. A probe of the binary confirmed this, not a read of a Makefile. To get them, you must build from source. Thus that build is now its own repo. It produces four variants, so the binary includes only the extensions that you use:

Geometry costs approximately 60 KB, and vectors cost approximately 170 KB. Both are small compared with the 1.77 MB minimum. But that minimum becomes approximately 2.7 MB after a base64 inline into an HTML file. Thus take the smallest variant that is sufficient.

Each variant declares itself as a drop-in @vlcn.io/wa-sqlite. It has the same package name and the same import specifiers. Thus, to adopt one variant, you change one dependency line, and you do not refactor: "@vlcn.io/wa-sqlite": "file:../sqlite-wasm-build/dist/vec". localRbac is the first project that runs on it. It runs on base, and its 46 RBAC tests pass against the locally built binary. Figure 3 shows the four variants behind one package name.

The extensions register through SQLITE_EXTRA_INIT. That macro names exactly one symbol, and cr-sqlite already claims it with core_init(). The build does not patch the submodule, but it concatenates a small file after it. That file chains the two steps: run the initializer of cr-sqlite, then register each other extension. R*Tree and Geopoly do not use that file, because they are in the SQLite amalgamation and -D flags alone turn them on. This is the reason that they are the correct spatial choice for this target.

For geometry, the useful answer is narrower than “use SpatiaLite”. Geopoly is in the amalgamation and has zero external dependencies. It gives indexed polygon predicates on top of R*Tree: geopoly_contains_point, geopoly_within, geopoly_overlap, and geopoly_area. It takes GeoJSON-style coordinate arrays, and those arrays pair well with JSON1. Geopoly has no projections, no geodesy, and no ST_* names, and it supports polygons only.

SpatiaLite gives you all of that, but it uses GEOS and PROJ to do it. A wasm port of those is a serious project, and it adds megabytes to the binary. The full point of this binary is one file that you open with a double click. If a project must have OGC semantics and reprojection, that is an argument for a server, not for this build.

A database GUI, in the same one file

The most convincing demonstration of a claim such as this is an application that usually must have a server. Thus the demonstration is SQLite Studio, a database GUI in a single index.html. It has a schema tree, a SQL editor with completion from the live schema, a results grid, CSV export, and a tab for ordinary CRUD. You can create a database, create tables, query them, and edit rows.

No server serves any part of it. Save the page and open it from file://, and it behaves identically. The reason is that the wasm is inline and the pages are in IndexedDB. The completion is schema-aware, not a keyword list. After each statement, Studio reads the schema again. Thus SELECT offers your tables, and notes. offers the columns of that table.

Studio has two details that are consequences of items above. A single-column primary key or rowid is necessary to address a row for an edit. Thus a view or a WITHOUT ROWID table without one stays read-only, and Studio says so. Studio refreshes the schema immediately after it runs a user query. Without a queue, that refresh causes the two-statements abort constantly. Thus each statement goes through one queue.

Problems that cost real time

The build against a current toolchain (Emscripten 6, Rust nightly) failed six times before it succeeded. None of the failures was self-explanatory. We record them here, because they will occur again:

• cr-sqlite main does not compile for wasm. It removed use core::alloc::Layout;, but it still uses Layout under #[cfg(target_family = "wasm")]. Build from the v0.16.3 tag.
• SQLite 3.45 builds, then fails at runtime. cr-sqlite v0.16.3 compiles against it, but crsql_as_crr() then fails with out of memory. 3.44 operates, and 3.44 is the version in the published package.
• WASM_BIGINT is on by default in Emscripten 6. int64 return values arrive as BigInt. But the sqlite-api.js of wa-sqlite still reads them the legalized way (lo32 + getTempRet0()) and multiplies. Thus it throws Cannot mix BigInt and other types on the first read of an integer column. Each count(*), rowid, and json_extract fails. Link with -s WASM_BIGINT=0.
• Emscripten does not attach the heap views automatically now. You must export Module.HEAPU8/HEAPU32 explicitly, or the API fails with Cannot read properties of undefined (reading 'buffer'). A command-line -s cannot correct this. The Makefile puts the extra flags before the @file flags, so the file always has priority.
• bindgen cannot find stdarg.h. The Rust layer of cr-sqlite runs bindgen. bindgen uses its own libclang, and it does not inherit the include paths of emcc.
• The repo layout has no documentation. 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 pair. That is the full reason to pin.

Dependencies, and what forces a rebuild

This stack is a pinned pair, not a set of libraries that move forward independently. These are the parts that can change and make us evaluate the stack again:

The guard against all of this is the same: a probe that executes SQL against the produced binary, not trust in compile flags. A test also asserts that each variant contains exactly its declared extensions and nothing else. A flag with no effect cannot pass that test.

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 with a pluggable VFS toolkit
• vlcn-io/wa-sqlite — the fork in use here: the same VFS, with FTS5 and cr-sqlite
• Geopoly · R*Tree — dependency-free spatial support, if you compile it in
• sqlite-vec — vector search in single-file C, so it is wasm-friendly
• sql.js-httpvfs — demand paging of a remote static DB through HTTP range requests
• SQLite VFS · sql.js — the VFS interface, and the in-memory approach that this post compares against
• localRbac — the encrypted step up with CRDT (per-page AES and cr-sqlite on this VFS)