Flyway / Migrations — Practical

Flyway / Migrations — Practical patterns

Project structure

db/
  migrations/
    V1__init.sql
    V2__users_table.sql
    V3__orders_table.sql
    V4__add_user_email_index.sql
    R__view_user_summary.sql
  conf/
    flyway.dev.conf
    flyway.prod.conf

flyway.conf

flyway.url=jdbc:postgresql://db:5432/app
flyway.user=app
flyway.password=${DB_PASSWORD}
flyway.locations=filesystem:db/migrations
flyway.cleanDisabled=true
flyway.validateOnMigrate=true
flyway.outOfOrder=false
flyway.baselineOnMigrate=true
flyway.baselineVersion=1

Online migration patterns

Add nullable, backfill, set NOT NULL

-- V42__add_user_status_step1.sql
ALTER TABLE users ADD COLUMN status TEXT;

-- backfill via app or Job (batched)
UPDATE users SET status = 'active' WHERE status IS NULL AND id BETWEEN 0 AND 100000;

-- V44__user_status_not_null.sql
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

Index without locking

-- V45__email_index.sql
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

CONCURRENTLY can’t run in transaction → put in own migration. Configure flyway: flyway.mixed=true if needed.

Rename column (multi-release)

-- V50: add and copy
ALTER TABLE users ADD COLUMN full_name TEXT;
-- backfill in code

-- V60 (next release): drop old after code is updated
ALTER TABLE users DROP COLUMN name;

Repeatable migration

-- R__user_summary_view.sql
DROP VIEW IF EXISTS user_summary;
CREATE VIEW user_summary AS
SELECT u.id, count(o.id) AS order_count
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

Migration as Kubernetes Job

apiVersion: batch/v1
kind: Job
metadata: { name: db-migrate-${GIT_SHA} }
spec:
  ttlSecondsAfterFinished: 600
  backoffLimit: 0
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: flyway
          image: flyway/flyway:10
          args: ["migrate"]
          env:
            - name: FLYWAY_URL
              value: "jdbc:postgresql://db:5432/app"
            - name: FLYWAY_USER
              value: app
            - name: FLYWAY_PASSWORD
              valueFrom: { secretKeyRef: { name: db, key: password } }
          volumeMounts: [{ name: migrations, mountPath: /flyway/sql }]
      volumes:
        - name: migrations
          configMap: { name: db-migrations }

Argo CD PreSync hook or helm install --wait waits before deploying app pods.

Test in CI

- run: |
    docker run -d --name pg -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:16
    sleep 3
    docker run --rm --network=host \
      -v $PWD/db/migrations:/flyway/sql \
      flyway/flyway:10 \
      -url=jdbc:postgresql://localhost:5432/postgres \
      -user=postgres -password=pw \
      migrate

Backfill helper (Node)

async function backfillStatus(batch = 1000) {
  while (true) {
    const r = await db.query(`
      WITH cte AS (
        SELECT id FROM users WHERE status IS NULL ORDER BY id LIMIT $1
        FOR UPDATE SKIP LOCKED
      )
      UPDATE users SET status='active'
      WHERE id IN (SELECT id FROM cte) RETURNING 1
    `, [batch]);
    if (r.rowCount === 0) break;
    await new Promise(r => setTimeout(r, 100));
  }
}

Useful tools

• Flyway Desktop — UI.
• Atlas — declarative, diff-based, lint.
• gh-ost / pt-osc (MySQL) — online schema change.
• pgroll (PostgreSQL) — versioned online migrations.
• squawk — PG migration linter.
• schemahero — K8s operator for declarative schema.