Database Migration Testing — Flyway and Liquibase with Testcontainers
Database migrations are the most dangerous code in most applications. A failed migration in production means downtime. A migration that succeeds but corrupts data is worse. Testing migrations against the actual target database — not H2, not a manual inspection — is the only way to catch migration bugs before they reach production.
This article covers testing Flyway and Liquibase migrations with Testcontainers, verifying schema correctness after migration, and testing rollback scenarios.
What You’ll Learn
- Running Flyway migrations against a real PostgreSQL container in tests
- Verifying migration state — which migrations applied, which are pending
- Testing schema correctness after migration
- Testing Liquibase changelogs with Testcontainers
- Testing destructive migrations (column drops, renames) safely
- Detecting migration/application model drift
Why Migration Testing Matters
The common approach to migration testing is “we run the application and see if it starts.” This is inadequate for three reasons:
It is too late. By the time you run the application, you have already deployed. A migration failure in production requires an emergency rollback.
H2 does not catch dialect errors. Flyway migration scripts for PostgreSQL often use PostgreSQL-specific syntax: CREATE INDEX CONCURRENTLY, JSONB columns, GENERATED ALWAYS AS IDENTITY. H2 compatibility mode covers some syntax but not all. You discover the incompatibility in production.
It does not test the full migration path. Running the application from a fresh database tests the current state. It does not test upgrading from version 1 to version 2 to the current state, which is what happens in production when you deploy a new release.
Flyway Testing
Project Setup
Assume the following Flyway migration structure:
src/main/resources/db/migration/
├── V1__create_orders_table.sql
├── V2__add_customer_email_to_orders.sql
├── V3__create_products_table.sql
└── V4__add_order_index.sql
-- V1__create_orders_table.sql
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id VARCHAR(50) NOT NULL,
status VARCHAR(20) NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- V2__add_customer_email_to_orders.sql
ALTER TABLE orders ADD COLUMN customer_email VARCHAR(255);
-- V4__add_order_index.sql
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);
The CREATE INDEX CONCURRENTLY in V4 is PostgreSQL-specific and will fail against H2.
Testing Migration Execution
@SpringBootTest
@Testcontainers
@TestPropertySource(properties = {
"spring.flyway.enabled=true",
"spring.jpa.hibernate.ddl-auto=validate"
})
class FlywayMigrationTest {
@Container
@ServiceConnection
static PostgreSQLContainer<?> postgres =
new PostgreSQLContainer<>("postgres:16-alpine");
@Autowired
private Flyway flyway;
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
void shouldApplyAllMigrationsSuccessfully() {
MigrationInfoService info = flyway.info();
MigrationInfo[] applied = info.applied();
assertThat(applied).isNotEmpty();
assertThat(applied)
.extracting(MigrationInfo::getState)
.containsOnly(MigrationState.SUCCESS);
}
@Test
void shouldHaveNoPendingMigrations() {
MigrationInfo[] pending = flyway.info().pending();
assertThat(pending).isEmpty();
}
@Test
void shouldApplyMigrationsInOrder() {
MigrationInfo[] applied = flyway.info().applied();
assertThat(applied[0].getVersion().getVersion()).isEqualTo("1");
assertThat(applied[1].getVersion().getVersion()).isEqualTo("2");
assertThat(applied[2].getVersion().getVersion()).isEqualTo("3");
assertThat(applied[3].getVersion().getVersion()).isEqualTo("4");
}
}
Verifying Schema After Migration
@Test
void shouldCreateOrdersTableWithCorrectColumns() {
List<String> columns = jdbcTemplate.queryForList(
"""
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position
""",
String.class
);
assertThat(columns).containsExactlyInAnyOrder(
"id", "customer_id", "status", "total_amount",
"created_at", "updated_at", "customer_email"
);
}
@Test
void shouldCreateOrderIndexes() {
List<String> indexes = jdbcTemplate.queryForList(
"""
SELECT indexname
FROM pg_indexes
WHERE tablename = 'orders'
""",
String.class
);
assertThat(indexes).contains(
"orders_pkey",
"idx_orders_customer_id",
"idx_orders_status"
);
}
@Test
void shouldEnforceNotNullConstraints() {
assertThatThrownBy(() -> jdbcTemplate.execute(
"INSERT INTO orders (status, total_amount) VALUES ('PENDING', 99.99)"
)).isInstanceOf(DataAccessException.class);
}
Testing Schema/Model Drift
spring.jpa.hibernate.ddl-auto=validate tells Hibernate to validate the database schema against the entity model at startup. If they do not match, the application fails to start. This catches drift between your migration scripts and your JPA entity definitions.
@Test
void shouldValidateSchemaMatchesEntityModel() {
// If this test passes, the application context loaded successfully,
// which means Hibernate's schema validation passed.
// Any mismatch between V1-V4 migrations and JPA entities would
// throw SchemaManagementException during context load.
assertThat(true).isTrue();
}
Add to application-test.yml:
spring:
jpa:
hibernate:
ddl-auto: validate # fail if schema does not match entity model
flyway:
enabled: true
Testing the Incremental Migration Path
The most valuable migration test simulates what happens during a production upgrade: apply old migrations to create the schema, then apply new migrations on top of it.
@Test
void shouldMigrateFromV1ToCurrentVersion() {
// Start a fresh database and apply only V1
Flyway flywayV1 = Flyway.configure()
.dataSource(
postgres.getJdbcUrl(),
postgres.getUsername(),
postgres.getPassword()
)
.locations("classpath:db/migration")
.target(MigrationVersion.fromVersion("1"))
.load();
flywayV1.migrate();
// Verify V1 state
assertThat(tableExists("orders")).isTrue();
assertThat(columnExists("orders", "customer_email")).isFalse();
// Now apply remaining migrations
Flyway flywayFull = Flyway.configure()
.dataSource(
postgres.getJdbcUrl(),
postgres.getUsername(),
postgres.getPassword()
)
.locations("classpath:db/migration")
.load();
flywayFull.migrate();
// Verify final state
assertThat(columnExists("orders", "customer_email")).isTrue();
}
private boolean tableExists(String tableName) {
Integer count = jdbcTemplate.queryForObject(
"SELECT COUNT(*) FROM information_schema.tables WHERE table_name = ?",
Integer.class,
tableName
);
return count != null && count > 0;
}
private boolean columnExists(String tableName, String columnName) {
Integer count = jdbcTemplate.queryForObject(
"SELECT COUNT(*) FROM information_schema.columns WHERE table_name = ? AND column_name = ?",
Integer.class,
tableName,
columnName
);
return count != null && count > 0;
}
Liquibase Testing
Liquibase works the same way — Spring Boot auto-runs changelogs on startup. PostgreSQLContainer + @ServiceConnection provides the real database.
Project Setup
src/main/resources/db/changelog/
├── db.changelog-master.xml
├── V1-create-orders-table.xml
└── V2-add-product-table.xml
<!-- db.changelog-master.xml -->
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog">
<include file="db/changelog/V1-create-orders-table.xml"/>
<include file="db/changelog/V2-add-product-table.xml"/>
</databaseChangeLog>
<!-- V1-create-orders-table.xml -->
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog">
<changeSet id="1" author="abhay">
<createTable tableName="orders">
<column name="id" type="BIGSERIAL">
<constraints primaryKey="true"/>
</column>
<column name="customer_id" type="VARCHAR(50)">
<constraints nullable="false"/>
</column>
<column name="status" type="VARCHAR(20)">
<constraints nullable="false"/>
</column>
<column name="total_amount" type="DECIMAL(10,2)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
</databaseChangeLog>
Testing Liquibase Changelogs
@SpringBootTest
@Testcontainers
@TestPropertySource(properties = {
"spring.liquibase.enabled=true",
"spring.jpa.hibernate.ddl-auto=validate"
})
class LiquibaseMigrationTest {
@Container
@ServiceConnection
static PostgreSQLContainer<?> postgres =
new PostgreSQLContainer<>("postgres:16-alpine");
@Autowired
private SpringLiquibase liquibase;
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
void shouldApplyAllChangeSetsSuccessfully() throws LiquibaseException {
Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(
new JdbcConnection(
DataSourceUtils.getConnection(
Objects.requireNonNull(liquibase.getDataSource())
)
)
);
Liquibase lb = new Liquibase(
liquibase.getChangeLog(),
new ClassLoaderResourceAccessor(),
database
);
List<ChangeSet> unrunChangeSets = lb.listUnrunChangeSets(null, null);
assertThat(unrunChangeSets).isEmpty();
}
@Test
void shouldCreateOrdersTable() {
Integer count = jdbcTemplate.queryForObject(
"SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'orders'",
Integer.class
);
assertThat(count).isEqualTo(1);
}
}
Testing Destructive Migrations
Destructive migrations — dropping columns, renaming tables, removing constraints — are the riskiest. Test them explicitly before production deployment.
-- V5__rename_customer_email_to_email.sql
-- PostgreSQL: safe to rename while data is present
ALTER TABLE orders RENAME COLUMN customer_email TO email;
@Test
void shouldRenameColumnWithoutDataLoss() {
// Insert data before migration
jdbcTemplate.execute(
"INSERT INTO orders (customer_id, status, total_amount, customer_email) " +
"VALUES ('c1', 'PENDING', 99.99, 'test@example.com')"
);
// Apply the rename migration
Flyway flyway = Flyway.configure()
.dataSource(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword())
.locations("classpath:db/migration")
.load();
flyway.migrate();
// Verify data survived the rename
String email = jdbcTemplate.queryForObject(
"SELECT email FROM orders WHERE customer_id = 'c1'",
String.class
);
assertThat(email).isEqualTo("test@example.com");
}
Common Pitfalls
Running migration tests with ddl-auto=create-drop. This creates the schema from entity model, then drops it — bypassing Flyway entirely. Set ddl-auto=validate or ddl-auto=none for migration tests.
Not testing the migration path from an older version. Testing from a clean database is necessary but not sufficient. The production database has an existing schema. Test that each migration applies cleanly on top of the previous state.
Ignoring migration execution time. Some migrations (adding an index to a large table, backfilling data) are slow. Measure migration execution time in tests and flag migrations that take more than a few seconds. A 10-minute migration blocks production deployment.
Summary
Flyway and Liquibase migrations tested against real databases catch dialect errors, schema drift, and destructive migration bugs before they reach production. PostgreSQLContainer with @ServiceConnection provides the target database. spring.jpa.hibernate.ddl-auto=validate catches model/schema drift at context load time.
The next article covers Kafka integration testing — producers, consumers, dead-letter topics, and consumer group behavior.