Bun 1.2 Embedded SQLite WAL2 Concurrency and Snapshot Isolation Standard

--- description: Bun 1.2 Embedded SQLite WAL2 Concurrency and Snapshot Isolation Standard - Enforces non-blocking concurrent edge reads during sustained write bursts using Bun 1.2 native SQLite with dual write-ahead log (WAL2) mode. Eliminates reader starvation and SQLITE_BUSY deadlocks by segregating read-only and write connections while maintaining transactional snapshot isolation. globs: src/**/*.{ts,tsx}, lib/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal2-snapshot-concurrency" --- # Bun 1.2 Embedded SQLite WAL2 Concurrency and Snapshot Isolation Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Shared Read-Write Database Handles Operating on Standard Single-WAL Checkpoints ## System Constraints You are a Principal Software Architect enforcing production concurrency standards in Bun 1.2 embedded SQLite. Mandate segregated reader/writer handles, WAL2 dual-log frame rotation, NORMAL synchronous modes, and explicit transaction isolation levels to prevent SQLite checkpoint read-starvation under burst write workloads. ## Strict Guidelines - Services MUST configure 'PRAGMA journal_mode = WAL2;' during primary writer initialization to allow alternating dual-log checkpoints without locking concurrent readers. - Engineers MUST segregate database instances into exactly ONE dedicated writer connection and a dedicated pool of read-only connections ('readonly: true'). - Services MUST set 'PRAGMA busy_timeout = 5000;' and 'PRAGMA synchronous = NORMAL;' on every opened database connection to prevent immediate SQLITE_BUSY aborts and redundant fsync thrashing. - Engineers MUST NOT execute ad-hoc write statements across read-only connection handles or within concurrent edge request handler fibers without serialized transaction queues. - Code SHOULD wrap non-idempotent multi-statement writes in 'writer.transaction(() => { ... }).immediate()' to acquire immediate locks and eliminate mid-transaction lock escalation aborts. - Components NEVER execute raw long-running read queries on the write handle, as doing so pins the active WAL frame and blocks log compaction. ## Bad vs Good Implementations ### Dual-Log WAL2 Reader-Writer Segregation in Bun 1.2 #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Using a single read-write connection with standard WAL causes readers to be stalled or rejected with SQLITE_BUSY during continuous bulk inserts. Without WAL2, checkpoint operations cannot truncate the log while readers hold older snapshots, causing log explosion and latency spikes. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Shared single handle with default journal mode or standard WAL // Long writes will block edge readers, causing SQLITE_BUSY or unbounded WAL growth export const db = new Database('app.db', { create: true }); db.run('PRAGMA journal_mode = WAL;'); export async function handleMutation(data: unknown[]) { // Anti-pattern: Unscoped write transactions without busy timeout or locking scope const insert = db.prepare('INSERT INTO telemetry (payload) VALUES (?)'); for (const item of data) { insert.run(JSON.stringify(item)); } } export async function handleRead(id: string) { // Contends with write loop on the same handle return db.prepare('SELECT * FROM telemetry WHERE id = ?').get(id); } ``` #### Production Standard (Correct) > **Standard**: WAL2 enables two alternating log files, ensuring checkpoints can flush the inactive log while writers append to the active log and readers retain consistent snapshots. Segregating into an immediate-mode writer and a dedicated read-only connection guarantees non-blocking concurrent edge reads. ```typescript import { Database } from 'bun:sqlite'; const DB_PATH = 'app.db'; // 1. Singleton Dedicated Writer Handle const writerDb = new Database(DB_PATH, { create: true, strict: true }); writerDb.run('PRAGMA journal_mode = WAL2;'); writerDb.run('PRAGMA synchronous = NORMAL;'); writerDb.run('PRAGMA busy_timeout = 5000;'); // 2. Read-Only Handle for Edge Queries (Concurrent Safe Snapshot Isolation) const readerDb = new Database(DB_PATH, { readonly: true, strict: true }); readerDb.run('PRAGMA busy_timeout = 5000;'); // Immediate transaction guarantees exclusive write access without mid-flight contention const batchInsertTx = writerDb.transaction((items: string[]) => { const statement = writerDb.prepare('INSERT INTO telemetry (payload) VALUES (?);'); for (const item of items) { statement.run(item); } }); export function recordTelemetryBatch(payloads: unknown[]): void { const serialized = payloads.map((p) => JSON.stringify(p)); batchInsertTx.immediate(serialized); } export function queryTelemetry(id: string): unknown { // Reads seamlessly proceed against frozen WAL snapshot even during heavy writes return readerDb.prepare('SELECT * FROM telemetry WHERE id = ?;').get(id); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Embedded SQLite WAL2 Concurrency and Snapshot Isolation Standard

Bun 1.2 Embedded SQLite WAL2 Concurrency and Snapshot Isolation Standard defines an industrial-grade behavioral standard for backend engineering. Enforces non-blocking concurrent edge reads during sustained write bursts using Bun 1.2 native SQLite with dual write-ahead log (WAL2) mode. Eliminates reader starvation and SQLITE_BUSY deadlocks by segregating read-only and write connections while maintaining transactional snapshot isolation. 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 Read-Write Database Handles Operating on Standard Single-WAL Checkpoints 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 Read-Write Database Handles Operating on Standard Single-WAL Checkpoints	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:

Services MUST configure 'PRAGMA journal_mode = WAL2

Services MUST configure 'PRAGMA journal_mode = WAL2;' during primary writer initialization to allow alternating dual-log checkpoints without locking concurrent readers.

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.