Bun 1.2 Native SQLite WAL Concurrent Read-Transaction Invariant

--- description: Bun 1.2 Native SQLite WAL Concurrent Read-Transaction Invariant - Mandates strict deterministic lifecycle bounds for read transactions, prepared statements, and connection-level PRAGMA configurations in Bun 1.2 native SQLite (bun:sqlite). Eliminates WAL file ballooning, memory-mapped view fragmentation, and checkpoint starvation across concurrent microservices and worker threads. globs: src/**/*.{ts,tsx}, server/**/*.{ts,tsx}, lib/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-checkpoint-starvation-guardrail" --- # Bun 1.2 Native SQLite WAL Concurrent Read-Transaction Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Leaking Long-Lived Read Transactions and Unfinalized Statements Across Async Event Loop Turns in WAL Mode ## System Constraints You are a Principal Engineer enforcing Bun 1.2 Native SQLite WAL Concurrent Read-Transaction Invariants. Mandate strict adherence to bun:sqlite connection parameters, eager statement finalization, and the total prohibition of lingering read locks across asynchronous task boundaries to prevent WAL checkpoint starvation and virtual memory map exhaustion. ## Strict Guidelines - Engineers MUST ALWAYS configure PRAGMA journal_mode = WAL, PRAGMA synchronous = NORMAL, and PRAGMA busy_timeout = 5000 immediately upon opening a bun:sqlite Database instance. - Engineers MUST NOT interleave asynchronous await calls or long-running event-loop operations while holding an active Statement cursor or inside a synchronous transaction closure. - Code SHOULD eagerly materialize query results using statement.all() or statement.values() before passing data into downstream asynchronous processing pipelines. - Services MUST implement deterministic periodic passive WAL checkpoints (PRAGMA wal_checkpoint(PASSIVE)) via scheduled micro-tasks to prevent WAL frame log accumulation. - Components NEVER use manual raw SQL string transactions (BEGIN/COMMIT) for read locks; callers MUST encapsulate atomic units using the native db.transaction() wrapper. ## Bad vs Good Implementations ### Handling Concurrent SQLite Reads and Checkpoints in Bun 1.2 #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Iterating over query.iterate() while awaiting network promises causes the read transaction lock to span multiple event-loop ticks. In SQLite WAL mode, any active read transaction locks the WAL read-mark at an older frame, preventing passive and auto-checkpoints from truncating the WAL, leading to massive memory-mapped file growth. ```typescript import { Database } from "bun:sqlite"; const db = new Database("service.db"); db.run("PRAGMA journal_mode = WAL;"); export async function streamDataOverNetwork(res: Response) { const query = db.query("SELECT id, payload FROM events WHERE processed = 0"); // ANTI-PATTERN: Iterating over an active SQLite cursor while awaiting asynchronous network I/O // keeps the read transaction open indefinitely. This starves the WAL checkpoint and triggers // virtual memory mapping fragmentation as the WAL file grows unbounded. for (const row of query.iterate()) { await fetch("https://analytics.internal/ingest", { method: "POST", body: JSON.stringify(row) }); } } ``` #### Production Standard (Correct) > **Standard**: By eagerly materializing the rows via statement.all() and promptly finalizing the statement before awaiting network calls, SQLite releases the read lock on the WAL index immediately. Configuring PRAGMA busy_timeout prevents SQLITE_BUSY crashes under write concurrency, and scheduled passive checkpoints safely reclaim WAL disk space. ```typescript import { Database } from "bun:sqlite"; const db = new Database("service.db"); // Enforce production invariants: WAL mode, safe synchronous flushing, and busy retry limit db.run("PRAGMA journal_mode = WAL;"); db.run("PRAGMA synchronous = NORMAL;"); db.run("PRAGMA busy_timeout = 5000;"); interface EventRecord { id: number; payload: string; } export async function streamDataOverNetwork(): Promise<void> { const statement = db.query<EventRecord, [number]>("SELECT id, payload FROM events WHERE processed = ? LIMIT 500"); // INVARIANT FIX: Materialize rows synchronously in bounded batches, releasing the WAL lock immediately let rows: EventRecord[]; try { rows = statement.all(0); } finally { statement.finalize(); } for (const row of rows) { await fetch("https://analytics.internal/ingest", { method: "POST", body: JSON.stringify(row) }); } } // Non-blocking, passive checkpoint scheduled periodically to reclaim WAL frames export function schedulePassiveCheckpoint(): void { try { db.run("PRAGMA wal_checkpoint(PASSIVE);"); } catch (err) { console.error("Checkpoint deferred due to concurrent lock", err); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL Concurrent Read-Transaction Invariant

Bun 1.2 Native SQLite WAL Concurrent Read-Transaction Invariant defines an industrial-grade behavioral standard for backend engineering. Mandates strict deterministic lifecycle bounds for read transactions, prepared statements, and connection-level PRAGMA configurations in Bun 1.2 native SQLite (bun:sqlite). Eliminates WAL file ballooning, memory-mapped view fragmentation, and checkpoint starvation across concurrent microservices and worker 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: Leaking Long-Lived Read Transactions and Unfinalized Statements Across Async Event Loop Turns in WAL Mode 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	Leaking Long-Lived Read Transactions and Unfinalized Statements Across Async Event Loop Turns in WAL Mode	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, PRAGMA synchronous = NORMAL, and PRAGMA busy_timeout = 5000 immediately upon opening a bun

Engineers MUST ALWAYS configure PRAGMA journal_mode = WAL, PRAGMA synchronous = NORMAL, and PRAGMA busy_timeout = 5000 immediately upon opening a bun:sqlite Database instance.

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.