Flyway & Database Migration Complete Guide 2025: Schema Versioning, ORM Integration, Zero-Downtime Deployment

Developer A: "I added a phone column to users table"
Developer B: "Huh? It's not on my local"
Developer A: "I shared it on Slack..."
Developer C: "What about staging? Production?"
(3 hours later, production incident)

-- Friday 6 PM, directly on production...
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE orders MODIFY COLUMN status ENUM('pending','processing','shipped','delivered','cancelled');

-- Accidentally forgot WHERE clause...
UPDATE users SET role = 'admin';
-- Ctrl+C... too late

Git for Code                DB Migration for Schemas
------------------         ---------------------------
commit history       →     migration history
branch/merge         →     migration branching
code review (PR)     →     migration review
rollback (revert)    →     undo migration
CI/CD pipeline       →     migration in pipeline

What is your project's tech stack?
├── Java/Spring → Flyway or Liquibase
│   ├── Prefer simplicity → Flyway
│   └── Need rollback/diff → Liquibase
├── TypeScript/Node.js
│   ├── Using Prisma → Prisma Migrate
│   ├── Using TypeORM → TypeORM Migration
│   └── Lightweight → Knex.js
├── Python
│   ├── Django → Django Migrations
│   └── Flask/FastAPI → Alembic
├── Go → golang-migrate
└── Language-agnostic, raw SQL → Flyway CLI

flyway_migrations/
├── V1__Create_users_table.sql
├── V2__Create_orders_table.sql
├── V3__Add_email_column.sql
├── V4__Create_products_table.sql
├── R__Create_views.sql          # Repeatable
└── U3__Add_email_column.sql     # Undo (Teams)

# Homebrew
brew install flyway

# Verify version
flyway --version
# Flyway Community Edition 10.x.x

docker run --rm \
  -v $(pwd)/sql:/flyway/sql \
  -v $(pwd)/conf:/flyway/conf \
  flyway/flyway:10-alpine \
  migrate

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>10.8.1</version>
    <configuration>
        <url>jdbc:postgresql://localhost:5432/mydb</url>
        <user>postgres</user>
        <password>secret</password>
        <locations>
            <location>filesystem:src/main/resources/db/migration</location>
        </locations>
    </configuration>
</plugin>

plugins {
    id 'org.flywaydb.flyway' version '10.8.1'
}

flyway {
    url = 'jdbc:postgresql://localhost:5432/mydb'
    user = 'postgres'
    password = 'secret'
    locations = ['filesystem:src/main/resources/db/migration']
}

V{version}__{description}.sql       # Versioned Migration
R__{description}.sql                # Repeatable Migration
U{version}__{description}.sql       # Undo Migration (Teams)

-- V1__Create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

-- V2__Create_orders_table.sql
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- V3__Add_phone_to_users.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN profile_image_url TEXT;

-- R__Create_reporting_views.sql (re-executed every time the file changes)
CREATE OR REPLACE VIEW v_user_order_summary AS
SELECT
    u.id AS user_id,
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;

# Execute migrations
flyway migrate

# Check current status
flyway info

# Validate migration integrity
flyway validate

# Drop all objects (CAUTION: destroys everything)
flyway clean

# Apply Flyway to existing database (baseline)
flyway baseline

# Repair failed migrations
flyway repair

+-----------+---------+---------------------+------+---------------------+---------+----------+
| Category  | Version | Description         | Type | Installed On        | State   | Undoable |
+-----------+---------+---------------------+------+---------------------+---------+----------+
| Versioned | 1       | Create users table  | SQL  | 2025-03-20 10:15:30 | Success | No       |
| Versioned | 2       | Create orders table | SQL  | 2025-03-20 10:15:31 | Success | No       |
| Versioned | 3       | Add phone to users  | SQL  |                     | Pending | No       |
+-----------+---------+---------------------+------+---------------------+---------+----------+

# Database connection
flyway.url=jdbc:postgresql://localhost:5432/mydb
flyway.user=postgres
flyway.password=secret

