Multi Tenant Database Migration with Flyway in Spring Application

3 min readJan 9, 2020

Multiple Datasource based Migration with Flywaydb

Flyway is very useful, lightweight and handy for daily database migration. In production environment, we don’t allow any ORM framework to change database ddl structure. And sometimes we need to update some data. To do this, we normally write queries and run it into database server. It goes separately to build process. If there is multiple databases, then it would be more burden. Integrating it with application life cycle will remove the burden of running query and ease the developer’s life.

Pre-requisite

A project with the implementation of database multi tenancy.
The project will be a Spring Framework application.
The project may use JPA or not (In this article we have used JPA).
The project will be a Maven project.
Existing Database (Optional) — we have used MySQL here.

Configuration

Add the following dependency into pom file.

<dependency>
 <groupId>org.flywaydb</groupId>
 <artifactId>flyway-maven-plugin</artifactId>
 <version>6.1.4</version>
</dependency>

We have used flyway of version 6.1.4, for Spring boot application, there is no need to use version number. There is no need to add any other dependency for Flyway integration.

Flyway Migration

The following class will handle all migration relation task of multi tenant database. In my case, I’ve implemented Datasource based multi tenancy. So, it uses datasource that is stored in RoutingDataSource which is the implementation of AbstractRoutingDataSource.

public class FlywayMigrationInitializer {

    @Inject
    private RoutingDataSource dataSource;

    public void migrate() {
        String scriptLocation = "db/migration";

        for (Tenant tenant : Tenant.values()) {
            String dbName = tenant.getName();

            Flyway flyway = Flyway.configure()
                    .locations(scriptLocation)
                    .baselineOnMigrate(Boolean.TRUE)
                    .dataSource(dataSource.
                                getDataSourceByTenant(tenant))
                    .schemas(dbName)
                    .load();

            flyway.migrate();
        }
    }
}

Here, we have a directory db/migration in resource (main/resource) directory. Tenant — is a enum which holds all tenant. Let assume that, database name is the same as tenant name. RoutingDataSource — holds all datasource which we implemented in the time of database multi tenancy. An example of routing data source would be look like this:

public class RoutingDataSource extends AbstractRoutingDataSource {
    private static final Map<Object, Object> dataSourceMap = new HashMap<>();    // other methods and logics goes here    public DataSource getDataSourceByTenant(Tenant tenant) {
        return (DataSource) dataSourceMap.get(tenant);
    }    // other methods and logics goes here
}

Init Migration

In the Spring configuration file, where we have configured all repository related beans and properties, we have to init our migration method like the following ways…

@Bean(initMethod = "migrate")
public FlywayMigrationInitializer flywayMigrationInitializer() {
    return new FlywayMigrationInitializer();
}

Also in the configuration file add DependsOn annotation before entity manager factory bean, it will ensure that, before initialising JPA entity manager, system will trigger migration process.

@Bean
@DependsOn(value = "flywayMigrationInitializer")
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
    //logic goes here
}

SQL Files

Here, we have used sql based versioned migration, so, we have to put sql file in main/resources/db/migration directory in the following format:

V2__query.sql

V represents version, 2 — version number. As we use baselineOnMigrate, so flyway treat current database as base and will not change anything. If we want to initialise databases (no existing database structure), we have to start version number from V1 which may contains queries of initial database structure. The “__” double underscore separate version number from file name. And .sql indicates sql based migration.

Run

When we run the project, Flyway runs before creating JPA entity manager and run the queries. Also, Flyway will create a table named flyway_schema_history to maintain its query execution history. Data of the history table should be looks like following:

flyway_schema_history table

If there is no file in the mentioned directory, at first run it will create the BASELINE row Only. After running once, it will not run the same query again.

Source

Full source code is available in the github repository.