SQLite persistence: recover or provide helper for corrupt local DB files

[KyleAMathews]

Summary

A downstream Electron app hit a startup failure when its TanStack DB SQLite persistence file became corrupt/truncated. The app uses TanStack DB with SQLite persistence as a local sync/cache DB; once the SQLite file was malformed, every launch failed until the file was manually removed.

Downstream PR with repro context and a proposed app-level workaround: superset-sh/superset#5085

Observed downstream failure

After an app auto-update/restart, the local tanstack-db.sqlite file was truncated/corrupt. On subsequent launches, TanStack DB persistence initialization threw via better-sqlite3, for example:

SqliteError: database disk image is malformed
code: SQLITE_CORRUPT

Because this occurred during app startup, the downstream Electron app failed before creating a window and remained as a windowless process. That windowless-process behavior is app-specific, but the underlying persistence failure mode is relevant here: a corrupt local TanStack DB SQLite persistence file can permanently brick startup until the DB file is manually deleted/quarantined.

Why this may belong in TanStack DB

For SQLite-backed persistence that functions as a rebuildable sync/cache store, corruption recovery could be handled or at least made easier at the TanStack DB persistence layer. Downstream apps should not each need to rediscover the same recovery pattern for SQLITE_CORRUPT / SQLITE_NOTADB cache files.

Proposed fix / API shape

Consider adding one of these to the SQLite persistence package:

1. Built-in corruption recovery option, e.g.

createNodeSQLitePersistence({
  database,
  // or dbPath, depending on API direction
  recoverCorruptDatabase: true,
  quarantineCorruptDatabase: true,
})

2. A helper/wrapper that downstream apps can use around SQLite persistence initialization:

openSqliteWithRecovery(dbPath, label, () => {
  const database = new Database(dbPath)
  const persistence = createNodeSQLitePersistence({ database })
  return { database, persistence }
})

Recommended recovery behavior:

• Detect SQLite corruption-style errors:
  • SQLITE_CORRUPT
  • SQLITE_CORRUPT_*
  • SQLITE_NOTADB
• Close any opened DB handle before file operations.
• Rename/quarantine the corrupt DB rather than deleting it, e.g. <db>.corrupt-<timestamp>.
• Also quarantine sidecar files if present:
  • <db>-wal
  • <db>-shm
• Retry opening once against a fresh DB path.
• Rethrow non-corruption errors.
• Rethrow if the retry also fails, to avoid infinite retry loops.

Implementation note

If the helper creates the better-sqlite3 handle, make sure construction is inside the try and close the handle in catch if one was created:

let opened: Database.Database | undefined

try {
  opened = new Database(dbPath)
  const persistence = createNodeSQLitePersistence({ database: opened })
  return { database: opened, persistence }
} catch (error) {
  opened?.close()
  throw error
}

This avoids relying on native cleanup behavior if better-sqlite3 throws during construction and helps on Windows where open handles can prevent renaming/quarantining the corrupt file.

Acceptance criteria

• SQLite corruption errors can be identified consistently.
• A corrupt persistence DB can be quarantined and recreated without manual deletion.
• -wal and -shm sidecars are handled with the main DB.
• Recovery retries once, not indefinitely.
• Non-corruption errors are not swallowed.
• Tests cover corruption detection, quarantine behavior, retry-once behavior, non-corruption rethrow, and second-failure propagation.