database-migrations

name	database-migrations
description	Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Kysely, Django, TypeORM, golang-migrate).
origin	ECC

Database Migration Patterns

Safe, reversible database schema changes for production systems.

When to Activate

• Creating or altering database tables
• Adding/removing columns or indexes
• Running data migrations (backfill, transform)
• Planning zero-downtime schema changes
• Setting up migration tooling for a new project

Core Principles

1. Every change is a migration — never alter production databases manually
2. Migrations are forward-only in production — rollbacks use new forward migrations
3. Schema and data migrations are separate — never mix DDL and DML in one migration
4. Test migrations against production-sized data — a migration that works on 100 rows may lock on 10M
5. Migrations are immutable once deployed — never edit a migration that has run in production

Migration Safety Checklist

Before applying any migration:

• Migration has both UP and DOWN (or is explicitly marked irreversible)
• No full table locks on large tables (use concurrent operations)
• New columns have defaults or are nullable (never add NOT NULL without default)
• Indexes created concurrently (not inline with CREATE TABLE for existing tables)
• Data backfill is a separate migration from schema change
• Tested against a copy of production data
• Rollback plan documented

Rollback Safety Requirements (AcademyHub Production)

Goose-Specific Rollback Patterns

Mandatory DOWN Scripts: Every Goose migration MUST include a complete, tested DOWN section that reverses the UP changes exactly.

-- +goose Up  
-- +goose StatementBegin
CREATE TABLE new_users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin  
DROP TABLE IF EXISTS new_users;
-- +goose StatementEnd

Production Rollback Scenarios

Emergency Rollback: If a migration causes production issues:

1. Set ROLLBACK_PREVIOUS_MIGRATION=true in environment
2. Redeploy container - entrypoint.sh will automatically rollback last migration
3. Investigate and fix the problematic migration
4. Create new forward migration with corrections

Targeted Rollback: To rollback to specific version:

1. Set ROLLBACK_MIGRATION_TO=20240325000000 (target version timestamp)
2. Redeploy container - will rollback all migrations after target version
3. Verify application functionality at target state

Rollback Testing Requirements

Local Testing: Before production deployment:

# Apply migration
./scripts/migrate-local.sh

# Test application functionality
go run ./cmd/server/main.go

# Rollback migration  
./scripts/rollback-local.sh

# Verify rollback worked correctly
# Check database state matches pre-migration

Staging Verification: Every migration must pass:

• UP migration succeeds without errors
• Application works correctly with migrated schema
• DOWN migration succeeds without errors
• Application works correctly after rollback
• Data integrity maintained through migrate/rollback cycle

Idempotent Rollback Best Practices

Use Safe DROP Operations:

-- Always use IF EXISTS for DROP operations
DROP TABLE IF EXISTS temporary_table;
DROP INDEX IF EXISTS idx_old_column;
ALTER TABLE users DROP COLUMN IF EXISTS deprecated_field;

Handle Data Loss Carefully:

• For destructive operations (data deletion), consider making migration irreversible
• Document irreversible migrations clearly in migration comments
• Implement backup strategies before running irreversible migrations

Transaction Safety:

• Goose runs each migration statement in its own transaction by default
• Use -- +goose StatementBegin/End to group related statements
• Avoid long-running transactions that could block other operations

Emergency Procedures

Rollback Without Code Changes:

# Manual rollback using goose CLI
goose -dir migrations postgres "$DATABASE_URL" down 1

# Rollback to specific version
goose -dir migrations postgres "$DATABASE_URL" down-to 20240325000000

Verification After Rollback:

• Check migration table: SELECT * FROM goose_db_version ORDER BY id DESC LIMIT 5;
• Verify application can start and connect to database
• Run smoke tests to ensure critical functionality works
• Monitor logs for any schema-related errors

Rollback Documentation Template

Every migration file should include rollback documentation:

-- Migration: Add user preferences table
-- Purpose: Store user-specific settings and configurations  
-- Risk Level: LOW (non-destructive, adds new table)
-- Rollback Impact: None - simply drops unused table
-- Tested: Yes - local and staging environments
-- Author: [e-mail]
-- Date: 2024-03-26

PostgreSQL Patterns

Adding a Column Safely

