Schema migration CI/CD (Flyway/Liquibase)

Symptom

Goal: Manage database schema changes with versioned migration files; apply them automatically but in a controlled way within CI/CD using Flyway or Liquibase.

1. Create versioned migration files (Vxxx__name.sql)

Write every schema change into a named file with an increasing version number; the Flyway convention is V<version>__<description>.sql (e.g. V1__initial_schemas.sql, V2__orders_table.sql). These files are kept in Git and applied in version order. Applied migrations are recorded in a tracking table (flyway_schema_history in Flyway), so each migration runs exactly once.

PostgreSQL

-- V2__orders_table.sql
CREATE TABLE orders (
  id       bigserial PRIMARY KEY,
  amount    numeric(12,2) NOT NULL,
  created_at   timestamptz DEFAULT now()
);

2. Preserve immutability with checksums

Flyway/Liquibase store the checksum of every applied migration. If you later edit an already applied file, the checksum no longer matches and the tool fails. Rule: NEVER edit an applied migration; make the fix with a new version file. This guarantees that all environments (dev/staging/prod) share the same history.

PostgreSQL

# repair on checksum mismatch (use with care):
flyway repair
# the right way: add the change in a new Vx file

3. Baseline an existing database

Use baseline when attaching the migration tool to an already populated database with no history; it treats the current state as the 'starting version' and does not try to recreate existing objects. Migrations after baselineVersion are applied. In an environment built from scratch no baseline is needed; migrations run from V1 onward.

PostgreSQL

flyway baseline -baselineVersion=1 -baselineDescription="existing schema"

4. Migrate automatically in the CI pipeline

Run the migration tool in a CI step to apply changes automatically to the test and staging databases; the migrate command runs only new (unapplied) files and is idempotent. Applying the migration first to a temporary/ephemeral database and validating it catches broken SQL before it reaches prod. The schema must be migrated BEFORE the application code is deployed (with backward-compatible changes).

PostgreSQL

# CI step (Flyway)
flyway -url=jdbc:postgresql://staging/appdb \
  -user=ci -password=$PW migrate

MySQL

flyway -url=jdbc:mysql://staging/appdb \
  -user=ci -password=$PW migrate

5. Add a manual approval gate for prod

Test/staging are automatic, but make the prod migrate require manual approval; add an 'approval gate' (manual approval) step in CI/CD. Before approval, show which migrations will run with 'flyway info' or 'liquibase status'. Always take a fresh backup before applying to prod so you can roll back if something goes wrong.

PostgreSQL

# what will run before approval?
flyway info
# liquibase equivalent:
liquibase status --verbose

6. Define reversibility and a rollback strategy

Flyway Community has no automatic undo; adopt a forward-only migration principle: instead of undoing a mistake, write a new migration that fixes it. Liquibase changesets can define a rollback block and be rolled back with 'liquibase rollback'. The most robust strategy: keep changes small and backward compatible, take a backup before risky operations (dropping columns), and prepare a written rollback plan.

PostgreSQL

-- Liquibase rollback example (inside a changeset)
--changeset dev:2
CREATE TABLE orders (...);
--rollback DROP TABLE orders;