Flyway/Liquibase Migrations

Versioned migrations (V3__add_orders.sql) run exactly once, in version order. Repeatable migrations (R__refresh_views.sql) have no version and re-run whenever their checksum changes, after all versioned ones.

• History is tracked in the flyway_schema_history table (version, description, checksum, success flag).
• Use repeatables for idempotent objects: views, functions, stored procedures (CREATE OR REPLACE).
• Repeatables run in alphabetical order by description, so dependencies between them need naming discipline.
• Spring Boot runs Flyway on startup when flyway-core is on the classpath (Boot 3 with Flyway 10 needs database modules like flyway-database-postgresql).

Someone changed the contents of an already applied migration file (even whitespace or line endings), so the stored checksum differs and validate aborts startup.

• Proper fix: restore the original file and add a new migration V6 with the change.
• If the edit is harmless (comment, formatting) and you are sure of the DB state, run flyway repair, which updates the stored checksums; it also removes failed entries.
• Check for CRLF/LF conversion by git (.gitattributes) or an IDE formatter as a frequent accidental cause.
• Never disable validation (validateOnMigrate=false) as the permanent answer, because drift goes undetected.

Flyway runs each migration in a transaction where the DB supports transactional DDL. PostgreSQL rolls the whole script back; MySQL (and Oracle) auto-commit DDL, so a failure leaves the schema half-applied and the history row marked failed.

• On MySQL a failed migration needs manual cleanup of the partial changes, then flyway repair, then re-run.
• Keep migrations small and single-purpose so partial failure is easy to reason about.
• Flyway on PostgreSQL still cannot run some statements inside a transaction, such as CREATE INDEX CONCURRENTLY; use -- flyway:executeInTransaction=false (script config) for those files.
• Newer Flyway versions refuse to start when a failed entry exists until repaired.

Never rename in place during a rolling deploy, since old and new app versions run against the same schema at once. Split it into backward-compatible steps across multiple releases.

1. Expand: add the new column full_name (nullable), deploy migration.
2. Deploy app version that writes both columns and reads the old (or new with fallback).
3. Backfill old rows in batches.
4. Deploy version that reads and writes only the new column.
5. Contract: drop the old column in a later release, once no running version uses it.

ALTER TABLE users ADD COLUMN full_name varchar(200);
-- backfill in batches
UPDATE users SET full_name = name WHERE id BETWEEN 1 AND 10000 AND full_name IS NULL;

• Same idea for NOT NULL: add nullable, backfill, add a CHECK ... NOT VALID then VALIDATE, then set NOT NULL.
• Keep columns in sync with a trigger if the old app version still writes the old column.

Naive DDL can take long locks that block reads/writes and queue all other queries behind it. Use online methods and lock timeouts.

• PostgreSQL: CREATE INDEX CONCURRENTLY (no write lock, cannot run in a transaction, can leave an INVALID index on failure); ADD COLUMN with a constant default is metadata-only since 11; set lock_timeout so the DDL fails fast instead of queueing and blocking everything.
• MySQL 8: ALGORITHM=INPLACE/INSTANT, LOCK=NONE where supported (ADD COLUMN instant since 8.0.12); otherwise gh-ost or pt-online-schema-change for shadow-table copies.
• Backfill data in small batches (id ranges, 5-10k rows, with sleeps) outside the DDL migration, ideally as a separate job, to avoid long transactions and replica lag.
• Run migrations separately from app startup for heavy changes so a slow migration does not fail health checks.

In practice you roll forward: ship a new corrective migration. Flyway's undo migrations (U5__...) exist only in paid editions; Liquibase supports rollback blocks per changeset (auto-generated for simple changes like createTable, but not for sql or data changes).

<changeSet id="7" author="dev">
  <addColumn tableName="orders"><column name="note" type="varchar(100)"/></addColumn>
  <rollback><dropColumn tableName="orders" columnName="note"/></rollback>
</changeSet>

• Destructive changes (drop column, delete data) cannot be rolled back without a backup; the expand-contract approach avoids needing it.
• Application rollback must remain compatible with the new schema; that is why migrations should be backward compatible.
• Test rollbacks in CI if you rely on them: liquibase rollbackCount 1 against a copy.

Both take a lock in the database before migrating; the second instance waits until the first finishes and then finds nothing to do.

• Flyway uses database-specific locking on the schema history table (for example a PostgreSQL advisory lock or a table lock); it does not use a separate lock table.
• Liquibase uses the DATABASECHANGELOGLOCK table. If the process is killed mid-migration the lock row can stay LOCKED=1 and every later start hangs or fails: fix with liquibase release-locks or update the row.
• Waiting instances may time out their readiness probe on long migrations; in Kubernetes prefer a dedicated migration Job or init container run once before the rollout.

Baseline the database: mark the current state as a given version so Flyway applies only newer migrations.

