Multiple datasources migrations using Flyway in a Spring Boot application

Question

We use Flyway for db migration in our Spring Boot based app and now we have a requirement to introduce multi tenancy support while using multiple datasources strategy. As part of that we also need to support migration of multiple data sources. All data sources should maintain the same structure so same migration scripts should be used for migrating of all data sources. Also, migrations should occur upon application startup (as opposed to build time, whereas it seems that the maven plugin can be configured to migrate multiple data sources). What is the best approach to use in order to achieve this? The app already has data source beans defined but Flyway executes the migration only for the primary data source.

Jacob van Lingen · Answer 1 · 2018-11-06T11:31:09.830

To make @Roger Thomas answer more the Spring Boot way:

Easiest solution is to annotate your primary datasource with @Primary (which you already did) and just let bootstrap migrate your primary datasource the 'normal' way.

For the other datasources, migrate those sources by hand:

@Configuration
public class FlywaySlaveInitializer {

     @Autowired private DataSource dataSource2;
     @Autowired private DataSource dataSource3;
     //other datasources

     @PostConstruct
     public void migrateFlyway() {
         Flyway flyway = new Flyway();
         //if default config is not sufficient, call setters here

         //source 2
         flyway.setDataSource(dataSource2);
         flyway.setLocations("db/migration_source_2");
         flyway.migrate();

         //source 3
         flyway.setDataSource(dataSource3);
         flyway.setLocations("db/migration_source_3");
         flyway.migrate();
     }
}

Where would the sql files be put for the second database? How would they be differentiated? — Marci-man, Apr 06 '18 at 07:13
make sure to set `flyway.setBaselineOnMigrate(true)`. This enables flyway to setup its version tracking table for the migration scripts. — jrlmx2, Feb 18 '19 at 16:46

score 10 · Accepted Answer · edited Feb 11 '20 at 14:33

10

Flyway supports migrations coded within Java and so you can start Flyway during your application startup.

https://flywaydb.org/documentation/migration/java

I am not sure how you would config Flyway to target a number of data sources via the its config files. My own development is based around using Java to call Flyway once per data source I need to work against. Spring Boot supports the autowiring of beans marked as @FlywayDataSource, but I have not looked into how this could be used.

For an in-java solution the code can be as simple as

    Flyway flyway = new Flyway();

    // Set the data source
    flyway.setDataSource(dataSource);

    // Where to search for classes to be executed or SQL scripts to be found
    flyway.setLocations("net.somewhere.flyway");

    flyway.setTarget(MigrationVersion.LATEST);
    flyway.migrate();

edited Feb 11 '20 at 14:33

Jacob van Lingen

8,989
7
48
78

answered May 17 '16 at 16:54

Roger Thomas

818
7
8

Spring Boot relies on a series of adaptive auto-configuration classes to create and configure beans and run methods on then during startup. The flyway one can be found at {org.springframework.boot.autoconfigure.flyway.FlywayAutoConfiguration} so you can basically copy the code you need from this class. I'm just about to attempt the same thing. – Luke Aug 02 '16 at 09:00
Flyway has changed the way you can create a Flyway object : `Flyway flyway = Flyway.configure() .dataSource(dataSource).target(MigrationVersion.LATEST).load();` – Renis1235 Nov 23 '20 at 19:35

score 7 · Answer 3 · answered Feb 06 '20 at 14:31

7

Having your same problem... I looked into the spring-boot-autoconfigure artifact for V 2.2.4 in the org.springframework.boot.autoconfigure.flyway package and I found an annotation FlywayDataSource.

Annotating ANY datasource you want to be used by Flyway should do the trick.
Something like this:

@FlywayDataSource
@Bean(name = "someDatasource")
public DataSource someDatasource(...) {
        <build and return your datasource>
}

answered Feb 06 '20 at 14:31

RobMcZag

605
9
15

Multiple datasources seems to be supported as well, see https://renewinkler.net/2018/03/04/configuring-multiple-datasources-in-spring/. – Jacob van Lingen Feb 11 '20 at 14:35
1

@JacobvanLingen Yes, that was the goal of the uppercase "ANY" in my answer. :) Thank you for the link, in that case -if I understand correctly- he is using the annotation to point Flyway away from the Primary data source. Interesting use! He is defining a different datasource to be used by Flyway, with a different prefix. The flyway DS could be just a different user on the same DB (because the user from the primary DS can not execute the migrations) or a totally different DB to track what gets done by the migrations. – RobMcZag Feb 12 '20 at 15:55

score 0 · Answer 4 · answered Jun 01 '20 at 21:11

Found an easy solution for that - I added the step during the creation of my emf:

@Qualifier(EMF2)
@Bean(name = EMF2)
public LocalContainerEntityManagerFactoryBean entityManagerFactory2(
    final EntityManagerFactoryBuilder builder
) {
    final DataSource dataSource = dataSource2();
    Flyway.configure()
          .dataSource(dataSource)
          .locations("db/migration/ds2")
          .load()
          .migrate();
    return builder
        .dataSource(dataSource)
        .packages(Role.class)
        .properties(jpaProperties2().getProperties())
        .persistenceUnit("domain2")
        .build();
}

I disabled spring.flyway.enabled for that.

SQL files live in resources/db/migration/ds1/... and resources/db/migration/ds2/...

score 0 · Answer 5 · answered Feb 03 '22 at 16:07

This worked for me.

import javax.annotation.PostConstruct;
import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;

@Configuration
public class FlywaySlaveInitializer {

  @Value("${firstDatasource.db.url}")
  String firstDatasourceUrl;
  @Value("${firstDatasource.db.user}")
  String firstDatasourceUser;
  @Value("${firstDatasource.db.password}")
  String firstDatasourcePassword;

  @Value("${secondDatasource.db.url}")
  String secondDatasourceUrl;
  @Value("${secondDatasource.db.user}")
  String secondDatasourceUser;
  @Value("${secondDatasource.db.password}")
  String secondDatasourcePassword;


  @PostConstruct
  public void migrateFlyway() {
    Flyway flywayIntegration = Flyway.configure()
        .dataSource(firstDatasourceUrl, firstDatasourceUser, firstDatasourcePassword)
        .locations("filesystem:./src/main/resources/migration.first")
        .load();

    Flyway flywayPhenom = Flyway.configure()
        .dataSource(secondDatasourceUrl, secondDatasourceUser, secondDatasourcePassword)
        .locations("filesystem:./src/main/resources/migration.second")
        .load();

    flywayIntegration.migrate();
    flywayPhenom.migrate();
  }

}

And in my application.yml this property:

spring:
  flyway:
    enabled: false

Multiple datasources migrations using Flyway in a Spring Boot application

5 Answers5

Linked