-- GOOD: Nullable column, no lock
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- GOOD: Column with default (Postgres 11+ is instant, no rewrite)
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

-- BAD: NOT NULL without default on existing table (requires full rewrite)
ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
-- This locks the table and rewrites every row

Adding an Index Without Downtime

-- BAD: Blocks writes on large tables
CREATE INDEX idx_users_email ON users (email);

-- GOOD: Non-blocking, allows concurrent writes
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- Note: CONCURRENTLY cannot run inside a transaction block
-- Most migration tools need special handling for this

Renaming a Column (Zero-Downtime)

Never rename directly in production. Use the expand-contract pattern:

-- Step 1: Add new column (migration 001)
ALTER TABLE users ADD COLUMN display_name TEXT;

-- Step 2: Backfill data (migration 002, data migration)
UPDATE users SET display_name = username WHERE display_name IS NULL;

-- Step 3: Update application code to read/write both columns
-- Deploy application changes

-- Step 4: Stop writing to old column, drop it (migration 003)
ALTER TABLE users DROP COLUMN username;

Removing a Column Safely

-- Step 1: Remove all application references to the column
-- Step 2: Deploy application without the column reference
-- Step 3: Drop column in next migration
ALTER TABLE orders DROP COLUMN legacy_status;

-- For Django: use SeparateDatabaseAndState to remove from model
-- without generating DROP COLUMN (then drop in next migration)

Large Data Migrations

-- BAD: Updates all rows in one transaction (locks table)
UPDATE users SET normalized_email = LOWER(email);

-- GOOD: Batch update with progress
DO $$
DECLARE
  batch_size INT := 10000;
  rows_updated INT;
BEGIN
  LOOP
    UPDATE users
    SET normalized_email = LOWER(email)
    WHERE id IN (
      SELECT id FROM users
      WHERE normalized_email IS NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    RAISE NOTICE 'Updated % rows', rows_updated;
    EXIT WHEN rows_updated = 0;
    COMMIT;
  END LOOP;
END $$;

Prisma (TypeScript/Node.js)

Workflow

# Create migration from schema changes
npx prisma migrate dev --name add_user_avatar

# Apply pending migrations in production
npx prisma migrate deploy

# Reset database (dev only)
npx prisma migrate reset

# Generate client after schema changes
npx prisma generate

Schema Example

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  avatarUrl String?  @map("avatar_url")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  orders    Order[]

  @@map("users")
  @@index([email])
}

Custom SQL Migration

For operations Prisma cannot express (concurrent indexes, data backfills):

# Create empty migration, then edit the SQL manually
npx prisma migrate dev --create-only --name add_email_index

-- migrations/20240115_add_email_index/migration.sql
-- Prisma cannot generate CONCURRENTLY, so we write it manually
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email);

Drizzle (TypeScript/Node.js)

Workflow

# Generate migration from schema changes
npx drizzle-kit generate

# Apply migrations
npx drizzle-kit migrate

# Push schema directly (dev only, no migration file)
npx drizzle-kit push

Schema Example

import { pgTable, text, timestamp, uuid, boolean } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull().unique(),
  name: text("name"),
  isActive: boolean("is_active").notNull().default(true),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

Kysely (TypeScript/Node.js)

Workflow (kysely-ctl)

# Initialize config file (kysely.config.ts)
kysely init

# Create a new migration file
kysely migrate make add_user_avatar

# Apply all pending migrations
kysely migrate latest

# Rollback last migration
kysely migrate down

# Show migration status
kysely migrate list

Migration File

// migrations/2024_01_15_001_create_user_profile.ts
import { type Kysely, sql } from 'kysely'

// IMPORTANT: Always use Kysely<any>, not your typed DB interface.
// Migrations are frozen in time and must not depend on current schema types.
export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('user_profile')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('email', 'varchar(255)', (col) => col.notNull().unique())
    .addColumn('avatar_url', 'text')
    .addColumn('created_at', 'timestamp', (col) =>
      col.defaultTo(sql`now()`).notNull()
    )
    .execute()

  await db.schema
    .createIndex('idx_user_profile_avatar')
    .on('user_profile')
    .column('avatar_url')
    .execute()
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable('user_profile').execute()
}

Programmatic Migrator

