Flyway Migration: Schema, Data & Rollback Testing

Database migration regression testing should prove that a database change preserves the required schema, existing business data, application behavior, and a usable recovery path. A successful migration command is only one piece of that evidence. With Flyway Migration, use migration-history validation alongside direct schema assertions, row-level data checks, application compatibility tests, and failure-and-recovery rehearsals. Flyway’s validate command checks available migrations against recorded migrations; it does not establish that an order total, status mapping, or application workflow is correct.

This guide covers Flyway Migration regression testing for schema changes on PostgreSQL; it is not an engine-to-engine data migration guide. This is a specialized form of database migration testing.

The illustrative order-management example targets PostgreSQL 18.6 and Redgate Flyway 13.12.1, using Community migration commands. Flyway 13.12.1 was released on November 5, 2026; undo remains a Teams capability. These are target versions, not a tested stack: the database examples were not executed during this revision. Validate the supplied bundle on your exact stack before adopting it.

Table of Content

• 1. What Flyway Migration Regression Testing Must Prove
• 2. Build a Production-Like Flyway Migration Test Baseline
• 3. Validate Flyway Migration History and Actual Schema Contracts
• 4. Prove Flyway Migration Data Integrity with Executable SQL Assertions
• 5. Test Flyway Migration Application Compatibility, Locks, and Writes
• 6. Inject Flyway Migration Failures and Verify Safe Recovery
• 7. Validate Flyway Migration Rollback Safety Without Assuming Every Change Is Reversible
• 8. Automate Flyway Migration Regression Gates in CI/CD

1. What Flyway Migration Regression Testing Must Prove

Migration history, database state, and application behavior are separate checks

Treat Flyway Migration regression testing as four connected acceptance layers:

• Layer: Migration history | Question to answer: Did the intended migrations run, with the expected scripts and ordering? | Suggested evidence: Validation results, migration output, history records
• Layer: Schema contract | Question to answer: Does the resulting database expose the required structure and permissions? | Suggested evidence: Catalog assertions and application-role checks
• Layer: Data and behavior | Question to answer: Were records transformed correctly, and do supported workflows still work? | Suggested evidence: Independent expected data, SQL assertions, application tests
• Layer: Recovery | Question to answer: Can the team reach a usable state without unacceptable data loss? | Suggested evidence: Failure-injection results and a rehearsed recovery record

Flyway’s schema-history table records applied migrations, checksums, and execution status. Use it as an audit trail, not as a substitute for querying the resulting objects and data.

Transaction rollback and release recovery are different outcomes

By default, Flyway handles each migration in its own transaction where the database and statements support that. If V2 commits and V3 fails, rolling back V3 does not automatically reverse V2. Grouping migrations changes the boundary, but is subject to database and statement restrictions.

For testing, distinguish transaction rollback, which reverses an unsuccessful transactional unit, from release recovery, which restores acceptable application and data behavior. Release recovery might retain an expanded schema, apply a corrective migration, execute a reviewed undo script, or restore a backup. Define the acceptable destination before deployment.

2. Build a Production-Like Flyway Migration Test Baseline

Pin the database, Flyway version, configuration, and database roles

Record the database engine and patch, extensions, encoding, collation, time zone, migration locations, placeholders, transaction settings, and application versions. Use the actual database engine rather than treating a different in-memory database as equivalent.

Separate the migration role from the application role. Run schema changes with the intended migration privileges and application checks with the intended runtime permissions. A superuser-only test does not demonstrate that the application’s credentials work.

For environment setup, see Codoid’s Docker Fundamentals for Testers: Images, Containers, Networks, and Volumes. Its environment and persistence guidance complements the migration-specific checks below.

Test fresh builds and supported upgrade starting points

Test three paths: an empty database, the currently deployed release with representative data, and every older release that you support upgrading from.

For upgrade testing, preserve the previous release’s actual migration history and repeatable scripts. The supplied V1-to-V2 exercise has only versioned migrations. When repeatables exist, build the starting database from the previous release artifact before switching to the candidate artifact; target=1 alone does not recreate an older repeatable definition.

Versioned migrations normally run once in version order. Once a script has reached a permanent downstream environment, corrective changes should normally be new migrations rather than edits to that applied file.

Prepare representative data and explicit expected results

Use synthetic or approved, sanitized fixtures. Include common records, empty tables, relevant nulls, boundary amounts, unsupported legacy values, and relationships. Maintain a separate volume profile for lock and duration tests.

