Drizzle ORM Distributed AbortSignal & PostgreSQL Backend Cancellation Invariant

--- description: Drizzle ORM Distributed AbortSignal & PostgreSQL Backend Cancellation Invariant - Terminates active PostgreSQL backend queries when incoming client HTTP requests or distributed trace deadlines abort. Merely rejecting TypeScript promises leaves database workers executing orphan queries (ghost queries), causing thread pool starvation and unreleased transactional table locks. globs: src/db/**/*.{ts,tsx}, src/server/**/*.{ts,tsx}, src/api/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/drizzle-pg-abortsignal-cancellation-standard" --- # Drizzle ORM Distributed AbortSignal & PostgreSQL Backend Cancellation Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Detached Client Aborts Spawning Ghost PostgreSQL Transactions via Unbound Drizzle Promises ## System Constraints You are a Principal Backend Architect enforcing robust database query lifecycle management in Drizzle ORM with PostgreSQL. Mandate that all long-running or user-facing queries propagate AbortSignal down to the connection level. Prevent ghost queries by binding signals to explicit pg_cancel_backend invocations or driver-native cancellation hooks. ## Strict Guidelines - Engineers MUST ALWAYS bind incoming request AbortSignals to active database operations to prevent zombie queries. - Engineers MUST NOT rely on Promise.race([signalPromise, queryPromise]) without an underlying cancel hook, as this leaves the query executing on the PostgreSQL server. - Code MUST capture the database process ID (processID / backend PID) of the connection servicing the Drizzle query to facilitate out-of-band cancellation. - Services MUST execute pg_cancel_backend($1) using a separate management pool connection when an AbortSignal fires before the query completes. - Components NEVER hold open database transactions across unmonitored network boundaries without an active signal listener and connection timeout. ## Bad vs Good Implementations ### Propagating AbortSignal to PostgreSQL via pg_cancel_backend in Drizzle ORM #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: When an HTTP client aborts (e.g., closing a browser tab or hitting an API timeout), Node.js abandons the promise chain, but the PostgreSQL backend process continues executing the expensive join. This starves the connection pool of active slots and leaves exclusive locks open. ```typescript import { db } from '@/db'; import { users, orders } from '@/db/schema'; import { eq } from 'drizzle-orm'; export async function getAggregatedReport(req: Request): Promise<Response> { // ANTI-PATTERN: Client disconnect triggers AbortSignal on req, // but Drizzle query continues executing on the PostgreSQL backend. // Promise is abandoned in V8, but connection remains locked in PG pool. const data = await db .select() .from(users) .innerJoin(orders, eq(users.id, orders.userId)); return Response.json(data); } ``` #### Production Standard (Correct) > **Standard**: Acquires a dedicated client from the pool to expose its PostgreSQL processID. An AbortSignal listener executes pg_cancel_backend via an out-of-band pool query upon client cancellation, cleanly freeing PostgreSQL engine resources immediately. ```typescript import { pool } from '@/db/pool'; import { drizzle } from 'drizzle-orm/node-postgres'; import { users, orders } from '@/db/schema'; import { eq } from 'drizzle-orm'; import type { PoolClient } from 'pg'; export async function getAggregatedReport(req: Request): Promise<Response> { const signal = req.signal; if (signal.aborted) { return new Response('Request Aborted', { status: 499 }); } const client: PoolClient = await pool.connect(); const processId = (client as unknown as { processID?: number }).processID; let queryFinished = false; const abortHandler = async () => { if (queryFinished || !processId) return; try { // Out-of-band cancellation using another connection from the pool await pool.query('SELECT pg_cancel_backend($1)', [processId]); } catch (err) { console.error(`Failed to cancel PG backend ${processId}:`, err); } }; signal.addEventListener('abort', abortHandler, { once: true }); try { const scopedDb = drizzle(client); const data = await scopedDb .select() .from(users) .innerJoin(orders, eq(users.id, orders.userId)); queryFinished = true; return Response.json(data); } catch (err: unknown) { if (signal.aborted) { return new Response('Client Closed Request', { status: 499 }); } throw err; } finally { queryFinished = true; signal.removeEventListener('abort', abortHandler); client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Drizzle ORM Distributed AbortSignal & PostgreSQL Backend Cancellation Invariant

Drizzle ORM Distributed AbortSignal & PostgreSQL Backend Cancellation Invariant defines an industrial-grade behavioral standard for backend engineering. Terminates active PostgreSQL backend queries when incoming client HTTP requests or distributed trace deadlines abort. Merely rejecting TypeScript promises leaves database workers executing orphan queries (ghost queries), causing thread pool starvation and unreleased transactional table locks. 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: Detached Client Aborts Spawning Ghost PostgreSQL Transactions via Unbound Drizzle Promises 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	Detached Client Aborts Spawning Ghost PostgreSQL Transactions via Unbound Drizzle Promises	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 bind incoming request AbortSignals to active database operations to prevent zombie queries.

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.