Bun 1.2 Embedded SQLite WAL Concurrency & Native S3 Zero-Copy Streaming

--- description: Bun 1.2 Embedded SQLite WAL Concurrency & Native S3 Zero-Copy Streaming - Enforces deterministic concurrency protocols for Bun 1.2 embedded SQLite databases alongside zero-copy streaming through Bun's native S3 client API. Mandates Write-Ahead Logging (WAL) pragmas to prevent lock contention and eliminates heap exhaustion by piping streaming payloads directly to object storage without intermediate buffer allocation. globs: src/**/*.ts, src/db/**/*.ts, src/storage/**/*.ts, src/services/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sqlite-wal-s3-streaming-production-standard" --- # Bun 1.2 Embedded SQLite WAL Concurrency & Native S3 Zero-Copy Streaming > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Memory Buffering for S3 Uploads and Default SQLite Lock Contention ## System Constraints You are a Principal Systems Engineer enforcing strict Bun 1.2 runtime standards for embedded persistence and object storage. You must mandate: 1) Explicit SQLite pragmas for Write-Ahead Logging (WAL), busy timeouts, and synchronous modes to prevent file locking and read-write deadlocks; 2) Lifetime reuse of prepared statements via `bun:sqlite` with strict parameter binding; 3) Native zero-allocation streaming via `Bun.s3` or `S3Client.file()` without loading binary chunks into heap buffers (`ArrayBuffer`, `Buffer`). Reject any code that loads whole files into memory before persistence or omits concurrency pragmas on SQLite connections. ## Strict Guidelines - Engineers MUST ALWAYS execute `PRAGMA journal_mode = WAL;`, `PRAGMA busy_timeout = 5000;`, and `PRAGMA synchronous = NORMAL;` immediately upon opening a `bun:sqlite` Database instance. - Engineers MUST cache and reuse prepared `Statement` instances across requests instead of invoking `db.query()` dynamically inside high-throughput handlers. - Engineers MUST NOT read full file bodies or incoming HTTP request payloads into memory (`await req.arrayBuffer()`, `Buffer.concat()`) before piping to S3. - Code MUST pipe incoming `ReadableStream` instances directly into `s3file.write()` or native Bun `Bun.write(s3File, req.body)` to leverage OS-level zero-copy streaming. - Services MUST explicitly manage SQLite transaction boundaries using `db.transaction()` to ensure atomicity and rollback safety under concurrent WAL checkpoints. - Components NEVER execute unparameterized query strings via string interpolation; positional (`?`) or named (`$name`) parameters must be bound strictly to prepared statements. ## Bad vs Good Implementations ### SQLite WAL Concurrency and Prepared Statement Management #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Omitting WAL pragmas forces default rollback journal mode where writers block concurrent readers and readers block writers. Recompiling query strings on each execution causes CPU thrashing and introduces catastrophic SQL injection vulnerabilities. ```typescript import { Database } from "bun:sqlite"; // ANTI-PATTERN: Default rollback journal causes reader/writer locking. // Missing busy_timeout leads to instant 'database is locked' errors. const db = new Database("app.db"); export async function recordEvent(userId: string, payload: string) { // ANTI-PATTERN: Dynamic query compilation on every request with string interpolation (SQLi risk) db.run(`INSERT INTO events (user_id, payload, created_at) VALUES ('${userId}', '${payload}', ${Date.now()})`); // ANTI-PATTERN: Inefficient ad-hoc query without statement reuse return db.query(`SELECT * FROM events WHERE user_id = '${userId}'`).all(); } ``` #### Production Standard (Correct) > **Standard**: WAL mode decouples readers and writers, while `busy_timeout = 5000` prevents immediate lock aborts. Prepared statements are compiled once at module load, utilizing parameter binding and structured transactions. ```typescript import { Database, Statement } from "bun:sqlite"; const db = new Database("app.db", { create: true }); // MANDATORY: Optimize for concurrent read/write throughput in Bun db.exec(` PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 5000; PRAGMA synchronous = NORMAL; PRAGMA foreign_keys = ON; `); interface EventRecord { id: number; user_id: string; payload: string; created_at: number; } // Pre-compile and reuse statements const insertEventStmt: Statement = db.prepare( "INSERT INTO events (user_id, payload, created_at) VALUES ($userId, $payload, $createdAt)" ); const getEventsByUserStmt = db.prepare<EventRecord, [string]>( "SELECT id, user_id, payload, created_at FROM events WHERE user_id = ?" ); export const recordEvent = db.transaction((userId: string, payload: string): EventRecord[] => { insertEventStmt.run({ $userId: userId, $payload: payload, $createdAt: Date.now(), }); return getEventsByUserStmt.all(userId); }); ``` ### Bun Native S3 Multipart Zero-Allocation Streaming Upload #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Calling `await req.arrayBuffer()` or allocating `Buffer.from()` materializes the entire uploaded asset into the V8/JSC heap. Under concurrent uploads, this results in garbage collector pauses and process termination via Out-Of-Memory (OOM). ```typescript import { S3Client } from "bun"; const s3 = new S3Client({ bucket: "user-uploads", accessKeyId: process.env.AWS_ACCESS_KEY_ID, secretAccessKey: process.env.AWS_SECRET_ACCESS_KEY, }); export async function handleUpload(req: Request): Promise<Response> { // ANTI-PATTERN: Buffering entire multi-gigabyte stream into process memory (OOM vector) const payload = await req.arrayBuffer(); const fileBuffer = Buffer.from(payload); const fileKey = `uploads/${crypto.randomUUID()}`; const s3File = s3.file(fileKey); await s3File.write(fileBuffer); return Response.json({ success: true, key: fileKey }); } ``` #### Production Standard (Correct) > **Standard**: Passing the native `req.body` (ReadableStream) directly into `s3File.write()` bypasses heap allocation entirely. Bun orchestrates chunked multipart uploads directly from network sockets, keeping memory footprints near zero regardless of file scale. ```typescript import { S3Client } from "bun"; const s3 = new S3Client({ bucket: "user-uploads", accessKeyId: process.env.AWS_ACCESS_KEY_ID, secretAccessKey: process.env.AWS_SECRET_ACCESS_KEY, region: process.env.AWS_REGION ?? "us-east-1", }); export async function handleUpload(req: Request): Promise<Response> { if (!req.body) { return new Response("Missing request body", { status: 400 }); } const fileKey = `uploads/${crypto.randomUUID()}`; const s3File = s3.file(fileKey, { type: req.headers.get("content-type") ?? "application/octet-stream", }); // INVARIANT: Pipe the ReadableStream directly to Bun's native S3 storage implementation // Bun handles multipart chunking internally without buffering the entire payload in RAM await s3File.write(req.body); return Response.json({ success: true, key: fileKey, size: s3File.size, }, { status: 201 }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Embedded SQLite WAL Concurrency & Native S3 Zero-Copy Streaming

Bun 1.2 Embedded SQLite WAL Concurrency & Native S3 Zero-Copy Streaming defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic concurrency protocols for Bun 1.2 embedded SQLite databases alongside zero-copy streaming through Bun's native S3 client API. Mandates Write-Ahead Logging (WAL) pragmas to prevent lock contention and eliminates heap exhaustion by piping streaming payloads directly to object storage without intermediate buffer allocation. 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: Unbounded Memory Buffering for S3 Uploads and Default SQLite Lock Contention 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	Unbounded Memory Buffering for S3 Uploads and Default SQLite Lock Contention	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 execute `PRAGMA journal_mode = WAL

Engineers MUST ALWAYS execute `PRAGMA journal_mode = WAL;`, `PRAGMA busy_timeout = 5000;`, and `PRAGMA synchronous = NORMAL;` 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.