Run the Flyway Migration example from the companion Flyway_Regression_Example_Review_Revision.zip bundle. Prerequisites are Bash 4+, Python 3.10+, a running Docker Engine/Desktop with Linux containers, and Docker Compose v2 supporting up –wait. The first run needs registry access. PostgreSQL, psql and Flyway run in containers, so no host database or Java installation is required.

EXAMPLE FILE MAP

• flyway-regression-example/: Root directory
• compose.yaml: Target images; isolated network
• run.sh: Provision, test, capture, remove
• sql/: V1, V2 and script configuration
• tests/: Fixtures, contracts and role checks
• cases/: Deliberately failing migrations
• scripts/: Error checks and two-session helper
• performance/: Before/after diagnostic queries
• VALIDATION_STATUS.md: What has and has not been run
• README.md: Setup, expected results, teardown

Extract the bundle, change into flyway-regression-example, and run:

bash run.sh
  

The runner creates a unique disposable environment with separate migration and application logins, no published database port, and throwaway credentials. It captures versions and logs, then removes its own containers and storage; artifacts remain on disk. Use the README’s project-specific teardown command after an interrupted host run. Do not reuse production credentials or attach an existing database.

Execution order: provision, fresh build, V1 and fixtures, V2, schema/data/behavior assertions, unchanged rerun, runtime-role and recovery checks, mutation/failure/concurrency cases, evidence capture, teardown.

Expected results, not observed output: the baseline maps orders 101/102/103 to 10/20/30; codes 40 and 99 fail; a widened predicate is detected; transactional failures leave V2 uncommitted; the runtime role cannot change the schema. The suite should exit nonzero for any unexpected outcome. A complete run should print SUITE EXPECTATIONS MET.

Start with sql/V1_create_orders.sql:

CREATE TABLE public.customers (id bigint PRIMARY KEY);
CREATE TABLE public.orders (
    id bigint PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES public.customers(id),
    legacy_status text NOT NULL,
    total_amount numeric(12,2) NOT NULL CHECK (total_amount >= 0)
);
  

Save this as tests/seed.sql, and run it only after V1, before the candidate migration:

INSERT INTO public.customers VALUES (1), (2);
INSERT INTO public.orders VALUES
(101, 1, 'new', 0.00),
(102, 1, 'paid', 49.95),
(103, 2, 'cancelled', 9999999999.99);

CREATE SCHEMA qa;
CREATE TABLE qa.orders_before AS
SELECT * FROM public.orders;
CREATE TABLE qa.expected_status (
id bigint PRIMARY KEY,
code smallint NOT NULL
);
INSERT INTO qa.expected_status VALUES
(101, 10), (102, 20), (103, 30);
  

The expected codes are explicit test data, not generated by reusing the migration. This is a test oracle: independently recorded expected results against which the implementation is checked. Keep the qa snapshot and expectation tables out of production migration locations. The three-row fixture checks correctness, not load capacity.

3. Validate Flyway Migration History and Actual Schema Contracts

Check Flyway migration versions, checksums, ordering, and repeatable changes

Capture flyway info before and after migration. Run migrate with validation enabled, then standalone validate against the completed candidate state. Applied migrations whose files are missing from configured locations, pending migrations, and changed repeatables have distinct validation states; do not assume standalone pre-deployment validate will accept every pending state.

Include these regression cases:

• Starting condition and action: Applied versioned file is changed in a disposable test copy | Expected result: Validation rejects the checksum mismatch
• Starting condition and action: An applied versioned file is omitted from configured locations | Expected result: Validation reports the missing migration under the intended rules
• Starting condition and action: A repeatable view definition changes | Expected result: It reruns after pending versioned migrations; dependent queries match the new contract
• Starting condition and action: Unchanged candidate is migrated again | Expected result: Versioned changes do not run again; business data does not duplicate
• Starting condition and action: An object is manually altered without changing migration files | Expected result: Direct schema assertions detect the deviation, even when migration-history validation passes

Repeatables rerun when their checksum changes and execute after pending versioned migrations, ordered by description. They must be safe to apply repeatedly. A second migrate call is not evidence that every versioned SQL script is independently idempotent.

Assert columns, constraints, indexes, permissions, and dependent objects

Define a schema contract covering column types, nullability and defaults; constraint definitions and enforcement; index keys, uniqueness and validity; views and routines; and ownership or grants used by the application.

