Restore run order of `data.sql`/`schema.sql` against high-level database migration tools

[michael-simons]

Spring Boot 2.5 changed the order in which high-level database migration tools like Flyway and Liquibase and the Spring specific data.sql and schema.sql are run.

Prior to 2.5 one was able to define a schema via Flyway and add an additional data.sql to populate the scheme. While this is not that useful in production, it is very valuable during tests.

For example, here's a schema from Flyway: https://github.com/michael-simons/biking2/tree/3d753e6c1dee236c81170ce4933223ce1379d2e8/src/main/resources/db/migration

In the test resources is a data.sql https://github.com/michael-simons/biking2/blob/5694e1c85f4df0961a106f586c3911954a99ab15/src/test/resources/data.sql which will be picked up via @DataJpaTest.

This works because first Flyway (or Liquibase) runs, create the schema and then the inserts are run.

While I completely agree that having a scheme.sql AND a migration tool doesn't make sense or even is harmful, combining migrations and data isn't.

Together with spring.datasource.initialization-mode=never (see this test one was able to selectively disable any additional data with one simple property.

In the case of Flyway (and my small example (I do have more, I like that pattern)), I can rework this basically with that idea

mkdir -p src/test/resources/db/migration/ && mv src/test/resources/data.sql src/test/resources/db/migration/R__add-test-data.sql

using a repeatable migration, but that is brittle and will start to cause issues when there are repeatables in production already, as one has to fiddle with their ordering.

So my suggestion is to restore the old order or provide an additional property like spring.jpa.defer-datasource-initialization but for Flyway/Liquibase with the same result.

I also hope you do reconsider this plan stated in the 2.5 docs:

If you are using a Higher-level Database Migration Tool, like Flyway or Liquibase, you should use them alone to create and initialize the schema. Using the basic schema.sql and data.sql scripts alongside Flyway or Liquibase is not recommended and support will be removed in a future release.

Thank you.

Edit: For Flyway there is an actual solution via Callbacks: https://flywaydb.org/documentation/concepts/callbacks (Via Richard on Twitter)

Here's my (final) solution: michael-simons/biking2@3dc263d The use case described above can be solved with Flyway alone. I don't know about Liquibase enough to propose something with it.

[philwebb]

We talked about this issue today and decided that we'd like to restore the old order (if it's not too hard) so that upgrades are easier.

We don't want to remove the documentation note since we still think it's best not to mix technologies but we'll try to add a how-to note describing test-only migrations (see #26796).

[gbrehmer]

Probably this change has broken our build test pipeline, because it was working with <= 2.5.0. we use schema.sql to prepopulate our test db. this sql file is generated by a nightly job based on the current master branch, an empty test db and all liquibase diff scripts. Then in a feature branch schema.sql is executed first and after that only new(!) liquibase diff files must be executed. So its used to reduce test run time.
Now with 2.5.1 schema.sql file is executed after(!) liquibase. So all tests are failing, because schema.sql contains most of the changes which are already applied by liquibase due to the order change.
It is possible to restore old <= 2.5.0 order? (additional configuration is okay)