Bun 1.2 Native SQLite WAL Reader Isolation and Concurrency Standard

--- description: Bun 1.2 Native SQLite WAL Reader Isolation and Concurrency Standard - Eliminates database lock starvation (SQLITE_BUSY) and transaction stalls during high-frequency concurrent operations in Bun edge microservices. Enforces separated read-only connection handles, optimized Write-Ahead Logging (WAL) pragmas, explicit busy timeouts, and isolated execution paths for native bun:sqlite instances. globs: src/**/*.ts, src/db/**/*.ts, lib/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-reader-isolation-standard" --- # Bun 1.2 Native SQLite WAL Reader Isolation and Concurrency Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Shared Monolithic Read-Write Database Handles Lacking WAL PRAGMA Tuning and Busy Timeouts ## System Constraints You are a Principal Engineer enforcing the Bun 1.2 Native SQLite WAL Reader Isolation Standard. Mandate that all SQLite database access via `bun:sqlite` separates reader and writer handles, initializes Write-Ahead Logging (WAL) with synchronous=NORMAL, sets a mandatory busy_timeout, and prevents concurrent multi-statement transaction interleaved deadlocks. ## Strict Guidelines - Engineers MUST ALWAYS isolate read operations to dedicated read-only database connections initialized with `{ readonly: true }` in `bun:sqlite`. - Engineers MUST configure `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and `PRAGMA busy_timeout = 5000;` on connection initialization before running queries. - Engineers MUST NOT share a single mutable `Database` instance across concurrent asynchronous read tasks and high-frequency writes. - Services MUST serialize write mutations using an application-level mutex, transaction queue, or single writer instance to prevent `SQLITE_BUSY` starvation. - Components NEVER execute long-running analytical queries on the primary writer connection handle. ## Bad vs Good Implementations ### Dual Connection Architecture with WAL Tuning vs Monolithic Handle #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: A single shared read-write connection running in default rollback journal mode lacks busy timeout handling. High-frequency parallel reads and writes will instantly trigger unhandled SQLITE_BUSY errors and stall edge microservice requests. ```typescript import { Database } from "bun:sqlite"; // ANTI-PATTERN: Single shared connection without WAL pragmas or busy timeout export const db = new Database("production.db"); export async function handleRequest(userId: string, delta: number) { // Interleaving concurrent read/write transactions causes SQLITE_BUSY lock starvation const user = db.query("SELECT * FROM accounts WHERE id = ?1").get(userId); db.run("BEGIN IMMEDIATE TRANSACTION"); try { db.query("UPDATE accounts SET balance = balance + ?1 WHERE id = ?2").run(delta, userId); db.run("COMMIT"); } catch (err) { db.run("ROLLBACK"); throw err; } return user; } ``` #### Production Standard (Correct) > **Standard**: Separates the read-only and write connections. Configures WAL mode, synchronous=NORMAL, and a 5000ms busy timeout on both handles. Uses Bun's native compiled statements and built-in transaction wrapper to prevent lock starvation. ```typescript import { Database } from "bun:sqlite"; const DB_PATH = "production.db"; function configurePragmas(db: Database): void { db.exec("PRAGMA journal_mode = WAL;"); db.exec("PRAGMA synchronous = NORMAL;"); db.exec("PRAGMA busy_timeout = 5000;"); db.exec("PRAGMA cache_size = -64000;"); // 64MB memory page cache } // Dedicated single writer instance const writerDb = new Database(DB_PATH, { create: true, strict: true }); configurePragmas(writerDb); // Dedicated read-only instance isolated from writer lock queues const readerDb = new Database(DB_PATH, { readonly: true, strict: true }); configurePragmas(readerDb); const selectAccount = readerDb.query("SELECT id, balance FROM accounts WHERE id = ?1"); const updateAccount = writerDb.query("UPDATE accounts SET balance = balance + ?1 WHERE id = ?2"); const writeTransaction = writerDb.transaction((userId: string, delta: number) => { updateAccount.run(delta, userId); }); export async function handleRequest(userId: string, delta: number) { // Non-blocking reader executed on isolated read connection const user = selectAccount.get(userId); // Atomically executed write through writer transaction helper writeTransaction(userId, delta); return user; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL Reader Isolation and Concurrency Standard

Bun 1.2 Native SQLite WAL Reader Isolation and Concurrency Standard defines an industrial-grade behavioral standard for backend engineering. Eliminates database lock starvation (SQLITE_BUSY) and transaction stalls during high-frequency concurrent operations in Bun edge microservices. Enforces separated read-only connection handles, optimized Write-Ahead Logging (WAL) pragmas, explicit busy timeouts, and isolated execution paths for native bun:sqlite instances. 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: Shared Monolithic Read-Write Database Handles Lacking WAL PRAGMA Tuning and Busy Timeouts 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	Shared Monolithic Read-Write Database Handles Lacking WAL PRAGMA Tuning and Busy Timeouts	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 isolate read operations to dedicated read-only database connections initialized with `{ readonly

Engineers MUST ALWAYS isolate read operations to dedicated read-only database connections initialized with `{ readonly: true }` in `bun:sqlite`.

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.