For PostgreSQL 18, inspect constraints by table identity as well as name. Check pg_constraint.convalidated and conenforced where applicable. A NOT VALID constraint can enforce new writes without proving that all existing rows satisfy it. Specify whether an intermediate unvalidated state is acceptable, and by which release it must be validated.

For indexes, inspect pg_index.indisvalid and indisready, not only the presence of an index name. Assert the intended definition and absence of unexpected invalid indexes after deployment. Include a query against each affected view or routine, using the application role.

The complete tests/assert_schema.sql checks the column type, nullability and default; the constraint’s table and column identity; validation/enforcement flags; and its reviewed predicate. It creates a temporary reference table with an independently specified CHECK (status_code IN (10,20,30)), then compares pg_get_constraintdef output from both objects on the same server. This avoids guessing a deparser string or copying the actual definition into the expectation. Review any intentional contract change; equivalent but differently written predicates may need explicit approval.

-- Excerpt: run through the complete tests/assert_schema.sql.
SELECT pg_get_constraintdef(oid, false)
INTO actual_definition
FROM pg_catalog.pg_constraint
WHERE conrelid = 'public.orders'::regclass
AND conname = 'orders_status_code_ck'
AND contype = 'c' AND convalidated AND conenforced
AND conkey = ARRAY[status_attnum]::smallint[];

IF actual_definition IS DISTINCT FROM expected_definition THEN
    RAISE EXCEPTION 'Schema contract mismatch: status predicate';
END IF;
  

Prove the test can detect a defect: tests/widen_constraint.sql changes only the predicate to IN (10,20,30,40), keeping its name and valid/enforced state. The schema assertion and the behavioral rejection of 40 must both fail. Merely rejecting 99 cannot distinguish the widened contract. The bundle contains this mutation test; its PostgreSQL outcome still requires execution.

4. Prove Flyway Migration Data Integrity with Executable SQL Assertions

Verify row-level transformations, relationships, and business invariants

The Flyway Migration example expands orders with status_code: new becomes 10, paid becomes 20, and cancelled becomes 30. Existing identifiers, customer relationships, legacy statuses, and amounts must remain unchanged.

This is a small, blocking, transactional example, not an online-migration recipe. Use PostgreSQL Read Committed isolation. V2 acquires its table lock before checking source values and retains it until the migration transaction ends; existing writers may delay it, and reads/writes can be blocked while it runs. The 3-second lock wait and 30-second statement limits below are illustrative test settings, not production targets.

File: sql/V2__expand_order_status.sql. Its adjacent .sql.conf file explicitly sets executeInTransaction=true; keep Flyway grouping disabled for this exercise. If the guard fails after ADD COLUMN, the transactional migration must roll back the new column.

-- Small, blocking, transactional example; PostgreSQL READ COMMITTED.
-- These are illustrative test limits, not production availability budgets.
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '30s';

-- Acquire the table lock BEFORE inspecting the source values.
ALTER TABLE public.orders ADD COLUMN status_code smallint;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM public.orders
        WHERE legacy_status NOT IN ('new', 'paid', 'cancelled')
    ) THEN
        RAISE EXCEPTION 'Unmapped legacy order status';
    END IF;
END $$

UPDATE public.orders
SET status_code = CASE legacy_status
    WHEN 'new' THEN 10
    WHEN 'paid' THEN 20
    WHEN 'cancelled' THEN 30
END;

ALTER TABLE public.orders
    ADD CONSTRAINT orders_status_code_ck
    CHECK (status_code IN (10, 20, 30));
  

status_code deliberately remains nullable so old writers can omit it. In PostgreSQL, a CHECK condition alone does not reject nulls. That is an explicit compatibility choice here, not proof that the migration has reached its final contract.

The bundle runs tests/assert_preserved.sql and tests/assert_data.sql immediately after migration, before application-role tests deliberately change the data. Their combined core assertions are shown below:

DO $$
BEGIN
    IF (SELECT count(*) FROM qa.orders_before) <> 3 THEN
        RAISE EXCEPTION 'Unexpected test fixture';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM qa.orders_before b
        FULL JOIN public.orders o USING (id)
        WHERE b.id IS NULL OR o.id IS NULL
            OR ROW(o.customer_id, o.legacy_status, o.total_amount)
            IS DISTINCT FROM
            ROW(b.customer_id, b.legacy_status, b.total_amount)
    ) THEN
        RAISE EXCEPTION 'Existing order data changed';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM qa.expected_status e
        FULL JOIN public.orders o USING (id)
        WHERE e.id IS NULL OR o.id IS NULL
            OR o.status_code IS DISTINCT FROM e.code
    ) THEN
        RAISE EXCEPTION 'Status mapping mismatch';
    END IF;
