Bun 1.2 Worker-Thread SQLite Connection Affinity and WAL Concurrency Guardrail

--- description: Bun 1.2 Worker-Thread SQLite Connection Affinity and WAL Concurrency Guardrail - Enforces strict thread-isolated SQLite connection instantiation across Bun 1.2 Worker threads and event loops. Prevents cross-isolate handle leakage, eliminates SQLITE_BUSY lock contention, and guarantees write-ahead log (WAL) checkpointing efficiency under high concurrency. globs: src/**/*.ts, workers/**/*.ts, lib/db/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-worker-thread-affinity-guardrail" --- # Bun 1.2 Worker-Thread SQLite Connection Affinity and WAL Concurrency Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Sharing bun:sqlite Database handles across Worker boundaries or running concurrent unmanaged multi-isolate writes without WAL thread affinity ## System Constraints You are a Principal Software Architect enforcing the Bun 1.2 Worker-Thread SQLite Connection Affinity Guardrail. In Bun 1.2, workers operate in distinct JavaScript isolates with discrete event loops. SQLite database handles (`bun:sqlite`) must never be shared across worker boundaries via structured clone or global references. Every worker must maintain its own private connection instance with explicit WAL configuration (`PRAGMA journal_mode = WAL`), a defensive `busy_timeout`, and explicit write-coordinator patterns to eliminate lock contention, WAL reader starvation, and database corruption. ## Strict Guidelines - Engineers MUST ALWAYS instantiate `bun:sqlite` Database objects inside the execution context of the specific Worker thread that uses them, enforcing strict 1:1 thread affinity. - Engineers MUST NOT attempt to pass `Database` or `Statement` instances across Worker boundaries via `postMessage` or shared memory buffers. - Code MUST configure `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and `PRAGMA busy_timeout = 5000;` on every newly initialized connection instance before executing queries. - Services MUST isolate write operations to a dedicated single-writer coordinator worker or use queued transactions to prevent SQLite write-lock contention and WAL checkpoint starvation. - Components NEVER execute long-running analytical read transactions on the single write-coordinator thread; long reads must run on separate read-only worker connections. ## Bad vs Good Implementations ### Anti-Pattern: Sharing Connection Handles Across Workers Without WAL Pragma Configuration #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Sharing a database connection across worker isolates causes undefined behavior and data race conditions. Failing to enable WAL mode (`PRAGMA journal_mode = WAL`) and `busy_timeout` leads to immediate `SQLITE_BUSY` errors when concurrent workers attempt simultaneous disk I/O. ```typescript import { Database } from 'bun:sqlite'; // ANTI-PATTERN: Global database instance shared or passed to workers export const sharedDb = new Database('production.sqlite'); // Main thread dispatches tasks passing or relying on shared handle const worker = new Worker(new URL('./worker.ts', import.meta.url).href); // Attempting to invoke operations or pass database reference directly worker.postMessage({ type: 'EXECUTE', query: 'INSERT INTO audit_log (msg) VALUES (?)' }); // worker.ts import { sharedDb } from './db'; self.onmessage = (event) => { // FAILS under load: Default DELETE journal mode causes SQLITE_BUSY // and cross-isolate lock starvation with unconfigured busy_timeout sharedDb.run(event.data.query, ['Worker payload']); }; ``` #### Production Standard (Correct) > **Standard**: The database connection is instantiated strictly within the worker's own isolate, ensuring connection affinity. Crucial WAL pragmas and `busy_timeout` are applied before query execution, permitting concurrent reads while managing writes safely without cross-thread lock contention. ```typescript import { Database } from 'bun:sqlite'; // worker.ts: Dedicated worker thread with private connection affinity interface InsertJob { id: string; payload: string; } // Invariant: Thread-local database instantiation inside the worker isolate const db = new Database('production.sqlite', { create: true }); // Configure WAL concurrency and fail-safe busy timeout on thread init db.exec('PRAGMA journal_mode = WAL;'); db.exec('PRAGMA synchronous = NORMAL;'); db.exec('PRAGMA busy_timeout = 5000;'); const insertStmt = db.prepare('INSERT INTO audit_log (id, payload) VALUES (?, ?)'); self.onmessage = (event: MessageEvent<InsertJob>) => { const { id, payload } = event.data; try { insertStmt.run(id, payload); self.postMessage({ id, status: 'COMMITTED' }); } catch (error) { self.postMessage({ id, status: 'ERROR', message: (error as Error).message }); } }; self.onclose = () => { insertStmt.finalize(); db.close(); }; ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Worker-Thread SQLite Connection Affinity and WAL Concurrency Guardrail

Bun 1.2 Worker-Thread SQLite Connection Affinity and WAL Concurrency Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict thread-isolated SQLite connection instantiation across Bun 1.2 Worker threads and event loops. Prevents cross-isolate handle leakage, eliminates SQLITE_BUSY lock contention, and guarantees write-ahead log (WAL) checkpointing efficiency under high concurrency. 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 bun:sqlite Database handles across Worker boundaries or running concurrent unmanaged multi-isolate writes without WAL thread affinity 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 bun:sqlite Database handles across Worker boundaries or running concurrent unmanaged multi-isolate writes without WAL thread affinity	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 instantiate `bun

Engineers MUST ALWAYS instantiate `bun:sqlite` Database objects inside the execution context of the specific Worker thread that uses them, enforcing strict 1:1 thread affinity.

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.