Syncing a Browser Database with a Server

This guide answers one task: take a database running in a browser tab and keep it consistent with a server, in both directions, across offline periods, concurrent edits and the possibility that the browser deletes the local copy without warning.

Prerequisites

  • [ ] A working local database — see running SQLite in the browser with Wasm.
  • [ ] A server API you can shape: at minimum a changes endpoint and a push endpoint.
  • [ ] A stable identifier per client installation, and per row.
  • [ ] A decision, made explicitly, about what “conflict” means in your product.

The three questions sync has to answer

Every sync design answers the same three questions, and the trouble starts when a team answers them implicitly.

What changed locally since the last sync? This requires tracking, not inference. Comparing snapshots does not scale and cannot see a row that was created and deleted between syncs.

What changed remotely since the last sync? This requires the server to expose changes ordered by something monotonic, so the client can ask for everything after a watermark rather than downloading everything.

What happens when the same thing changed in both places? This is a product decision before it is a technical one. “Last write wins” is a legitimate answer; so is “the server always wins”; so is “show the user both”. What is not legitimate is not deciding, because the code will then decide arbitrarily and differently in each code path.

One sync cycle Local edits accumulate in a change log. A cycle pushes unsent changes, pulls remote changes after the stored watermark, resolves any overlap, applies the result and advances the watermark atomically. local change log rows awaiting push push idempotent, batched pull since watermark ordered, paged apply + advance one transaction The last box is the one people get wrong: applying changes and advancing the watermark must be atomic, or an interruption loses changes silently. Everything before it can fail freely — a failed push simply leaves the log intact for the next cycle. Run the cycle on reconnect, on an interval, and after a burst of local edits settles.

Tracking local changes with triggers

The local database can record its own changes. Triggers keep the log correct even when a code path forgets to write to it, which over a year of development is every code path at least once.

CREATE TABLE IF NOT EXISTS change_log (
  seq     INTEGER PRIMARY KEY AUTOINCREMENT,
  tbl     TEXT NOT NULL,
  row_id  TEXT NOT NULL,
  op      TEXT NOT NULL,             -- insert | update | delete
  at      INTEGER NOT NULL
);

CREATE TRIGGER notes_ai AFTER INSERT ON notes BEGIN
  INSERT INTO change_log (tbl, row_id, op, at) VALUES ('notes', NEW.uid, 'insert', unixepoch('subsec') * 1000);
END;
CREATE TRIGGER notes_au AFTER UPDATE ON notes BEGIN
  INSERT INTO change_log (tbl, row_id, op, at) VALUES ('notes', NEW.uid, 'update', unixepoch('subsec') * 1000);
END;
CREATE TRIGGER notes_ad AFTER DELETE ON notes BEGIN
  INSERT INTO change_log (tbl, row_id, op, at) VALUES ('notes', OLD.uid, 'delete', unixepoch('subsec') * 1000);
END;

Two design points matter here. Row identifiers should be client-generated — a UUID or similar — so a row created offline has its final identity immediately and does not need remapping when the server sees it. And deletes need a record, which is why the log stores the identifier rather than relying on the row that no longer exists.

Pushing changes idempotently

The network will interrupt a push after the server committed and before the client learned about it. Every push must therefore be safe to repeat, which means the server keys on something the client generated rather than on receipt order.

async function push(db, endpoint) {
  const pending = db.selectObjects(`
    SELECT c.seq, c.tbl, c.row_id, c.op, n.*
    FROM change_log c LEFT JOIN notes n ON n.uid = c.row_id
    ORDER BY c.seq LIMIT 500
  `);
  if (!pending.length) return 0;

  const res = await fetch(endpoint, {
    method: 'POST',
    headers: { 'content-type': 'application/json', 'idempotency-key': batchKey(pending) },
    body: JSON.stringify({ clientId, changes: pending }),
  });
  if (!res.ok) throw new Error(`push failed: ${res.status}`);

  const { acceptedThrough } = await res.json();
  db.exec({ sql: 'DELETE FROM change_log WHERE seq <= ?', bind: [acceptedThrough] });
  return pending.length;
}

Delete from the log only after the server confirms, and only up to the sequence it confirms. Clearing the whole log on a partial success is how changes vanish, and the bug is nearly impossible to reproduce because it needs an interrupted request at exactly the wrong moment.

Pulling remote changes by watermark

The server exposes changes after a cursor. A monotonically increasing sequence number is better than a timestamp — clocks disagree, and two rows can share a millisecond.

async function pull(db, endpoint) {
  const since = db.selectValue('SELECT value FROM sync_state WHERE key = ?', ['watermark']) ?? '0';
  const res = await fetch(`${endpoint}?since=${encodeURIComponent(since)}&limit=1000`);
  const { changes, nextWatermark, more } = await res.json();

  db.transaction(() => {
    for (const ch of changes) applyRemote(db, ch);
    db.exec({ sql: 'INSERT OR REPLACE INTO sync_state (key, value) VALUES (?, ?)',
              bind: ['watermark', nextWatermark] });
  });
  return more;
}

