使用H2数据库与Flyway的Spring Boot应用测试数据初始化失败

Let's break down why your seed data isn't loading even though Flyway migrations worked, and fix it step by step.

First, let's recap your configuration for reference:

spring:
  datasource:
    url: jdbc:h2:mem:appdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false
  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
  flyway:
    url: jdbc:h2:mem:appdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false
    user: "root"
    password: ""
    locations: classpath:/db/migration, classpath:/db/seed

Common Causes & Fixes #

1. Seed Script Naming Violates Flyway Conventions #

Flyway relies strictly on file naming rules to execute scripts in order. If your seed files don't follow these rules, they'll be completely ignored:

• One-time seed data: Use the format V{version}__{description}.sql (e.g., V1.1__insert_initial_users.sql). The version number must be higher than your migration scripts to ensure it runs after tables are created.
• Repeatable seed data (runs every time the app starts): Use R__{description}.sql (e.g., R__refresh_reference_data.sql).

Double-check that your seed files in classpath:/db/seed match these patterns—even a missing underscore or wrong version number will cause Flyway to skip them.

2. SQL Syntax Issues in Seed Scripts #

Since you're using H2 with DATABASE_TO_UPPER=false, there are a few critical gotchas:

• Case sensitivity: Your seed script's table/column names must exactly match what's in your migration scripts (e.g., if you created a user table, don't reference USER in the seed).
• H2-specific syntax: Avoid database-specific syntax from MySQL/PostgreSQL. For example, H2 uses STRING instead of VARCHAR(MAX), and IDENTITY instead of AUTO_INCREMENT.
• String quoting: Make sure you're using single quotes for string values ('admin' instead of "admin").

3. Missing Flyway Debug Logs #

You need to see what Flyway is actually doing with your seed scripts. Add this to your application.yml to enable debug logging:

logging:
  level:
    org.flywaydb: DEBUG

When you restart the app, check the logs for lines like:

• Found candidate script: db/seed/V1.1__insert_initial_users.sql (means the script was detected)
• Successfully executed SQL script or specific error messages (like table not found, syntax error)

This will tell you exactly why the seed step is failing.

4. Incorrect Flyway User Permissions #

While H2 in-memory databases are permissive by default, your configured flyway.user: root might not have the right permissions to insert data. Try switching to the default H2 user sa (with empty password) to rule this out:

flyway:
  user: "sa"
  password: ""

5. Classpath or Directory Structure Issues #

Ensure your db/seed directory is properly placed in your project (e.g., under src/main/resources for a Maven/Gradle project). If the directory is missing or misnamed, Flyway won't pick up the scripts.

Quick Testing Tip #

After making changes, you can use the H2 console to verify if tables exist and seed data is present. Add this to your config to enable the console:

spring:
  h2:
    console:
      enabled: true

Access it at http://localhost:8080/h2-console, use your datasource URL (jdbc:h2:mem:appdb) and user/password to log in.

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