# Migration locations
flyway.locations=filesystem:./sql,classpath:db/migration

# Schemas
flyway.schemas=public,app
flyway.defaultSchema=app

# History table
flyway.table=flyway_schema_history

# Encoding
flyway.encoding=UTF-8

# Placeholders
flyway.placeholders.env=production
flyway.placeholders.schema_name=app

# Validation
flyway.validateOnMigrate=true
flyway.validateMigrationNaming=true

# Miscellaneous
flyway.outOfOrder=false
flyway.baselineOnMigrate=false
flyway.cleanDisabled=true

[environments.default]
url = "jdbc:postgresql://localhost:5432/mydb"
user = "postgres"
password = "secret"

[flyway]
locations = ["filesystem:./sql"]
defaultSchema = "app"
table = "flyway_schema_history"
validateOnMigrate = true
cleanDisabled = true

[flyway.placeholders]
env = "production"
schema_name = "app"

-- V5__Create_audit_table.sql
CREATE TABLE ${schema_name}.audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    operation VARCHAR(10) NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON TABLE ${schema_name}.audit_log IS 'Audit log for ${env} environment';

# flyway-dev.conf
flyway.url=jdbc:postgresql://localhost:5432/mydb_dev
flyway.placeholders.env=development

# flyway-staging.conf
flyway.url=jdbc:postgresql://staging-db:5432/mydb
flyway.placeholders.env=staging

# flyway-prod.conf
flyway.url=jdbc:postgresql://prod-db:5432/mydb
flyway.placeholders.env=production
flyway.cleanDisabled=true

# Run per environment
flyway -configFiles=flyway-dev.conf migrate
flyway -configFiles=flyway-staging.conf migrate
flyway -configFiles=flyway-prod.conf migrate

Callback file names:
beforeMigrate.sql      - Before migrate
beforeEachMigrate.sql  - Before each migration
afterEachMigrate.sql   - After each migration
afterMigrate.sql       - After migrate completes
beforeClean.sql        - Before clean
afterClean.sql         - After clean completes
beforeValidate.sql     - Before validate
afterValidate.sql      - After validate completes

-- afterMigrate.sql
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_stats;
ANALYZE users;
ANALYZE orders;

INSERT INTO migration_audit_log (action, executed_at)
VALUES ('migration_completed', NOW());

// V6__Encrypt_user_emails.java
package db.migration;

import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import java.sql.*;

public class V6__Encrypt_user_emails extends BaseJavaMigration {

    @Override
    public void migrate(Context context) throws Exception {
        Connection conn = context.getConnection();

        String selectSql = "SELECT id, email FROM users WHERE encrypted_email IS NULL";
        String updateSql = "UPDATE users SET encrypted_email = ? WHERE id = ?";

        try (Statement select = conn.createStatement();
             PreparedStatement update = conn.prepareStatement(updateSql)) {

            ResultSet rs = select.executeQuery(selectSql);
            int batchSize = 0;

            while (rs.next()) {
                long id = rs.getLong("id");
                String email = rs.getString("email");
                String encrypted = encrypt(email);

                update.setString(1, encrypted);
                update.setLong(2, id);
                update.addBatch();

                if (++batchSize % 1000 == 0) {
                    update.executeBatch();
                }
            }

            if (batchSize % 1000 != 0) {
                update.executeBatch();
            }
        }
    }

    private String encrypt(String value) {
        // Actual encryption logic here
        return "ENC:" + value; // Example placeholder
    }
}

# application.yml
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    baseline-version: '0'
    validate-on-migrate: true
    out-of-order: false
    table: flyway_schema_history
    schemas:
      - public
    placeholders:
      env: ${SPRING_PROFILES_ACTIVE:dev}
    clean-disabled: true
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: postgres
    password: secret

// FlywayConfig.java
@Configuration
public class FlywayConfig {

