Bun 1.2 Native SQLite WAL Isolation and Multi-Threaded Concurrency Guardrail

--- description: Bun 1.2 Native SQLite WAL Isolation and Multi-Threaded Concurrency Guardrail - Mandates write-ahead logging (WAL), synchronous normal modes, tuned busy timeouts, and immediate transaction locking for Bun 1.2 native bun:sqlite multiplexing across clustered workers. Eliminates SQLITE_BUSY contention deadlocks, connection exhaustion, and dirty reads during concurrent read/write transactions across OS threads. globs: src/**/*.{ts,js}, workers/**/*.{ts,js}, lib/db/**/*.{ts,js} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-concurrency-isolation-production-standard" --- # Bun 1.2 Native SQLite WAL Isolation and Multi-Threaded Concurrency Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Ad-Hoc Unsynchronized bun:sqlite Handles with Deferred Transactions across Bun Workers ## System Constraints You are a Principal Software Architect enforcing production safety for Bun 1.2 native SQLite (bun:sqlite). You must enforce strict Write-Ahead Logging (WAL) configuration, appropriate busy timeouts, and immediate transaction semantics across multi-threaded and worker environments. Reject ad-hoc unpooled connection instances, deferred write transactions that risk lock-upgrade deadlocks, and unindexed concurrent queries. ## Strict Guidelines - Engineers MUST ALWAYS configure `PRAGMA journal_mode = WAL;` and `PRAGMA synchronous = NORMAL;` immediately upon opening any bun:sqlite database handle intended for multi-threaded access. - Engineers MUST ALWAYS set `PRAGMA busy_timeout = 5000;` (or higher depending on SLA) to allow Bun clustered workers to wait for locked WAL frames rather than instantly throwing SQLITE_BUSY. - Engineers MUST ALWAYS use `BEGIN IMMEDIATE` or Bun's `.transaction()` with explicit immediate write isolation when performing mutations to avoid deadlock during write-lock escalation. - Engineers MUST NOT instantiate new `new Database(path)` instances per HTTP request; database handles MUST be managed as persistent singletons per worker thread. - Services MUST execute `PRAGMA wal_autocheckpoint` management on designated coordinator processes to avoid checkpoint starvation during sustained write bursts. - Workers NEVER execute destructive operations like `VACUUM` or explicit DDL alterations concurrently while active reader workers hold open transaction snapshots. ## Bad vs Good Implementations ### Configuring bun:sqlite for Thread-Safe WAL Concurrency vs Ad-Hoc Deferred Locking #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Opening database handles per request bypasses Bun's native connection caching and creates race conditions across OS threads. Default DELETE journal mode blocks concurrent readers during writes, and deferred transaction start causes upgrade deadlocks under high-frequency writes. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Connection opened per request without WAL or busy_timeout configuration export async function handleWriteRequest(payload: { id: string; data: string }) { const db = new Database('app.db'); // Opens database handle with default DELETE journal mode and 0ms busy timeout // Deferred transaction: SQLite starts in read mode and tries to upgrade to write lock // This throws 'SQLITE_BUSY: database is locked' under concurrent Bun worker load db.run('BEGIN TRANSACTION'); try { db.query('INSERT INTO audit_events (id, data) VALUES (?1, ?2)').run(payload.id, payload.data); db.run('COMMIT'); } catch (err) { db.run('ROLLBACK'); throw err; } finally { db.close(); } } ``` #### Production Standard (Correct) > **Standard**: Initializes SQLite in WAL mode with synchronous=NORMAL and a 5000ms busy timeout. Uses an application-scoped singleton handle with prepared statements and Bun's native immediate transaction wrapper, guaranteeing starvation-free shared memory isolation across clustered workers. ```typescript import { Database } from 'bun:sqlite'; // PRODUCTION INVARIANT: Persistent per-worker singleton configured with WAL and busy timeout class DatabaseClient { private static instance: Database | null = null; public static getInstance(): Database { if (!DatabaseClient.instance) { const db = new Database('app.db', { create: true }); // Enforce WAL mode, normal sync for fast durability, and 5s busy wait retry db.exec(` PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA busy_timeout = 5000; PRAGMA foreign_keys = ON; `); DatabaseClient.instance = db; } return DatabaseClient.instance; } } export async function handleWriteRequest(payload: { id: string; data: string }): Promise<void> { const db = DatabaseClient.getInstance(); const insertStmt = db.prepare('INSERT INTO audit_events (id, data) VALUES (?1, ?2)'); // db.transaction() in bun:sqlite defaults to BEGIN IMMEDIATE semantics, preventing lock upgrade races const insertTransaction = db.transaction((id: string, data: string) => { insertStmt.run(id, data); }); insertTransaction(payload.id, payload.data); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL Isolation and Multi-Threaded Concurrency Guardrail

Bun 1.2 Native SQLite WAL Isolation and Multi-Threaded Concurrency Guardrail defines an industrial-grade behavioral standard for backend engineering. Mandates write-ahead logging (WAL), synchronous normal modes, tuned busy timeouts, and immediate transaction locking for Bun 1.2 native bun:sqlite multiplexing across clustered workers. Eliminates SQLITE_BUSY contention deadlocks, connection exhaustion, and dirty reads during concurrent read/write transactions across OS threads. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Ad-Hoc Unsynchronized bun:sqlite Handles with Deferred Transactions across Bun Workers Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Ad-Hoc Unsynchronized bun:sqlite Handles with Deferred Transactions across Bun Workers	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

Engineers MUST ALWAYS configure `PRAGMA journal_mode = WAL

Engineers MUST ALWAYS configure `PRAGMA journal_mode = WAL;` and `PRAGMA synchronous = NORMAL;` immediately upon opening any bun:sqlite database handle intended for multi-threaded access.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.