END $$
  

The full joins expose missing and unexpected rows, while the row comparison checks preserved values. IS DISTINCT FROM makes null differences observable; ordinary inequality can evaluate to unknown for null inputs. RAISE EXCEPTION turns a mismatch into an SQL error rather than a diagnostic row that CI might ignore.

Extend this pattern to the business invariants affected by each migration: orphan detection, tenant ownership, per-currency totals, timestamp meaning, uniqueness, and generated-key behavior. Counts and aggregate sums are useful diagnostics, but use independently expected row values for critical transformations.

Exercise invalid inputs, boundaries, and unchanged data

In a separate V1 database, change a fixture status to on_hold and update the recorded pre-migration expectation to reflect that deliberate setup. Attempt V2. Require SQLSTATE P0001 with the unmapped-status message, no committed status_code column or V2 history entry, and preservation of the source data for investigation. The fresh-build job separately covers the empty table; do not apply the fixed three-row assertion to it.

After a successful V2, tests/assert_behavior.sql accepts 10/20/30 and the intentional null state, then requires 40 and 99 to fail specifically against orders_status_code_ck. It first checks that the fixture row exists, so a zero-row UPDATE cannot produce a misleading result. All attempted test writes are enclosed in a transaction that is rolled back:

-- Roll back accepted writes too: this test must not alter the data oracle.
BEGIN;
DO $$
DECLARE
    code smallint;
    violated_constraint text;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM public.orders WHERE id=101) THEN
        RAISE EXCEPTION 'Missing behavioral fixture';
    END IF;
    FOREACH code IN ARRAY ARRAY[10,20,30]::smallint[] LOOP
        UPDATE public.orders SET status_code=code WHERE id=101;
    END LOOP;
    UPDATE public.orders SET status_code=NULL WHERE id=101;
    IF (SELECT status_code FROM public.orders WHERE id=101) IS NOT NULL THEN
        RAISE EXCEPTION 'Intentional null contract was not preserved';
    END IF;
    FOREACH code IN ARRAY ARRAY[40,99]::smallint[] LOOP
        BEGIN
            UPDATE public.orders SET status_code=code WHERE id=101;
            RAISE EXCEPTION 'Unintended code % was accepted', code;
        EXCEPTION WHEN check_violation THEN
            GET STACKED DIAGNOSTICS violated_constraint=CONSTRAINT_NAME;
            IF violated_constraint <> 'orders_status_code_ck' THEN
                RAISE;
            END IF;
        END;
    END LOOP;
END $$
ROLLBACK;
  

The handler accepts only the named check-constraint violation. Missing tables, permission failures, or an unexpectedly accepted value fail the test. PostgreSQL supports inspecting the constraint name in an exception handler; changes within a failed block roll back. The outer ROLLBACK also removes the accepted-value probes.

Add independent cases for duplicate primary keys, nonexistent customers, null required fields, negative amounts, and permitted zero or maximum values. For the expansion state, also prove that an old-style insert without status_code remains accepted. Use separate fixtures or rolled-back transactions so negative tests do not contaminate later checks.

5. Test Flyway Migration Application Compatibility, Locks, and Writes

Check old and new application releases against intended schema states

An added nullable field is not a complete compatibility strategy. Define which representation remains authoritative while old and new writers coexist.

For this example, keep legacy_status authoritative during the overlap. The new application writes both representations in one transaction but continues to derive business status from the legacy field. Do not switch reads to the new code merely because the backfill completed.

• Application and schema state: Old application + V1 | Required application test: Existing create, read, and status-update workflows pass
• Application and schema state: Old application + expanded V2 | Required application test: Old SQL remains valid; inserts may omit the new field
• Application and schema state: New application + expanded V2 during overlap | Required application test: Reads use the authoritative legacy value; writes maintain both values
• Application and schema state: New application + V1 | Required application test: Unsupported in this example; deployment sequencing must prevent it
• Application and schema state: Old application + later contracted schema | Required application test: Treat as unsupported once required legacy fields are removed