    @Bean
    public FlywayMigrationStrategy flywayMigrationStrategy() {
        return flyway -> {
            MigrationInfo[] pending = flyway.info().pending();
            if (pending.length > 0) {
                System.out.println("Pending migrations: " + pending.length);
                for (MigrationInfo info : pending) {
                    System.out.println("  - " + info.getVersion()
                        + ": " + info.getDescription());
                }
            }
            flyway.migrate();
        };
    }
}

my-app/
├── src/main/resources/
│   └── db/
│       └── migration/
│           ├── V1__Create_schema.sql
│           ├── V2__Create_users.sql
│           ├── V3__Create_products.sql
│           ├── V4__Create_orders.sql
│           ├── V5__Add_indexes.sql
│           ├── V6__Create_audit_triggers.sql
│           └── R__Update_views.sql
├── flyway.toml
├── pom.xml
└── docker-compose.yml

# docker-compose.yml
version: '3.8'
services:
  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: secret
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

  flyway:
    image: flyway/flyway:10-alpine
    depends_on:
      - db
    volumes:
      - ./src/main/resources/db/migration:/flyway/sql
      - ./flyway.toml:/flyway/flyway.toml
    command: migrate
    environment:
      FLYWAY_URL: jdbc:postgresql://db:5432/myapp
      FLYWAY_USER: postgres
      FLYWAY_PASSWORD: secret

volumes:
  pgdata:

# db/changelog/db.changelog-master.yaml
databaseChangeLog:
  - changeSet:
      id: 1
      author: dev-team
      changes:
        - createTable:
            tableName: users
            columns:
              - column:
                  name: id
                  type: bigint
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: username
                  type: varchar(50)
                  constraints:
                    nullable: false
                    unique: true
              - column:
                  name: email
                  type: varchar(255)
                  constraints:
                    nullable: false
                    unique: true
              - column:
                  name: created_at
                  type: timestamp with time zone
                  defaultValueComputed: NOW()
      rollback:
        - dropTable:
            tableName: users

  - changeSet:
      id: 2
      author: dev-team
      changes:
        - addColumn:
            tableName: users
            columns:
              - column:
                  name: phone
                  type: varchar(20)
      rollback:
        - dropColumn:
            tableName: users
            columnName: phone

-- changeset dev-team:1
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- rollback DROP TABLE users;

-- changeset dev-team:2
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- rollback ALTER TABLE users DROP COLUMN phone;

# Run migrations
liquibase update

# Rollback last N changesets
liquibase rollbackCount 1

# Rollback to specific tag
liquibase rollback v1.0

# Diff current DB against changelog
liquibase diff

# Generate changelog from existing DB
liquibase generateChangeLog

# Check status
liquibase status

Choose Flyway when:
  - You want to write raw SQL directly
  - You prefer a simple workflow
  - You are in the Spring Boot ecosystem
  - Your team is comfortable with SQL

Choose Liquibase when:
  - Rollback support is mandatory
  - Multi-database vendor support is needed
  - You need to reverse-engineer existing DB into a changelog
  - You prefer XML/YAML declarative management
  - You need fine-grained context/conditional execution

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  phone     String?
  posts     Post[]
  orders    Order[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int      @map("author_id")
  createdAt DateTime @default(now()) @map("created_at")

  @@map("posts")
}

model Order {
  id         Int         @id @default(autoincrement())
  user       User        @relation(fields: [userId], references: [id])
  userId     Int         @map("user_id")
  totalAmount Decimal    @map("total_amount") @db.Decimal(10, 2)
  status     OrderStatus @default(PENDING)
  items      OrderItem[]
  createdAt  DateTime    @default(now()) @map("created_at")

  @@map("orders")
}

model OrderItem {
  id       Int   @id @default(autoincrement())
  order    Order @relation(fields: [orderId], references: [id])
  orderId  Int   @map("order_id")
  product  String
  quantity Int
  price    Decimal @db.Decimal(10, 2)

  @@map("order_items")
}

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

