Skip to content
Source
Sign inSign up
TanStack/db

browser-db-sqlite-persistence: closing a tab mid-write corrupts the OPFS database for tabs that stay open #2357

Send to my agent
Openjustshrey(GitHub profile, opens in a new tab) opened this issue 0 comments
  • [X ] I've validated the bug against the latest version of DB packages (@tanstack/browser-db-sqlite-persistence@1.0.0-rc.1.3 from npm, and main at 8feab6ea)

Describe the bug Two tabs share one OPFS database. One tab is closed while it is in the middle of a write transaction. The tab that stays open then reads the half-finished write: its queries fail with database disk image is malformed, or return rows from a transaction that never committed. If the surviving tab writes anything afterwards, the damage becomes permanent, and reloading every tab does not bring the data back.

The cause is in wa-sqlite's OPFSCoopSyncVFS, which this package bundles into its OPFS worker. I have reported it upstream, with a fix:

In short: SQLite recovers from an interrupted write by rolling back the journal the writer left behind. The VFS records which files exist once, when a connection opens, and never looks again. A journal left later by another tab is not in that record, so the VFS tells SQLite "no journal", and SQLite skips the rollback. A tab opened after the writer died recovers correctly; a tab that already had the database open does not.

I am filing here too because the upstream fix will not reach this package's users by itself. See "Why this needs a change here" below.

To Reproduce No collections, sync engine, or settings are involved. The reproduction uses only openBrowserWASQLiteOPFSDatabase() and plain SQL at default settings.

StepTab A (writer)Tab B (survivor)
1Opens the database, commits 4,000 rows (~4 MB)
2Opens the same database, reads 4,000 rows
3BEGIN IMMEDIATE; UPDATE every row (no COMMIT)
4Tab is closed
5Reads again: database disk image is malformed

At step 5 the rollback journal is on disk (about 4.1 MB) with everything needed to undo step 3. Tab B never reads it.

install chromiumif Playwright's Chromium is missing. 3.bunx playwright test`

Result on 1.0.0-rc.1.3, identical in 5 of 5 runs:

✓ control: a tab opened after the writer died sees the committed state
✘ bug: a tab that was already open sees a half-written database
✘ bug: the survivor's next write makes the damage permanent

-   "integrity_check": "ok",
+   "integrity_check": "*** in database main ***
+ Tree 2 page 521 cell 14: Rowid 2060 out of order",
-   "n": 4000,
+   "error": "OPFSWorkerRequestError: database disk image is malformed",
The five files

package.json

{
  "private": true,
  "type": "module",
  "dependencies": {
    "@journeyapps/wa-sqlite": "1.7.2",
    "@tanstack/browser-db-sqlite-persistence": "1.0.0-rc.1.3",
    "@tanstack/db": "1.0.0-rc.1.3"
  },
  "devDependencies": {
    "@playwright/test": "1.64.1",
    "vite": "8.3.6"
  }
}

index.html

<!doctype html>
<meta charset="utf-8" />
<title>OPFS hot-journal repro</title>
<script type="module" src="/main.ts"></script>

main.ts

// The whole "app": open one OPFS SQLite database through the public API.
import { openBrowserWASQLiteOPFSDatabase } from '@tanstack/browser-db-sqlite-persistence'

const databaseName =
  new URLSearchParams(location.search).get(`db`) ?? `repro.sqlite`

;(window as any).db = await openBrowserWASQLiteOPFSDatabase({ databaseName })

playwright.config.ts

import { defineConfig } from '@playwright/test'

const port = 5179

export default defineConfig({
  testDir: `.`,
  testMatch: `*.spec.ts`,
  timeout: 60_000,
  workers: 1,
  use: { baseURL: `http://localhost:${port}` },
  webServer: {
    command: `vite --port ${port} --strictPort`,
    url: `http://localhost:${port}`,
    reuseExistingServer: false,
  },
})

repro.spec.ts

import { expect, test } from '@playwright/test'
import type { BrowserContext, Page } from '@playwright/test'

type Row = Record<string, unknown>

async function openTab(context: BrowserContext, db: string): Promise<Page> {
  const page = await context.newPage()
  await page.goto(`/?db=${db}`)
  await page.waitForFunction(() => (window as any).db !== undefined)
  return page
}

const sql = (page: Page, statement: string): Promise<Array<Row>> =>
  page.evaluate((s) => (window as any).db.execute(s), statement)

/** First row of a query, or the error it failed with. */
const ask = (page: Page, statement: string): Promise<Row> =>
  page.evaluate(
    (s) =>
      (window as any).db.execute(s).then(
        (rows: Array<Row>) => rows[0],
        (error: unknown) => ({ error: String(error) }),
      ),
    statement,
  )

/** Size of an OPFS file as the browser reports it, independent of SQLite. */
const fileBytes = (page: Page, file: string): Promise<number> =>
  page.evaluate(async (name) => {
    const root = await navigator.storage.getDirectory()
    return (await (await root.getFileHandle(name)).getFile()).size
  }, file)

// Committed state: 4,000 rows, each holding a 1,000-character value (~4 MB).
const SEED = `
  CREATE TABLE t (id INTEGER PRIMARY KEY, value TEXT);
  CREATE TABLE note (text TEXT);
  WITH RECURSIVE n(x) AS (VALUES (1) UNION ALL SELECT x + 1 FROM n WHERE x < 4000)
  INSERT INTO t SELECT x, printf('%01000d', x) FROM n;
`

