Spring-boot: Document how to insert test data with flyway

[derylseale]

When I include the Flyway library in my classpath, Spring Boot correctly locates and executes my migrations. It does not, however, make use of my data.sql script, which I use to import fixture data for unit tests. Per the documentation, I am setting the jpa.hibernate.ddl-auto property to false so that Hibernate doesn't try to generate the schema itself. If I remove this property, then Spring boot _does_ load the data.sql file, but Hibernate is also clearly trying to generate the schema redundantly, which is at the very least unnecessary but also probably error prone.

Is there a way to use Flyway along with the normal Spring Boot datasource initialization features?

2015-03-30 16:04:09.851  INFO 56097 --- [    Test worker] o.f.core.internal.util.VersionPrinter    : Flyway 3.2.1 by Boxfuse
2015-03-30 16:04:10.113  INFO 56097 --- [    Test worker] o.f.c.i.dbsupport.DbSupportFactory       : Database: jdbc:h2:mem:testdb (H2 1.4)
2015-03-30 16:04:10.169  INFO 56097 --- [    Test worker] o.f.core.internal.command.DbValidate     : Validated 2 migrations (execution time 00:00.013s)
2015-03-30 16:04:10.183  INFO 56097 --- [    Test worker] o.f.c.i.metadatatable.MetaDataTableImpl  : Creating Metadata table: "PUBLIC"."schema_version"
2015-03-30 16:04:10.209  INFO 56097 --- [    Test worker] o.f.core.internal.command.DbMigrate      : Current version of schema "PUBLIC": << Empty Schema >>
2015-03-30 16:04:10.209  INFO 56097 --- [    Test worker] o.f.core.internal.command.DbMigrate      : Migrating schema "PUBLIC" to version 1 - create user info tables
2015-03-30 16:04:10.242  INFO 56097 --- [    Test worker] o.f.core.internal.command.DbMigrate      : Migrating schema "PUBLIC" to version 2 - create recipe tables
2015-03-30 16:04:10.251  INFO 56097 --- [    Test worker] o.f.core.internal.command.DbMigrate      : Successfully applied 2 migrations to schema "PUBLIC" (execution time 00:00.070s).
2015-03-30 16:04:10.364  INFO 56097 --- [    Test worker] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
2015-03-30 16:04:10.378  INFO 56097 --- [    Test worker] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
    name: default
    ...]
2015-03-30 16:04:10.490  INFO 56097 --- [    Test worker] org.hibernate.Version                    : HHH000412: Hibernate Core {4.3.8.Final}
2015-03-30 16:04:10.492  INFO 56097 --- [    Test worker] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2015-03-30 16:04:10.495  INFO 56097 --- [    Test worker] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
2015-03-30 16:04:10.795  INFO 56097 --- [    Test worker] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {4.0.5.Final}
2015-03-30 16:04:10.853  INFO 56097 --- [    Test worker] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.H2Dialect
2015-03-30 16:04:10.990  INFO 56097 --- [    Test worker] o.h.h.i.ast.ASTQueryTranslatorFactory    : HHH000397: Using ASTQueryTranslatorFactory
2015-03-30 16:04:11.255  INFO 56097 --- [    Test worker] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000227: Running hbm2ddl schema export
2015-03-30 16:04:11.267  INFO 56097 --- [    Test worker] org.hibernate.tool.hbm2ddl.SchemaExport  : HHH000230: Schema export complete
2015-03-30 16:04:11.348  INFO 56097 --- [    Test worker] o.s.jdbc.datasource.init.ScriptUtils     : Executing SQL script from URL [file:/redacted/main/data.sql]

[philwebb]

It looks like this is currently intentional. See DataSourceInitializedPublisher.isInitializingDatabase(). I imagine that it simply changing this code would probably not solve your issue as you want to run data.sql scripts after Flyway.

You might be able to add your own ResourceDatabasePopulator bean to add your test data.

/cc @dsyer in case he has any ideas.

[farko88]

Try setting spring.jpa.hibernate.ddl-auto=none

Reference from documentation:

spring.jpa.hibernate.ddl-auto
java.lang.String

