Use H2 Database for Spring Test Profile with Flyway

Question

I'm attempting to setup my end-to-end tests to use an in memory database that can easily be brought up, shutdown, erased and seeded with test data. I am working on a spring project and am using flyway to migrate the database. When starting my spring server without any profiles, flyway correctly runs the migrations and all is good. However, when running in my "test" profile, the flyway migrations do not run.

application.properties

# Database Properties
spring.jpa.database=POSTGRESQL
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=validate
spring.database.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/mydb

# Data Rest Properties
spring.data.rest.basePath=/api

# Logging Properties
logging.level.root=WARN
logging.level.org.flywaydb=INFO
logging.level.com.myproj=INFO

application-test.properties

# Server Properties
server.port=8081

# Database Properties
spring.jpa.database=H2
spring.database.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:mydb-test

# Dev Tools Properties
spring.devtools.restart.enabled=false

# Flyway Properties
flyway.locations=classpath:db/migration,classpath:db/test_seed_data

This is the output I get when starting the spring server with the test profile:

 .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::             (v1.4.0.M2)

2016-05-11 23:01:16.052  INFO 86897 --- [  restartedMain] com.myproj.myprojApplicationKt           : Starting myprojApplicationKt on me.local with PID 86897 (/Users/me/Workspace/myproj/target/classes started by me in /Users/me/Workspace/myproj)
2016-05-11 23:01:16.054  INFO 86897 --- [  restartedMain] com.me.myprojApplicationKt               : The following profiles are active: test
2016-05-11 23:01:20.312 ERROR 86897 --- [ost-startStop-1] o.s.b.c.embedded.tomcat.TomcatStarter    : Error starting Tomcat context: org.springframework.beans.factory.UnsatisfiedDependencyException
2016-05-11 23:01:20.379  WARN 86897 --- [  restartedMain] ationConfigEmbeddedWebApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.context.ApplicationContextException: Unable to start embedded container; nested exception is org.springframework.boot.context.embedded.EmbeddedServletContainerException: Unable to start embedded Tomcat
2016-05-11 23:01:20.404 ERROR 86897 --- [  restartedMain] o.s.boot.SpringApplication               : Application startup failed

And the ultimate error is the validation failing (still fails to create the table when I turn off validation):

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing table [table-name]

Any idea why the migrations do not run for the "test" profile?

EDIT

Just realized that my migrations are written in PostgresQL and I'm expecting them to work with H2... I think that is obviously one issue. So expand this question to how to have the same migrations run on two different database types (if it's even possible)...

But why don't I get an error stating that Flyway tried to run the migration and that the database didn't accept the queries?

I used to use liquibase, which provides a DB-agnostic syntax for migrations (in XML, for example). Did you find any good solution with flyway? — Ko-Chih Wu

Hamish Carpenter Hamish Carpenter · Accepted Answer · 2017-07-31T22:53:31

This answer based on your question update; "how to have the same migrations run on two different database types". From the Flyway FAQ:

What is the best strategy for handling database-specific sql?

Assuming you use Derby in TEST and Oracle in PROD.

You can use the flyway.locations property. It would look like this:

TEST (Derby): flyway.locations=sql/common,sql/derby

PROD (Oracle): flyway.locations=sql/common,sql/oracle

You could then have the common statements (V1__Create_table.sql) in common and different copies of the DB-specific statements (V2__Alter_table.sql) in the db-specific locations.

From your config it looks like you already have a different location for test data so you're well on the way. Turn on debug with -X on command line to see logging for how flyway searches for migrations to help manage the three directories. I'm not sure how to do this from spring, this answer may help: logging-flyway-sql-with-spring-boot.

Use H2 Database for Spring Test Profile with Flyway

1 Answers