Skip to content

bug: node:sqlite connection lacks busy_timeout -> intermittent "database is locked" #673

Description

@vivek7405

Problem

The scaffolded/blog SQLite connection throws intermittent Error: database is locked (code: 'ERR_SQLITE_ERROR') under concurrent access. This is a regression from #670 (the better-sqlite3 removal): node:sqlite's DatabaseSync defaults to PRAGMA busy_timeout = 0 (verified at runtime: new DatabaseSync(':memory:').prepare('PRAGMA busy_timeout').get() returns { timeout: 0 }), whereas better-sqlite3 defaulted to a 5000ms busy timeout (its timeout constructor option defaults to 5000). So before #670 the connection had an implicit 5s busy timeout; after it, concurrent writers immediately get SQLITE_BUSY.

This flakes the "In-repo app tests (website + blog)" CI job: the failing test MOVES run-to-run (currentUser / createComment one run, login the next), the signature of lock contention, all with Error: database is locked. It affects main, so every PR's in-repo app test job is now intermittently red (it blocked PR #672, whose content was unrelated).

Design / approach

After opening the raw SQLite client (before handing it to drizzle), set a busy timeout to restore the pre-#670 behavior, and WAL journal mode to reduce writer/reader contention:

const client = new DatabaseSync(url);
client.exec('PRAGMA busy_timeout = 5000');   // restores better-sqlite3's old default
client.exec('PRAGMA journal_mode = WAL');     // concurrent readers + one writer
return drizzle({ client, relations: schema.relations });

busy_timeout = 5000 alone is the minimal parity fix; WAL is the robustness add. Apply to the node:sqlite branch and the bun:sqlite branch (verify Bun's Database default busy_timeout and set it for parity).

Implementation notes (for the implementing agent)

  • Where to edit: the connection generator packages/cli/lib/create.js (the runtime-neutral db/connection.server.ts it emits, the node:sqlite branch with new DatabaseSync(url) and the bun:sqlite branch with new Database(url)), and the in-repo blog connection examples/blog/db/connection.server.ts (same shape). The connection keeps the if (globalThis.Bun) runtime switch from feat: remove better-sqlite3, use native node:sqlite + bun:sqlite #670.
  • The pragma MUST run on the raw driver client BEFORE drizzle wraps it (drizzle({ client })).
  • Landmines: WAL creates -wal / -shm sidecar files for a file DB (fine; a no-op for :memory:). Keep the connection runtime-neutral. The blog test suite runs tests concurrently against one dev.db, which is what exposes this; reproduce by running cd examples/blog && npm test a few times (or with concurrency) and confirm no database is locked.
  • Invariants: the .server.ts boundary keeps the driver server-only (invariant 1); runtime parity (Support both Bun and Node runtimes (first-class create + run) #508), so prove on Node AND Bun.
  • Tests + docs: a connection test asserting rapid concurrent writes do not throw database is locked (a Node test + test/bun/* for Bun); re-run the blog suite to confirm the flake is gone (the counterfactual: without the pragma, concurrent writes throw). Update agent-docs/built-ins.md (DB section) and the generated connection comment to note the busy_timeout/WAL pragmas.

Acceptance criteria

  • The generated + blog connections set busy_timeout (and WAL) on both the node:sqlite and bun:sqlite branches.
  • Rapid concurrent writes no longer throw database is locked (proven by a test; counterfactual: removing the pragma reproduces the throw).
  • The "In-repo app tests (website + blog)" job is stable across repeated runs.
  • Proven on Node AND Bun.
  • Docs (agent-docs/built-ins.md + the connection comment) updated.

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Type

No type
No fields configured for issues without a type.

Projects

Status
Done

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions