Safe Migration Patterns

Safe Migration Patterns

The engine automatically applies 6+ safe patterns for common dangerous operations.

---

1. NOT NULL Constraint with Data

Problem

Adding NOT NULL to a column with existing NULL values fails:

ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- ERROR: column "email" contains null values

Solution: 3-Step Pattern

-- Step 1: Add a safe default value
ALTER TABLE users ALTER COLUMN email SET DEFAULT '';

-- Step 2: Update existing NULL values
UPDATE users SET email = '' WHERE email IS NULL;

-- Step 3: Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Step 4: Remove default if not needed
ALTER TABLE users ALTER COLUMN email DROP DEFAULT;

Engine Behavior

// Detected automatically
const diff = engine.diff(desired, current);
// Generates 4-step safe migration

// Or use backfill options
const result = await engine.migrate(pool, desired, {
  notNullBackfill: true,
  backfillValue: ''  // or use a function
});

---

2. Foreign Key Constraint

Problem

Adding FK constraint locks both tables:

ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id);
-- Locks both orders and users tables
-- Scans all rows - slow on large tables

Solution: NOT VALID + VALIDATE

-- Step 1: Add constraint without validation
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;
-- Does not scan existing data, minimal lock

-- Step 2: Validate in separate transaction
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;
-- Scans data but holds SHARE UPDATE EXCLUSIVE lock only
-- Allows concurrent reads/writes

Engine Behavior

const result = await engine.migrate(pool, desired, {
  fkNotValid: true,  // Add FK without validation first
  validateFk: true   // Validate in separate step
});

---

3. Index Creation

Problem

Creating index locks table against writes:

CREATE INDEX idx_users_email ON users(email);
-- Blocks all writes to users table
-- Can take minutes on large tables

Solution: CONCURRENTLY

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Does not block writes
-- Takes longer but allows normal operations

Engine Behavior

const result = await engine.migrate(pool, desired, {
  concurrentIndex: true  // Always use CONCURRENTLY
});

---

4. Column Type Change with USING

Problem

Changing column type can lose data:

ALTER TABLE users ALTER COLUMN age TYPE VARCHAR(10);
-- ERROR: cannot cast type integer to character varying

Solution: USING Clause

-- Safe: Explicit cast
ALTER TABLE users ALTER COLUMN age TYPE VARCHAR(10) USING age::VARCHAR(10);

-- Or for complex conversions
ALTER TABLE users ALTER COLUMN age TYPE VARCHAR(10)
  USING CASE WHEN age IS NULL THEN 'N/A' ELSE age::VARCHAR(10) END;

Engine Behavior

The engine:

1. Detects if cast is possible
2. Generates USING clause automatically
3. If cast is unsafe, blocks with warning

const plan = engine.plan(diff);
// If type change is unsafe:
plan.blocked === true;
plan.blocker.reason === 'UNSAFE_TYPE_CAST';

---

5. UNIQUE Constraint via Index

Problem

Adding UNIQUE constraint requires table scan and lock:

ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);
-- Locks table during full scan

Solution: Create Index First

-- Step 1: Create unique index concurrently
CREATE UNIQUE INDEX CONCURRENTLY uk_email_idx ON users(email);

-- Step 2: Convert to constraint using existing index
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE USING INDEX uk_email_idx;
-- Minimal lock, uses existing index

Engine Behavior

const result = await engine.migrate(pool, desired, {
  uniqueViaConcurrentIndex: true  // Use index-first pattern
});

---

6. CHECK Constraint NOT VALID

Problem

Adding CHECK constraint validates all existing rows:

ALTER TABLE users ADD CONSTRAINT ck_age CHECK (age >= 18);
-- Validates all rows - slow on large tables
-- Holds ACCESS EXCLUSIVE lock

Solution: NOT VALID + VALIDATE

-- Step 1: Add constraint without validation
ALTER TABLE users ADD CONSTRAINT ck_age CHECK (age >= 18) NOT VALID;
-- Minimal lock, no row scan

-- Step 2: Validate separately
ALTER TABLE users VALIDATE CONSTRAINT ck_age;
-- Scans rows but only SHARE UPDATE EXCLUSIVE lock
-- Allows concurrent operations

Engine Behavior

const result = await engine.migrate(pool, desired, {
  checkNotValid: true,  // Add without validation first
  validateCheck: true   // Validate in separate step
});

---

7. ENUM Value Addition

Problem

Adding ENUM value requires special handling:

ALTER TYPE status ADD VALUE 'archived';
-- Cannot be executed in a transaction block (PG < 14)
-- Cannot add value after existing values without reordering

Solution: Pre-Transaction DDL

// PG >= 14: Works in transaction
// PG < 14: Execute before main transaction
const result = await engine.migrate(pool, desired, {
  handleEnumAddValue: true
});

Engine automatically:

1. Detects PG version
2. For PG < 14: Executes ALTER TYPE ADD VALUE in separate transaction
3. Continues with remaining DDL

---

8. Large Table Alterations

Problem

Altering large tables can timeout or lock too long.

Solution: Batch Processing

// Backfill in batches
const result = await engine.migrate(pool, desired, {
  backfillBatchSize: 10000,
  backfillSleep: 100  // ms between batches
});

---

9. Drop Operations

Problem

Dropping objects can cascade unexpectedly.

Solution: Explicit Dependency Check

Engine behavior:

1. Checks all dependents before DROP
2. Fails if dependents exist (unless CASCADE explicitly specified)
3. Generates DROP statements in reverse dependency order

const plan = engine.plan(diff);

// If dropping a table with dependents:
plan.warnings.find(w => w.code === 'HAS_DEPENDENTS');

// Force cascade (dangerous):
const result = await engine.migrate(pool, desired, {
  force: true,
  cascade: true
});

---

Summary: Automatic Safe Patterns

Operation	Unsafe	Safe Pattern	Automatic
ADD NOT NULL	Direct ALTER	3-step with default	✅ Yes
ADD FK	Direct ADD	NOT VALID + VALIDATE	✅ Yes
CREATE INDEX	Standard	CONCURRENTLY	✅ Yes (opt-in)
ALTER TYPE	Direct CAST	USING clause	✅ Yes
ADD UNIQUE	Direct constraint	Index first, then constraint	✅ Yes
ADD CHECK	Direct ADD	NOT VALID + VALIDATE	✅ Yes
ENUM ADD VALUE	In transaction	Pre-transaction	✅ Yes
DROP	CASCADE	Dependency check	✅ Yes
Large table	Single operation	Batched	✅ Opt-in

---

See Also

• Risk-Assessment — Risk levels for all operations
• API-Reference — Configuration options