The stale-value case matters: V2 sets order 101 to code 10. An old instance changes its legacy status to paid, leaving code 10 behind. A new reader that falls back only when the code is null will return the wrong status. Make that sequence an explicit regression test.

Before switching authority, stop old application instances or prevent them from writing to the database, reconcile derived codes, and verify no mismatches remain. Retain reverse-compatible writes for the agreed rollback window. Removing the legacy representation is a later, separately gated change. Backward-compatible schema evolution can preserve the option to roll back application code.

For broader service-level coverage, see Codoid’s Microservices Testing: A Practical Strategy for QA and Development Teams.

The companion runtime job connects as app_runtime with separate credentials. tests/app_overlap.sql performs an old-style insert, creates order 104 using both fields, and proves the stale-code sequence on order 101. tests/denied_ddl.sql and tests/denied_create.sql must fail with SQLSTATE 42501; a generic connection failure does not count. These are SQL-client contract simulations, not tests of your application binaries, APIs, ORM, or deployed rollback.

Exercise concurrent activity, permissions, and timeout behavior

Run representative reads and writes while the migration executes. Record blocked sessions, wait durations, application errors, and whether acknowledged writes survive. Compare results with team-approved budgets rather than inventing universal migration-time limits.

The bundle’s two-session helper first holds a writer transaction on V1, starts V2, and polls pg_locks until it observes the migration waiting for AccessExclusiveLock. In the interleaving case, the writer then commits on_hold; the now-locked guard must reject it and roll V2 back. In a separate case, the writer retains its lock until V2 reports SQLSTATE 55P03; after release, a reviewed retry must succeed. Failure to observe the intended wait fails the test rather than asserting that the race occurred. These database cases are supplied but have not been run here.

For concurrent backfills, deliberately update a row while it is being processed, interrupt and restart a batch, and retry the same logical work. Assert no lost updates or duplicate transformations against a workload-specific expected dataset.

Also compare query behavior after migration, not only deployment duration. Use equivalent representative datasets and the same hardware, statistics policy, warm-up, parameter distribution and concurrency. Measure an affected order-read request and status-write request before and after V2; record latency percentiles, throughput, errors and waits against budgets agreed before testing. Keep the new writer’s dual-field update in the candidate workload rather than measuring a different business operation.

The performance/ directory supplies before.sql, after.sql and an unfilled acceptance.csv. The query below is a plan diagnostic for the order-read workload; repeat it under the declared measurement protocol, not as a substitute for a load test.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id, total_amount
FROM public.orders
WHERE customer_id = 1 AND legacy_status = 'paid'
ORDER BY id LIMIT 50;
  

EXPLAIN ANALYZE executes the statement. For write diagnostics, use disposable data and an explicit transaction/ROLLBACK; rollback does not reverse external trigger effects or sequence consumption. Compare measured behavior, using plans to investigate regressions, not as exact golden-text assertions. No performance result or universal percentage threshold is claimed here.

Test the application role’s permitted reads and writes, as well as prohibited schema changes. Permission failure during migration is a separate case. A runtime role should not silently gain migration privileges as a workaround.

6. Inject Flyway Migration Failures and Verify Safe Recovery

Contrast transactional failures with partially committed changes

Use throwaway databases and deliberately modified Flyway Migration artifacts. Inject a guaranteed SQL error after an earlier schema or data statement, then inspect objects, business rows, and migration history. Do not modify a script already applied to a shared environment.

• Engine or migration mode: PostgreSQL migration containing transaction-compatible statements | Failure expectation to verify: The failed migration’s transactional changes roll back; earlier committed migrations remain
• Engine or migration mode: PostgreSQL non-transactional operation | Failure expectation to verify: Inspect surviving objects and operation-specific recovery requirements
• Engine or migration mode: MySQL 8.4 migration containing implicit-commit DDL | Failure expectation to verify: Earlier statements may remain committed; do not expect whole-file rollback

Flyway documents these transactional boundaries. MySQL’s atomic DDL is statement-level atomicity, not permission to combine multiple DDL statements into a rollback-capable transaction.

For PostgreSQL, CREATE INDEX CONCURRENTLY cannot run inside a SQL transaction and a failed build may leave an invalid index. Flyway also has a separate advisory-lock setting: Redgate documents postgresql.transactional.lock=false for this case. Disabling the script’s SQL transaction alone does not configure that lock.

The isolated index-failure example uses these two controls; it does not change V1/V2 to non-transactional execution:

