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

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)
})