Bun 1.2 Native SQLite WAL2 Concurrency and Read-Replica Connection Guardrail

--- description: Bun 1.2 Native SQLite WAL2 Concurrency and Read-Replica Connection Guardrail - Enforces strict isolation between a singleton writer connection and dedicated read-only replica handles using Bun 1.2 native bun:sqlite. Prevents thread-pool deadlocks, SQLITE_BUSY lock escalation anomalies, and writer starvation during high-throughput multi-threaded worker offloading. globs: src/**/*.ts, src/**/*.worker.ts, workers/**/*.ts, lib/db/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal2-concurrent-replica-guardrail" --- # Bun 1.2 Native SQLite WAL2 Concurrency and Read-Replica Connection Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Sharing Mutable SQLite Handles Across Multi-Threaded Workers Without WAL Tuning or Read-Only Segregation ## System Constraints You are a Principal Software Architect enforcing Bun 1.2 Native SQLite concurrency and connection lifecycle guardrails. In multi-threaded edge microservices and worker offloading architectures, SQLite locks file descriptors at the OS level. You must ensure that applications never share mutable Database instances across threads, always enable WAL mode with calibrated busy timeouts, and enforce strict separation between a single-writer pipeline and worker-bound read-only replicas. ## Strict Guidelines - Services MUST isolate write mutations to a single designated primary connection with PRAGMA journal_mode = WAL (or WAL2 if compiled) and PRAGMA busy_timeout configured. - Worker threads offloading compute-intensive queries MUST initialize dedicated read-only handles using new Database(path, { readonly: true }) instead of receiving shared references. - Engineers MUST NOT execute DML/DDL write statements (INSERT, UPDATE, DELETE, CREATE) within distributed worker threads. - Database initialization MUST configure PRAGMA synchronous = NORMAL and PRAGMA foreign_keys = ON on all connected instances. - Multi-statement write transactions MUST leverage db.transaction(() => { ... }) to ensure atomic lock acquisition and deterministic release. - Services MUST implement explicit connection teardown via db.close() in worker termination lifecycle hooks to prevent file-descriptor leaks and lock hangs. ## Bad vs Good Implementations ### Unsafe Shared Read-Write Connection Across Concurrent Workers #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Default journal mode (DELETE) locks the entire database file during reads and writes. Sharing a single mutable instance across concurrent asynchronous operations and worker pipelines without busy_timeout triggers unhandled SQLITE_BUSY errors and crashes edge services under high throughput. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Shared read-write handle across concurrent workers // Missing WAL mode, busy_timeout, and readonly replica isolation export const db = new Database('production.db'); export function executeWorkerQuery(sql: string, params: any[]) { // Running directly on shared instance across concurrent asynchronous promises // Risk of SQLITE_BUSY: database is locked when write transactions overlap return db.query(sql).all(...params); } export async function updateRecord(id: string, data: any) { db.run('UPDATE records SET payload = ? WHERE id = ?', [JSON.stringify(data), id]); } ``` #### Production Standard (Correct) > **Standard**: Configures WAL mode with NORMAL synchronicity to decouple read-lock contention from writes. Write operations run exclusively through a configured primary transaction engine, while worker threads instantiate dedicated read-only handles with query_only enabled, completely eliminating file-descriptor starvation. ```typescript import { Database } from 'bun:sqlite'; const DB_PATH = 'production.db'; // 1. Singleton Primary Writer Instance export const writerDb = new Database(DB_PATH, { create: true }); // Configure WAL concurrency, synchronous level, and wait timeout writerDb.exec(` PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA busy_timeout = 5000; PRAGMA foreign_keys = ON; `); // Transaction helper guaranteeing atomic write lock lifecycles export const updateRecord = writerDb.transaction((id: string, data: Record<string, unknown>) => { const statement = writerDb.query('UPDATE records SET payload = ? WHERE id = ?'); return statement.run(JSON.stringify(data), id); }); // 2. Read-Only Factory for Multi-Threaded Workers export function createWorkerReplicaConnection(): Database { const replica = new Database(DB_PATH, { readonly: true }); replica.exec(` PRAGMA busy_timeout = 5000; PRAGMA query_only = ON; `); return replica; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL2 Concurrency and Read-Replica Connection Guardrail

Bun 1.2 Native SQLite WAL2 Concurrency and Read-Replica Connection Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict isolation between a singleton writer connection and dedicated read-only replica handles using Bun 1.2 native bun:sqlite. Prevents thread-pool deadlocks, SQLITE_BUSY lock escalation anomalies, and writer starvation during high-throughput multi-threaded worker offloading. 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: Sharing Mutable SQLite Handles Across Multi-Threaded Workers Without WAL Tuning or Read-Only Segregation 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	Sharing Mutable SQLite Handles Across Multi-Threaded Workers Without WAL Tuning or Read-Only Segregation	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 isolate write mutations to a single designated primary connection with PRAGMA journal_mode = WAL (or WAL2 if compiled) and PRAGMA busy_timeout configured.

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.