spring:
  flyway:
    baseline-on-migrate: true
    baseline-version: 1

• Export current schema as the baseline script (V1__baseline.sql) for new environments, and baseline production at version 1 so it is skipped there.
• baseline-on-migrate only acts when the schema has tables but no history table; risky if left on, because pointing at the wrong non-empty DB silently baselines it.
• For Liquibase, generate a changelog with generateChangeLog and mark it as run with changelogSync.

Two files with the same version fail with a "Found more than one migration with version 8" error. A lower version added after higher ones were applied is flagged as "detected resolved migration not applied to database" and fails validation unless outOfOrder is enabled.

• Fix by renumbering your file to a new highest version before merge; this is a rebase-time conflict, caught in CI.
• spring.flyway.out-of-order=true applies the late migration, but environments can end up in different orders, so use it sparingly.
• Use timestamp versions (V20261004_1530__...) to reduce collisions, at the cost of order surprises.
• Liquibase has no global version number: changesets are identified by id + author + file path, so ordering comes from the changelog include order.

Run every migration from scratch against a real engine in CI (Testcontainers), validate that the app starts and ORM mappings match, and apply migrations as a controlled pipeline step before the app rollout.

@Container
static PostgreSQLContainer<?> pg = new PostgreSQLContainer<>("postgres:16");

@DynamicPropertySource
static void props(DynamicPropertyRegistry r) {
    r.add("spring.datasource.url", pg::getJdbcUrl);
    r.add("spring.datasource.username", pg::getUsername);
    r.add("spring.datasource.password", pg::getPassword);
}

• Set spring.jpa.hibernate.ddl-auto=validate so entity/schema mismatch fails the test.
• Do not test on H2 in a different dialect mode; DDL and types differ from production.
• Also test upgrade paths: restore a recent prod-like dump, migrate forward, time it.
• Lint SQL (squawk, sqlfluff) to catch locking operations; fail on edits to applied files.

Flyway is SQL-first and simple (plain numbered scripts); Liquibase is changelog-first (XML/YAML/JSON/SQL) with database-independent change types, preconditions, contexts and built-in rollback.

• Choose Flyway for single-database teams comfortable writing SQL and wanting minimal concepts.
• Choose Liquibase for multi-database products, rollback requirements, preconditions (tableExists), and contexts/labels to vary changes by environment.
• Both store applied history in the DB and validate checksums; both integrate with Spring Boot by auto-configuration (spring.liquibase.change-log).
• Database-agnostic Liquibase types still produce engine-specific SQL, so vendor features need raw sql changes anyway.

Keep small, fast data fixes in a migration; run large backfills as batched application or job code outside the migration transaction. A migration that updates 100M rows in one statement holds locks, bloats the WAL/undo log, and may time out startup.

UPDATE orders SET status_v2 = status
WHERE id IN (SELECT id FROM orders WHERE status_v2 IS NULL ORDER BY id LIMIT 5000);

• Loop the batch (job, shell loop, or Flyway Java migration) until zero rows updated; each batch commits separately.
• Make backfills idempotent (WHERE new_col IS NULL) so they can resume after failure.
• Watch replica lag and pause when it grows.
• Java-based migrations (BaseJavaMigration) are versioned and checksummed by class name rather than content, so editing them does not trip checksum validation.

Two tools then own the schema; Hibernate may create or alter objects before or after Flyway, producing drift, failed migrations ("table already exists") and differences between environments.

• With a migration tool, set spring.jpa.hibernate.ddl-auto=none or validate.
• Flyway runs before the EntityManagerFactory in Spring Boot, so validate sees the migrated schema.
• Generate a draft migration from entity diffs (Liquibase diff, IDE tools), review it by hand, then commit it.
• Embedded test DBs with create-drop hide missing migrations; test with the migrations themselves.

Keep schema migrations identical in all environments and vary only seed or environment data through separate locations or contexts.

spring:
  flyway:
    locations: classpath:db/migration,classpath:db/seed-dev

• Flyway: environment-specific locations via Spring profiles, or placeholders (${schema_name}) inside scripts.
• Liquibase: context="dev" on changesets, enabled with spring.liquibase.contexts=dev; labels for finer filters.
• Versions in extra locations share the same version namespace, so avoid collisions with main migrations.
• Never put real secrets in migration scripts or placeholders that are logged.

First stop all code reading and writing it and let that release fully roll out, then drop in a later release. Add a safety window and a backup.

• Step 1: remove usage in code (and ORM mappings; SELECT * or entity mapping with a missing column fails).
• Step 2: optionally rename to _deprecated_old_col (instant) and wait a release cycle; failures show up as clear errors that you can undo by renaming back.
• Step 3: drop in a later migration, after verifying through query statistics that nothing touches it.
• Keep an export or snapshot, since a drop is not reversible by the migration tool.
• Also check external consumers: reports, BI tools, other services, triggers and views that reference it.