# Development migration (create + apply)
npx prisma migrate dev --name create_initial_schema

# Production migration (apply only)
npx prisma migrate deploy

# Reset database
npx prisma migrate reset

# Check migration status
npx prisma migrate status

# Diff schema vs database
npx prisma migrate diff \
  --from-schema-datamodel prisma/schema.prisma \
  --to-schema-datasource prisma/schema.prisma

-- prisma/migrations/20250325_create_initial_schema/migration.sql
CREATE TYPE "OrderStatus" AS ENUM ('PENDING', 'PROCESSING', 'SHIPPED', 'DELIVERED', 'CANCELLED');

CREATE TABLE "users" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT,
    "phone" TEXT,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "posts" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,
    "content" TEXT,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "author_id" INTEGER NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "posts_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "users_email_key" ON "users"("email");

ALTER TABLE "posts"
    ADD CONSTRAINT "posts_author_id_fkey"
    FOREIGN KEY ("author_id") REFERENCES "users"("id")
    ON DELETE RESTRICT ON UPDATE CASCADE;

1. Create temporary Shadow DB
2. Apply all existing migrations to Shadow DB
3. Generate new migration SQL
4. Verify new migration on Shadow DB
5. Delete Shadow DB

// src/entities/User.ts
import {
  Entity, PrimaryGeneratedColumn, Column,
  CreateDateColumn, UpdateDateColumn, OneToMany
} from 'typeorm';
import { Order } from './Order';

@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', length: 50, unique: true })
  username: string;

  @Column({ type: 'varchar', length: 255, unique: true })
  email: string;

  @Column({ type: 'varchar', length: 20, nullable: true })
  phone: string;

  @OneToMany(() => Order, order => order.user)
  orders: Order[];

  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;

  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt: Date;
}

# Auto-generate migration from entity changes
npx typeorm migration:generate -d src/data-source.ts src/migrations/AddPhoneToUser

# Create empty migration
npx typeorm migration:create src/migrations/SeedAdminUser

# Run migrations
npx typeorm migration:run -d src/data-source.ts

# Revert last migration
npx typeorm migration:revert -d src/data-source.ts

// src/migrations/1711234567890-AddPhoneToUser.ts
import { MigrationInterface, QueryRunner } from 'typeorm';

export class AddPhoneToUser1711234567890 implements MigrationInterface {
    name = 'AddPhoneToUser1711234567890';

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(
            `ALTER TABLE "users" ADD "phone" varchar(20)`
        );
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(
            `ALTER TABLE "users" DROP COLUMN "phone"`
        );
    }
}

# Install Alembic
pip install alembic sqlalchemy psycopg2-binary

# Initialize project
alembic init alembic

# alembic/env.py (key section)
from myapp.models import Base

target_metadata = Base.metadata

def run_migrations_online():
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
    )
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            compare_type=True,
            compare_server_default=True,
        )
        with context.begin_transaction():
            context.run_migrations()

# Auto-generate (detect model changes)
alembic revision --autogenerate -m "add phone to users"

# Manual creation
alembic revision -m "seed initial data"

# Upgrade to latest
alembic upgrade head

# Upgrade to specific version
alembic upgrade abc123

# Rollback one step
alembic downgrade -1

# Check current state
alembic current

# View migration history
alembic history

# alembic/versions/abc123_add_phone_to_users.py
"""add phone to users"""

from alembic import op
import sqlalchemy as sa

revision = 'abc123'
down_revision = 'prev456'
branch_labels = None
depends_on = None

def upgrade():
    op.add_column('users',
        sa.Column('phone', sa.String(20), nullable=True)
    )
    op.create_index('ix_users_phone', 'users', ['phone'])

def downgrade():
    op.drop_index('ix_users_phone', 'users')
    op.drop_column('users', 'phone')

# models.py
from django.db import models