DDL mode ("none", "validate", "update", "create", "create-drop"). This is actually a shortcut for the "hibernate.hbm2ddl.auto" property. Default to "create-drop" when using an embedded database, "none" otherwise.

[derylseale]

Actually, I tried that -- I incorrectly wrote false in the issue above when I meant to write none. That does pick up data.sql, but it's also trying to create the schema after Flyway runs. In simple cases, that's just redundant and it has nothing to do, but I'm concerned that there will be a conflict as the code grows in complexity.

[mdoninger]

The following configuration works for a project of mine (using Flyway to create the tables and then data.sql to populate them with multiple test data.sql files):

spring.jpa.properties.hibernate.hbm2ddl.auto=validate
spring.datasource.initialize=true
spring.datasource.data=classpath*:data-from-migration.sql,classpath*:data.sql,classpath*:data-gen.sql,classpath*:data-personas.sql

Every bean related to Hibernate and Datasources is configured by the respective autoconfigurations, we override only the Flyway bean, because we run a clean before migrate. Maybe you can try those properties (you can leave the spring.datasource.data out, if you only use the default data.sql file).

[derylseale]

Yes, I've tried that as well. The issue there is that I use MySQL for a live application, but an embedded database for testing, and the validation fails in the latter case due to incomplete MySQL emulation. That's clearly a consequence of a design decision I've made, but it's to support a quick testing loop. At this point, the only option I have is described above.

[aahlenst]

A Flyway SQL callback (http://flywaydb.org/documentation/callbacks.html) placed in src/test/resources might be of help. Create a file named afterMigrate.sql in src/test/resources/db/migration with your initialization script.

[derylseale]

Thanks, that looks promising. I'll take a look.

[aaguilera]

Thanks, @aahlenst. Your workaround works for me. In my current setup (I use Maven), migration callbacks under src/test/resources/db/migration are run only during the "test" phase, i.e. when running mvn test, which is just fine for me.

[wortner]

Actually it seams it started to work for me after I have completely omitted spring.jpa.hibernate.ddl-auto from application configuration, before even filling in the empty or "none" value was not enough.
After that the data.sql has been executed after flyway migration

[Turbots]

I have a similar issue at the moment.

All the Oracle databases at the client (DEV,STG,PRD) have separate users for DDL and DML, so I cannot execute schema.sql files at startup of my application.

This piece of code is the cause:

https://github.com/spring-projects/spring-boot/blob/355860fd0970943e8e52a73bb8f9467f8e71f3e3/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/jdbc/DataSourceInitializer.java#L78-L96

private void runSchemaScripts() {
    List<Resource> scripts = getScripts(this.properties.getSchema(), "schema");
    if (!scripts.isEmpty()) {
        runScripts(scripts);
        try {
            this.applicationContext.publishEvent(new DataSourceInitializedEvent(this.dataSource));
            // The listener might not be registered yet, so don't rely on it.
            if (!this.initialized) {
                runDataScripts();
                this.initialized = true;
            }
        }
        catch (IllegalStateException ex) {
            logger.warn("Could not send event to complete DataSource initialization ("
                + ex.getMessage() + ")");
        }
    }
}

If possible, I would like my data.sql scripts to be executed, even when there are no schema.sql files.

Should I make a pull request for this? Maybe adding an extra property to force executing the data.sql scripts even when schema.sql scripts are absent?

[wilkinsona]

@Turbots Are you using Flyway?

[Turbots]

:blush:
No, mysql in my local profile and oracle on production profile. I should probably move this comment into a new issue, shouldn't I @wilkinsona :bowtie:

PS: I feel like an idiot for posting the above, but I'm just stuck with the separate DDL/DML users we have here. I made a stupid workaround where I basically put a dummy command in a schema-oracle.sql file, so that my data-oracle.sql gets executed. This script is idempotent and makes sure the database is in the same state every time I run my application. If anyone has suggestions on how to work with two datasources (DDL user datasource for create-dropping my tables and DML user for importing the data) that would be perfect :-)

[steve-taylor]

After switching from Liquibase to Flyway today, I came across this issue and solved it by overriding flyway.locations in my application-dev.yml:

flyway:
  locations: classpath:/db/migration,/db/data/dev

And my database migration scripts are arranged like this:

src/main/resources/
  db/
    data/
      dev/
        V0002__initial-data.sql
    migration/
      V0001__initial-schema.sql
      V0003__another-ddl-update.sql

[vayalaivadivel]

Hi, I getting below error while running spring boot with flyway and postgres sql. Any one can please help on to fix this issue,

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: Validate failed: Detected resolved migration not applied to database: 1.0
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1578)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:545)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:482)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:772)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:839)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:538)
    at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:118)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:766)
    at org.springframework.boot.SpringApplication.createAndRefreshContext(SpringApplication.java:361)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:307)
    at org.springframework.boot.test.SpringApplicationContextLoader.loadContext(SpringApplicationContextLoader.java:98)
    at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:98)
    at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:116)
    ... 25 more
