Drizzle ORM Prepared Statement Multiplexing & PgBouncer Safety Standard

--- description: Drizzle ORM Prepared Statement Multiplexing & PgBouncer Safety Standard - Enforces safe execution semantics for Drizzle ORM when operating over PgBouncer in transaction-pooling mode. Mandates driver-level disabling of persistent named prepared statements and isolates session-level bindings to prevent cross-tenant statement collisions and state pollution. globs: src/**/*.{ts,tsx}, lib/**/*.{ts,tsx}, db/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/drizzle-pgbouncer-prepared-statement-multiplexing-guardrail" --- # Drizzle ORM Prepared Statement Multiplexing & PgBouncer Safety Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Persisting static named prepared statements across multiplexed PgBouncer transaction-mode pools, causing 'prepared statement already exists' crashes and cross-tenant session leaks. ## System Constraints You are a Principal Engineer enforcing the Drizzle ORM Prepared Statement Connection Multiplexing Guardrail. You must ensure that all PostgreSQL database connections managed through Drizzle ORM over PgBouncer in transaction-pooling mode explicitly disable driver-level persistent named prepared statements or scope them dynamically per session. You must reject any global static statement names that lead to backend connection collisions or tenant context bleed across pooled connections. ## Strict Guidelines - Engineers MUST ALWAYS configure underlying database drivers (such as `postgres.js`) with `prepare: false` when connecting through PgBouncer transaction-mode pools, or use unnamed prepared statements exclusively. - Engineers MUST NOT invoke `.prepare('static_name')` on Drizzle queries with globally shared, hardcoded statement identifiers when operating over a multiplexed pool. - Services MUST execute tenant-scoped queries within isolated transactions (`db.transaction()`) whenever applying transaction-local configuration settings such as `SET LOCAL app.current_tenant_id`. - Engineers MUST NEVER reuse pre-compiled prepared statements across disparate tenant execution contexts without parameter re-binding at execution time. - Code SHOULD leverage Drizzle's direct query execution API (`db.select().from(...).where(...)`) without explicit `.prepare()` caching unless the pooler runs in session mode or driver-level statement de-allocation is rigorously managed. ## Bad vs Good Implementations ### Configuring postgres.js with Drizzle ORM for PgBouncer Transaction Pooling #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Default driver configurations retain named prepared statements on the backend PostgreSQL session. When PgBouncer reassigns backend connections in transaction pooling mode, a subsequent client executing `.prepare('get_user_by_id')` on that backend throws a duplicate statement error or re-executes against dirty session state. ```typescript import { drizzle } from 'drizzle-orm/postgres-js'; import postgres from 'postgres'; import { users } from './schema'; import { eq } from 'drizzle-orm'; // ANTI-PATTERN: Default postgres.js config enables server-side prepared statements. // When multiplexed across PgBouncer in transaction mode, prepared statements are orphaned // on physical backends, colliding on subsequent transactions or leaking tenant state. const client = postgres(process.env.DATABASE_URL!); // prepare: true by default export const db = drizzle(client); // ANTI-PATTERN: Globally registered static prepared statement name export const getTenantUser = db .select() .from(users) .where(eq(users.id, users.id)) .prepare('get_user_by_id'); // Crashes with: prepared statement "get_user_by_id" already exists ``` #### Production Standard (Correct) > **Standard**: Setting `prepare: false` in postgres.js instructs the driver to use the unnamed extended query protocol or simple query execution, which is fully compatible with PgBouncer transaction-mode multiplexing. Tenant IDs are bound per-query, and transactions ensure atomic connection isolation. ```typescript import { drizzle } from 'drizzle-orm/postgres-js'; import postgres from 'postgres'; import { users } from './schema'; import { eq, and } from 'drizzle-orm'; // PRODUCTION FIX: Disable prepared statements at the driver level for PgBouncer transaction pooling const client = postgres(process.env.DATABASE_URL!, { prepare: false, // Forces unnamed protocol-level statements max: 20, idle_timeout: 30, }); export const db = drizzle(client); // PRODUCTION FIX: Use dynamic queries or transaction-isolated execution without static server-side names export async function getTenantUser(tenantId: string, userId: string) { return db.transaction(async (tx) => { return tx .select() .from(users) .where( and( eq(users.tenantId, tenantId), eq(users.id, userId) ) ); }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Drizzle ORM Prepared Statement Multiplexing & PgBouncer Safety Standard

Drizzle ORM Prepared Statement Multiplexing & PgBouncer Safety Standard defines an industrial-grade behavioral standard for backend engineering. Enforces safe execution semantics for Drizzle ORM when operating over PgBouncer in transaction-pooling mode. Mandates driver-level disabling of persistent named prepared statements and isolates session-level bindings to prevent cross-tenant statement collisions and state pollution. 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: Persisting static named prepared statements across multiplexed PgBouncer transaction-mode pools, causing 'prepared statement already exists' crashes and cross-tenant session leaks. 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	Persisting static named prepared statements across multiplexed PgBouncer transaction-mode pools, causing 'prepared statement already exists' crashes and cross-tenant session leaks.	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 configure underlying database drivers (such as `postgres.js`) with `prepare

Engineers MUST ALWAYS configure underlying database drivers (such as `postgres.js`) with `prepare: false` when connecting through PgBouncer transaction-mode pools, or use unnamed prepared statements exclusively.

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.