import { Migrator, FileMigrationProvider } from 'kysely'
import { promises as fs } from 'fs'
import * as path from 'path'
// ESM only — CJS can use __dirname directly
import { fileURLToPath } from 'url'
const migrationFolder = path.join(
  path.dirname(fileURLToPath(import.meta.url)),
  './migrations',
)

// `db` is your Kysely<any> database instance
const migrator = new Migrator({
  db,
  provider: new FileMigrationProvider({
    fs,
    path,
    migrationFolder,
  }),
  // WARNING: Only enable in development. Disables timestamp-ordering
  // validation, which can cause schema drift between environments.
  // allowUnorderedMigrations: true,
})

const { error, results } = await migrator.migrateToLatest()

results?.forEach((it) => {
  if (it.status === 'Success') {
    console.log(`migration "${it.migrationName}" executed successfully`)
  } else if (it.status === 'Error') {
    console.error(`failed to execute migration "${it.migrationName}"`)
  }
})

if (error) {
  console.error('migration failed', error)
  process.exit(1)
}

Django (Python)

Workflow

# Generate migration from model changes
python manage.py makemigrations

# Apply migrations
python manage.py migrate

# Show migration status
python manage.py showmigrations

# Generate empty migration for custom SQL
python manage.py makemigrations --empty app_name -n description

Data Migration

from django.db import migrations

def backfill_display_names(apps, schema_editor):
    User = apps.get_model("accounts", "User")
    batch_size = 5000
    users = User.objects.filter(display_name="")
    while users.exists():
        batch = list(users[:batch_size])
        for user in batch:
            user.display_name = user.username
        User.objects.bulk_update(batch, ["display_name"], batch_size=batch_size)

def reverse_backfill(apps, schema_editor):
    pass  # Data migration, no reverse needed

class Migration(migrations.Migration):
    dependencies = [("accounts", "0015_add_display_name")]

    operations = [
        migrations.RunPython(backfill_display_names, reverse_backfill),
    ]

SeparateDatabaseAndState

Remove a column from the Django model without dropping it from the database immediately:

class Migration(migrations.Migration):
    operations = [
        migrations.SeparateDatabaseAndState(
            state_operations=[
                migrations.RemoveField(model_name="user", name="legacy_field"),
            ],
            database_operations=[],  # Don't touch the DB yet
        ),
    ]

golang-migrate (Go)

Workflow

# Create migration pair
migrate create -ext sql -dir migrations -seq add_user_avatar

# Apply all pending migrations
migrate -path migrations -database "$DATABASE_URL" up

# Rollback last migration
migrate -path migrations -database "$DATABASE_URL" down 1

# Force version (fix dirty state)
migrate -path migrations -database "$DATABASE_URL" force VERSION

Migration Files

-- migrations/000003_add_user_avatar.up.sql
ALTER TABLE users ADD COLUMN avatar_url TEXT;
CREATE INDEX CONCURRENTLY idx_users_avatar ON users (avatar_url) WHERE avatar_url IS NOT NULL;

-- migrations/000003_add_user_avatar.down.sql
DROP INDEX IF EXISTS idx_users_avatar;
ALTER TABLE users DROP COLUMN IF EXISTS avatar_url;

Goose SQL Migrations (AcademyHub Backend Standard)

Workflow Priority

Source of Truth: Goose SQL migrations are the primary and production-safe source of truth for schema changes in AcademyHub. GORM AutoMigrate is development-only and must never be used in staging or production environments.

Migration File Format

All migrations must use single-file format with proper Goose directives:

YYYYMMDDHHMMSS_description.sql

File structure:

-- +goose Up
-- +goose StatementBegin
-- SQL UP statements here
-- +goose StatementEnd

-- +goose Down  
-- +goose StatementBegin
-- SQL DOWN statements here  
-- +goose StatementEnd

Do NOT use separate .up.sql / .down.sql files in AcademyHub projects.

Environment Configuration

Variable	Purpose	Default
RUN_GOOSE_MIGRATION	Run goose on container start	true (staging/prod)
RUN_AUTO_MIGRATION	Run GORM AutoMigrate	false
ENV	Environment label	production
ROLLBACK_PREVIOUS_MIGRATION	Rollback 1 migration on start	false
ROLLBACK_MIGRATION_TO	Rollback to specific version	empty

Production Deployment Safety

