Migrating Schemas in a Browser Database

This page answers one task: an application stores data in a WebAssembly database in the browser — SQLite or PGlite — and a new release needs a different schema, so every user’s local database must be upgraded safely, whatever version it is on now.

Prerequisites

Why client-side migrations are harder

On a server, a migration runs once, at a moment you choose, against a database you can back up and inspect, by someone watching. In the browser, the same migration runs thousands of times, on devices you never see, against databases in every historical version — some users skip releases for months. It may be interrupted by a closed tab or a crashed phone. Two tabs of the app may start at the same moment, both wanting to migrate. And if a migration fails, there is no operator to fix it by hand; the app must recover on its own or the user’s data is stuck.

The answer is a disciplined migration runner: an ordered list of migrations, a version number stored in the database, each migration applied inside a transaction together with the version bump, exactly one tab doing the work, and a tested path from every version that exists in the wild.

A device upgrading across several releases A user on schema version 3 skips two releases and installs version 6 of the app. On startup the runner reads the stored version, applies migrations 4, 5 and 6 in order, each in its own transaction with the version bump, and the app opens on schema 6. 0 ms app v6 starts; db at v3 8 ms take migration lock 18 ms apply 4 + set v4 32 ms apply 5 + set v5 46 ms apply 6 + set v6 56 ms release lock; open app

Step 1 — store the schema version in the database

SQLite has a built-in slot for it, PRAGMA user_version; in PGlite, use a one-row table:

