SQLite OPFS for Durable Browser Data

SQLite OPFS is a strong browser storage option when an offline workload benefits from SQL, transactions, and a single coherent database file. It is still origin-scoped browser storage, not an invisible substitute for server backup.

This guide places SQLite WASM in a dedicated worker, persists the database through the origin private file system, injects an interrupted transaction, and verifies recovery. The comparison with IndexedDB stays limited to this relational workload rather than declaring a universal browser database winner.

Choose SQLite OPFS for the actual workload

The target application is an offline research notebook with relational notes, tags, citations, and full-text queries. SQL joins and atomic multi-table changes make browser SQLite attractive; a simple key-value preference store would not justify the extra runtime. OPFS offers origin-private file access suitable for database persistence, while SQLite's WASM distribution documents several persistence options and their environment constraints.

Write down browser support, cross-origin isolation needs, worker topology, estimated data size, export expectations, and whether the browser copy is primary or a cache. Those choices determine whether this architecture is responsible.

Keep SQLite WASM inside a worker

Run database initialization, queries, transactions, and file access in a dedicated worker so expensive SQL and synchronous-capable OPFS mechanisms cannot block the UI thread. Define a typed message contract with request ID, operation, bounded parameters, cancellation semantics, and structured error response. The main thread owns presentation and pending-state UX; the worker serializes database ownership.

Avoid opening the same file through uncontrolled tabs or workers unless the selected SQLite persistence layer explicitly supports that topology. A single writer with deliberate coordination is easier to recover than several optimistic owners sharing an undocumented lock assumption.

Initialize OPFS and verify persistent identity

getDirectory() in the worker-capable environment and create the application directory and database using the pinned SQLite WASM persistence API. Store a schema version and application instance ID inside the database, not only in JavaScript memory. After closing and recreating the worker, query those values and a known row.

Feature detection must lead to a supported fallback or a clear unavailable state; it should not silently create an in-memory database that appears to save. Requesting persistent storage can reduce eviction risk in supporting browsers, but the application must still explain storage limits and provide export.

Design transactions around interruption

The crash fixture begins a multi-table note update, writes content and tag rows, and interrupts at controlled boundaries: before commit, after journal or WAL durability, and after commit acknowledgement. On restart, SQLite should expose either the old transaction or the committed new one, never a half-applied relationship. The illustrative Node artifact models this invariant, while the acceptance harness must run the actual pinned SQLite WASM build and persistence mode in a real browser. Capture integrity_check, row counts, schema version, and expected business invariant after every injected interruption.

Runnable artifact: The small transaction model isolates the commit-marker recovery invariant; the production recipe must run the pinned SQLite WASM build in a real browser worker. Its opfs-transaction-model.test.mjs receipt keeps the article's simplified boundary executable and reviewable.

Save the inspectable proof as opfs-transaction-model.test.mjs and run node opfs-transaction-model.test.mjs. Expected final line: PASS: OPFS commit recovered.

import assert from "node:assert/strict";
const recover=({db,journal,crash})=>{const durable={...db};if(!journal.committed)return durable;if(crash==="after-journal")return {...durable,...journal.pages};return {...durable,...journal.pages}};
const before={page1:"schema",page2:"old"},journal={committed:true,pages:{page2:"new",page3:"row"}};assert.deepEqual(recover({db:before,journal,crash:"after-journal"}),{page1:"schema",page2:"new",page3:"row"});assert.deepEqual(recover({db:before,journal:{...journal,committed:false},crash:"before-commit"}),before);console.log("PASS: OPFS commit recovered");

Treat quota, eviction, and corruption as product states

An origin private file system remains governed by browser storage policy and device pressure. Query storage estimates, warn before large imports, reject writes safely on quota errors, and retain enough free-space margin for transaction journals and export. Do not write copy that guarantees data can never be evicted.

Provide a support path for integrity failures and keep migration backups or snapshots appropriate to the sensitivity of the data. If the offline web database is primary custody, encrypted export and tested restore become core features rather than settings-page decoration.

Compare IndexedDB only on this relational case

IndexedDB is widely available and naturally stores JavaScript records and indexes; SQLite adds SQL, mature transaction semantics, and a portable database representation at the cost of WASM weight, worker messaging, and persistence complexity. Build one representative import, search, tag update, and export on each candidate if the choice is uncertain. Measure correctness and maintenance as well as speed.

A benchmark that inserts anonymous rows says little about schema migrations, query evolution, debugging, or user recovery. The matrix therefore records workload fit and ownership instead of naming a general champion.

Ship schema migration and export together

A durable browser database needs a forward-only migration ledger and a user-controlled export. Test migration from every supported schema version using fixtures that include real edge shapes, then reopen and run integrity checks. Export can be a SQLite file or a documented portable format, but it needs consistency, metadata, and an import verifier.

Coordinate service-worker and application updates so a new UI does not race an old worker or incompatible database schema. Keep a rollback story that respects irreversible migrations; reverting JavaScript alone cannot restore dropped data.

Release with real-browser recovery evidence

The acceptance suite runs supported browsers and devices where OPFS is available, initializes the database worker, writes the relational fixture, injects interruption, reloads the page, verifies integrity, exports, deletes local state by explicit test setup, and restores from export. It also covers unavailable APIs, private browsing behavior where relevant, quota failure, multiple tabs, sign-out cleanup, and application upgrades. Report browser-specific results rather than assuming one engine proves all. SQLite OPFS passes when the data contract and its limits remain truthful through ordinary failure, not merely when a query works once.

Storage specifications meet recovery practice

SQLite's WASM persistence guide explains the available VFS choices, MDN documents StorageManager.getDirectory, and the File System specification defines the browser primitive beneath OPFS. For product behavior around that primitive, revisit the IndexedDB offline queue, cross-tab locking, ETag autosave conflicts, and service-worker upgrades; those are the seams most likely to disturb a durable database during real application change.

Interrupt the database at named boundaries

A credible browser fixture needs more than a successful reload. Start a transaction that changes several pages, interrupt the worker before commit, repeat after the durable commit marker, reopen through the pinned VFS, and verify both invariants against a logical export. SQLite OPFS is the right result only if the chosen browser matrix recovers consistently, quota refusal leaves the last committed state readable, schema upgrades are transactional, and the product offers an escape route when a user loses access to that origin or device.

Record the VFS name, SQLite WASM version, browser build, storage-persistence status, schema version, transaction fixture, and final logical digest for each interruption point. The SQLite OPFS decision is then grounded in recoverable application state rather than the mere presence of database files, and later runtime upgrades can replay the same custody promise.

SQLite OPFS is responsible browser storage only with tested interruption recovery, limits, and export. Re-run the browser persistence matrix whenever APIs, SQLite WASM, schema, or custody promises move.