class User(models.Model):
    username = models.CharField(max_length=50, unique=True)
    email = models.EmailField(unique=True)
    phone = models.CharField(max_length=20, blank=True, null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = 'users'

# Create migration
python manage.py makemigrations

# Apply migrations
python manage.py migrate

# Check migration status
python manage.py showmigrations

# Preview SQL
python manage.py sqlmigrate myapp 0002

# Squash migrations
python manage.py squashmigrations myapp 0001 0005

# migrations/0003_populate_default_roles.py
from django.db import migrations

def create_roles(apps, schema_editor):
    Role = apps.get_model('myapp', 'Role')
    roles = ['admin', 'editor', 'viewer']
    for role_name in roles:
        Role.objects.get_or_create(name=role_name)

def remove_roles(apps, schema_editor):
    Role = apps.get_model('myapp', 'Role')
    Role.objects.filter(name__in=['admin', 'editor', 'viewer']).delete()

class Migration(migrations.Migration):
    dependencies = [
        ('myapp', '0002_create_role_table'),
    ]

    operations = [
        migrations.RunPython(create_roles, remove_roles),
    ]

Phase 1 (Expand): Add new columns, write to both
Phase 2 (Migrate): Backfill data, switch reads
Phase 3 (Contract): Remove old column

-- Migration V10: Add new columns
ALTER TABLE users ADD COLUMN first_name VARCHAR(50);
ALTER TABLE users ADD COLUMN last_name VARCHAR(50);

-- Trigger for bidirectional sync
CREATE OR REPLACE FUNCTION sync_user_names()
RETURNS TRIGGER AS $func$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        IF NEW.first_name IS NULL AND NEW.username IS NOT NULL THEN
            NEW.first_name := split_part(NEW.username, ' ', 1);
            NEW.last_name := split_part(NEW.username, ' ', 2);
        ELSIF NEW.username IS NULL AND NEW.first_name IS NOT NULL THEN
            NEW.username := NEW.first_name || ' ' || COALESCE(NEW.last_name, '');
        END IF;
    END IF;
    RETURN NEW;
END;
$func$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_user_names
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION sync_user_names();

Deploy at this point: new code writes to first_name/last_name
Old code writes to username (trigger syncs)

-- Migration V11: Backfill existing data
UPDATE users
SET first_name = split_part(username, ' ', 1),
    last_name = split_part(username, ' ', 2)
WHERE first_name IS NULL;

-- Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN first_name SET NOT NULL;

-- Migration V12: Remove old column (after all code has switched)
DROP TRIGGER IF EXISTS trg_sync_user_names ON users;
DROP FUNCTION IF EXISTS sync_user_names();
ALTER TABLE users DROP COLUMN username;

Step 1: Add new column + trigger
Step 2: Copy data
Step 3: Switch code to new column
Step 4: Remove old column

-- Dangerous: full table lock
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Safe: PostgreSQL 12+ CHECK constraint
ALTER TABLE users ADD CONSTRAINT users_email_not_null
    CHECK (email IS NOT NULL) NOT VALID;

-- Validate in separate transaction (no AccessExclusiveLock)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- Then convert to NOT NULL constraint
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_email_not_null;

gh-ost \
  --host=db-primary \
  --database=myapp \
  --table=users \
  --alter="ADD COLUMN phone VARCHAR(20)" \
  --execute \
  --allow-on-master \
  --chunk-size=1000 \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=100

pt-online-schema-change \
  --alter "ADD COLUMN phone VARCHAR(20)" \
  --host=db-primary \
  --user=admin \
  --ask-pass \
  --chunk-size=500 \
  --max-lag=1s \
  --execute \
  D=myapp,t=users

-- Dangerous: table lock
CREATE INDEX idx_users_email ON users(email);

-- Safe: concurrent index creation (no lock)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Note: cannot be used inside a transaction
-- In Flyway, disable transaction for this migration

-- Using CONCURRENTLY in Flyway
-- V15__Add_index_concurrently.sql

-- Disable Flyway transaction (comment at top of file)
-- flyway:executeInTransaction=false

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email
ON users(email);

-- Dangerous: full table scan + lock
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id);