// A transaction that is never committed. It rewrites more data than SQLite's
// default 2 MB page cache holds, so SQLite has to start writing changed pages
// into the database file before COMMIT. That is normal, and it is exactly what
// the rollback journal exists to undo.
const WRITE_WITHOUT_COMMIT = `
  BEGIN IMMEDIATE;
  UPDATE t SET value = printf('%02000d', id);
`

const COMMITTED_STATE = {
  integrity: { integrity_check: `ok` },
  rows: { n: 4000 },
  rowsWithUncommittedValue: { n: 0 },
}

async function observe(page: Page) {
  return {
    integrity: await ask(page, `PRAGMA integrity_check(1)`),
    rows: await ask(page, `SELECT count(*) AS n FROM t`),
    rowsWithUncommittedValue: await ask(
      page,
      `SELECT count(*) AS n FROM t WHERE length(value) <> 1000`,
    ),
  }
}

test(`control: a tab opened after the writer died sees the committed state`, async ({
  context,
}) => {
  const db = `control-${Date.now()}.sqlite`
  const writer = await openTab(context, db)
  await sql(writer, SEED)

  await sql(writer, WRITE_WITHOUT_COMMIT)
  await writer.close()

  const survivor = await openTab(context, db)
  expect(await fileBytes(survivor, `${db}-journal`)).toBeGreaterThan(0)
  expect(await observe(survivor)).toEqual(COMMITTED_STATE)
})

test(`bug: a tab that was already open sees a half-written database`, async ({
  context,
}) => {
  const db = `survivor-${Date.now()}.sqlite`
  const writer = await openTab(context, db)
  await sql(writer, SEED)

  const survivor = await openTab(context, db)
  expect(await observe(survivor)).toEqual(COMMITTED_STATE)

  await sql(writer, WRITE_WITHOUT_COMMIT)
  await writer.close()

  // The rollback journal is on disk, holding the original pages.
  expect(await fileBytes(survivor, `${db}-journal`)).toBeGreaterThan(0)
  expect(await observe(survivor)).toEqual(COMMITTED_STATE)
})

test(`bug: the survivor's next write makes the damage permanent`, async ({
  context,
}) => {
  const db = `permanent-${Date.now()}.sqlite`
  const writer = await openTab(context, db)
  await sql(writer, SEED)

  const survivor = await openTab(context, db)
  expect(await observe(survivor)).toEqual(COMMITTED_STATE)

  await sql(writer, WRITE_WITHOUT_COMMIT)
  await writer.close()

  // Any committed write by the survivor reuses and then empties the journal,
  // destroying the only copy of the original pages.
  await sql(survivor, `INSERT INTO note VALUES ('written by the survivor')`)
  expect(await fileBytes(survivor, `${db}-journal`)).toBe(0)
  await survivor.close()

  // A brand-new tab has nothing cached and opens the database from scratch.
  const later = await openTab(context, db)
  expect(await observe(later)).toEqual(COMMITTED_STATE)
})

Expected behavior After tab A is closed, tab B reads the last committed state: 4,000 rows with their original values, and PRAGMA integrity_check returns ok. The unfinished transaction is lost, which the README already documents for pagehide. It must not be half-applied. The control test shows that the same interrupted write is fully recoverable when the journal is seen.

Desktop (please complete the following information):

  • OS: Ubuntu 26.04 (arm64)
  • Browser: Chromium
  • Version: 156.0.8078.4 (Playwright 1.64.1, standalone reproduction) and 148.0.7778.96 (Playwright 1.60.0, this repository's OPFS harness)

Firefox and Safari are untested. The faulty logic is plain JavaScript in the VFS, so I expect them to behave the same.

Additional context

Why this needs a change here, and not only upstream.

This package makes the trigger ordinary. The connection terminates its worker synchronously on pagehide, so closing a tab stops SQLite wherever it is. The database is exposed during any transaction larger than the page cache (2 MB by default), such as an initial sync; that is the case the reproduction exercises. It is also exposed briefly during every COMMIT. I did not test that window directly, it follows from SQLite's commit sequence. In our application's two-tab leader-close stress test, 12 of 80 runs failed, logging database disk image is malformed thousands of times.

  • Apps cannot fix it by upgrading wa-sqlite. The published dist/assets/opfs-worker.js has wa-sqlite 1.5.0 compiled in. The @journeyapps/wa-sqlite version an app installs as a peer does not change the worker's code.

I can confirm that the upstream fix also fixes this package. I applied the change from rhashimoto/wa-sqlite#393 to the 1.5.0 source that main bundles (6 lines added, 5 removed) and changed nothing in this package. In this repository's OPFS harness the two failing cases then passed in 3 of 3 runs, and the existing Chromium OPFS suite still passed, 48 of 48. With the original file restored, both cases failed again.

How would you like me to create a PR for this? Based on the rules for PR - might be easiest for now to do this as a patch on @journeyapps/wa-sqlite@1.5.0 (pnpm patchedDependencies) and drop it when a fixed release is adopted. No source file in this package should need any change?.

Alternatively - we wait and update the @journeyapps/wa-sqlite release? is there a reason we have basically fixed it at ^1.4.1 ? Its currently at 2.0.10?

Sign in to join this conversation.