如何在Spring Boot中测试Flyway迁移？跨版本数据迁移正确性验证

Great question! Testing Flyway migrations in Spring Boot—especially data migrations like your People/Address example—is totally feasible with unit and integration tests, and it’ll eliminate the need for manual SQL checks in the long run. Let’s walk through how to do this properly.

First, let’s cover the basics of setting up your test environment, then dive into validating your specific data migration scenario.

1. Configure Your Test Environment #

We’ll use an in-memory database (like H2) for fast, isolated tests, and ensure Flyway handles schema creation instead of Hibernate. Here’s what you need:

Dependencies #

Add these to your build file (Maven example):

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

Test Configuration #

Create src/test/resources/application-test.properties to configure the test database and Flyway:

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=none # Critical: Disable Hibernate's auto-schema generation
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration # Path to your Flyway scripts

2. Basic Migration Execution Test #

First, write a test to confirm Flyway runs all migrations successfully and the schema is as expected. Use JdbcTemplate to query metadata:

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest(properties = "spring.profiles.active=test")
class FlywaySchemaValidationTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void testMigrationsCreateExpectedTablesAndColumns() {
        // Verify PEOPLE table exists with ADDRESSID column
        boolean peopleTableExists = jdbcTemplate.queryForObject(
            "SELECT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PEOPLE')",
            Boolean.class
        );
        assertTrue(peopleTableExists, "PEOPLE table should exist after migrations");

        boolean addressIdColumnExists = jdbcTemplate.queryForObject(
            "SELECT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'PEOPLE' AND COLUMN_NAME = 'ADDRESSID')",
            Boolean.class
        );
        assertTrue(addressIdColumnExists, "PEOPLE.ADDRESSID column should exist");

        // Verify ADDRESS table exists
        boolean addressTableExists = jdbcTemplate.queryForObject(
            "SELECT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ADDRESS')",
            Boolean.class
        );
        assertTrue(addressTableExists, "ADDRESS table should exist");
    }
}

3. Validating Your Specific Data Migration #

This is the critical part: ensuring data moves correctly from PEOPLE.street to ADDRESS, and the PEOPLE.addressId foreign key links properly. We’ll use Flyway’s API to control migration steps:

Step 1: Write Your V2 Migration Script #

First, make sure your V2 Flyway script (src/main/resources/db/migration/V2__Migrate_people_street_to_address.sql) handles the data migration:

-- Create ADDRESS table
CREATE TABLE ADDRESS (
    id INT AUTO_INCREMENT PRIMARY KEY,
    street VARCHAR(255) NOT NULL
);

-- Migrate unique street values to ADDRESS
INSERT INTO ADDRESS (street)
SELECT DISTINCT street FROM PEOPLE WHERE street IS NOT NULL;

-- Add ADDRESSID column to PEOPLE
ALTER TABLE PEOPLE ADD COLUMN addressId INT;

-- Link PEOPLE to their corresponding ADDRESS
UPDATE PEOPLE p
SET addressId = (SELECT id FROM ADDRESS a WHERE a.street = p.street);

-- Remove old STREET column from PEOPLE
ALTER TABLE PEOPLE DROP COLUMN street;

Step 2: Write the Data Migration Test #

This test will:

1. Reset the database to a clean state
2. Migrate only to V1 (your initial schema)
3. Insert test data into the V1 PEOPLE table
4. Run the V2 migration
5. Validate data was migrated correctly

import org.flywaydb.core.Flyway;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest(properties = "spring.profiles.active=test")
class PeopleAddressDataMigrationTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private Flyway flyway;

    @BeforeEach
    void setupCleanEnvironment() {
        // Reset database to ensure no leftover data from previous tests
        flyway.clean();
        // Migrate only to V1 (replace "1" with your actual V1 version number if needed)
        flyway.migrate("1");

        // Insert V1 test data
        jdbcTemplate.update("INSERT INTO PEOPLE (id, name, street) VALUES (1, 'Alice', 'Main St 123')");
        jdbcTemplate.update("INSERT INTO PEOPLE (id, name, street) VALUES (2, 'Bob', 'Oak Ave 456')");
        jdbcTemplate.update("INSERT INTO PEOPLE (id, name, street) VALUES (3, 'Charlie', 'Main St 123')"); // Duplicate street
    }

    @Test
    void testDataIsMigratedCorrectly() {
        // Run all remaining migrations (including V2)
        flyway.migrate();

        // 1. Verify ADDRESS table has unique street entries
        long addressCount = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM ADDRESS", Long.class);
        assertEquals(2, addressCount, "ADDRESS should have 2 unique entries");

        // 2. Verify Alice's address links correctly
        String aliceStreet = jdbcTemplate.queryForObject(
            "SELECT a.street FROM ADDRESS a JOIN PEOPLE p ON a.id = p.addressId WHERE p.id = 1",
            String.class
        );
        assertEquals("Main St 123", aliceStreet);

        // 3. Verify Charlie shares the same address as Alice
        Integer charlieAddressId = jdbcTemplate.queryForObject(
            "SELECT addressId FROM PEOPLE WHERE id = 3",
            Integer.class
        );
        Integer aliceAddressId = jdbcTemplate.queryForObject(
            "SELECT addressId FROM PEOPLE WHERE id = 1",
            Integer.class
        );
        assertEquals(aliceAddressId, charlieAddressId, "Charlie should share Alice's address");

        // 4. Verify PEOPLE.STREET column is gone
        boolean streetColumnExists = jdbcTemplate.queryForObject(
            "SELECT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'PEOPLE' AND COLUMN_NAME = 'STREET')",
            Boolean.class
        );
        assertFalse(streetColumnExists, "PEOPLE.STREET column should be dropped");

        // 5. Verify no PEOPLE entries have null addressId
        long peopleWithNullAddress = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM PEOPLE WHERE addressId IS NULL",
            Long.class
        );
        assertEquals(0, peopleWithNullAddress, "All PEOPLE entries should have an addressId");
    }
}

Key Tips for Reliable Migration Tests #

• Isolate Tests: Use flyway.clean() in @BeforeEach to ensure every test starts with a fresh database.
• Test Edge Cases: Include scenarios like duplicate streets, null street values, or empty tables to ensure your migration handles them gracefully.
• Use Flyway API: Controlling migration steps (migrate to V1, insert data, migrate to V2) lets you test the exact migration flow, not just the end state.
• Avoid Hibernate Auto-Gen: Setting spring.jpa.hibernate.ddl-auto=none ensures Flyway is the sole source of schema truth, matching production behavior.

内容的提问来源于stack exchange，提问作者MrNetroful