AgentRules.org
RulesStacksClientsAuditorPrunerSelf-HealRFCsBenchmarkCLICI/CDAuthorsSubmit
This specification is maintained by the AgentRules Working Group. Have enhancements or want to claim maintainership?Submit Specification →
backendcritical PriorityID: drizzle-prepared-statement-pool-eviction-standard

Drizzle ORM Prepared Statement Connection-Pool Eviction & Multiplexer Protection

Enforces strict bounds on dynamic prepared statement generation and mandates explicit statement lifecycle controls across multi-tenant PostgreSQL pools. Dynamic interpolation and unconstrained statement names exhaust backend connection memory and trigger prepared statement collision errors behind transaction poolers like PgBouncer and Supabase Supavisor. All dynamic queries must execute in direct un-cached modes or maintain deterministic, tenant-isolated parameterized identities.

Prohibited Anti-Pattern: Unbounded Dynamic Prepared Statement Proliferation Causing Session-Pooler Exhaustion and Tenant Cross-Contamination

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: Drizzle ORM Prepared Statement Connection-Pool Eviction & Multiplexer Protection - Enforces strict bounds on dynamic prepared statement generation and mandates explicit statement lifecycle controls across multi-tenant PostgreSQL pools. Dynamic interpolation and unconstrained statement names exhaust backend connection memory and trigger prepared statement collision errors behind transaction poolers like PgBouncer and Supabase Supavisor. All dynamic queries must execute in direct un-cached modes or maintain deterministic, tenant-isolated parameterized identities.
globs: src/db/**/*.{ts,tsx}, src/repositories/**/*.{ts,tsx}, src/services/**/*.{ts,tsx}, src/lib/db/**/*.{ts,tsx}
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/drizzle-prepared-statement-pool-eviction-standard"
---

# Drizzle ORM Prepared Statement Connection-Pool Eviction & Multiplexer Protection

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unbounded Dynamic Prepared Statement Proliferation Causing Session-Pooler Exhaustion and Tenant Cross-Contamination

## System Constraints
You are a Principal Backend Architect enforcing strict connection pooling and SQL execution invariants for Drizzle ORM paired with PostgreSQL connection multiplexers (PgBouncer, Supavisor, AWS RDS Proxy). You must prevent unbounded prepared statement allocations, dynamic SQL interpolation in `.prepare()`, and cross-tenant memory leakage. Mandate deterministic statement names, disable prepared statements for transaction-mode poolers where applicable, and force query parameterization.

## Strict Guidelines
- Engineers MUST ALWAYS configure the underlying PostgreSQL driver (e.g., `postgres-js` or `pg`) with `prepare: false` when routing through a transaction-mode connection pooler like PgBouncer, unless explicitly using a statement-caching multiplexer configured for session pooling.
- Engineers MUST NOT create dynamic, runtime-interpolated prepared statement names via `.prepare(statementName)`; statement identifiers MUST be compile-time constants or statically enumerable tokens.
- Engineers MUST NEVER use dynamic condition concatenation within `.prepare()` blocks that result in combinatorial explosions of unique statement shapes on the database session.
- Code MUST isolate multi-tenant parameters within bound variables (`sql.placeholder` or positional values) rather than altering query structural signatures across tenant executions.
- Services MUST explicitly manage LRU eviction or limit prepared statements in persistent worker runtimes to avoid unbounded `pg_prepared_statements` bloat across horizontal replicas.

