AgentRules.org
RulesStacksClientsAuditorPrunerSelf-HealRFCsBenchmarkCLICI/CDAuthorsSubmit
This specification is maintained by the AgentRules Working Group. Have enhancements or want to claim maintainership?Submit Specification →
backendcritical PriorityID: bun-sqlite-wal-s3-streaming

Bun 1.2 SQLite WAL Concurrency and Native S3 Zero-Copy Streaming

Enforces high-throughput, low-latency data patterns in Bun 1.2 by configuring SQLite with Write-Ahead Logging (WAL) and proper busy timeouts, alongside native Bun S3 streaming primitives. Prevents connection starvation, SQLITE_BUSY deadlocks, and excessive heap memory allocation from in-memory stream buffering.

Prohibited Anti-Pattern: Ad-Hoc Unprepared SQLite Queries with Default Rollback Journals and In-Memory S3 Buffering

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: Bun 1.2 SQLite WAL Concurrency and Native S3 Zero-Copy Streaming - Enforces high-throughput, low-latency data patterns in Bun 1.2 by configuring SQLite with Write-Ahead Logging (WAL) and proper busy timeouts, alongside native Bun S3 streaming primitives. Prevents connection starvation, SQLITE_BUSY deadlocks, and excessive heap memory allocation from in-memory stream buffering.
globs: src/**/*.{ts,tsx}, lib/**/*.{ts,tsx}, server/**/*.{ts,tsx}
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/bun-sqlite-wal-s3-streaming"
---

# Bun 1.2 SQLite WAL Concurrency and Native S3 Zero-Copy Streaming

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Ad-Hoc Unprepared SQLite Queries with Default Rollback Journals and In-Memory S3 Buffering

## System Constraints
You are a Principal Systems Engineer enforcing strict Bun 1.2 backend invariants. You mandate optimized bun:sqlite configuration (WAL mode, busy_timeout, normal synchronous) and native S3 zero-copy streaming, preventing memory leaks, lock contention, and legacy Node.js SDK overhead.

## Strict Guidelines
- Engineers MUST ALWAYS initialize `bun:sqlite` databases with `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and `PRAGMA busy_timeout = 5000;` to prevent blocking under concurrent transactions.
- Engineers MUST ALWAYS reuse prepared statements created with `db.query()` or `db.prepare()` rather than re-compiling SQL strings inside request handlers.
- Engineers MUST ALWAYS use native `Bun.s3` (or `new S3Client()`) for object operations, piping `ReadableStream` instances directly via `s3.file(key).write(stream)` instead of accumulating payloads with `await req.arrayBuffer()` or `Buffer.concat()`.
- Code SHOULD utilize typed `.as(Model)` or zero-allocation `.values()` on SQLite statements when reading high-volume analytical records.
- Services MUST NOT import heavy external AWS S3 SDK clients (`@aws-sdk/client-s3`) when native `bun` S3 API provides standard S3-compatible protocol support.
- Components NEVER execute write transactions inside long-lived asynchronous loops without closing or committing, avoiding long-held SQLite locks.

## Bad vs Good Implementations
### SQLite WAL Configuration & Zero-Copy S3 Ingestion

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Default SQLite settings cause immediate SQLITE_BUSY lock errors under concurrency. Creating queries dynamically on each request invalidates execution plans and invites SQL injection. Using AWS SDK with full buffer buffering causes high heap usage and GC pauses.
```typescript
import { Database } from 'bun:sqlite';
import { S3Client, PutObjectCommand } from '@aws-sdk/client-s3';

const db = new Database('app.db'); // Defaults to DELETE journal mode and 0ms busy_timeout
const s3 = new S3Client({ region: 'us-east-1' });

