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)

[felix4321]

@gbrehmer I'm having exactly the same problem.

[wilkinsona]

Sorry for the inconvience. This only really worked in 2.4 and earlier by accident rather than design. We used to document that it wouldn't work at all, but refined that to more accurately reflect the (rather complex) reality. Since that update in Spring Boot 2.3.x, the documentation has said the following:

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.

In short, you should move away from using both schema.sql and Liquibase at the same time with any version of Spring Boot as soon as you can. In the meantime, we plan to make a small change that will allow you to fine tune database initialization ordering with a bean factory post-processor of your own:

@Bean
@Order(Ordered.LOWEST_PRECEDENCE)
static BeanFactoryPostProcessor databaseInitializationReordering() {
    return (beanFactory) -> {
        modifyDependencies(beanFactory.getBeanDefinition("liquibase"),
                (dependencies) -> dependencies.add("dataSourceScriptDatabaseInitializer"));
        modifyDependencies(beanFactory.getBeanDefinition("dataSourceScriptDatabaseInitializer"),
                (dependencies) -> dependencies.remove("liquibase"));
    };
}

static void modifyDependencies(BeanDefinition definition, Consumer<Set<String>> modifier) {
    String[] dependsOn = definition.getDependsOn();
    Set<String> dependencies = new LinkedHashSet<>(
            dependsOn != null ? Arrays.asList(dependsOn) : Collections.emptySet());
    modifier.accept(dependencies);
    definition.setDependsOn(dependencies.toArray(new String[0]));
}

This'll make it possible to restore 2.4.x's ordering with 2.5 but it should only be relied upon in the short-term and, as I said above, you should move to using a single database initialization tool as soon as you can.

[gbrehmer]

Ok so we need a new solution to reduce test duration. and that will probably be a difficult task
we currently keep all db-changesets since beginning (e.g. for documentation)

[philwebb]

Perhaps you can write a JUnit test extension that could prepare your database before the Spring ApplicationContext is initialized. The actual code used to run the script is pretty minimal. You might even be able to use the @Sql annotation from org.springframework.test.context.jdbc to do this already (although I'm not sure exactly when those scripts run).

[gbrehmer]

@philwebb thanks for the hints. @Sql was not working but I found a pretty simple solution for H2

you can add init script to h2 connection string: ;INIT=RUNSCRIPT FROM './src/test/resources/schema.sql' and if you want to use the schema.sql filename you have to disable the auto init feature with spring.sql.init.enabled: false

Because init command is called for every connection, it should only be added to the liquibase url spring.liquibase.url
Otherwise it will produce problems e.g. in async threads or nested transactions.