Dokploy deployment automatically handles migrations via entrypoint.sh:

1. Backup (production only)
2. Rollback (if rollback env vars set)
3. Goose migrations (if RUN_GOOSE_MIGRATION=true)
4. AutoMigrate (only if dev or explicitly enabled)
5. Start server

Migration Checklist for AcademyHub

• SQL migration created in single-file Goose format
• UP and DOWN sections present and tested
• Types compatible with GORM models
• Idempotent where possible (IF NOT EXISTS, guard clauses)
• Tested in staging environment before production
• Rollback plan verified and documented
• Follows expand → migrate → contract pattern for breaking changes

Common Tools

Migration Status:

# Check current migration status
goose -dir migrations postgres "connection_string" status

Local Development:

# Run migrations locally (uses ./scripts/migrate-local.sh)
./scripts/migrate-local.sh

# Rollback last migration (uses ./scripts/rollback-local.sh)  
./scripts/rollback-local.sh

Production Safety Rules:

• Never use AutoMigrate in production
• Always test migrations in staging first
• Maintain clear rollback paths with tested DOWN scripts
• Use expand/migrate/contract for breaking changes
• Ensure migration compatibility with existing GORM models

Zero-Downtime Migration Strategy

For critical production changes in AcademyHub applications, follow the expand → migrate → contract pattern rigorously:

Phase 1: EXPAND
  - Add new column/table (nullable or with default)
  - Keep old fields intact (no breaking changes)
  - Deploy: app writes to BOTH old and new fields
  - Backfill existing data in separate migration

Phase 2: MIGRATE  
  - Deploy: app reads from NEW field, writes to BOTH
  - Verify data consistency between old and new
  - Monitor for any discrepancies or errors
  - Ensure all services can handle dual-field state

Phase 3: CONTRACT
  - Deploy: app only uses NEW field
  - Remove all references to old field from codebase
  - Drop old column/table in separate migration
  - Update GORM models to reflect final state

Timeline Example (AcademyHub Production)

Day 1: Migration 20240326000001 adds new_status column (nullable)
Day 1: Deploy app v2 — writes to both status and new_status
Day 2: Migration 20240327000001 backfills existing rows
Day 3: Deploy app v3 — reads from new_status only, still writes to both
Day 5: Deploy app v4 — removes all status field references, writes only to new_status  
Day 7: Migration 20240402000001 drops old status column

Idempotent Migration Implementation (Goose Format)

Always use IF NOT EXISTS or guard clauses to ensure migrations are safe to run multiple times:

-- +goose Up
-- +goose StatementBegin
ALTER TABLE students ADD COLUMN IF NOT EXISTS parent_id BIGINT NULL;
CREATE INDEX IF NOT EXISTS idx_students_parent_id ON students(parent_id);
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints
    WHERE constraint_name = 'fk_students_parent'
      AND table_name = 'students'
  ) THEN
    ALTER TABLE students ADD CONSTRAINT fk_students_parent
      FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE SET NULL;
  END IF;
END $$;
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
ALTER TABLE students DROP CONSTRAINT IF EXISTS fk_students_parent;
DROP INDEX IF EXISTS idx_students_parent_id;
ALTER TABLE students DROP COLUMN IF EXISTS parent_id;
-- +goose StatementEnd

Critical Safety Rules for Expand-Migrate-Contract

1. Never skip phases - Each phase requires separate deployment cycles
2. Test each phase - Verify dual-write/read functionality before proceeding
3. Monitor data consistency - Use observability tools during migrate phase
4. Plan rollback for each phase - Each migration must have working DOWN script
5. Coordinate with frontend/mobile teams - Breaking changes affect all clients
6. Update API contracts gradually - Use versioning or backward-compatible changes

Anti-Patterns

Anti-Pattern	Why It Fails	Better Approach
Manual SQL in production	No audit trail, unrepeatable	Always use migration files
Editing deployed migrations	Causes drift between environments	Create new migration instead
NOT NULL without default	Locks table, rewrites all rows	Add nullable, backfill, then add constraint
Inline index on large table	Blocks writes during build	CREATE INDEX CONCURRENTLY
Schema + data in one migration	Hard to rollback, long transactions	Separate migrations
Dropping column before removing code	Application errors on missing column	Remove code first, drop column next deploy