export async function handleUpload(req: Request) {
  const url = new URL(req.url);
  const id = url.searchParams.get('id');
  
  // Bad: Buffers entire stream into Node.js heap Buffer/ArrayBuffer
  const payload = await req.arrayBuffer();
  
  await s3.send(new PutObjectCommand({
    Bucket: 'assets',
    Key: `uploads/${id}.bin`,
    Body: Buffer.from(payload)
  }));

  // Bad: Re-compiles SQL query on every HTTP request and risks SQLITE_BUSY
  db.query(`INSERT INTO uploads (id, bytes) VALUES ('${id}', ${payload.byteLength})`).run();

  return new Response('Uploaded');
}
```

#### Production Standard (Correct)
> **Standard**: Configures WAL mode and a 5000ms busy timeout to allow non-blocking concurrent reads and resilient writes. Reuses a pre-compiled SQLite statement to avoid SQL parsing overhead and ensure injection safety. Uses native Bun.s3 to stream request data directly to S3 without heap memory buffering.
```typescript
import { Database } from 'bun:sqlite';
import { s3 } from 'bun';

const db = new Database('app.db', { strict: true });

// Enforce concurrency invariants: WAL mode, safe timeout, durable synchronous
db.exec(`
  PRAGMA journal_mode = WAL;
  PRAGMA synchronous = NORMAL;
  PRAGMA busy_timeout = 5000;
`);

// Pre-compile statement once for zero-allocation query execution
const insertUploadStmt = db.query<
  void,
  { $id: string; $bytes: number }
>('INSERT INTO uploads (id, bytes) VALUES ($id, $bytes);');

export async function handleUpload(req: Request): Promise<Response> {
  const id = new URL(req.url).searchParams.get('id');
  if (!id || !req.body) {
    return new Response('Invalid request', { status: 400 });
  }

  const destination = s3.file(`uploads/${id}.bin`);

  // Zero-copy stream directly from HTTP socket to S3
  const bytesWritten = await destination.write(req.body);

  // Execute pre-compiled parameterized statement
  insertUploadStmt.run({
    $id: id,
    $bytes: bytesWritten,
  });

  return new Response('Uploaded', { status: 201 });
}
```
Recommended MCP Servers (mcp-serverlist.com)

Equip agent with system tools to verify code for this stack.

SQLite MCP Server
Inspects SQLite schemas, PRAGMA settings, index coverage, and slow query execution plans.
Recommended Agent Skills (askills.dev)

Ready-to-use executable skill functions tailored to this stack.

Bun CLI Runner
Executes Bun native tests and inspects memory benchmarks and file descriptor usage.
Architectural Specification & Invariant Rationale

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

Bun 1.2 SQLite WAL Concurrency and Native S3 Zero-Copy Streaming defines an industrial-grade behavioral standard for backend engineering. Enforces high-throughput, low-latency data patterns in Bun 1.2 by configuring SQLite with Write-Ahead Logging (WAL) and proper busy timeouts, alongside native Bun S3 streaming primitives. Prevents connection starvation, SQLITE_BUSY deadlocks, and excessive heap memory allocation from in-memory stream buffering. 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: Ad-Hoc Unprepared SQLite Queries with Default Rollback Journals and In-Memory S3 Buffering 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 ModeAI Symptom (The Anti-Pattern)SeverityProduction Impact & Remedy
AI Hallucination & Deprecation DriftAd-Hoc Unprepared SQLite Queries with Default Rollback Journals and In-Memory S3 BufferingCritical
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 InconsistencyAI 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:

1

Engineers MUST ALWAYS initialize `bun

Engineers MUST ALWAYS initialize `bun:sqlite` databases with `PRAGMA journal_mode = WAL;`, `PRAGMA synchronous = NORMAL;`, and `PRAGMA busy_timeout = 5000;` to prevent blocking under concurrent transactions.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
2

Engineers MUST ALWAYS reuse prepared statements created with `db.query()` or `db.prepare()` rather than re-compiling SQL strings inside request handlers.

Engineers MUST ALWAYS reuse prepared statements created with `db.query()` or `db.prepare()` rather than re-compiling SQL strings inside request handlers.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
3

