SQLite in the browser and the end of the API round trip

SQLite compiled to WebAssembly, with an origin-private filesystem backing store, gives the browser a real relational database with real SQL, real transactions, and real durability.

That is a bigger change to web application architecture than it first appears.

what it replaces#

The default architecture for a data-heavy web application is: the browser holds almost nothing, every view is a request, and the server does the querying.

That produces:

• A round trip for every interaction, at whatever latency the user's network has.
• An API endpoint per view, because generic endpoints are inefficient and specific ones proliferate.
• A loading state for everything, and the accompanying skeleton screens.
• Nothing works offline.
• Server cost proportional to reads, which are the overwhelming majority of traffic.

If the client has the data, all of that changes. A view is a query against local storage. It returns in a millisecond. There is no loading state because there is no load.

the practical setup#

import { sqlite3Worker1Promiser } from '@sqlite.org/sqlite-wasm';

const db = await sqlite3Worker1Promiser.v2();
await db('open', { filename: 'file:app.sqlite3?vfs=opfs' });

await db('exec', {
  sql: `select id, title, updated_at from notes
        where project_id = ? order by updated_at desc limit 50`,
  bind: [projectId],
  rowMode: 'object',
});

Run it in a worker. The synchronous access handle API — which is what makes durable storage fast — is only available off the main thread, and you did not want to block the main thread with database I/O anyway.

the design questions this forces#

How much data does a client get? All of it is fine for a personal notes app. It is catastrophic for a system where a user may see a tiny fraction of a large corpus, and it is a security problem if the partition is not enforced server-side.

The general answer is a working set: the user's own data, recently accessed shared data, and lazy loading for the rest. Deciding what is in the working set is the hard design problem and it is application-specific.

How does it sync? This is where the complexity lives. Options range from "pull changes since a watermark and last-write-wins" to full CRDT-based merge. The right answer depends on whether concurrent edits to the same record are possible and what should happen when they are.

Start with the simplest thing that is correct for your data. Most applications have very little genuine concurrent editing and do not need CRDTs.

What about permissions? The client has the data, so the client can read the data, so anything the client should not see must not be synced. Your sync layer has to understand your authorization model.

This is the constraint that rules out local-first for a lot of enterprise applications, and pretending otherwise is how you build a data leak.

What about migrations? Users have databases on their machines in old schemas. You need versioned migrations that run on the client, and you need to handle a client that has been offline across four versions.

Build this on day one. It is very painful to retrofit.

what it is genuinely good for#

• Applications with a personal working set: notes, tasks, editors, trackers, design tools, dashboards over a user's own data.
• Anything where the user expects instant interaction.
• Anything that should work on a plane.
• Anything where read traffic dominates and server costs scale with it.

what it is not good for#

• Large shared datasets with narrow per-user views.
• Anything requiring server-authoritative state.
• Anything with fine-grained dynamic permissions.
• Regulatory environments where data on client devices is restricted.

the performance note that surprises people#

For a dataset that fits — and "fits" is generous, tens of megabytes is unremarkable — local SQL queries are faster than a network request by roughly three orders of magnitude.

That is not an optimization. It is a different category of user experience, and users notice it immediately even if they cannot say why.

The application that responds instantly to every action feels like a tool. The one with a spinner on every click feels like a website. That distinction is worth more than most features.