Caused by: org.flywaydb.core.api.FlywayException: Validate failed: Detected resolved migration not applied to database: 1.0
    at org.flywaydb.core.Flyway.doValidate(Flyway.java:1000)
    at org.flywaydb.core.Flyway.access$100(Flyway.java:72)
    at org.flywaydb.core.Flyway$2.execute(Flyway.java:974)
    at org.flywaydb.core.Flyway$2.execute(Flyway.java:971)
    at org.flywaydb.core.Flyway.execute(Flyway.java:1361)

Thanks and Regards,
VADIVEL

[philwebb]

@vayalaivadivel Your best bet is to ask on stackoverflow.com. My guess is that your SQL file is not valid so it might be worth creating a small sample to show the problem.

[kjan]

Flyway hangs with Spring Boot 1.4.1.RELEASE and MySQL 5.7 when using Flyway with multiple schemas

2016-10-05 19:56:49.344  INFO 10932 --- [ost-startStop-1] o.f.core.internal.util.VersionPrinter    : Flyway 4.0.3 by Boxfuse
2016-10-05 19:56:49.622  INFO 10932 --- [ost-startStop-1] o.f.c.i.dbsupport.DbSupportFactory       : Database: jdbc:mysql://localhost/ (MySQL 5.7)
2016-10-05 19:56:49.674  INFO 10932 --- [ost-startStop-1] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.010s)
2016-10-05 19:56:49.686  INFO 10932 --- [ost-startStop-1] o.f.core.internal.command.DbSchemas      : Creating schema `test1` ...
2016-10-05 19:56:49.690  INFO 10932 --- [ost-startStop-1] o.f.core.internal.command.DbSchemas      : Creating schema `test2` ...
2016-10-05 19:56:49.694  INFO 10932 --- [ost-startStop-1] o.f.core.internal.command.DbSchemas      : Creating schema `test3` ...
2016-10-05 19:56:49.700  INFO 10932 --- [ost-startStop-1] o.f.c.i.metadatatable.MetaDataTableImpl  : Creating Metadata table: `test1`.`schema_version`
2016-10-05 19:56:49.772  INFO 10932 --- [ost-startStop-1] o.f.core.internal.command.DbMigrate      : Current version of schema `test1`: 0
2016-10-05 19:56:49.772  INFO 10932 --- [ost-startStop-1] o.f.core.internal.command.DbMigrate      : Migrating schema `test1` to version 0.93 - Create tables

my application.properties looks like this

flyway.enabled=true
flyway.url: jdbc:mysql://localhost/
flyway.user: root
flyway.password: pass
flyway.schemas=test1, test2,test3

spring.jpa.generate-ddl = false
#spring.jpa.hibernate.ddl-auto = none
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.properties.hibernate.globally_quoted_identifiers=true
spring.jpa.show-sql = true

[wilkinsona]

@kjan This issue has nothing to do with Flyway hanging. If you believe the hang is caused by Spring Boot, please open a new issue explaining why you think that's the case. If it's a general Flyway problem then please report it to the Flyway team.

[philwebb]

We'll update the documentation with the suggestion by @steve-taylor above