How to set Flyway migration file location using Spring profiles

Question

I have two Spring profiles dev and test configured for development and test environment. And in each environment I am using different databases viz h2 in dev and postgresql in testing. Following are my properties files for each profile where {vendor} is resolved by spring boot to h2 and postgresql repectively as per datasource configured.

application-dev.properties

spring.flyway.locations=classpath:db/migration/{vendor}

application-test.properties

#Data source
spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

#Flyway
spring.flyway.check-location=false
spring.flyway.locations=classpath:/db/migration/test/{vendor}

Flyway migration files for dev profile are under test/resources and for test profile under main/resources

This is working fine when I run my application with test profile where it picks migration files only under main/resources. However, When I run my unit test using dev profile. I expect it to pick files only under src/test/resources/db/migration/h2. But Flyway is picking up migration files from main/resources and test/resources both leading to error

org.flywaydb.core.api.FlywayException: Found more than one migration with version 1

I don't understand this behavior. Any inputs on how to fix this?

Answer 1

So, here is how I did it.

Requirements:

1. Use Spring profiles to configure application for different environments viz dev, test and prod.
2. Use Spring profiles to load flyway migration files as per the environment.

Database per environement:

1. H2 database for dev environment.
2. postgresql database for test environment.
3. postgresql database for prod environment.

Configuration

1. Create Spring profiles dev, test and prod in pom.xml.

   <profiles> <profile> <id>dev</id> <activation> <activeByDefault>true</activeByDefault> </activation> </profile> <profile> <id>test</id> </profile> <profile> <id>prod</id> </profile> </profiles>

2. Create properties files for each profile

application-dev.properties

spring.flyway.locations=classpath:db/migration/{vendor}

Since, H2 database is configured by Spring boot when H2 driver is on classpath. We don't need to configure it explicitly.

application-test.properties

spring.datasource.url=jdbc:postgresql://localhost:5432/db_test
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

spring.flyway.locations=/db/{vendor}/common,/db/{vendor}/test

application-prod.properties

spring.datasource.url=jdbc:postgresql://localhost:5432/db_prod
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

spring.flyway.locations=/db/{vendor}/common,/db/{vendor}/prod

3. Flyway migration file locations.

If you will notice, I have not used db/migration under src/main/resources to place migration files which is the default location. For simple reason that Flyway picks all the files under this location and which results in version conflict between files for different environments. For e.g V2__data_insertion.sql is present for all three environments and this will not work if these were nested under db/migration. Since, H2 migration files pertain to default profile I have left them at the default flyway migration file location.

Hope that helps !!!