## Bad vs Good Implementations
### Dynamic SQL in Prepared Statement with Transaction Pooling Anti-Pattern vs. Safe Parameterized Execution

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Dynamic statement names and conditional SQL fragments inside `.prepare()` generate unique prepared statements on every query variation. In pooled architectures (PgBouncer in transaction mode), this causes 'prepared statement already exists' errors, tenant memory bloat in PostgreSQL's pg_prepared_statements cache, and multiplexer backend exhaustion.
```typescript
import { sql } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import { users } from './schema';

// ANTI-PATTERN: Client configured without disabling prepared statements on transaction pooler
const client = postgres(process.env.DATABASE_URL!); // Default enables statement caching
export const db = drizzle(client);

export async function getTenantUsersDynamic(tenantId: string, filterStatus?: string) {
  // Leaks memory: statementName is uniquely generated per invocation
  // Dynamic SQL structure changes the cached statement signature continuously
  const statementName = `find_users_${tenantId}_${filterStatus ?? 'all'}`;
  
  const query = db
    .select()
    .from(users)
    .where(sql`${users.tenantId} = ${tenantId} ${filterStatus ? sql`AND ${users.status} = ${filterStatus}` : sql``}`)
    .prepare(statementName);

  return await query.execute();
}
```

#### Production Standard (Correct)
> **Standard**: Disabling driver-level implicit prepared statements protects transaction poolers. For queries that benefit from explicit preparation, the statement shape is completely static with compile-time named identifiers and placeholders (`sql.placeholder`), ensuring exact reuse without backend PostgreSQL memory exhaustion or collision across tenants.
```typescript
import { sql } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import { users } from './schema';

// PRODUCTION FIX: Disable client-side prepared statements when behind transaction-mode pooler
const client = postgres(process.env.DATABASE_URL!, {
  prepare: false, // Prevents session memory bloat on multiplexer
  max: 20,
  idle_timeout: 30,
});

export const db = drizzle(client);

// Static, fully parameterized prepared statement with bound placeholders
const findTenantUsersByStatusStatement = db
  .select()
  .from(users)
  .where(
    sql`${users.tenantId} = ${sql.placeholder('tenantId')} AND (${sql.placeholder('status')}::text IS NULL OR ${users.status} = ${sql.placeholder('status')})`
  )
  .prepare('find_tenant_users_by_status');

export async function getTenantUsers(tenantId: string, filterStatus: string | null) {
  // Deterministic statement name and static SQL AST reused across all tenants
  return await findTenantUsersByStatusStatement.execute({
    tenantId,
    status: filterStatus,
  });
}
```
Recommended MCP Servers (mcp-serverlist.com)

Equip agent with system tools to verify code for this stack.

PostgreSQL Context Server
Inspect active backend connections, lock states, and query `pg_prepared_statements` to detect connection pool bloat.
Recommended Agent Skills (askills.dev)

Ready-to-use executable skill functions tailored to this stack.

PgBouncer Metrics and Session Inspector
Runs operational inspection of backend pooling mode, server pool saturation, and prepared statement state allocation.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: Drizzle ORM Prepared Statement Connection-Pool Eviction & Multiplexer Protection

Drizzle ORM Prepared Statement Connection-Pool Eviction & Multiplexer Protection defines an industrial-grade behavioral standard for backend engineering. Enforces strict bounds on dynamic prepared statement generation and mandates explicit statement lifecycle controls across multi-tenant PostgreSQL pools. Dynamic interpolation and unconstrained statement names exhaust backend connection memory and trigger prepared statement collision errors behind transaction poolers like PgBouncer and Supabase Supavisor. All dynamic queries must execute in direct un-cached modes or maintain deterministic, tenant-isolated parameterized identities. 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: Unbounded Dynamic Prepared Statement Proliferation Causing Session-Pooler Exhaustion and Tenant Cross-Contamination 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 ModeAI Symptom (The Anti-Pattern)SeverityProduction Impact & Remedy
AI Hallucination & Deprecation DriftUnbounded Dynamic Prepared Statement Proliferation Causing Session-Pooler Exhaustion and Tenant Cross-ContaminationCritical
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 InconsistencyAI 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:

1