Engineers MUST ALWAYS use native `Bun.s3` (or `new S3Client()`) for object operations, piping `ReadableStream` instances directly via `s3.file(key).write(stream)` instead of accumulating payloads with `await req.arrayBuffer()` or `Buffer.concat()`.

Engineers MUST ALWAYS use native `Bun.s3` (or `new S3Client()`) for object operations, piping `ReadableStream` instances directly via `s3.file(key).write(stream)` instead of accumulating payloads with `await req.arrayBuffer()` or `Buffer.concat()`.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
4

Code SHOULD utilize typed `.as(Model)` or zero-allocation `.values()` on SQLite statements when reading high-volume analytical records.

Code SHOULD utilize typed `.as(Model)` or zero-allocation `.values()` on SQLite statements when reading high-volume analytical records.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
5

Services MUST NOT import heavy external AWS S3 SDK clients (`@aws-sdk/client-s3`) when native `bun` S3 API provides standard S3-compatible protocol support.

Services MUST NOT import heavy external AWS S3 SDK clients (`@aws-sdk/client-s3`) when native `bun` S3 API provides standard S3-compatible protocol support.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
6

Components NEVER execute write transactions inside long-lived asynchronous loops without closing or committing, avoiding long-held SQLite locks.

Components NEVER execute write transactions inside long-lived asynchronous loops without closing or committing, avoiding long-held SQLite locks.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
Technical Questions & Answers

Frequently Asked Technical Questions

Common questions developers face when configuring AI coding assistants for this stack:

Q:Why is Bun 1.2 SQLite WAL Concurrency and Native S3 Zero-Copy Streaming critical for AI coding assistants?

Enforces high-throughput, low-latency data patterns in Bun 1.2 by configuring SQLite with Write-Ahead Logging (WAL) and proper busy timeouts, alongside native Bun S3 streaming primitives. Prevents connection starvation, SQLITE_BUSY deadlocks, and excessive heap memory allocation from in-memory stream buffering. Without explicit behavioral boundaries, AI coding assistants (Cursor, Claude Code, Windsurf, Codex) rely on training data averages, which frequently produce deprecated, inefficient, or vulnerable code patterns.

Q:How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Ad-Hoc Unprepared SQLite Queries with Default Rollback Journals and In-Memory S3 Buffering) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q:Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q:Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Open Specification · CC BY 4.0·Free for Personal, Team & Commercial In-Repo Use

Citation & Open Source Attribution Guidelines

This specification is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0). You are completely free to adapt and share this rule in your workspace. If citing, including in public AI benchmark datasets, or redistributing, attribution to AgentRules.org is required.

Related BACKEND Rules

View all rules →
backend
42

Fastify 5 Schema Compilers & Encapsulation Production Standard

Fastify relies on lexical encapsulation contexts to sandbox decorators, hooks, and compiled validation/serialization schemas without memory leaks or cross-tenant bleed. In Fastify 5, misuse of global compilers or failing to encapsulate plugin boundaries causes compiled `fast-json-stringify` serializers and Ajv validators to either crash on duplicate IDs, leak private data across sibling scopes, or bypass JIT compilation optimizations. Production services must enforce strict encapsulation hierarchies and type-safe schema compilation across every routing subtree.

backend
68

Bun Embedded SQLite WAL Mode & Transaction Synchronization Standard

Production database standard for bun:sqlite. Mandates enabling Write-Ahead Logging (PRAGMA journal_mode = WAL), busy timeouts, and using db.transaction() for atomic operations without lock starvation.

backend
68

Drizzle ORM Batch API & Interactive Transaction Isolation Standard

Production database standard for Drizzle ORM (PostgreSQL & MySQL). Mandates using db.batch() over HTTP drivers to prevent round-trip waterfalls, explicit db.transaction() with serializable/read-committed isolation, and strictly avoids unawaited query promises.

1 Rule in Stack

Bun