This article contains a set of best practices for Flyway integration (taken from my experience and experience of other people) that I hope you will find useful.  I also assume you are already familiar with what Flyway is used for, and how it works. If you are not, check out a simple introduction on the Flywaydb.org website: https://flywaydb.org/getstarted/.

We will take a look at the following best practices in this article:

• Team Arrangement and Branching with Flyway
• Idempotent delta scripts
• Baseline
• Flyway Configuration using Spring Boot
• Flyway and H2 Unit Tests
• Versioned and Repeatable Migrations
• Dealing with Hotfixes
• Multiple Instances with Flyway
• Manage Multiple Schemes or Shards with Flyway
• Flyway in Production
• Dry Runs
• Rollback of Flyway Migrations
• Flyway Log

This is not the exhaustive list of Flyway practices, but the ones listed are the most essential in my humble opinion.

Team Arrangement and Branching with Flyway

The best branching strategy for Flyway-based database migrations depends on your team arrangement.

Shared Development Database and  Dedicated DBA Role

If your team uses a single development database shared by all team members, if you have a dedicated DBA who responds to your requests for database changes, if you favor trunk and rarely use branches, then you should be good with the default Flyway configuration and numbering scheme, which uses integer version numbers for your delta files, such as:

• V1__Update_1.sql
• V2__Update_2.sql
• … and so on.

When using this approach, you will do best with checking in all migrations to the trunk. Your DBA will be a single authority to resolve conflicts and to make sure that database doesn’t break when new delta files are applied. Your DBA will also be responsible for rolling back migrations and cleaning up database if something goes wrong.

Multiple Developers Making DB Changes

If you are in the true DevOps model and give multiple developers control over database changes, if they work independently on different features, then your team should follow a strict process when implementing migrations and resolving conflicts.  When developers have power over database changes, they have a great responsibility and they should be very careful when deploying their delta files. To make sure that database migrations work smoothly, try to follow the best practices below.

1. Each developer should work with his/her own database copy.

This requirement is essential. Since database changes can break other people’s work, every developer should have a personal copy of the database which can be hosted on developer’s laptop or on development server.

2. Each developer should work in his/her own branch.

When developer works on a separate feature then he/she should maintain dependency between the DB changes and the code changes within a separate branch. Issues may start when DB changes that this developer makes in his own branch depend on the changes that another developer makes. In this case both developers should work in the same branch.

Developer who implements migrations in his own branch and in his own database will be responsible for rollbacks and for cleaning up DB if migrations fail to work. This developer will need to use Flyway maintenance commands, such as CLEAN and MIGRATE.

3. Use timestamps for delta file versions instead of integers.

When all branches are merged into trunk, and you use integers for delta file versions (as Flyway suggests), then conflicts are possible. Developers will constantly have to check with each other when they create a new version of a migration. The alternative is to use timestamps for the delta file versions instead of integers, then possibility for conflicts is reduced dramatically. A timestamp will allow migrations to be applied in order they were originally created.

Flyway only recognizes integer numbers, so the timestamp should be converted into integer, i.e. ‘12/30/2016 12:30:55.282’ should be converted to 20161230123055282.

4. Enable out of order migrations.

By default, Flyway will ignore migrations/delta scripts that are older than the one already applied to DB. This will create an issue if the branch with the later version is merged into CI environment and built before the one with the earlier version. On the diagram above the red Branch #1 was merged into trunk before purple Branch #2. Also, an update to trunk was made before purple Branch #2 was merged. If the project utilizes continuous integration, then migrations of the purple Branch #2 will be ignored. The reason is that Flyway will check the latest applied migration version (which is 20170410171256) and ignore migration version 20170120081315 as outdated when purple Branch #2 is merged. This is true for timestamp version numbers as well as for integer version numbers.

