How can i use Flyway migration with single schema and multiple projects

Question

How to manage multiple projects dealing with the same DB schema. The Flyway migration scripts in each of the project does not allow to start if it is modified by the other project.

For example:

I have a spring boot Project X with a FlywayInitializer class.

   @PostConstruct
    public void migrateFlyway() {
        final Flyway flyway = new Flyway();

        flyway.setSchemas("schema1");
        flyway.setLocations("classpath:x.migration");
        flyway.migrate();
    }

And i have a submodule Project Y with also his own FlywayInitializer class

   @PostConstruct
    public void migrateFlyway() {
        final Flyway flyway = new Flyway();

        flyway.setSchemas("schema1");
        flyway.setLocations("classpath:y.migration");
        flyway.migrate();
    }

Project Structure:

Project X
    src
      |
      main
          |
           java 
                FlywayInitializerX.java 
          |
           resources
               V1.0_create_tableX.sql
               V1.1_update_tableX.sql 

Project Y 
    src
      |
      main
          |
           java 
                FlywayInitializerY.java 
          |
            resources
               V1.0_create_tableY.sql 
               V1.1_update_tableY.sql

How can i use the for both Project X and Y the same schemaname "schema1" with Flyway ?

EDIT: Thanks @jesper_bk that helped me. Its exactly what i wanted, that the two projects have completely "independent lives" in the same schema. But now i have the following problem:

The first executed project X create tables correcty, but if Project Y is started i get the error Found non-empty schema without metadata table. So i have to set BaselineOnMigrate to true. But if i set BaselineOnMigrate to true the Project Y skip the sql file V1.0_create_tableY.sql and starts with V1.1_update_tableY.sql. How can i reach, that the first sql script V1.0_create_tableY.sql is also executed for Project Y?

 @PostConstruct
    public void migrateFlyway() {
        final Flyway flyway = new Flyway();

        flyway.setBaselineVersionAsString("1");
        flyway.setBaselineOnMigrate(true);

        flyway.setSchemas("schema1");
        flyway.setLocations("classpath:y.migration");
        flyway.migrate();

    }

Concerning your second question I am not completely certain, but I believe baselineVersionAsString should be < 1 (e.g. 0). If the baselibne version is 1, it will determine that your first script with version 1.0 matches the baseline, and should already have been executed. — jesper_bk
No problem. Added the comment to the solution for posterity. — jesper_bk

jesper_bk jesper_bk · Accepted Answer · 2018-04-10T12:57:29

If you can live with two projects having completely "independent lives" in the same schema, you could use separate version tables for the two, i.e.:

@PostConstruct
public void migrateFlyway() {
    final Flyway flyway = new Flyway();

    flyway.setSchemas("schema1");
    flyway.setLocations("classpath:x.migration");
    flyway.setTable("schema_version_y");
    flyway.migrate();
}

If you want them to use the same versioning scheme, you are probably better served with putting all SQL scripts in separate third project, or - even more complicated - have a third project that automatically gathers and enumerates the SQL scripts from the main project.

Concerning your second question, baselineVersionAsString should be < 1 (e.g. 0). If the baseline version is 1, it will determine that your first script with version 1.0 matches the baseline, and should already have been executed.

How can i use Flyway migration with single schema and multiple projects

2 Answers