Drizzle ORM Zero-Downtime Safe DDL Migrations & Advisory Locking Standard

--- description: Drizzle ORM Zero-Downtime Safe DDL Migrations & Advisory Locking Standard - Enforces non-blocking zero-downtime database migrations with Drizzle ORM through strict PostgreSQL advisory locking, bounded lock timeouts, and multi-phase expand-contract schema evolution. Eliminates race conditions across auto-scaling replicas and prevents cascading connection pool exhaustion caused by unconstrained ACCESS EXCLUSIVE locks. globs: src/db/migrate.{ts,js}, src/db/schema/**/*.{ts,js}, drizzle.config.ts, scripts/migrate.{ts,js} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/drizzle-orm-zero-downtime-migration-advisory-locking-standard" --- # Drizzle ORM Zero-Downtime Safe DDL Migrations & Advisory Locking Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Running concurrent unprotected Drizzle migrations on app boot without advisory locks or bounded lock timeouts. ## System Constraints You are a Principal Software Architect enforcing zero-downtime database evolution and concurrency guardrails for Drizzle ORM. Mandate transactional PostgreSQL advisory locks, strict statement and lock timeouts, and decoupled execution architectures to eliminate deployment deadlocks and catastrophic table locks. ## Strict Guidelines - Engineers MUST ALWAYS acquire an explicit PostgreSQL advisory lock (e.g., via `pg_advisory_xact_lock`) before executing Drizzle DDL operations across distributed environments. - Engineers MUST ALWAYS enforce bounded session-level timeouts (`lock_timeout = '3s'` and `statement_timeout = '30s'`) prior to running migrations to prevent queued DDL statements from blocking application queries. - Engineers MUST NOT execute `migrate()` or DDL queries within application runtime initialization loops across horizontally scaled web containers. - Engineers MUST execute all schema mutations via an isolated, single-runner orchestrator (such as a dedicated CI/CD pipeline step or Kubernetes Job). - Engineers MUST execute column renames, drops, and constraints using expand-contract patterns across multiple deployment phases rather than single-step destructive alterations. - Engineers MUST specify non-blocking index creation (`CREATE INDEX CONCURRENTLY`) outside transaction blocks when adding indexes to high-traffic tables. ## Bad vs Good Implementations ### Unsafe boot-time migration without advisory locking or lock timeouts #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Running migrate() on server boot across multiple auto-scaling containers causes race conditions and concurrent schema modifications. Without setting lock_timeout, any pending DDL statement acquires an ACCESS EXCLUSIVE lock queue, blocking all subsequent reads and writes and exhausting connection pools. ```typescript import { drizzle } from 'drizzle-orm/node-postgres'; import { migrate } from 'drizzle-orm/node-postgres/migrator'; import { Pool } from 'pg'; import express from 'express'; const pool = new Pool({ connectionString: process.env.DATABASE_URL }); const db = drizzle(pool); const app = express(); // ANTI-PATTERN: Migrations run inside every pod startup simultaneously, // with no distributed locks and no lock timeouts, triggering ACCESS EXCLUSIVE blocking. async function startServer() { await migrate(db, { migrationsFolder: './drizzle' }); app.listen(3000, () => console.log('Server running')); } startServer(); ``` #### Production Standard (Correct) > **Standard**: The migration runs in an isolated single-client process. It sets a 3-second `lock_timeout` to prevent blocking the query queue if a lock cannot be immediately acquired, and leverages `pg_advisory_xact_lock` to ensure strictly serialized execution across distributed runners. ```typescript import { drizzle } from 'drizzle-orm/node-postgres'; import { migrate } from 'drizzle-orm/node-postgres/migrator'; import { sql } from 'drizzle-orm'; import { Pool } from 'pg'; const MIGRATION_LOCK_ID = 8529340192837482n; // Unique 64-bit advisory lock key export async function runZeroDowntimeMigration(): Promise<void> { const pool = new Pool({ connectionString: process.env.MIGRATION_DATABASE_URL, max: 1, connectionTimeoutMillis: 5000, }); const client = await pool.connect(); const db = drizzle(client); try { await client.query('BEGIN'); // Invariant 1: Constrain lock wait time to 3 seconds to avoid queuing behind long transactions await client.query("SET LOCAL lock_timeout = '3s'"); await client.query("SET LOCAL statement_timeout = '30s'"); // Invariant 2: Distributed mutual exclusion via transaction-scoped advisory lock await client.query('SELECT pg_advisory_xact_lock($1)', [MIGRATION_LOCK_ID.toString()]); // Invariant 3: Execute versioned migrations under locked transaction context await migrate(db, { migrationsFolder: './drizzle' }); await client.query('COMMIT'); console.log('Migration successfully applied with advisory lock.'); } catch (error) { await client.query('ROLLBACK'); console.error('Migration aborted to protect production workloads:', error); throw error; } finally { client.release(); await pool.end(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Drizzle ORM Zero-Downtime Safe DDL Migrations & Advisory Locking Standard

Drizzle ORM Zero-Downtime Safe DDL Migrations & Advisory Locking Standard defines an industrial-grade behavioral standard for backend engineering. Enforces non-blocking zero-downtime database migrations with Drizzle ORM through strict PostgreSQL advisory locking, bounded lock timeouts, and multi-phase expand-contract schema evolution. Eliminates race conditions across auto-scaling replicas and prevents cascading connection pool exhaustion caused by unconstrained ACCESS EXCLUSIVE 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: Running concurrent unprotected Drizzle migrations on app boot without advisory locks or bounded lock timeouts. 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	Running concurrent unprotected Drizzle migrations on app boot without advisory locks or bounded lock timeouts.	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 acquire an explicit PostgreSQL advisory lock (e.g., via `pg_advisory_xact_lock`) before executing Drizzle DDL operations across distributed environments.

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.