To avoid this situation, enable the out-of-order migrations setting in Flyway. With Spring Boot you need to set the property flyway.out-of-order=true (see this link for more info: https://flywaydb.org/documentation/commandline/migrate ).

5. Use continuous integration DB environment to merge all DB changes.

A good idea is to have a dedicated environment where all your deltas (migrations) will be applied. You can use development, test or staging environment for this purpose. Usually this environment will be associated with a trunk. If you have a dedicated environment where your CI build is performed, then use this environment for Flyway database updates.

6. Do a DB code review before merging changes to CI environment (trunk).

This is a very important requirement: all developers who make changes to DB should review other teammate’s delta scripts before they get merged into trunk. This is an obvious step to make sure that one migration will not break the other.

7. If feasible, combine multiple changes from different branches into one delta file.

This is an optional step that will help maintaining cleaner deployment code. Imagine you have a hundred delta scripts (migrations) accumulated before the release. Those migrations will be sequentially applied in all higher environments including production. It will be easier to maintain all changes in one single delta file. To do that, before merging individual branches into trunk, all delta scripts in several branches should be manually merged into a single delta script that will be pushed to trunk.

This approach is not always feasible as it doesn’t play well with CI and CD processes that favor frequent incremental updates. Still, if your team merges all branches once before a release or you have a dedicated DBA who doesn’t mind additional work combining several scripts into one, then this approach will save time and effort in the long run, when the number of migrations becomes huge after a few years. Also, check the Baseline section later in this article.

Idempotent delta scripts

Every delta script that represents database migration should be written in a way that allows executing it multiple times and obtaining the same result (idempotent operation).

This means that every action in the script should be preceded by the action that checks if the change already exists or the action that reverses the change.

Checking for existence is preferable, but not always easy. MySQL, for example, does not allow IF ELSE statements outside of the procedures, and though workarounds exist, they don’t play well with H2 unit tests (I explain it later in this article).

The common idempotent workaround for MySQL is creating a stored procedure that performs the migration, then dropping this stored procedure afterwards. Stored procedure will use IF-ELSE to check for existing objects. Example is below:

DELIMITER $$

DROP PROCEDURE IF EXISTS upgrade_database_1_0_to_2_0 $$

CREATE PROCEDURE upgrade_database_1_0_to_2_0()

BEGIN

-- rename a table safely

IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()

AND TABLE_NAME='my_old_table_name') ) THEN

RENAME TABLE

my_old_table_name TO my_new_table_name,

END IF;

-- add a column safely

IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()

AND COLUMN_NAME='my_additional_column' AND TABLE_NAME='my_table_name') ) THEN

ALTER TABLE my_table_name ADD my_additional_column varchar(2048) NOT NULL DEFAULT '';

END IF;

END $$

CALL upgrade_database_1_0_to_2_0() $$

DELIMITER ;

Source: http://blog.geekq.net/2010/08/11/add-column-safely-mysql/

Baseline

You can set your existing database as a baseline version and instruct Flyway to apply all migrations on top of this version. You should start with your production database DDL – that will be your baseline. Then you can create new environments that are aligned with your production database from scratch using Flyway, thus implementing continuous integration on your DB frontier.

Obviously, you don’t want to break your production database, so when you established the baseline and tested everything in dev/test environment then create a production copy of your database (aka pre-prod or integration environment) and test the first migrations there.

Even if you used Flyway since the beginning of your DB life cycle, you might want to baseline it as an existing database later in time, so you will avoid checking and applying hundreds of migrations collected over the years. You will need to follow the process described on the Flyway web site.

A good and eaasy description of the baseline process is published on Flyway website: https://flywaydb.org/documentation/existing.html

Flyway Configuration using Spring Boot

Spring Boot comes with out-of-the-box support for Flyway, all you need to do is to add a line into build.gradle:

compile "org.flywaydb:flyway-core:4.0.3"

Flyway will be automatically called when application starts. If you use H2 for unit tests, Flyway will be called for H2 initialization as well.

Flyway will search your classpath for migration files (delta scripts) and apply them accordingly.

