Bun 1.2 Multithreaded SQLite WAL Memory-Mapped Synchronization Standard

--- description: Bun 1.2 Multithreaded SQLite WAL Memory-Mapped Synchronization Standard - Enforces strict isolation, WAL journal mode configuration, and single-writer coordination across Bun 1.2 multithreaded worker pools accessing SQLite databases. Prevents database lock contention (SQLITE_BUSY), memory-mapped (mmap) dirty page corruption, and inconsistent file descriptors when multiple concurrent workers access local and edge-replicated SQLite instances. 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-mmap-concurrency-guardrail" --- # Bun 1.2 Multithreaded SQLite WAL Memory-Mapped Synchronization Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Uncoordinated Multi-Worker Writes and Unbounded MMAP Allocations Causing SQLITE_BUSY Deadlocks and Corrupted WAL Buffers ## System Constraints You are a Principal Backend Systems Architect enforcing strict Bun 1.2 native SQLite thread-safety and WAL synchronization invariants. Bun's native `bun:sqlite` uses in-process C-bindings that require explicit single-writer orchestration, deterministic busy timeouts, WAL journaling, and bounded memory-mapped I/O (`PRAGMA mmap_size`) across multithreaded workers to prevent SQLITE_BUSY deadlocks and memory corruption. ## Strict Guidelines - Engineers MUST ALWAYS initialize `bun:sqlite` connections with `PRAGMA journal_mode = WAL;` and set an explicit `PRAGMA busy_timeout = 5000;` immediately upon opening. - Engineers MUST NEVER execute direct write transactions across multiple parallel Bun worker threads without a dedicated single-writer queue or mutex actor. - Services MUST bound SQLite memory-mapped I/O using `PRAGMA mmap_size = 268435456;` (256MB) or lower to prevent dirty page buffer corruption and virtual memory exhaustion across concurrent worker instances. - Code MUST use `db.transaction()` for atomic mutations and ALWAYS use `IMMEDIATE` transaction mode (`db.exec('BEGIN IMMEDIATE')`) to fail fast instead of escalating locks mid-transaction. - Engineers MUST configure `PRAGMA synchronous = NORMAL;` when WAL mode is active to balance durability and write throughput without risking database structural corruption. - Workers NEVER open independent, unmanaged write connections to the same SQLite database file concurrently; edge-replicated databases must funnel writes through a leader thread or Unix domain socket IPC writer. ## Bad vs Good Implementations ### Uncoordinated Multi-Connection Direct Writes vs Single-Writer WAL Thread Pool #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Opening multiple raw SQLite connections across worker threads without setting WAL mode and an explicit busy_timeout leads to immediate SQLITE_BUSY errors under load. Omitting mmap bounds allows SQLite to over-allocate virtual memory, risking corrupted dirty pages when edge sync tools or threads overlap. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Each worker opens an unconfigured connection and writes concurrently export async function handleWorkerWrite(dbPath: string, payload: { id: string; data: string }) { const db = new Database(dbPath); // Missing WAL mode, missing busy_timeout, missing mmap bounding // Default journal mode causes exclusive table locks and SQLITE_BUSY collisions db.run('INSERT INTO events (id, data) VALUES (?, ?)', [payload.id, payload.data]); db.close(); } ``` #### Production Standard (Correct) > **Standard**: Configures WAL mode, a 5-second busy timeout, NORMAL sync, and a bounded 256MB mmap footprint. Executes writes inside an immediate transaction (`BEGIN IMMEDIATE`), ensuring lock acquisition happens before modification begins and preventing mid-transaction deadlocks across threads. ```typescript import { Database } from 'bun:sqlite'; // PRODUCTION INVARIANT: Configured WAL connection with bounded mmap and single-writer lease export class SafeDatabaseCoordinator { private static instance: Database | null = null; public static getConnection(dbPath: string): Database { if (!this.instance) { const db = new Database(dbPath, { create: true, strict: true }); // Critical WAL and concurrency pragmas db.exec(` PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 5000; PRAGMA synchronous = NORMAL; PRAGMA mmap_size = 268435456; PRAGMA foreign_keys = ON; `); this.instance = db; } return this.instance; } public static executeWrite<T>(dbPath: string, fn: (db: Database) => T): T { const db = this.getConnection(dbPath); // BEGIN IMMEDIATE avoids mid-transaction lock upgrades that cause SQLITE_BUSY const runImmediateTx = db.transaction((operation: (db: Database) => T) => { return operation(db); }); return runImmediateTx.immediate(fn); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Multithreaded SQLite WAL Memory-Mapped Synchronization Standard

Bun 1.2 Multithreaded SQLite WAL Memory-Mapped Synchronization Standard defines an industrial-grade behavioral standard for backend engineering. Enforces strict isolation, WAL journal mode configuration, and single-writer coordination across Bun 1.2 multithreaded worker pools accessing SQLite databases. Prevents database lock contention (SQLITE_BUSY), memory-mapped (mmap) dirty page corruption, and inconsistent file descriptors when multiple concurrent workers access local and edge-replicated 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: Uncoordinated Multi-Worker Writes and Unbounded MMAP Allocations Causing SQLITE_BUSY Deadlocks and Corrupted WAL Buffers 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	Uncoordinated Multi-Worker Writes and Unbounded MMAP Allocations Causing SQLITE_BUSY Deadlocks and Corrupted WAL Buffers	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 initialize `bun

Engineers MUST ALWAYS initialize `bun:sqlite` connections with `PRAGMA journal_mode = WAL;` and set an explicit `PRAGMA busy_timeout = 5000;` immediately upon opening.

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.