Drizzle ORM Serverless Transaction-Pool Prepared Statement Invariant

--- description: Drizzle ORM Serverless Transaction-Pool Prepared Statement Invariant - Enforces explicit disabling of named prepared statements and caps connection allocations when connecting Drizzle ORM to transaction-mode connection poolers such as PgBouncer, Supabase Pooler, or AWS RDS Proxy. Prevents SQLSTATE 42P05 collisions, cross-client state contamination, and ephemeral serverless socket exhaustion. Mandates strict client configuration for postgres.js, node-postgres, and serverless edge adapters. globs: src/db/**/*.{ts,tsx}, src/lib/db/**/*.{ts,tsx}, src/server/db/**/*.{ts,tsx}, drizzle.config.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/drizzle-serverless-pgbouncer-prepared-statement-invariant" --- # Drizzle ORM Serverless Transaction-Pool Prepared Statement Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Instantiating Drizzle ORM in serverless runtimes using default driver options, triggering duplicate prepared statement exceptions (42P05) and pooler socket exhaustion. ## System Constraints You are a Principal Backend Architect enforcing strict connection pooling invariants in Drizzle ORM for serverless environments. In transaction-mode pooling (e.g., PgBouncer, Supabase Port 6543, Neon pooler, AWS RDS Proxy), backend Postgres connections are re-assigned on every transaction boundary. The driver MUST NEVER register named prepared statements on the backend socket, as subsequent transactions mapped to the same connection will fail with 'prepared statement already exists' (42P05) or leak context. Furthermore, serverless functions MUST configure connection limits (max: 1) and idle timeouts to prevent client-side connection leaks and upstream pool starvation. You will reject any Drizzle initialization code that fails to set prepare: false or lacks connection pool containment in serverless runtimes. ## Strict Guidelines - Engineers MUST ALWAYS set `prepare: false` when initializing `postgres.js` (`postgres()`) or configure `statement_cache_size: 0` when using `pg` behind a transaction-mode connection pooler. - Engineers MUST configure `max: 1` (or single-connection client lifecycle) for serverless handlers (AWS Lambda, Vercel Functions, Cloudflare Workers) to eliminate socket leaks and avoid pooler socket exhaustion. - Engineers MUST NOT invoke `db.query.<table>.findMany().prepare()` or call `.prepare()` on Drizzle relational queries when attached to a transaction-pooled connection. - Database connection factories MUST separate transaction-mode pooled endpoints (used for runtime OLTP workloads) from session-mode direct endpoints (used exclusively for migrations via `drizzle-kit migrate`). - Components NEVER instantiate database client singletons globally without guards against repeated instantiation across hot-reloading development runtimes. ## Bad vs Good Implementations ### postgres.js Driver Initialization with Transaction-Mode PgBouncer #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Default postgres.js configuration enables named prepared statements. In PgBouncer transaction mode, reusing backend server connections with named statements causes fatal collisions (error: prepared statement "..." already exists). Additionally, default client connection pooling rapidly exhausts upstream connection pools in serverless scale-outs. ```typescript import { drizzle } from 'drizzle-orm/postgres-js'; import postgres from 'postgres'; import * as schema from './schema'; // ANTI-PATTERN: Default postgres() client enables named prepared statements // and multi-connection pooling, causing SQLSTATE 42P05 in transaction-mode PgBouncer // and socket exhaustion across serverless function invocations. const client = postgres(process.env.DATABASE_URL!); export const db = drizzle(client, { schema }); ``` #### Production Standard (Correct) > **Standard**: Passing `prepare: false` instructs postgres.js to execute queries using PostgreSQL's unnamed statement protocol or simple query protocol, avoiding state retention in PgBouncer. Setting `max: 1` prevents unbounded socket growth in serverless environments. ```typescript import { drizzle } from 'drizzle-orm/postgres-js'; import postgres from 'postgres'; import * as schema from './schema'; const connectionString = process.env.DATABASE_URL; if (!connectionString) { throw new Error('DATABASE_URL environment variable is missing'); } // INVARIANT: Disable named prepared statements for PgBouncer transaction pooling // and cap client connections to 1 for ephemeral serverless runtimes. const client = postgres(connectionString, { prepare: false, max: 1, idle_timeout: 20, connect_timeout: 10, }); export const db = drizzle(client, { schema }); ``` ### node-postgres (pg.Pool) Setup for Transaction Poolers #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: node-postgres pools can exhaust serverless upstream proxies by holding default pools open, and default query configurations can inadvertently cache prepared statements or attempt multi-statement setups incompatible with transaction poolers. ```typescript import { drizzle } from 'drizzle-orm/node-postgres'; import { Pool } from 'pg'; import * as schema from './schema'; // ANTI-PATTERN: Default pg.Pool maintains statement cache and large default connection pools const pool = new Pool({ connectionString: process.env.DATABASE_URL, }); export const db = drizzle(pool, { schema }); ``` #### Production Standard (Correct) > **Standard**: Capping pool size to 1 and setting explicit aggressive idle and connection timeouts prevents serverless instance freezes from keeping stale socket locks in PgBouncer. ```typescript import { drizzle } from 'drizzle-orm/node-postgres'; import { Pool } from 'pg'; import * as schema from './schema'; const connectionString = process.env.DATABASE_URL; if (!connectionString) { throw new Error('DATABASE_URL environment variable is missing'); } // INVARIANT: Explicitly constrain pool size and connection timeouts for serverless workloads const pool = new Pool({ connectionString, max: 1, connectionTimeoutMillis: 5000, idleTimeoutMillis: 10000, }); export const db = drizzle(pool, { schema }); ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Drizzle ORM Serverless Transaction-Pool Prepared Statement Invariant

Drizzle ORM Serverless Transaction-Pool Prepared Statement Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces explicit disabling of named prepared statements and caps connection allocations when connecting Drizzle ORM to transaction-mode connection poolers such as PgBouncer, Supabase Pooler, or AWS RDS Proxy. Prevents SQLSTATE 42P05 collisions, cross-client state contamination, and ephemeral serverless socket exhaustion. Mandates strict client configuration for postgres.js, node-postgres, and serverless edge adapters. 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 in serverless runtimes using default driver options, triggering duplicate prepared statement exceptions (42P05) and pooler socket exhaustion. 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 in serverless runtimes using default driver options, triggering duplicate prepared statement exceptions (42P05) and pooler socket exhaustion.	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 set `prepare

Engineers MUST ALWAYS set `prepare: false` when initializing `postgres.js` (`postgres()`) or configure `statement_cache_size: 0` when using `pg` behind a transaction-mode connection pooler.

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.