trying to solve IT problems

In our project we selected Flyway to handle out database migrations.

This is a great library which can easily be configured to do the database migrations for you. For example using Spring all you need is:

<bean class="org.flywaydb.core.Flyway" init-method="migrate">
    <property name="dataSource" ref="dataSource" />
    <property name="locations" value="scripts" />
</bean>

Basically you point it to the data source and the package where the migration scripts can be found (or migration classes).

In our setup, the migrations script have names like the following (conforming to the default conventions):

• V001__initial_schema.sql
• V002__initial_data.sql
• V003__additional_script.sql

Flyway will check if the selected database schema (default is “public”) is empty. If it is, the table which stores information about the database migrations is created and the found migrations are run. If the schema exists, it will use the table to figure out which migrations need to be performed. As a sanity check it verifies whether there are any conflicts between previous migrations and currently available migrations.

Unfortunately this does not work in our setup.

• The project has multiple modules which all have their own migration scripts but share the public schema.
• We also use Activiti which manages its database structure itself, creating the tables before Flyway kicks in. This causes Flyway to stop as the migrations table does not exit and the schema is non-empty.

Fortunately Flyway allows for this case using the following configuration (shown here for two modules).

<bean class="org.flywaydb.core.Flyway" init-method="migrate">
    <property name="dataSource" ref="dataSource" />
    <property name="table" value="module1_schema_version" />
    <property name="locations" value="scripts/module1" />
    <property name="initOnMigrate" value="true" />
    <property name="initVersion">
        <bean class="org.flywaydb.core.api.MigrationVersion">
            <constructor-arg index="0" value="000" />
        </bean>
    </property>
</bean>

<bean class="org.flywaydb.core.Flyway" init-method="migrate">
    <property name="dataSource" ref="dataSource" />
    <property name="table" value="module2_schema_version" />
    <property name="locations" value="scripts/module2" />
    <property name="initOnMigrate" value="true" />
    <property name="initVersion">
        <bean class="org.flywaydb.core.api.MigrationVersion">
            <constructor-arg index="0" value="000" />
        </bean>
    </property>
</bean>

There are a couple of tricks.

• We use different tables for the migration info for each of the modules (using “table”).
• We use a different classpath location for the migration script for each of the modules (using “locations”).
• We assure Flyway always initialises itself (using “initOnMigrate”). This assures the migrations table is always created if it does not exist.
• Assure all out scripts are run using “initVersion” setting it to “000”. When initOnMigrate is true, a record is written in the migratinos table to indicate that Flyway init was run. By default this is marked as version “1” which would then means that our first migration script (“V001__initial_schema.sql” in the example above) is not run as it is also version “1”.