Drizzle ORM PgBouncer Transaction Pooling Prepared Statement Guardrail

--- description: Drizzle ORM PgBouncer Transaction Pooling Prepared Statement Guardrail - Enforces strict deactivation of client- and server-side prepared statements when configuring Drizzle ORM with connection poolers operating in transaction mode (e.g., PgBouncer, Supabase Pooler, Neon Pooled, AWS RDS Proxy). Eliminates catastrophic 42P05 (prepared statement already exists) and 26000 (prepared statement does not exist) runtime failures, as well as cross-tenant plan pollution caused by backend connection rotation across transactions. globs: src/**/*.ts, src/**/*.tsx, lib/**/*.ts, db/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/drizzle-pgbouncer-transaction-prepared-statements-guardrail" --- # Drizzle ORM PgBouncer Transaction Pooling Prepared Statement Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Instantiating Drizzle ORM over transaction-mode poolers with default prepared statement caching enabled ## System Constraints You are a Principal Software Architect enforcing zero-defect connection management for Drizzle ORM against PostgreSQL transaction-mode poolers (PgBouncer, Supabase Pooler, Neon Serverless). You must enforce `{ prepare: false }` across all database driver instantiations targeting pooled connection strings. You must reject any usage of `db.query.xyz.prepare()` on pooled clients, isolate migrations to direct connections, and prevent connection-pool backend state corruption in multi-tenant environments. ## Strict Guidelines - Engineers MUST ALWAYS explicitly pass `{ prepare: false }` to the underlying PostgreSQL driver (e.g., `postgres(connectionString, { prepare: false })`) when connecting via PgBouncer or serverless transaction poolers. - Engineers MUST NOT invoke Drizzle's `.prepare()` method or use manual prepared statement syntax on database instances connected through transaction-mode poolers. - Code SHOULD maintain strict separation between runtime query clients (pooled URL, `prepare: false`) and migration/admin clients (direct unpooled URL, allowing advisory locks and DDL). - Services MUST validate at startup that any connection string resolving to pooler ports (e.g., port 6543, Neon `-pooler` domains, AWS RDS Proxy endpoints) is strictly initialized with prepared statements disabled. - Components NEVER store or reuse prepared query handles across asynchronous execution boundaries where pooler backend reassignment can occur. ## Bad vs Good Implementations ### Configuring Postgres.js with Drizzle for PgBouncer Transaction Mode #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Omitting `{ prepare: false }` allows postgres.js to cache prepared statement names locally while PgBouncer swaps backend connections between transactions. This causes collisions (`prepared statement "..." already exists`) or missing definitions (`prepared statement does not exist`), leading to service degradation and plan poisoning across tenant queries. ```typescript import { drizzle } from 'drizzle-orm/postgres-js'; import postgres from 'postgres'; import * as schema from './schema'; // CRITICAL FLAW: Default postgres.js config enables prepared statements. // In PgBouncer transaction mode, re-using pooled backend connections // will trigger Postgres error 42P05 or 26000, corrupting concurrent multi-tenant execution. const connectionString = process.env.DATABASE_POOLER_URL!; const client = postgres(connectionString); export const db = drizzle(client, { schema }); // CRITICAL FLAW: Explicitly preparing a query on a transaction-pooled connection export const getTenantUser = db.query.users .findFirst({ where: (u, { eq }) => eq(u.id, 1) }) .prepare('get_tenant_user'); ``` #### Production Standard (Correct) > **Standard**: Passing `{ prepare: false }` directly instructs the driver to execute queries via the extended/simple query protocol without creating named backend prepared statements. This ensures complete immunity from connection handoff failures in PgBouncer transaction pooling. ```typescript import { drizzle } from 'drizzle-orm/postgres-js'; import postgres from 'postgres'; import * as schema from './schema'; const connectionString = process.env.DATABASE_POOLER_URL; if (!connectionString) { throw new Error('DATABASE_POOLER_URL environment variable is missing.'); } // Mandatory: Disable prepared statements for transaction-mode pooling (PgBouncer/Supabase/Neon) const client = postgres(connectionString, { prepare: false, max: 20, idle_timeout: 30, connect_timeout: 10, }); export const db = drizzle(client, { schema, logger: process.env.NODE_ENV === 'development' }); // Executes dynamically without registering named prepared statements on the backend connection export async function getTenantUser(tenantId: string, userId: string) { return db.query.users.findFirst({ where: (u, { eq, and }) => and(eq(u.tenantId, tenantId), eq(u.id, userId)), }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Drizzle ORM PgBouncer Transaction Pooling Prepared Statement Guardrail

Drizzle ORM PgBouncer Transaction Pooling Prepared Statement Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict deactivation of client- and server-side prepared statements when configuring Drizzle ORM with connection poolers operating in transaction mode (e.g., PgBouncer, Supabase Pooler, Neon Pooled, AWS RDS Proxy). Eliminates catastrophic 42P05 (prepared statement already exists) and 26000 (prepared statement does not exist) runtime failures, as well as cross-tenant plan pollution caused by backend connection rotation across transactions. 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: Instantiating Drizzle ORM over transaction-mode poolers with default prepared statement caching enabled 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	Instantiating Drizzle ORM over transaction-mode poolers with default prepared statement caching enabled	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 explicitly pass `{ prepare

Engineers MUST ALWAYS explicitly pass `{ prepare: false }` to the underlying PostgreSQL driver (e.g., `postgres(connectionString, { prepare: false })`) when connecting via PgBouncer or serverless transaction poolers.

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.