Bun 1.2 Native PostgreSQL Pipelining and Batching Architectural Invariant

--- description: Bun 1.2 Native PostgreSQL Pipelining and Batching Architectural Invariant - Enforces bounded pipelined SQL command execution using Bun 1.2's native `bun:sql` driver to eliminate N+1 network round-trip query serialization bottlenecks under microservice workloads. Prohibits sequential async/await round-trips over pooled connections and requires transactional batching or pipelined query dispatches within bounded queue limits. globs: src/**/*.ts, src/db/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-v12-postgres-pipelining-batch-invariant" --- # Bun 1.2 Native PostgreSQL Pipelining and Batching Architectural Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Sequential Network Round-Trip Awaits and Unbounded Connection Hoarding in High-Throughput Handlers ## System Constraints You are a Principal Backend Architect enforcing high-throughput database invariants for Bun 1.2. Bun 1.2 provides high-performance native PostgreSQL support (`import { SQL } from 'bun'`). You must strictly prevent sequential per-query network round-trips (head-of-line blocking at the application level) and unbounded parallel connection starvation. Enforce native connection pipelining or transactional array-parameterized batching, ensuring connection checkouts are minimized and query pipelines are bounded. ## Strict Guidelines - Engineers MUST ALWAYS leverage Bun 1.2's native `SQL` client (`import { SQL } from 'bun'`) and execute concurrent independent read queries via `Promise.all` over the pipelining-capable client rather than sequential `await` chains. - Engineers MUST NOT sequentially await multiple non-dependent queries inside request handlers, as this forces N sequential network round-trips (RTT) and starves the connection pool. - Engineers MUST limit batch pipeline depth to a maximum bounded threshold (e.g., chunks of 50-100 operations) when issuing bulk mutations to avoid saturating PostgreSQL command buffers and socket buffers. - Services MUST execute multi-statement write workflows inside atomic transactions (`sql.begin()`) using parameterized batch arrays (`UNNEST` or multi-row `VALUES`) instead of iterative single-row insert/update loops. - Components NEVER instantiate ad-hoc `new SQL(...)` connection pools inside request handlers or middleware; instances MUST be singletons configured with explicit `max` connection boundaries. ## Bad vs Good Implementations ### Independent Multi-Query Execution: Sequential Await Anti-Pattern vs Native Pipelining #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Sequential `await` statements serialize network operations, introducing latency equal to the sum of round-trip times (RTT) across all queries and increasing connection hold time under load. ```typescript import { SQL } from 'bun'; const sql = new SQL(process.env.DATABASE_URL!); export async function getDashboardMetrics(orgId: string) { // ANTI-PATTERN: Sequential awaits force 3 full network round-trips (3x RTT) // Application waits for the server response of query 1 before dispatching query 2 const users = await sql`SELECT id, name FROM users WHERE org_id = ${orgId} LIMIT 10`; const metrics = await sql`SELECT total_events, active_seats FROM org_stats WHERE org_id = ${orgId}`; const audit = await sql`SELECT action, created_at FROM audit_logs WHERE org_id = ${orgId} ORDER BY created_at DESC LIMIT 5`; return { users, metrics: metrics[0], audit }; } ``` #### Production Standard (Correct) > **Standard**: Executing concurrent queries with `Promise.all` on Bun 1.2's native PostgreSQL client allows pipelined socket writing, dispatching multiple query commands before waiting for earlier results, reducing total latency to ~1 RTT. ```typescript import { SQL } from 'bun'; // Singleton connection instance configured with bounded connection pool export const sql = new SQL({ url: process.env.DATABASE_URL!, max: 20, idleTimeout: 30, }); export async function getDashboardMetrics(orgId: string) { // PRODUCTION FIX: Concurrently dispatched queries leverage PostgreSQL protocol-level // pipelining over pooled connections in Bun 1.2, executing in a single network round-trip window const [users, metrics, audit] = await Promise.all([ sql`SELECT id, name FROM users WHERE org_id = ${orgId} LIMIT 10`, sql`SELECT total_events, active_seats FROM org_stats WHERE org_id = ${orgId}`, sql`SELECT action, created_at FROM audit_logs WHERE org_id = ${orgId} ORDER BY created_at DESC LIMIT 5`, ]); return { users, metrics: metrics[0], audit }; } ``` ### Bulk Ingestion: Iterative Row Writes vs Bounded Pipelined Batching #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Looping through an array with sequential `await` statements generates massive connection contention, high CPU consumption from serialization overhead, and quadratic latency degradation as the collection grows. ```typescript import { SQL } from 'bun'; const sql = new SQL(process.env.DATABASE_URL!); interface DeviceReading { deviceId: string; reading: number; timestamp: Date; } export async function recordReadings(readings: DeviceReading[]) { // ANTI-PATTERN: O(N) database round-trips inside a loop. Saturates network sockets and pool. for (const item of readings) { await sql`INSERT INTO telemetry (device_id, reading, recorded_at) VALUES (${item.deviceId}, ${item.reading}, ${item.timestamp})`; } } ``` #### Production Standard (Correct) > **Standard**: Bundles records into bounded chunks utilizing PostgreSQL's native `UNNEST` array mapping inside a single atomic transaction block, converting thousands of socket round-trips into deterministic, bounded chunk operations. ```typescript import { SQL } from 'bun'; export const sql = new SQL({ url: process.env.DATABASE_URL!, max: 20, }); interface DeviceReading { deviceId: string; reading: number; timestamp: Date; } const CHUNK_SIZE = 100; export async function recordReadings(readings: DeviceReading[]): Promise<void> { if (readings.length === 0) return; // PRODUCTION FIX: Transactional batching with bounded chunking via UNNEST // Dispatches bulk writes efficiently with minimal network round-trips await sql.begin(async (tx) => { for (let i = 0; i < readings.length; i += CHUNK_SIZE) { const chunk = readings.slice(i, i + CHUNK_SIZE); const deviceIds = chunk.map(r => r.deviceId); const values = chunk.map(r => r.reading); const timestamps = chunk.map(r => r.timestamp); await tx` INSERT INTO telemetry (device_id, reading, recorded_at) SELECT * FROM UNNEST(${deviceIds}::text[], ${values}::float8[], ${timestamps}::timestamptz[]) `; } }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native PostgreSQL Pipelining and Batching Architectural Invariant

Bun 1.2 Native PostgreSQL Pipelining and Batching Architectural Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces bounded pipelined SQL command execution using Bun 1.2's native `bun:sql` driver to eliminate N+1 network round-trip query serialization bottlenecks under microservice workloads. Prohibits sequential async/await round-trips over pooled connections and requires transactional batching or pipelined query dispatches within bounded queue limits. 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: Sequential Network Round-Trip Awaits and Unbounded Connection Hoarding in High-Throughput Handlers 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	Sequential Network Round-Trip Awaits and Unbounded Connection Hoarding in High-Throughput Handlers	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 leverage Bun 1.2's native `SQL` client (`import { SQL } from 'bun'`) and execute concurrent independent read queries via `Promise.all` over the pipelining-capable client rather than sequential `await` chains.

Engineers MUST ALWAYS leverage Bun 1.2's native `SQL` client (`import { SQL } from 'bun'`) and execute concurrent independent read queries via `Promise.all` over the pipelining-capable client rather than sequential `await` chains.

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.