Bun 1.2 Native SQLite WAL Concurrent Reader Isolation and Checkpoint Invariant

--- description: Bun 1.2 Native SQLite WAL Concurrent Reader Isolation and Checkpoint Invariant - Enforces architectural isolation between single-writer and multi-reader connections in Bun 1.2 native SQLite ('bun:sqlite'). Prevents event loop thread contention, SQLite lock starvation (SQLITE_BUSY), and unbounded write-ahead log (WAL) checkpoint latency under high-throughput concurrent loads. globs: src/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-reader-isolation-invariant" --- # Bun 1.2 Native SQLite WAL Concurrent Reader Isolation and Checkpoint Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Monolithic bun:sqlite Handle Sharing Mixed Read-Write Queries Without WAL Isolation and Busy Timeouts ## System Constraints You are a Principal Engineer enforcing Bun 1.2 Native SQLite WAL Concurrent Reader Isolation Invariant. Mandate invariants that eliminate event loop thread stalls, lock contention, and WAL file bloating in embedded SQLite services. Reject any code that shares a single writable database connection across read-heavy event loop tasks or omits WAL concurrency PRAGMA configurations. ## Strict Guidelines - Engineers MUST ALWAYS isolate the single dedicated writer connection from read connections by instantiating distinct 'bun:sqlite' Database handles, marking read handles with { readonly: true }. - Engineers MUST NOT execute ad-hoc, long-running analytical queries or large reads on the primary writer database connection handle. - Code SHOULD configure PRAGMA journal_mode = WAL, PRAGMA synchronous = NORMAL, and a non-zero PRAGMA busy_timeout (minimum 5000ms) immediately upon opening any connection handle. - Services MUST implement scheduled passive/truncate WAL checkpointing (e.g., PRAGMA wal_checkpoint(PASSIVE)) decoupled from user-facing transaction paths to prevent unbounded WAL growth. - Components NEVER execute raw transaction blocks across interleaved asynchronous microtask awaits without ensuring queries are contained within synchronous db.transaction() callbacks to prevent transaction leakage across the event loop. ## Bad vs Good Implementations ### Shared Read/Write Database Handle vs Isolated WAL Architecture #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: A single Database instance without WAL mode defaults to DELETE journal mode, acquiring full database locks for writes. When concurrent reads and writes interleave, operations throw SQLITE_BUSY errors or freeze the Bun event loop thread during disk flushes. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Single connection instance for mixed read/write // Missing WAL mode, busy timeout, and read isolation. export const db = new Database('service.db'); export async function handleUserUpdate(id: string, name: string) { // Can trigger SQLITE_BUSY or block readers during heavy I/O const query = db.query('UPDATE users SET name = ?1 WHERE id = ?2'); return query.run(name, id); } export async function getUsers() { // Contends with writes on the same handle and blocks on uncheckpointed logs return db.query('SELECT * FROM users').all(); } ``` #### Production Standard (Correct) > **Standard**: Separating connections into a primary writer and a read-only instance leverages SQLite WAL concurrency: readers never block writers and writers never block readers. Explicit PRAGMA busy_timeout prevents SQLITE_BUSY crashes under load, while synchronous db.transaction() preserves atomic isolation. ```typescript import { Database } from 'bun:sqlite'; // PRODUCTION INVARIANT: Dedicated isolated handles with explicit WAL configuration const DB_PATH = 'service.db'; // 1. Dedicated Single Writer Connection export const writeDb = new Database(DB_PATH, { create: true }); writeDb.exec('PRAGMA journal_mode = WAL;'); writeDb.exec('PRAGMA synchronous = NORMAL;'); writeDb.exec('PRAGMA busy_timeout = 5000;'); writeDb.exec('PRAGMA cache_size = -64000;'); // 64MB cache // 2. Dedicated Read-Only Connection Pool/Instance export const readDb = new Database(DB_PATH, { readonly: true }); readDb.exec('PRAGMA busy_timeout = 5000;'); readDb.exec('PRAGMA query_only = true;'); // 3. Atomic synchronous transactions to prevent loop-stalling microtask interleaving const updateUserTx = writeDb.transaction((id: string, name: string) => { writeDb.query('UPDATE users SET name = ?1 WHERE id = ?2').run(name, id); }); export function handleUserUpdate(id: string, name: string) { return updateUserTx(id, name); } export function getUsers() { // Concurrent readers operate fully non-blocking against WAL snapshots return readDb.query('SELECT id, name FROM users').all(); } // 4. Background WAL maintenance decoupled from request handlers export function checkpointWal(): void { writeDb.exec('PRAGMA wal_checkpoint(PASSIVE);'); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL Concurrent Reader Isolation and Checkpoint Invariant

Bun 1.2 Native SQLite WAL Concurrent Reader Isolation and Checkpoint Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces architectural isolation between single-writer and multi-reader connections in Bun 1.2 native SQLite ('bun:sqlite'). Prevents event loop thread contention, SQLite lock starvation (SQLITE_BUSY), and unbounded write-ahead log (WAL) checkpoint latency under high-throughput concurrent loads. 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: Monolithic bun:sqlite Handle Sharing Mixed Read-Write Queries Without WAL Isolation 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	Monolithic bun:sqlite Handle Sharing Mixed Read-Write Queries Without WAL Isolation 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 the single dedicated writer connection from read connections by instantiating distinct 'bun

Engineers MUST ALWAYS isolate the single dedicated writer connection from read connections by instantiating distinct 'bun:sqlite' Database handles, marking read handles with { readonly: true }.

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.