The list of Flyway properties that can be specified in application.properties/application.yml is the following (for the latest updates check this URL: https://docs.spring.io/spring-boot/docs/current/reference/html/common-application-properties.html ).

# FLYWAY (FlywayProperties)

flyway.baseline-description= #

flyway.baseline-version=1 # version to start migration

flyway.baseline-on-migrate= #

flyway.check-location=false # Check that migration scripts location exists.

flyway.clean-on-validation-error= #

flyway.enabled=true # Enable flyway.

flyway.encoding= #

flyway.ignore-failed-future-migration= #

flyway.init-sqls= # SQL statements to execute to initialize a connection immediately after obtaining it.

flyway.locations=classpath:db/migration # locations of migrations scripts

flyway.out-of-order= #

flyway.password= # JDBC password if you want Flyway to create its own DataSource

flyway.placeholder-prefix= #

flyway.placeholder-replacement= #

flyway.placeholder-suffix= #

flyway.placeholders.*= #

flyway.schemas= # schemas to update

flyway.sql-migration-prefix=V #

flyway.sql-migration-separator= #

flyway.sql-migration-suffix=.sql #

flyway.table= #

flyway.url= # JDBC url of the database to migrate. If not set, the primary configured data source is used.

flyway.user= # Login user of the database to migrate.

flyway.validate-on-migrate= #

For other Flyway execution modes and plugins (Maven, Ant, Grails, etc.) see Flyway documentation: https://flywaydb.org/documentation/ (check the left-side navigation).

Flyway and H2 Unit Tests

When you use H2 for unit tests, Spring will automatically run the Flyway on H2 database when application starts. This approach will make sure your H2 database matches your application database.

The recommended approach is to create two profiles in your application .yml file: one for development and one for testing, i.e.:

spring:
 profiles: development
 server :
 port : 8080
 jdbcConnection :
 driverClassName: com.mysql.jdbc.Driver
 url: jdbc:mysql://localhost/mydatabase
 username: user
 password: mypassword
spring:
 profiles: test
 server :
 port : 8080
 jdbcConnection :
 driverClassName: org.h2.Driver
 url : "jdbc:h2:mem:mydatabase;MODE=MySQL;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;"
 username: user
 password: mypassword

Configure your development profile with your application DB (i.e. MySQL) connection string, and test profile with H2 database connection string.
You will also need to install the latest version of H2 database, otherwise you may have compatibility errors, at the time of writing the following versions were used:

h2Version = '1.3.176'
mysqlVersion = '5.1.6'
flywayVersion = '4.0.3'

When running your application, you should select development profile with the following command:

./gradlew bootRun -Dspring.profiles.active=development

For running test cases you can select a test profile (or set it as active in application.yml).

Another issue that you may experience is that Flyway closes connection after it applies all migrations. To keep connection open you should explicitly add the following options to the connection string:

DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;

Flyway compatibility case: MySQL and H2

When you use H2 for unit tests in Spring Boot, all Flyway migrations will be automatically applied to H2 database. Therefore, H2 database should understand the syntax of your DB SQL language.

The compatibility mode must be turned on in the H2 database connection string using the following option:

MODE=MySQL;

Another compatibility issue comes from the fact that H2 automatically creates public schema in upper case letters which may conflict with MySQL database name if it was set in lower case letters. You need to turn off uppercase letters in H2 by providing the following option:

DATABASE_TO_UPPER=false;

Another workaround is to force MySQL to use upper case letters for schema name.

Versioned and Repeatable Migrations

Flyway supports both versioned and repeatable migrations. Versioned migration file name has a prefix V# where # is a version number (unless you change the prefix in Flyway configuration). This migration is applied only once.

Repeatable migrations will be applied after all versioned migrations. They will be applied again on the next run if the checksum of the delta file changes. Repeatable migrations are useful in the following situations:

• Rebuilding indexes, views and stored procedures.
• Adding permissions
• Other maintenance tasks

Repeatable migrations are especially convenient for resolving the conflicts as you have one source file that multiple developers can update.

Note that if you want to apply the same post-migration script on every application run, then instead of repeatable migrations you need to use Flyway callbacks (https://flywaydb.org/documentation/callbacks.html ).

Dealing with Hotfixes

Activate outOfOrder property to allow applying the Flyway migrations out of order and fill the gaps (as described in “Enable out of order migrations” section above).

If your production environment is at version 5 and your dev and test environments are already at version 6, but you need to run hotfix 5.5, then outOfOrder property needs to be activated in dev/test environments in order to apply version 5.5 after version 6. You should have activated it anyway if you have multiple developers working in different branches.

Multiple Instances with Flyway

Flyway is advertised as a “thread-safe” application. If your production environment has several instances of the application (nodes, containers) then every instance will run a Flyway “migrate” command. For every migration Flyway locks the SCHEMA_VERSION table, so other instances will be waiting until migration gets completed, and only then will proceed further, so no conflicts or duplicate migrations occur.

Since Flyway only locks the SCHEMA_VERSION table for one of the delta scripts at a time, multiple Flyway instances can grab different migrations and implement them in the order defined by migration version numbers.

The following simplified diagram shows how 3 instances of Flyway performed a series of 6 migrations.

Instance 1 on the diagram performed 3 migrations, while Instance 2 performed 2 migrations and Instance 3 performed only 1 migration. The order in which instances were started (1,2,3) contributed to the order in which instances acquired locks and implemented migrations. Migration 2 was the longest to implement, so both instances 2 and 3 had to get in queue and wait for their turns until migration 2 was finished. Please note that though this diagram is simplified, it represents the actual sequence of operations that take place during migration.

One scenario was reported when multi-node Flyway configuration failed. This may happen during the initial deployment when database is empty and multiple nodes are initialized at exact same time. Since SCHEMA_VERSION table doesn’t exist yet, there is nothing to lock, so while the first node is creating this table, the second node attempts this too, but since the table is already being created, the second node fails. This scenario was described here: https://github.com/flyway/flyway/issues/1067

The workarounds to the issue above would be:

• Run Flyway manually from the command line when deploying for the first time.
• Create SCHEMA_VERSION table in advance, before the first deployment.
• Start the second and consecutive instances of the application with some delay, so the first instance will have enough time to create the table.

Manage Multiple Schemes or Shards with Flyway

You should use one DB schema per micro service to avoid conflicts with other projects. Still, if your micro service uses multiple DB schemes or shards, you can configure Flyway to deal with them. The process is described in Flyway documentation: https://flywaydb.org/documentation/faq#multiple-schemas

There are 3 scenarios covered by Flyway:

1. You have multiple identical schemes (i.e. one per tenant) and you want to apply the same migration scripts to all those schemes.
2. You have similar schemes that use the same migration scripts.
3. You have multiple different schemes and each schema needs different migration scripts.

All those scenarios are easy to implement using Flyway configuration parameters. If you have some other scenario (unlikely) then you can always extend Flyway with your custom logic written in Java.

Note that MySQL doesn’t support schemes, so you will have to deal with multiple databases instead.

Flyway in Production

There are a few things that you will need to do when planning to use Flyway in production environment. But first, remember to test flyway migrations on production DB copy! Especially if you are integrating Flyway for the first time in your project. You might already maintain your production DB copy as an integration/preview/pre-prod environment, so as long as it matches production, do your pre-release testing in this environment first!

1. You will need to make sure you baseline your production database.
2. You will need to disable the Flyway Clean command in production.
3. You will need to enable out-of-order migrations to allow hotfixes (out-of-order migrations are described earlier in this document).
4. You will need to consider a dedicated connection string for your Flyway migrations.
5. You will need to decide if your application will trigger Flyway migrations in production, or your DBA will run it manually from command line.

1. Baseline of production database is extremely important, so when you deploy the Flyway for the first time. Read a section about setting DB baseline in this document and on Flyway web site.

2. When you run the Clean command, Flyway deletes all objects from the database, even if they were there before baseline, so this command can’t be applied in production. Flyway has the option to disable the Clean command:

cleanDisabled = true.

Add it to your production profile/configuration. More info about the command is here: https://flywaydb.org/documentation/gradle/clean

4. Flyway can be configured to use a dedicated connection string with admin access, which is especially important for production environment where you don’t want to mix admin and application accounts.

5. Some enterprises use policies that prohibit storing admin passwords in production configuration file. Therefore, application will not be able to run Flyway in such production environments. Still, DBA can invoke Flyway from the command line, manually entering admin password. This approach breaks the Continuous Delivery (CD) paradigm, but provides the policy compliance.

Dry Runs

Dry run is a recently added feature in Flyway that allows you to do two things:

• Preview your changes to DB before running MIGRATE command
• Perform releases without running Flyway directly.

Performing dry run is as easy as adding a key –dryRunOutput to MIGRATE command, like this:

flyway migrate -dryRunOutput=dryrun.sql

Instead of running the multiple scripts on the DB, Flyway will combine them into one big script and save it to the specified file. The file will contain the whole set of database updates as if Flyway ran them. The file (in our example it’s called dryrun.sql) can be presented to DBA for review/approval. It can also be run directly on the database which will be an alternative to running migrations with Flyway.

If Flyway can’t be run in production environment for any reason, dry run output can be used instead. Just keep in mind that you will still need to run Flyway with MIGRATE command somewhere (i.e. test environment) to advance with normal Flyway process and perform a new release next time.

Rollback of Flyway Migrations

Flyway supports rollbacks with the following features:

• DDL transaction support
• Rollback scripts

DDL Transaction Support. Every migration script in Flyway is automatically wrapped up in transaction. If your database supports DDL transactions (like PostgreSQL does), then any error in your migration will roll back the whole migration script automatically. If you have SQL Server then every DDL query will be automatically committed, so your script will not roll back automatically and you will need to create manual rollback scripts (or use idempotent scripts).

Of course, if your migration script does not contain DDL commands, then in case of error it will be automatically rolled back by most of the DB engines.

Rollback Scripts. Flyway recently added a support for rollback scripts. For every versioned migration file you can create a script that rollbacks changes and uses naming convention U###_ instead of V###_ as you used to do with migration scripts. Then if V123_ migration fails, Flyway will run U123_ rollback script.

There is an UNDO command in Flyway that will trigger rollback manually. By default it will rollback the latest migration.

Another approach would be do a snapshot of the database before release and restore it if any migration goes wrong. This will be the cleanest approach since it will rollback all migrations in the release, but it is not always feasible (i.e. when update is made on live database or downtime for snapshot restoration is too long), rollback scripts should be used in this case.

It’s important to remember that a rollback strategy with Flyway rests solely on your shoulders. It will be your responsibility to create a proper rollback script. It helps tremendously if you use idempotent migrations as described earlier in this document. Your rollback will be much easier with idempotent scripts.

Flyway Log

Flyway will automatically use Log4J if it is available in the classpath. To change the level of logging and log SQL scripts output you can set the following options (set in logback.xml):

<logger name="org.flywaydb" level="DEBUG"/>

<logger name="org.flywaydb.core.internal.dbsupport.SqlScript" level="DEBUG"/>

References

• https://flywaydb.org
• http://www.jeremyjarrell.com/using-flyway-db-with-distributed-version-control/
• http://enterprisecraftsmanship.com/2015/08/18/state-vs-migration-driven-database-delivery/

To be continued….

After upgrading from Flyway 4.0 to Flyway 4.1/4.2 our migrations to Percona (MySQL) XtraDB cluster 5.6 started failing. The issue is apparently a Percona’s PXC Strict Mode that Percona recommends to be set to ENFORCING. The error we get is:

Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.dbsupport.FlywaySqlException: 
Unable to acquire MySQL named lock: Flyway-605484229
----------------------------------------------------
SQL State  : HY000
Error Code : 1105
Message    : Percona-XtraDB-Cluster prohibits use of GET_LOCK with pxc_strict_mode = ENFORCING

Apparently, the issue with the new version of Flyway is that they moved from locking the metadata table (VERSION_HISTORY) to named locks (GET_LOCK() function) which is considered experimental in Percona.

The workaround would be to enable the PERMISSIVE mode on Percona (at least temporarily):

mysql> SET pxc_strict_mode=PERMISSIVE;

This is not recommended by Percona itself, and DBA’s don’t like that either. So far we had to rollback to the previous version of Flyway 4.0 that was working fine in this setup.

I asked this question to Flyway guys, they should do something about it.

Useful links: 

https://www.percona.com/doc/percona-xtradb-cluster/5.7/features/pxc-strict-mode.html

https://github.com/rails/rails/issues/28666

https://github.com/flyway/flyway/issues/1556

https://stackoverflow.com/questions/45332033/flyway-4-1-4-2-incompatible-with-perconas-pxc-strict-mode