-- Safe: add NOT VALID then validate separately
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;

Forward-Only (Flyway default):
  V1 → V2 → V3 → V4
  If V4 has issues → create V5 to fix

Rollback Support (Liquibase, Flyway Teams):
  V1 → V2 → V3 → V4
  If V4 has issues → rollback to V3

-- U4__Remove_status_column.sql (Undo for V4)
ALTER TABLE orders DROP COLUMN IF EXISTS status;

# Execute undo
flyway undo

# Undo to specific version
flyway undo -target=2

-- V4__Add_status_to_orders.sql (original migration)
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- V5__Revert_status_from_orders.sql (compensating migration)
ALTER TABLE orders DROP COLUMN IF EXISTS status;

Do NOT rollback when:
  1. Column deletion caused data loss
     - DROP COLUMN cannot be recovered
     - Always verify backups before deletion

  2. Data transformation destroyed originals
     - UPDATE changed data format
     - Original data was not preserved

  3. Large-scale data migration
     - Millions of rows moved
     - Rollback time exceeds acceptable limits

  4. External system integration
     - API schema changes adopted by external consumers
     - Cannot rollback external systems

# .github/workflows/db-migration.yml
name: Database Migration

on:
  push:
    branches: [main]
    paths:
      - 'db/migration/**'
  pull_request:
    paths:
      - 'db/migration/**'

jobs:
  validate:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16-alpine
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: testpass
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v4

      - name: Run Flyway Validate
        run: |
          docker run --rm --network host \
            -v $(pwd)/db/migration:/flyway/sql \
            flyway/flyway:10-alpine \
            -url=jdbc:postgresql://localhost:5432/testdb \
            -user=postgres \
            -password=testpass \
            validate

      - name: Run Flyway Info
        run: |
          docker run --rm --network host \
            -v $(pwd)/db/migration:/flyway/sql \
            flyway/flyway:10-alpine \
            -url=jdbc:postgresql://localhost:5432/testdb \
            -user=postgres \
            -password=testpass \
            info

      - name: Test Migration
        run: |
          docker run --rm --network host \
            -v $(pwd)/db/migration:/flyway/sql \
            flyway/flyway:10-alpine \
            -url=jdbc:postgresql://localhost:5432/testdb \
            -user=postgres \
            -password=testpass \
            migrate

      - name: Verify Schema
        run: |
          PGPASSWORD=testpass psql -h localhost -U postgres -d testdb -c "
            SELECT table_name FROM information_schema.tables
            WHERE table_schema = 'public'
            ORDER BY table_name;
          "

  deploy-staging:
    needs: validate
    if: github.ref == 'refs/heads/main'
    runs-on: ubuntu-latest
    environment: staging
    steps:
      - uses: actions/checkout@v4

      - name: Deploy to Staging
        run: |
          docker run --rm \
            -v $(pwd)/db/migration:/flyway/sql \
            flyway/flyway:10-alpine \
            -url=$STAGING_DB_URL \
            -user=$STAGING_DB_USER \
            -password=$STAGING_DB_PASSWORD \
            migrate
        env:
          STAGING_DB_URL: ${{ secrets.STAGING_DB_URL }}
          STAGING_DB_USER: ${{ secrets.STAGING_DB_USER }}
          STAGING_DB_PASSWORD: ${{ secrets.STAGING_DB_PASSWORD }}

  deploy-production:
    needs: deploy-staging
    if: github.ref == 'refs/heads/main'
    runs-on: ubuntu-latest
    environment: production
    steps:
      - uses: actions/checkout@v4

      - name: Deploy to Production
        run: |
          docker run --rm \
            -v $(pwd)/db/migration:/flyway/sql \
            flyway/flyway:10-alpine \
            -url=$PROD_DB_URL \
            -user=$PROD_DB_USER \
            -password=$PROD_DB_PASSWORD \
            -validateOnMigrate=true \
            migrate
        env:
          PROD_DB_URL: ${{ secrets.PROD_DB_URL }}
          PROD_DB_USER: ${{ secrets.PROD_DB_USER }}
          PROD_DB_PASSWORD: ${{ secrets.PROD_DB_PASSWORD }}

