Flyway / Migrations — Basics

Flyway / DB Migrations — Basics

What it is

Flyway is a versioned database migration tool. SQL or Java migration files run in order, idempotently, with state tracked in a metadata table.

Alternatives:

• Liquibase — XML/YAML changesets, more abstract.
• Alembic — Python (SQLAlchemy).
• Knex / Prisma migrate / TypeORM / Sequelize — Node ORM-bound.
• Atlas — declarative + diff-based, modern, language-agnostic.
• sqitch — depend on planning rather than versioning.
• golang-migrate / Goose — Go.

Concepts

• Migration file — V1__init.sql, V2__add_users.sql. Naming pattern: V{version}__{description}.{ext}.
• Versioned migration — runs once, recorded in flyway_schema_history.
• Repeatable migration — R__view.sql. Re-run when checksum changes (good for views, functions).
• Undo — paid feature; rarely safe in prod.
• Baseline — mark current state as version N for legacy DBs.
• Locations — filesystem: or classpath: paths to migrations.

Lifecycle

flyway migrate     # apply pending
flyway info        # show pending and applied
flyway validate    # check checksums match
flyway repair      # fix history table after error
flyway clean       # DROP everything! disable in prod (cleanDisabled=true)
flyway baseline    # adopt existing DB

File layout

db/migrations/
  V1__create_users.sql
  V2__add_email_index.sql
  V3__add_audit_columns.sql
  R__user_summary_view.sql

Example migration

-- V12__add_user_status.sql
ALTER TABLE users
  ADD COLUMN status TEXT NOT NULL DEFAULT 'active'
  CHECK (status IN ('active', 'archived', 'deleted'));

CREATE INDEX idx_users_status ON users (status) WHERE status != 'active';

Idempotency

Flyway runs each version once. But you should still write defensive SQL when possible:

CREATE TABLE IF NOT EXISTS ...
ALTER TABLE ... ADD COLUMN IF NOT EXISTS ...   -- PG 9.6+
DROP INDEX IF EXISTS ...

This protects against partial-apply scenarios + dev hand-edits.

Online schema change patterns

For big tables on busy DBs, native ALTER may lock too long.

Expand-Contract (multi-release)

Renaming email → email_address:

1. Expand: add email_address, dual-write, backfill in batches, deploy code reading both.
2. Contract: drop email after several releases.

Adding NOT NULL column

Bad:

ALTER TABLE big ADD COLUMN status TEXT NOT NULL;  -- rewrites whole table

Good:

-- step 1
ALTER TABLE big ADD COLUMN status TEXT;
-- backfill in batches
UPDATE big SET status = 'active' WHERE id BETWEEN 1 AND 1000;
...
-- step 2
ALTER TABLE big ALTER COLUMN status SET NOT NULL;
ALTER TABLE big ADD CONSTRAINT status_check CHECK (status IN (...));

PG 11+ optimizes constant defaults — ADD COLUMN ... DEFAULT 'x' doesn’t rewrite. Still careful with NOT NULL.

Big index

CREATE INDEX CONCURRENTLY idx_huge ON big_table (col);

CONCURRENTLY = no lock on writes; slower; can fail (clean up INVALID index).

Tools

• gh-ost (MySQL) — online migration via triggerless replication.
• pt-online-schema-change (MySQL).
• pgroll (PostgreSQL) — versioned, dual-write driven.

Transactional safety

• PostgreSQL: most DDL is transactional; whole migration runs in tx.
• MySQL: DDL implicitly commits; can’t rollback.
• Some statements (like CREATE INDEX CONCURRENTLY) cannot run in transaction → split into separate migrations or use executeInTransaction=false.

CI/CD integration

flyway -url=$DB_URL -user=$DB_USER -password=$DB_PASS \
       -locations=filesystem:./db/migrations \
       migrate

In K8s: init container in deployment OR Argo CD PreSync hook OR separate migration Job.

Common pitfalls

• Modifying an applied migration file (changes checksum). Flyway refuses → use flyway repair or new migration.
• clean enabled in prod → wipes DB.
• Running migrations from many replicas simultaneously (use locking).
• Big locking ALTER on hot table.
• No backup before migration.
• Migration that depends on application logic.
• Putting data backfill in the same migration as DDL — can take hours and lock prod.