Migration files not executed in test

Question

I have test class which, if configured in this way, works

@DataJpaTest
@AutoConfigureEmbeddedDatabase(
 refresh = AutoConfigureEmbeddedDatabase.RefreshMode.BEFORE_EACH_TEST_METHOD,
 provider = AutoConfigureEmbeddedDatabase.DatabaseProvider.ZONKY
)
@TestPropertySource(
 properties = [
 "spring.flyway.enabled=true",
 "spring.jpa.hibernate.ddl-auto=none",
 "spring.flyway.locations=classpath:db/test",
 "spring.flyway.baseline-on-migrate=true",
 "logging.level.org.flywaydb=DEBUG",
 "logging.level.org.hibernate.SQL=DEBUG",
 "spring.jpa.properties.hibernate.format_sql=true"
 ]
)

In the folder test/db/test/ are two files, V1.0__setup_schema.sql and V2.0__insert_data.sql.

When I change the configuration to this

@DataJpaTest
@AutoConfigureEmbeddedDatabase(
 refresh = AutoConfigureEmbeddedDatabase.RefreshMode.BEFORE_EACH_TEST_METHOD,
 provider = AutoConfigureEmbeddedDatabase.DatabaseProvider.ZONKY
)
@Sql(
 "/db/migration/V1.0__2022_01_21_00__setup_schema.sql",
 "/db/test/V2.0__insert_data.sql",
 executionPhase = Sql.ExecutionPhase.BEFORE_TEST_CLASS
)

I get this error

Caused by: org.postgresql.util.PSQLException: ERROR: relation "foo" does not exist
 Position: 312
 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2736)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2421)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
 at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:525)
 at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:435)
 at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:196)
 at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:139)
 at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:251)

which I do not understand. V1.0__setup_schema.sql in test/db/test/ is a copy & paste of /db/migration/V1.0__2022_01_21_00__setup_schema.sql. It feels like @Sql is not loading the file to create the tables?

• Kent: You were talking about ...setup_schema.sql. However, in your test code, you used /db/migration/V1.0_2022_.... sql

Answer 1

@Sql(
 "classpath:/db/migration/V1.0__2022_01_21_00__setup_schema.sql",
 "classpath:/db/test/V2.0__insert_data.sql",
 executionPhase = Sql.ExecutionPhase.BEFORE_TEST_CLASS
)

Should work because @Sql needs to know where to find the files