browser-db-sqlite-persistence: closing a tab mid-write corrupts the OPFS database for tabs that stay open #2357
Send to my agent- [X ] I've validated the bug against the latest version of DB packages (
@tanstack/browser-db-sqlite-persistence@1.0.0-rc.1.3from npm, andmainat8feab6ea)
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:
- rhashimoto/wa-sqlite#392 (the bug, reproduced with wa-sqlite alone)
- rhashimoto/wa-sqlite#393 (pull request pending)
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.
| Step | Tab A (writer) | Tab B (survivor) |
|---|---|---|
| 1 | Opens the database, commits 4,000 rows (~4 MB) | |
| 2 | Opens the same database, reads 4,000 rows | |
| 3 | BEGIN IMMEDIATE; UPDATE every row (no COMMIT) | |
| 4 | Tab is closed | |
| 5 | Reads 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.jshas wa-sqlite 1.5.0 compiled in. The@journeyapps/wa-sqliteversion 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.