Migrating multiple schemas with distinct life-cycle using Flyway and Spring Boot

Question

The Flyway FAQ separates three cases of multiple schemas:

1. Multiple identical schemas
2. The schemas are distinct, but have the same life-cycle
3. The schemas have a distinct life-cycle or must be autonomous and cleanly separated

We're building a multi-module Spring Boot 4.5.9 project using Maven. Each module is entirely independent, and has its own database schema. All schemas reside on a single database, hence there's only one Spring Data Source.

Because the modules are independent, we want to manage their respective schema migrations separately, and so option (3) above is the most appropriate.

However, I couldn't find a way to configure Spring Boot's Flyway integration in the way the Flyway FAQ suggests:

Use multiple Flyway instances. Each instance manages its own schema and references its own schema history table. Place migrations for each schema in a distinct location.

Ideally, each module would have its own db/migration folder with it's own migration SQL scripts. The versions of each module's script should be independent of script versions in other modules, and the migration history of each module should be stored in a table in that module's schema.

If I put migration scripts in each module's resources/db/migration folder, flyway detects them but then complain that:

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

Anybody knows how one might accomplish the required setup?

P.S. the end goal of all of this is to be able to (one day, when the system scales) to pull these modules into separate services, without going through hell to break the database to multiple parts.

Answer 1

This is a length answer with two approach that work.

• a convention based versioning with out of order execution
• multiple schemas

Convention Based Approach

One way that this can be done is to follow a convention of assigning each module a version number and then have all the modules migrations be minor versions of the major version. For example assume that you have the following module structure.

• config module containing common spring boot configs all other modules inherit from this module. This is module where application.yml will be kept
• user module containing the user registration module, user depends on config
• email module containing code for sending emails in the background, email depends on config

The user module will have a db/migration folder with the following files in it.

• V2.001__create_users_schema.sql
• V2.002__create_account_tables.sql
• V2.003__create_x_tables.sql

The email module will have a db/migration folder with the following files in it

• V3.001__create_email_schema.sql
• V3.002__create_outbox_table.sql

With the above versioning convention you can always go back and add a new module specific migration. For example after the above migrations are applied you can add a V2.004__create_y_table.sql and flyway will fill in the migration between V2.003 and V3.0001

You will need to configure flyway to allow out order migrations otherwise you will get an error. in boot you can set.

spring:
  flyway:
    out-of-order: true

The key thing is that the first migration file for each module start by issuing a CREATE SCHEMA statement then follow up migrations include the schema name in all DDL statements or object references.

For example V2.001__create_users_schema.sql contains

CREATE SCHEMA users;

and V2.002__create_account_tables.sql contains

CREATE TABLE users.login(
    username text
);

Notice that the name passed to the CREATE TABLE is users.login which includes the schema name.

By using separate schemas for each module it will be easier to extract modules and their db schemas into separate binaries in the future. Since spring boot is using a single database connection pool you must have 1 database user that has access to the schemas of all the modules. This requires vigilance to make sure the following things don't happen accidentally.

• write a view, query, stored procedure that references multiple schemas
• have one module insert data into another module's schema
• have @Transactional methods in different modules call each other. This one is the easiest to mess up since modules will typically call each other.You can try to work around this issue in two ways. Option 1 All @Service classes are package protected, modules call each other over HTTP only. Option 2 All @Service class methods use requires new trascation propagation @Transactional(propagation = Propagation.REQUIRES_NEW). I think option 1 is better if the goal is to eventually extract to a microservices architecture.

This approach works with PostgreSQL not sure how well it works with other databases and is based on the the FAQ https://flywaydb.org/documentation/faq#hot-fixes

Multiple Flyway Instances

SpringBoot looks for a bean of type FlywayMigrationStrategywhen its time to perform the migration. You can implement this interface ignore the app level migration and create several module specific migrations, code below works.

import org.flywaydb.core.Flyway;
import org.springframework.boot.autoconfigure.flyway.FlywayMigrationStrategy;
import org.springframework.stereotype.Component;

@Component
public class MultiModuleFlywayMigrationStrategy implements FlywayMigrationStrategy {


  @Override
  public void migrate(Flyway flyway) {
    var  dataSource = flyway.getConfiguration().getDataSource();
    Flyway testModule = Flyway.configure()
        .schemas("test")
        .locations("db/test")
        .dataSource(dataSource).load();

    Flyway ratingsModule = Flyway.configure()
        .schemas("rating")
        .locations("db/ratings")
        .dataSource(dataSource).load();

    // don't call flyway.migrate() since we don't want any migrations in db/migration
    testModule.migrate();
    ratingsModule.migrate();

  }
}