Timeline →

1. [Blue active] DB v1, App v1
2. [Blue active] DB v1 → v2 migration (backward compatible)
3. [Blue active] Deploy App v2 to Green
4. [Blue → Green switch] Traffic cutover
5. [Green active] Decommission Blue

Key: DB migration MUST be backward compatible
     Old version code must work with new schema

# k8s/deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: myapp
spec:
  replicas: 3
  selector:
    matchLabels:
      app: myapp
  template:
    metadata:
      labels:
        app: myapp
    spec:
      initContainers:
        - name: flyway-migrate
          image: flyway/flyway:10-alpine
          args: ["migrate"]
          env:
            - name: FLYWAY_URL
              valueFrom:
                secretKeyRef:
                  name: db-secret
                  key: url
            - name: FLYWAY_USER
              valueFrom:
                secretKeyRef:
                  name: db-secret
                  key: username
            - name: FLYWAY_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: db-secret
                  key: password
          volumeMounts:
            - name: migrations
              mountPath: /flyway/sql
      containers:
        - name: myapp
          image: myapp:latest
          ports:
            - containerPort: 8080
      volumes:
        - name: migrations
          configMap:
            name: flyway-migrations

# k8s/migration-job.yaml
apiVersion: batch/v1
kind: Job
metadata:
  name: flyway-migrate-v10
  labels:
    app: flyway-migration
spec:
  backoffLimit: 3
  activeDeadlineSeconds: 300
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: flyway
          image: flyway/flyway:10-alpine
          args: ["migrate"]
          env:
            - name: FLYWAY_URL
              valueFrom:
                secretKeyRef:
                  name: db-secret
                  key: url
            - name: FLYWAY_USER
              valueFrom:
                secretKeyRef:
                  name: db-secret
                  key: username
            - name: FLYWAY_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: db-secret
                  key: password
            - name: FLYWAY_VALIDATE_ON_MIGRATE
              value: "true"
            - name: FLYWAY_CONNECT_RETRIES
              value: "5"
          volumeMounts:
            - name: migrations
              mountPath: /flyway/sql
      volumes:
        - name: migrations
          configMap:
            name: flyway-migrations

# helm/templates/migration-job.yaml
apiVersion: batch/v1
kind: Job
metadata:
  name: "{{ .Release.Name }}-flyway-migrate"
  annotations:
    "helm.sh/hook": pre-install,pre-upgrade
    "helm.sh/hook-weight": "-1"
    "helm.sh/hook-delete-policy": hook-succeeded
spec:
  backoffLimit: 3
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: flyway
          image: "flyway/flyway:{{ .Values.flyway.version }}"
          args: ["migrate"]
          env:
            - name: FLYWAY_URL
              value: "jdbc:postgresql://{{ .Values.db.host }}:{{ .Values.db.port }}/{{ .Values.db.name }}"
            - name: FLYWAY_USER
              valueFrom:
                secretKeyRef:
                  name: "{{ .Values.db.secretName }}"
                  key: username
            - name: FLYWAY_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: "{{ .Values.db.secretName }}"
                  key: password
          volumeMounts:
            - name: migrations
              mountPath: /flyway/sql
      volumes:
        - name: migrations
          configMap:
            name: "{{ .Release.Name }}-migrations"

Flyway built-in locking:
  - flyway_schema_history table has built-in lock mechanism
  - First instance runs the migration
  - Others wait and then skip

Additional safeguards:
  - K8s Job (single Pod) recommended
  - Init Container approach: each Pod attempts (Flyway handles locking)
  - DB Advisory Lock for extra safety

-- Custom locking mechanism (Advisory Lock)
-- beforeMigrate.sql
SELECT pg_advisory_lock(12345);

