Drizzle ORM Zero-Downtime Concurrent Migration & Shadow View Swap Invariant

--- description: Drizzle ORM Zero-Downtime Concurrent Migration & Shadow View Swap Invariant - Enforces an expand-and-contract dual-column shadowing protocol for all schema evolutions and type conversions executed with Drizzle ORM. Eliminates ACCESS EXCLUSIVE write-lock contention under heavy concurrent workloads by forbidding monolithic in-place column alterations and blocking DDLs. Mandates phased backward-compatible migrations guarded by strict lock timeouts, asynchronous backfills, and atomic view/column aliasing swaps. globs: src/db/**/*.{ts,tsx}, drizzle/**/*.{ts,sql}, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/drizzle-zero-downtime-concurrent-migration-invariant" --- # Drizzle ORM Zero-Downtime Concurrent Migration & Shadow View Swap Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: In-Place Synchronous Column Alteration with Blocking Table Locks ## System Constraints You are a Principal Software Architect enforcing zero-downtime database evolution standards for Drizzle ORM. You must strictly reject all in-place column mutations, blocking type conversions, and synchronous monolithic schema rewrites. Mandate the expand-and-contract pattern: introduce nullable shadow columns, enforce application dual-writes or shadow view aliasing, conduct throttled chunked backfills, swap references atomically, and drop deprecated structures only after full fleet deployment. ## Strict Guidelines - Engineers MUST ALWAYS wrap destructive DDL statements (column type changes, renames, non-null additions) in multi-phase expand-contract migrations using shadow columns. - Engineers MUST ALWAYS prepend DDL migration scripts with aggressive lock timeout guards (`SET LOCAL lock_timeout = '2s';`) to prevent cascading queue exhaustion under concurrent query load. - Engineers MUST NOT generate or apply direct `ALTER TABLE ... ALTER COLUMN TYPE` or non-nullable column additions without explicit pre-existing default values on active tables. - Services MUST execute data backfills asynchronously in deterministic chunked transaction batches with backoff pauses to prevent write-ahead log (WAL) and replication saturation. - Components NEVER drop or rename legacy columns during the same deployment cycle as the shadow column introduction. ## Bad vs Good Implementations ### Migrating Column Types from Integer to Bigint under Heavy Concurrency #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Directly altering column data types triggers an ACCESS EXCLUSIVE table lock in PostgreSQL. On tables with active concurrent transactions, incoming read/write queries queue behind the DDL request, exhausting pool connections and causing operational outages. ```typescript import { pgTable, integer, text } from 'drizzle-orm/pg-core'; // ANTI-PATTERN: Direct type mutation in schema file. // Generating and applying migrations directly from this schema causes Drizzle Kit // to issue `ALTER TABLE orders ALTER COLUMN amount TYPE bigint;` // This acquires an ACCESS EXCLUSIVE lock and rewrites the entire table on disk, // stalling all incoming write transactions and triggering connection pool starvation. export const orders = pgTable('orders', { id: text('id').primaryKey(), amount: integer('amount').notNull(), // Changed from integer to bigint in-place }); ``` #### Production Standard (Correct) > **Standard**: Splitting the migration into expand, dual-write/backfill, and swap phases ensures zero-downtime. The shadow column is added as a non-blocking operation, backfilling occurs in throttled batches without table-level write locks, and final deprecation occurs in an isolated subsequent deployment. ```typescript import { pgTable, text, bigint, integer } from 'drizzle-orm/pg-core'; import { sql } from 'drizzle-orm'; // PHASE 1 (EXPAND): Introduce nullable shadow column alongside legacy column. export const orders = pgTable('orders', { id: text('id').primaryKey(), // Legacy column maintained for backward compatibility amount: integer('amount').notNull(), // Shadow column introduced concurrently without blocking locks amountShadow: bigint('amount_shadow', { mode: 'bigint' }), }); // Associated migration SQL executed with strict lock timeouts: // SET LOCAL lock_timeout = '2s'; // ALTER TABLE "orders" ADD COLUMN IF NOT EXISTS "amount_shadow" bigint; // PHASE 2 (DUAL-WRITE & BACKFILL CHUNKER): export async function backfillOrderAmounts(db: any, batchSize = 1000) { let hasMore = true; let lastId = ''; while (hasMore) { const updated = await db.execute(sql` WITH batch AS ( SELECT id FROM orders WHERE amount_shadow IS NULL AND id > ${lastId} ORDER BY id ASC LIMIT ${batchSize} ) UPDATE orders SET amount_shadow = amount FROM batch WHERE orders.id = batch.id RETURNING orders.id; `); if (updated.rows.length === 0) { hasMore = false; } else { lastId = updated.rows[updated.rows.length - 1].id as string; // Throttled pause to clear WAL buffers and yield read locks await new Promise((resolve) => setTimeout(resolve, 50)); } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Drizzle ORM Zero-Downtime Concurrent Migration & Shadow View Swap Invariant

Drizzle ORM Zero-Downtime Concurrent Migration & Shadow View Swap Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces an expand-and-contract dual-column shadowing protocol for all schema evolutions and type conversions executed with Drizzle ORM. Eliminates ACCESS EXCLUSIVE write-lock contention under heavy concurrent workloads by forbidding monolithic in-place column alterations and blocking DDLs. Mandates phased backward-compatible migrations guarded by strict lock timeouts, asynchronous backfills, and atomic view/column aliasing swaps. 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: In-Place Synchronous Column Alteration with Blocking Table Locks 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	In-Place Synchronous Column Alteration with Blocking Table Locks	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 wrap destructive DDL statements (column type changes, renames, non-null additions) in multi-phase expand-contract migrations using shadow columns.

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.