Bun 1.2 Native SQL Connection Pool Eviction and Session State Reset Standard

--- description: Bun 1.2 Native SQL Connection Pool Eviction and Session State Reset Standard - Enforces deterministic connection pool eviction, prepared statement invalidation, and session state reset for Bun 1.2 native SQL clients under high-concurrency and network partition recovery. Eliminates silent cross-tenant parameter leakage and stale session cache desynchronization caused by unhygienic socket reuse across multiplexed asynchronous worker contexts. globs: src/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/bun-sql-pool-eviction-session-reset-standard" --- # Bun 1.2 Native SQL Connection Pool Eviction and Session State Reset Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unchecked Connection Reuse Leaking Session Variables and Corrupted Prepared Statements Post-Partition ## System Constraints You are a Principal Software Architect enforcing Bun 1.2 Native SQL connection management standards. You must mandate that all native Bun SQL client instances rigorously isolate connection state, prevent leakage of session-scoped settings (search_path, role, local variables), deallocate or purge prepared statement catalogs upon transient socket failures, and force connection eviction when network partitions interrupt pooled sockets. ## Strict Guidelines - Engineers MUST ALWAYS isolate session-level mutations (such as SET ROLE, SET search_path, or SET LOCAL) to explicit reserved connection scopes using `sql.reserve()` or strict transaction boundaries, guaranteeing eviction or a full `DISCARD ALL` upon socket release. - Engineers MUST NOT execute mutating session configuration commands on root multiplexed SQL pool handles without explicit checkout semantics. - Services MUST configure explicit connection lifecycle invariants in `new SQL()`—including `max`, `idleTimeout`, `connectionTimeout`, and an `onconnect` initialization hook that enforces baseline connection state. - Code SHOULD catch network partition errors (such as ECONNRESET, EPIPE, or server-side socket terminations) and immediately evict poisoned reserved connections from rotation rather than returning them to the shared pool. - Components NEVER assume prepared statements survive network partition recovery intact; code must handle statement re-registration or enable automatic statement invalidation on connection re-establishment. ## Bad vs Good Implementations ### Unsafe Shared-Pool Mutation vs. Deterministic Reserved Connection Eviction #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing session-level state changes on a shared SQL handle mutates a pooled connection that is multiplexed across concurrent tasks. If an error or socket partition occurs before manual reset, subsequent queries executed on that connection run with leaked tenant context and elevated roles. ```typescript import { SQL } from "bun"; // ANTI-PATTERN: Shared client pool executes session-level mutations directly. // If a network blip occurs or an unhandled exception surfaces, downstream workers // inherit modified session state (e.g., search_path, role, or dirty local variables). export const sql = new SQL(process.env.DATABASE_URL!); export async function runTenantReport(tenantId: string, roleName: string) { // Leaks session variables onto shared pool socket; affects other concurrent tasks await sql`SET LOCAL app.current_tenant_id = ${tenantId}`; await sql`SET ROLE ${sql(roleName)}`; const results = await sql`SELECT * FROM sensitive_records WHERE tenant_id = ${tenantId}`; // Unreliable reset: If a network partition interrupts execution here, // the connection returns to the pool with poisoned credentials. await sql`RESET ROLE`; return results; } ``` #### Production Standard (Correct) > **Standard**: The production fix uses `sql.reserve()` to obtain a dedicated connection handle, isolates dynamic state using transactional `SET LOCAL` and `set_config(..., true)`, and enforces `DISCARD ALL` upon release. If a network partition or socket error occurs, the connection is closed and evicted rather than recycled. ```typescript import { SQL } from "bun"; // PRODUCTION FIX: Configured pool with baseline onconnect sanitizer and scoped reservation export const sql = new SQL({ url: process.env.DATABASE_URL!, max: 20, idleTimeout: 30, connectionTimeout: 5, onconnect: async (client) => { // Enforce baseline session hygiene upon connection establishment await client`RESET ALL; SET TIME ZONE 'UTC';`; }, }); export async function runTenantReport(tenantId: string, roleName: string) { // Reserve a dedicated connection from the pool const conn = await sql.reserve(); let isPoisoned = false; try { return await conn.begin(async (tx) => { // Scope mutations strictly within a transaction await tx`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`; await tx`SET LOCAL ROLE ${tx(roleName)}`; return await tx`SELECT * FROM sensitive_records WHERE tenant_id = ${tenantId}`; }); } catch (error) { // Flag connection for eviction on transport/socket failures isPoisoned = true; throw error; } finally { if (isPoisoned) { // Evict broken socket entirely from the pool on partition or network abort await conn.close().catch(() => {}); } else { // Sanitize connection state before releasing back to the pool try { await conn`DISCARD ALL`; await conn.release(); } catch { await conn.close().catch(() => {}); } } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Bun 1.2 Native SQL Connection Pool Eviction and Session State Reset Standard

Bun 1.2 Native SQL Connection Pool Eviction and Session State Reset Standard defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic connection pool eviction, prepared statement invalidation, and session state reset for Bun 1.2 native SQL clients under high-concurrency and network partition recovery. Eliminates silent cross-tenant parameter leakage and stale session cache desynchronization caused by unhygienic socket reuse across multiplexed asynchronous worker contexts. 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: Unchecked Connection Reuse Leaking Session Variables and Corrupted Prepared Statements Post-Partition 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	Unchecked Connection Reuse Leaking Session Variables and Corrupted Prepared Statements Post-Partition	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 isolate session-level mutations (such as SET ROLE, SET search_path, or SET LOCAL) to explicit reserved connection scopes using `sql.reserve()` or strict transaction boundaries, guaranteeing eviction or a full `DISCARD ALL` upon socket release.

Engineers MUST ALWAYS isolate session-level mutations (such as SET ROLE, SET search_path, or SET LOCAL) to explicit reserved connection scopes using `sql.reserve()` or strict transaction boundaries, guaranteeing eviction or a full `DISCARD ALL` upon socket release.

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.