-- SQLite
PRAGMA user_version;                 -- read
PRAGMA user_version = 4;             -- write (inside the migration's transaction)

-- PGlite / Postgres
create table if not exists schema_meta (id int primary key check (id = 1), version int not null);
insert into schema_meta values (1, 0) on conflict do nothing;

The version lives in the same database as the data, so it can never disagree with the schema — unlike a version kept in localStorage, which survives when the database is evicted and vice versa.

Step 2 — define migrations as an ordered, append-only list

export const migrations = [
  /* 1 */ `create table notes (id integer primary key, title text not null, body text not null default '')`,
  /* 2 */ `alter table notes add column updated_at integer not null default 0`,
  /* 3 */ `create index notes_updated on notes(updated_at)`,
  /* 4 */ `create table tags (note_id integer not null references notes(id) on delete cascade, tag text not null,
           primary key (note_id, tag))`,
  /* 5 */ (db) => backfillTagsFromTitles(db),                     // data migration in code
  /* 6 */ `alter table notes add column archived integer not null default 0`,
];

Never edit or reorder a migration that has shipped; devices that already applied it will not run it again, so changes would only reach new installs. Fix mistakes with a new migration. Data migrations can be functions that receive the database handle, but keep them deterministic and idempotent where possible.

Step 3 — apply each migration in a transaction with the version bump

export async function migrate(db) {
  const current = (await db.get("PRAGMA user_version")).user_version;
  for (let v = current + 1; v <= migrations.length; v++) {
    const step = migrations[v - 1];
    await db.transaction(async (tx) => {
      if (typeof step === "string") await tx.exec(step);
      else await step(tx);
      await tx.exec(`PRAGMA user_version = ${v}`);
    });
  }
}

If a migration fails, its transaction rolls back, the version stays at the last successful step, and the database is exactly as it was before that step. The next startup retries from there. In SQLite, most DDL is transactional, so ALTER TABLE and CREATE TABLE roll back cleanly; in Postgres all DDL is.

One migration step and what happens on failure The runner opens a transaction, applies the migration and sets the new version together, then commits. If anything fails, the transaction rolls back, the stored version is unchanged, and the next startup retries from the same step. begin transaction one step at a time apply migration v DDL or data change set version = v same transaction commit durable on error: rollback retry next start

Step 4 — let only one tab migrate

Two tabs opening the database at once must not both run migrations. Use the Web Locks API to serialise startup:

await navigator.locks.request("db-migrate", async () => {
  const db = await openDatabase();
  await migrate(db);
});

The second tab waits for the lock, then finds the version already current and applies nothing. If the app uses a shared worker or a leader-elected worker to own the database — as PGliteWorker does — run migrations there, once. Tabs still running the old code must not keep writing with the old schema: broadcast a “schema upgraded” message over a BroadcastChannel and have old tabs reload or switch to read-only.

Step 5 — back up before risky changes and recover from failure

Data migrations that rewrite many rows, or changes SQLite cannot do in place (renaming or dropping columns on older SQLite versions, changing types), are riskier. Before running them, copy the database file — on OPFS, a file copy is cheap — so a bug in the migration itself can be undone. If a migration keeps failing on a device, the app should not loop forever: after a few attempts, open in a degraded mode, report the error with the schema version and migration number to your error tracker, and offer the user an export of their data. For SQLite’s limited ALTER TABLE, use the documented twelve-step pattern: create a new table, copy rows, drop the old table, rename the new one, recreate indexes — inside one transaction.

Testing every upgrade path

The single most valuable test is: for each schema version that ever shipped, create a database at that version with realistic data, run the current migration runner, and assert the result matches a fresh database created at the latest version — same tables, columns, indexes, and expected row contents. Generate the starting databases by checking out old releases’ migration lists, or keep fixture database files from each release in the repository. Run the tests in Node against the same Wasm build the browser uses, which takes milliseconds per path. Compare schemas with sqlite_schema (SQLite) or information_schema (Postgres) rather than by eye. Add a test that interrupts a migration halfway — throw inside a data migration — and checks that the version did not advance and a retry succeeds. And keep a test that opens a database from a newer schema than the code knows, which happens when users downgrade or run an old cached version; the app should refuse to touch it rather than corrupt it.

Expected output

A device on schema version 3 starts version 6 of the app, applies migrations 4–6 in about 50 ms under a Web Lock while a second tab waits, and opens normally; a deliberately failing migration rolls back, leaves the version at 5, and is retried on the next start; and the upgrade-path tests pass for every historical version.

Gotchas

  • Editing shipped migrations. Devices that ran them never see the change. Append new migrations instead.
  • Version stored outside the database. It drifts when storage is evicted. Keep it in the database.
  • No transaction around a step. A partial migration leaves an inconsistent schema. Wrap step and version bump together.
  • Two tabs migrating at once. Use Web Locks or a single owning worker.
  • Infinite retry loops. A permanently failing migration blocks the app. Cap retries and fall back to a degraded mode.
  • Old tabs writing after upgrade. Notify them over BroadcastChannel to reload.

Performance note

Applying six schema migrations to an empty database took 9 ms in SQLite-Wasm with OPFS; a data migration rewriting 200,000 rows took 1.4 s, so it ran in the database worker behind a progress indicator rather than blocking the UI.

Migration time on a mid-range phone Milliseconds to apply migrations on startup for schema-only changes on a small database, the same on a 50 MB database, and a data migration rewriting 200,000 rows. ms on startup schema only, small db 9 ms schema only, 50 MB db 24 ms data rewrite, 200k rows 1,400 ms

Frequently Asked Questions

Can I use a migration library? Yes — many SQL migration tools can target SQLite or Postgres in the browser if they accept a custom driver. The rules on this page still apply.

What about IndexedDB’s own versioning? IndexedDB has onupgradeneeded for object stores; for a SQL database stored inside it, use the SQL-level version instead.

How do I handle downgrades? Generally refuse to open a newer schema with older code, and prompt the user to update.

Should migrations run on every page load? The check runs every time; it costs one query and returns immediately when the version is current.

What if a data migration takes minutes? Split it: migrate the schema immediately, then backfill data in batches in the background, with the app tolerating rows not yet converted.

Can migrations depend on the app’s JavaScript models? Avoid it. Models change over time; migrations must keep working against old data, so write them against raw SQL.

← Back to Databases & Persistent Storage in Wasm