Engineers MUST ALWAYS configure the underlying PostgreSQL driver (e.g., `postgres-js` or `pg`) with `prepare

Engineers MUST ALWAYS configure the underlying PostgreSQL driver (e.g., `postgres-js` or `pg`) with `prepare: false` when routing through a transaction-mode connection pooler like PgBouncer, unless explicitly using a statement-caching multiplexer configured for session pooling.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
2

Engineers MUST NOT create dynamic, runtime-interpolated prepared statement names via `.prepare(statementName)`

Engineers MUST NOT create dynamic, runtime-interpolated prepared statement names via `.prepare(statementName)`; statement identifiers MUST be compile-time constants or statically enumerable tokens.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
3

Engineers MUST NEVER use dynamic condition concatenation within `.prepare()` blocks that result in combinatorial explosions of unique statement shapes on the database session.

Engineers MUST NEVER use dynamic condition concatenation within `.prepare()` blocks that result in combinatorial explosions of unique statement shapes on the database session.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
4

Code MUST isolate multi-tenant parameters within bound variables (`sql.placeholder` or positional values) rather than altering query structural signatures across tenant executions.

Code MUST isolate multi-tenant parameters within bound variables (`sql.placeholder` or positional values) rather than altering query structural signatures across tenant executions.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
5

Services MUST explicitly manage LRU eviction or limit prepared statements in persistent worker runtimes to avoid unbounded `pg_prepared_statements` bloat across horizontal replicas.

Services MUST explicitly manage LRU eviction or limit prepared statements in persistent worker runtimes to avoid unbounded `pg_prepared_statements` bloat across horizontal replicas.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
Technical Questions & Answers

Frequently Asked Technical Questions

Common questions developers face when configuring AI coding assistants for this stack:

Q:Why is Drizzle ORM Prepared Statement Connection-Pool Eviction & Multiplexer Protection critical for AI coding assistants?

Enforces strict bounds on dynamic prepared statement generation and mandates explicit statement lifecycle controls across multi-tenant PostgreSQL pools. Dynamic interpolation and unconstrained statement names exhaust backend connection memory and trigger prepared statement collision errors behind transaction poolers like PgBouncer and Supabase Supavisor. All dynamic queries must execute in direct un-cached modes or maintain deterministic, tenant-isolated parameterized identities. Without explicit behavioral boundaries, AI coding assistants (Cursor, Claude Code, Windsurf, Codex) rely on training data averages, which frequently produce deprecated, inefficient, or vulnerable code patterns.

Q:How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unbounded Dynamic Prepared Statement Proliferation Causing Session-Pooler Exhaustion and Tenant Cross-Contamination) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q:Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q:Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Open Specification · CC BY 4.0·Free for Personal, Team & Commercial In-Repo Use

Citation & Open Source Attribution Guidelines

This specification is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0). You are completely free to adapt and share this rule in your workspace. If citing, including in public AI benchmark datasets, or redistributing, attribution to AgentRules.org is required.

Related BACKEND Rules

View all rules →
backend
42

Fastify 5 Schema Compilers & Encapsulation Production Standard

Fastify relies on lexical encapsulation contexts to sandbox decorators, hooks, and compiled validation/serialization schemas without memory leaks or cross-tenant bleed. In Fastify 5, misuse of global compilers or failing to encapsulate plugin boundaries causes compiled `fast-json-stringify` serializers and Ajv validators to either crash on duplicate IDs, leak private data across sibling scopes, or bypass JIT compilation optimizations. Production services must enforce strict encapsulation hierarchies and type-safe schema compilation across every routing subtree.

backend
68

Bun Embedded SQLite WAL Mode & Transaction Synchronization Standard

Production database standard for bun:sqlite. Mandates enabling Write-Ahead Logging (PRAGMA journal_mode = WAL), busy timeouts, and using db.transaction() for atomic operations without lock starvation.

backend
68

Drizzle ORM Batch API & Interactive Transaction Isolation Standard

Production database standard for Drizzle ORM (PostgreSQL & MySQL). Mandates using db.batch() over HTTP drivers to prevent round-trip waterfalls, explicit db.transaction() with serializable/read-committed isolation, and strictly avoids unawaited query promises.

1 Rule in Stack

Drizzle