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:

spring-boot/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/jdbc/DataSourceInitializer.java

Lines 78 to 96 in 355860f

	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() + ")");
	}
	}
	}

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]

😊
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

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