Applying changes and storing the new watermark inside one transaction is the crucial detail. If the tab closes between them, the next cycle either re-applies the same changes — harmless, because application is idempotent by row identifier — or has already advanced past them, which would silently skip data.

Resolving conflicts you can explain

A conflict is a row changed in both places since the last sync. Detecting it needs a version per row, which the server maintains and the client stores.

function applyRemote(db, ch) {
  const local = db.selectObject('SELECT uid, version, dirty FROM notes WHERE uid = ?', [ch.uid]);
  if (!local) return insertRemote(db, ch);
  if (!local.dirty || local.version === ch.baseVersion) return updateFromRemote(db, ch);
  resolve(db, local, ch);            // genuine conflict
}

Three resolutions cover almost every product. Server wins is simplest and correct when the server is authoritative — the local edit is discarded and the user told. Last write wins by timestamp is acceptable for low-stakes data and quietly loses edits, so say so in the interface. Keep both creates a second row or a merge prompt, which is the only honest answer for documents and notes people care about.

Field-level merging is the fourth option and is far more work: it requires per-field versions and a merge rule per field, and it only pays for itself in genuinely collaborative products. If you are heading that way, look at conflict-free replicated data types rather than building a bespoke merge, because the edge cases are numerous and non-obvious.

Pick a rule and tell the user about it Server wins discards the local edit, last write wins discards one edit silently, and keeping both surfaces the conflict to the user. Each is defensible; not choosing one is not. server wins local edit discarded simple, predictable tell the user it happened last write wins newest timestamp survives clock skew decides ties silently loses an edit keep both duplicate row or prompt no data lost right for user documents Whichever you pick, make it visible: a sync that quietly discards work erodes trust faster than one that occasionally asks a question.

Recovering from eviction

Browser storage can disappear. When the local database is gone, the client must be able to rebuild from the server without pretending it is a fresh installation — otherwise it re-pushes nothing and silently loses anything that had not synced.

Handle it explicitly: on startup, if the schema is missing but the client identifier persists elsewhere, perform a full pull from watermark zero and inform the user that local unsynced work may have been lost. Keeping the client identifier in a separate, smaller store makes this detectable. It is not a pleasant message, but it is far better than a client that silently diverges.

What a sync round actually does Changes are recorded locally as they happen, pushed as a batch, and the server's answer is applied back. Conflicts are resolved by a rule chosen in advance, not at the moment they appear. local writes recorded in a log push batch since last cursor server merges applies the rule pull and apply local state updated Decide the conflict rule before shipping: last-write-wins, per-field merge, or a queue for a human. Every batch needs an idempotency key, because a retried push must not apply the same change twice. Keep the cursor durable with the data; a cursor lost on reload means a full resync.

Gotchas

  • Server-assigned identifiers. A row created offline has no server identifier, so every reference to it needs remapping later. Generate identifiers on the client.
  • Timestamps as watermarks. Clock skew and equal milliseconds both break ordering. Use a server-side sequence.
  • Clearing the change log optimistically. Only delete up to the sequence the server confirmed.
  • Applying remote changes outside a transaction. A partial apply plus an advanced watermark loses data permanently.
  • Sync running in several tabs at once. Elect one owner, as with the database connection itself.
  • No backoff on failure. A client that retries a failing sync every second becomes a denial-of-service attack on your own API when something breaks.

Performance note

For a dataset of about 50,000 rows with a few hundred changes per session, a full cycle — push, pull, apply — takes roughly 200–600 ms, almost entirely network. Batching pushes at 500 changes and pulls at 1000 keeps individual requests small enough to retry cheaply. The local apply is the fastest part: 1000 upserts inside one transaction take around 40 ms, which is why the transaction boundary should wrap the whole batch rather than each row.

Frequently Asked Questions

Do I need a change log if the server is authoritative? If the client can edit offline, yes. Without a log there is no record of what to send when the connection returns. A read-only client that never edits can skip it entirely and just pull.

How often should sync run? On reconnect, on a slow interval — thirty to sixty seconds — and after local edits settle. Syncing on every keystroke wastes requests and makes conflicts more likely rather than less.

Can I use WebSockets instead of polling? Yes, for the pull direction: the server pushes a notification and the client runs a cycle. Keep the watermark-based pull as the mechanism; the socket is a hint that changes exist, not a replacement for ordered, resumable delivery.

What should the user see while a sync is running? A small, honest indicator: the time of the last successful cycle, whether anything is pending, and an error state that offers a retry. Hiding sync entirely works right up until it fails, at which point the user has no way to tell whether their work is safe.

← Back to Databases & Persistent Storage in Wasm