# cases/concurrent_index/V3__index_failure_demo.sql.conf
executeInTransaction=false
  

# Excerpt from run.sh; fw is the bundle's Flyway wrapper.
fw "$db" \
-locations=filesystem:/flyway/sql,filesystem:/flyway/cases/concurrent_index \
-postgresql.transactional.lock=false migrate
  

This deliberately attempts a unique index on duplicated customer IDs to produce a failed-build state, not a legitimate orders-domain rule. Require the expected uniqueness failure and an invalid index record; preserve history and catalog evidence. Review cleanup, any necessary history repair, and a valid retry separately. Interruption and a complete cleanup/retry rehearsal remain additional acceptance tests.

Reconcile database state before retrying or repairing history

Test permission denial, lock timeout, and connection loss near completion. For connection loss, treat the outcome as unknown until a fresh connection checks both history and database state. A failed client command is not a sufficient basis for manually replaying statements.

Use the following recovery sequence: preserve logs and the release identifier; inspect committed objects and data; select a DBA-reviewed cleanup, corrective migration, or restore; reconcile history only when required; then rerun assertions and application tests.

Flyway repair changes schema-history records. It can remove failed entries, realign recorded metadata, and mark missing migrations as deleted; leftover user objects still require separate cleanup. Use the same migration locations as migrate, and never make automatic repair the response to every failed deployment.

Also verify that later migrations did not execute after the failure. Preserve the failed-state evidence before cleanup, and confirm that a retry does not duplicate backfilled or reference data.

• Symptom: Checksum mismatch | Evidence to retain: Applied history and candidate script diff | Next action: Restore the correct artifact or add a reviewed migration; do not repair merely to silence validation.
• Symptom: Unmapped legacy status | Evidence to retain: P0001, source row, rolled-back schema/history | Next action: Agree the mapping or correct source data; rerun the assertions.
• Symptom: Lock timeout | Evidence to retain: 55P03, blocking session and transactional state | Next action: Resolve the blocker or reschedule; verify state before retry.
• Symptom: Invalid concurrent index | Evidence to retain: Index definition, indisvalid/indisready, history | Next action: Choose reviewed cleanup and retry; repair only the history changes actually required.

7. Validate Flyway Migration Rollback Safety Without Assuming Every Change Is Reversible

Choose application rollback, Flyway undo, a forward fix, or restore

Choose recovery according to the actual state and business-data risk of your Flyway Migration, not according to whether an inverse SQL statement can be written.

• Recovery option: Application rollback, retaining the schema | Candidate use: The expanded schema remains compatible with the older application | Evidence required before relying on it: Old application passes against current data and schema
• Recovery option: Flyway undo — Teams | Candidate use: A completed versioned migration has a reviewed, applicable inverse | Evidence required before relying on it: Undo preserves required data and handles dependencies
• Recovery option: Forward fix | Candidate use: Keeping committed changes and applying a corrective migration is safer | Evidence required before relying on it: The correction repairs the defect without breaking current writes
• Recovery option: Backup or point-in-time restore | Candidate use: Destructive or inconsistent changes cannot be safely corrected in place | Evidence required before relying on it: Successful restore, reconciliation plan, and acceptable recovery objectives

Flyway undo uses a corresponding U migration and assumes the versioned migration completed. It is not a general handler for partially executed, non-transactional changes. Repeatables have no undo operation; restore the desired definition through the repeatable migration and reapply it.

For PostgreSQL point-in-time recovery, test the base backup and required write-ahead log archive. A restore to before a destructive migration also returns the database to that earlier business state; later legitimate writes need explicit reconciliation. A database restore does not by itself reverse external payments, messages, or other side effects.

Test post-deployment writes and the rollback window

A useful rollback rehearsal includes acknowledged writes after migration. The companion SQL-client simulation creates order 104 with paid/code 20, changes order 101 through the old writer, and inserts order 105 without the new field. It then models returning to the old writer by creating order 106 while retaining V2. tests/app_rollback.sql checks an independently specified six-row expected dataset, including identifiers, customer relationships, statuses and amounts.

For a real application-only rollback rehearsal, execute the same sequence through the actual old/new application releases. Confirm that post-deployment orders remain readable and new old-version writes still work. Do not reuse the original three-row expectation: the recovery oracle must include the intentional later changes. Passing the SQL simulation alone does not establish application rollback readiness.

For a schema undo rehearsal, additionally prove that all post-deployment values remain representable in the old schema. A newly introduced status with no legacy equivalent should block that recovery route until its data-handling policy is defined.

