Bun 1.2 Native SQLite WAL Concurrency & S3 Direct-Stream Pipeline Invariants

--- description: Bun 1.2 Native SQLite WAL Concurrency & S3 Direct-Stream Pipeline Invariants - Enforces strict production invariants for Bun 1.2 embedded database and object storage operations. Mandates WAL-mode PRAGMA concurrency configuration, prepared statement lifecycle reuse in 'bun:sqlite', and zero-copy S3 streaming via 'Bun.s3' to prevent node heap buffer exhaustion. 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-wal-s3-streaming-invariants" --- # Bun 1.2 Native SQLite WAL Concurrency & S3 Direct-Stream Pipeline Invariants > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Buffering multi-megabyte payloads in process heap while executing un-indexed SQLite writes in rollback journal mode without statement preparation. ## System Constraints You are a Principal Backend Systems Architect specializing in Bun 1.2 runtimes. Enforce strict adherence to native 'bun:sqlite' low-latency APIs and native 'Bun.s3' storage operations. Ban bloated legacy dependencies (@aws-sdk/client-s3, better-sqlite3) when native Bun primitives exist. Require WAL journal mode, explicit busy timeouts, prepared statement caching, and direct zero-copy stream piped payloads across all database and object-store transitions. ## Strict Guidelines - Engineers MUST ALWAYS initialize 'bun:sqlite' connections with 'PRAGMA journal_mode = WAL;', 'PRAGMA synchronous = NORMAL;', and 'PRAGMA busy_timeout = 5000;' immediately upon opening. - Engineers MUST NEVER execute ad-hoc inline query strings repeatedly; reusable queries MUST be instantiated via 'db.query(...)' outside hot paths or cached as prepared statements. - Engineers MUST ALWAYS use native 'Bun.s3' or 's3' from 'bun' rather than heavy AWS SDK client packages for S3 interactions in Bun >= 1.2. - Engineers MUST NOT read full files into memory via 'await file.arrayBuffer()' or 'Buffer.from()' prior to S3 upload; stream directly using 's3.file(key).write(streamOrBlob)'. - Code MUST wrap multi-step SQLite operations inside explicit 'db.transaction()' wrappers to maintain ACID isolation and minimize lock contention duration. - Services MUST execute 'PRAGMA wal_checkpoint(PASSIVE)' or 'TRUNCATE' during controlled shutdowns to eliminate orphan WAL accumulation. ## Bad vs Good Implementations ### Configuring bun:sqlite Concurrency and Zero-Copy S3 Upload #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Omits essential WAL pragmas leading to 'database is locked' errors under concurrent requests. Uses heavy external AWS SDK and buffers the entire payload into JS heap memory via synchronous file I/O instead of utilizing Bun's native zero-copy streaming primitives. ```typescript import { Database } from "bun:sqlite"; import { S3Client, PutObjectCommand } from "@aws-sdk/client-s3"; import { readFileSync } from "node:fs"; const db = new Database("production.db"); // BAD: Rollback journal default blocks readers during writes; no busy timeout configured const s3Client = new S3Client({ region: "us-east-1" }); export async function backupRecord(id: string, filePath: string) { // BAD: Query executed repeatedly without statement preparation const row = db.query(`SELECT * FROM items WHERE id = '${id}'`).get(); // BAD: Loading entire file into node Buffer causes heap spikes const fileBuffer = readFileSync(filePath); await s3Client.send(new PutObjectCommand({ Bucket: "backups", Key: `items/${id}.bin`, Body: fileBuffer, })); } ``` #### Production Standard (Correct) > **Standard**: Applies WAL journal mode and busy_timeout to resolve lock contention, reuses a pre-compiled SQLite statement to bypass parse overhead, and leverages Bun 1.2's native 's3.file().write()' with a direct ReadableStream to avoid allocating intermediate heap buffers. ```typescript import { Database, Statement } from "bun:sqlite"; import { s3 } from "bun"; // GOOD: Initialize connection with WAL and concurrency safeguards export const db = new Database("production.db", { create: true, strict: true }); db.run("PRAGMA journal_mode = WAL;"); db.run("PRAGMA synchronous = NORMAL;"); db.run("PRAGMA busy_timeout = 5000;"); // GOOD: Pre-compile query statement with typed bindings const selectItemStmt: Statement = db.query( "SELECT id, name, metadata FROM items WHERE id = $id LIMIT 1;" ); export async function backupRecord(id: string, fileRef: Bun.BunFile): Promise<void> { const row = selectItemStmt.get({ $id: id }); if (!row) { throw new Error(`Record ${id} not found`); } // GOOD: Native Bun 1.2 S3 zero-copy streaming from BunFile stream directly to S3 destination const s3Destination = s3.file(`backups/items/${id}.bin`, { bucket: "backups", acl: "private", }); // Streams without buffering into JS V8 heap await s3Destination.write(fileRef.stream()); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQLite WAL Concurrency & S3 Direct-Stream Pipeline Invariants

Bun 1.2 Native SQLite WAL Concurrency & S3 Direct-Stream Pipeline Invariants defines an industrial-grade behavioral standard for backend engineering. Enforces strict production invariants for Bun 1.2 embedded database and object storage operations. Mandates WAL-mode PRAGMA concurrency configuration, prepared statement lifecycle reuse in 'bun:sqlite', and zero-copy S3 streaming via 'Bun.s3' to prevent node heap buffer exhaustion. 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: Buffering multi-megabyte payloads in process heap while executing un-indexed SQLite writes in rollback journal mode without statement preparation. 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	Buffering multi-megabyte payloads in process heap while executing un-indexed SQLite writes in rollback journal mode without statement preparation.	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 initialize 'bun

Engineers MUST ALWAYS initialize 'bun:sqlite' connections with 'PRAGMA journal_mode = WAL;', 'PRAGMA synchronous = NORMAL;', and 'PRAGMA busy_timeout = 5000;' immediately upon opening.

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.