-- afterMigrate.sql
SELECT pg_advisory_unlock(12345);

# argocd/application.yaml
apiVersion: argoproj.io/v1alpha1
kind: Application
metadata:
  name: myapp
spec:
  project: default
  source:
    repoURL: https://github.com/myorg/myapp.git
    path: helm
    targetRevision: main
  destination:
    server: https://kubernetes.default.svc
    namespace: production
  syncPolicy:
    automated:
      prune: true
      selfHeal: true
    syncOptions:
      - CreateNamespace=true
    retry:
      limit: 3
      backoff:
        duration: 5s
        factor: 2
        maxDuration: 3m

ArgoCD Sync Flow:
1. Detect Git changes
2. Helm hook (pre-upgrade) → Execute Flyway Job
3. Flyway migration succeeds
4. Application Deployment updates
5. ArgoCD sync complete

Good:
  V1__Create_users_table.sql
  V2__Add_email_index.sql
  V3__Create_orders_table.sql

Bad:
  V1__Create_all_tables_and_indexes_and_data.sql

Never do this:
  - Edit an already-executed V1__Create_users.sql
  - Flyway detects changes via checksum → error

Correct approach:
  - Create a new migration file (V5__Fix_users_table.sql)

# Test with production-scale data
# Testing ALTER TABLE on a 10 million row table

# Generate test data
INSERT INTO users_test (username, email)
SELECT
    'user_' || generate_series,
    'user_' || generate_series || '@example.com'
FROM generate_series(1, 10000000);

# Measure migration execution time
\timing on
ALTER TABLE users_test ADD COLUMN phone VARCHAR(20);
# Time: 45123.456 ms (~45 seconds) - 45 seconds of locking in production!

V10__Add_status_column.sql       # Schema change
V11__Backfill_status_data.sql    # Data change
V12__Add_status_not_null.sql     # Add constraint

-- Bad: update everything at once
UPDATE users SET normalized_email = LOWER(email);

-- Good: batch processing
DO $block$
DECLARE
    batch_size INT := 10000;
    total_updated INT := 0;
    rows_affected 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
        );
        GET DIAGNOSTICS rows_affected = ROW_COUNT;
        total_updated := total_updated + rows_affected;
        RAISE NOTICE 'Updated % rows (total: %)', rows_affected, total_updated;
        EXIT WHEN rows_affected = 0;
        PERFORM pg_sleep(0.1);
    END LOOP;
END;
$block$;

Anti-pattern 1: Running DDL directly on production
  → Always go through migration files

Anti-pattern 2: Different migration files per environment
  → Same migrations in all environments (use placeholders)

Anti-pattern 3: Business logic in migrations
  → Migrations for schema/data changes only. Logic belongs in application

Anti-pattern 4: Ignoring backward compatibility
  → For zero-downtime deployment, old code must work with new schema

Anti-pattern 5: Deploying to production without testing
  → Always validate in CI with test database

Anti-pattern 6: Deploying without a rollback plan
  → Prepare rollback/compensating strategy for every migration

Anti-pattern 7: Not version-locking migration tools
  → Use lock files or explicit version pinning

Pre-Deployment Review:
  [ ] Migration SQL code reviewed
  [ ] Backward compatibility verified (works with old code)
  [ ] Tested on production-like data
  [ ] Execution time measured (watch out for large tables)
  [ ] Lock impact analyzed
  [ ] Rollback/compensating migration prepared

During Deployment:
  [ ] Database backup confirmed
  [ ] Monitoring dashboard checked (CPU, connections, slow queries)
  [ ] Execute within maintenance window (if needed)
  [ ] Run flyway validate first
  [ ] Verify flyway info after migration
  [ ] Application health check confirmed

Post-Deployment:
  [ ] Schema changes verified (tables, indexes, constraints)
  [ ] Application functionality confirmed
  [ ] Performance monitoring (query performance changes)
  [ ] Error logs reviewed
  [ ] Team notified (changes, impact)