Set the rollback window in operational terms: which application versions can run, which writes remain backward-compatible, and which contract change closes the window. Measure recovery time against the team’s recovery time objective, and permitted data loss against its recovery point objective. Do not substitute a backup-success notification for a restore rehearsal.

Reapply changes and verify the final state

After a successful undo rehearsal, reapply the candidate migrations and rerun schema, data, and application assertions. Check the resulting history states rather than manually deleting audit records; Flyway distinguishes an undone migration from an ordinary pending migration.

After application-only rollback, the migration may still be applied. In that case, a subsequent migrate should not repeat the versioned backfill. Test redeployment of the compatible application separately.

For restored databases, identify the restored schema and business-data point, reconcile permitted later writes, and then exercise the supported upgrade path. Report the final usable state, retained data, remaining limitations, and actual recovery duration.

8. Automate Flyway Migration Regression Gates in CI/CD

Build, migrate, assert, recover, and reapply your Flyway Migration

Use separate jobs or databases for the happy path, each supported upgrade, invalid fixtures, concurrency, and recovery. Expected-failure jobs must assert the specific failure and resulting state; suppressing an error with `|` true is not a test.

The companion run.sh is the single entry point described in the setup section. It exercises fresh build, seeded upgrade, unchanged rerun, runtime permissions, SQL-client recovery, predicate mutation, malformed data, mid-migration failure, controlled contention and invalid-index detection in separate disposable databases. The snippets below are orchestration excerpts, not standalone commands; the complete wrappers and files are in the bundle.

log_run schema sql_file "$db" tests/assert_schema.sql
log_run data assert_data "$db"
log_run behavior sql_file "$db" tests/assert_behavior.sql
log_run unchanged-rerun fw "$db" migrate

# On the separately mutated fixture:
expect_failure mutation-schema P0001 \
  'Schema contract mismatch' \
  sql_file "$db" tests/assert_schema.sql
  

psql runs with `ON_ERROR_STOP` and verbose diagnostics so SQL errors stop execution and expose SQLSTATE. The harness captures the command exit code, log and expected diagnostic; an unexpected success or wrong error fails the suite. Its exit trap preserves an existing failure, captures server logs and tears down only the new Compose project. Local shell-helper checks are not evidence that these database cases passed.

Validation-on-migrate and clean-disabled settings remain enabled. Credentials are ephemeral and separated by role; the database is not exposed on a host port. Neither a database-name prefix nor a local test pass is a security audit. Keep credentials out of source control and shell tracing, and upload restricted-access artifacts even when CI fails.

Preserve evidence and define release blockers

Attach the source commit and fixture checksums, image digests, PostgreSQL server/client patch, Flyway distribution/version, JDBC jar identity and checksum, configuration without secrets, history snapshots, assertions, and recovery logs. The bundle captures runtime inventory when executed; no tested digest or JDBC runtime version is invented here. Retain catalog evidence and actual application results alongside it.

A practical release gate can use this matrix:

• Gate: Pull request | Minimum checks: Fresh build, current-release upgrade, schema and data assertions, unchanged rerun | Suggested blocking condition: Unexpected history, wrong mapping, lost data, broken constraints
• Gate: Pre-release compatibility | Minimum checks: Supported old/new application combinations and runtime roles | Suggested blocking condition: Required workflow or permission contract fails
• Gate: Pre-release operations | Minimum checks: Representative data volume, concurrent traffic, lock and timeout cases | Suggested blocking condition: Agreed availability or performance budget is breached
• Gate: Recovery readiness | Minimum checks: Applicable rollback, forward-fix, or restore rehearsal with new writes | Suggested blocking condition: Unexplained data loss, untested recovery, or unmet recovery objectives

Assign each blocker an owner. QA owns the test evidence; application owners confirm business semantics; database and operations owners approve recovery and operational limits. A green migration job should mean that the agreed checks passed not merely that no SQL error reached the console.

Conclusion

Use the four evidence layers as your release gate, and retain the schema, data, application and recovery results with the release artifact. The question is not only “Did Flyway succeed?” but “Does the evidence support this deployment?” Effective Flyway Migration regression testing requires this complete evidence chain.

For support with database migration testing and CI/CD integration, explore Codoid’s Database Testing Services.

Struggling with Migration Safety?

Talk to a Database QA Expert

Frequently Asked Questions