Bun 1.2 Native SQLite WAL Structured Concurrency & Single-Writer Invariant

--- description: Bun 1.2 Native SQLite WAL Structured Concurrency & Single-Writer Invariant - Enforces strict single-writer serialized queuing and concurrent multi-reader pools for Bun 1.2's native `bun:sqlite` engine under Write-Ahead Logging (WAL) mode. Prevents catastrophic SQLITE_BUSY / 'database is locked' panics in multi-threaded edge worker environments by decoupling write mutations into a dedicated serialization lane with structured concurrency controls. globs: src/**/*.ts, src/db/**/*.ts, src/repositories/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-structured-concurrency" --- # Bun 1.2 Native SQLite WAL Structured Concurrency & Single-Writer Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unsynchronized Concurrent Multi-Threaded Writes directly opening 'bun:sqlite' in WAL Mode Triggering SQLITE_BUSY Exceptions ## System Constraints You are a Principal Backend Systems Architect enforcing the Bun 1.2 Native SQLite Structured Concurrency WAL Invariant. SQLite in WAL mode permits concurrent readers but strictly enforces exactly ONE writer at any given instant across all threads and processes. You must mandate that all mutations (INSERT, UPDATE, DELETE, schema DDL) route through an in-memory asynchronous serialized lane (or dedicated writer Worker) backed by a dedicated Database connection with busy timeouts and synchronous=NORMAL. Under no circumstances may arbitrary requests or worker threads execute write transactions concurrently. ## Strict Guidelines - Engineers MUST ALWAYS configure SQLite instances with `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and `PRAGMA busy_timeout = 5000;` immediately upon opening via `bun:sqlite`. - Engineers MUST serialize all mutating transactions through a singular async Mutex or dedicated single-writer Worker lane, guaranteeing zero competing write locks. - Engineers MUST NOT initialize ad-hoc `Database` instances inside individual ephemeral HTTP handlers or concurrent Worker threads without routing writes through the unified writer coordinator. - Code SHOULD separate read-only queries (executed against pooled read-only `Database` instances opened with `readonly: true`) from write mutations to preserve maximum snapshot isolation throughput. - Services MUST wrap multi-statement writes inside `db.transaction()` callbacks to leverage Bun's native C-level atomic batching and minimize write-lock holding duration. - Components NEVER execute long-running asynchronous side-effects (e.g., fetch, disk I/O) inside a SQLite transaction lock context. ## Bad vs Good Implementations ### Unsynchronized Direct Database Writes vs Structured Single-Writer WAL Channel #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Opening SQLite database handles per request and issuing uncoordinated concurrent writes guarantees SQLITE_BUSY exceptions under high-throughput edge traffic, even when journal_mode is WAL. ```typescript import { Database } from 'bun:sqlite'; // Anti-pattern: Each handler creates a connection or writes concurrently without synchronization export async function handleUserCreate(name: string, email: string) { const db = new Database('production.db'); // Opens per request or runs concurrently db.run('PRAGMA journal_mode = WAL;'); // Flaw: Concurrent execution triggers SQLITE_BUSY: database is locked const query = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)'); query.run(name, email); db.close(); } ``` #### Production Standard (Correct) > **Standard**: Configures WAL with synchronous=NORMAL and a 5-second busy timeout. Segregates reads to a read-only handle and routes all writes through a sequentially resolved Promise chain, ensuring exactly one transaction holds the write lock at any instant. ```typescript import { Database } from 'bun:sqlite'; import { AsyncLocalStorage } from 'node:async_hooks'; // Production Invariant: Dedicated writer instance with WAL and busy timeout configuration const writeDb = new Database('production.db'); writeDb.exec(` PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA busy_timeout = 5000; `); // Pool of read-only handles for lock-free snapshot reads const readDb = new Database('production.db', { readonly: true }); readDb.exec('PRAGMA busy_timeout = 5000;'); // Serialized single-writer queue ensuring strict structured concurrency class SQLiteWriteQueue { private queue = Promise.resolve(); public enqueue<T>(task: (db: Database) => T): Promise<T> { return new Promise<T>((resolve, reject) => { this.queue = this.queue.then(() => { try { const result = writeDb.transaction(task)(writeDb); resolve(result); } catch (error) { reject(error); } }); }); } } export const dbQueue = new SQLiteWriteQueue(); export async function createUser(name: string, email: string) { return dbQueue.enqueue((db) => { const stmt = db.prepare('INSERT INTO users (name, email) VALUES (?, ?) RETURNING id'); return stmt.get(name, email) as { id: number }; }); } export function getUser(id: number) { const stmt = readDb.prepare('SELECT * FROM users WHERE id = ?'); return stmt.get(id); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL Structured Concurrency & Single-Writer Invariant

Bun 1.2 Native SQLite WAL Structured Concurrency & Single-Writer Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces strict single-writer serialized queuing and concurrent multi-reader pools for Bun 1.2's native `bun:sqlite` engine under Write-Ahead Logging (WAL) mode. Prevents catastrophic SQLITE_BUSY / 'database is locked' panics in multi-threaded edge worker environments by decoupling write mutations into a dedicated serialization lane with structured concurrency controls. 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: Unsynchronized Concurrent Multi-Threaded Writes directly opening 'bun:sqlite' in WAL Mode Triggering SQLITE_BUSY Exceptions 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	Unsynchronized Concurrent Multi-Threaded Writes directly opening 'bun:sqlite' in WAL Mode Triggering SQLITE_BUSY Exceptions	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 SQLite instances with `PRAGMA journal_mode = WAL

Engineers MUST ALWAYS configure SQLite instances with `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and `PRAGMA busy_timeout = 5000;` immediately upon opening via `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.