Database Migrations in an agile project – using Flyway

In this post I’m going to explain how we used Flyway for database migrations in one of our projects. Flyway allows version control for databases, hence, easing the process of release maintenance & deployment.
To read more about Flyway:

Flyway creates a meta-data table for maintaining the migration versions.We used SQL migrations and ant-based execution style. In the migration directory, sub-directories for different database objects were created, for modularity.
For example:
migrations/table – all table scripts
migrations/sequence – all sequence scripts
migrations/trigger – all trigger scripts

The separation of database objects also helps in maintaining execution order, when you have dependent objects; for example a package depends on some tables. As the versioning in Flyway is custom, meaning, it is done on the basis of the script file-name, we introduced codes in the file-name for indicating the database object for which the migration was applied; allowing more clarity in the meta-data table.
V1__10_Person.sql
10 -> code for table

One major customization we did was for reverts. Flyway does not provide any revert functionality for rollbacks.
Hence, we created a custom ant task for managing reverts. This needed a rollback folder, just like the migrations folder for applying migrations, and the same sub-directory structure.
The corresponding rollback file-name would be R1__10_Person.sql.
This custom ant task would apply all rollbacks in the order as well as clean-up the Flyway meta-data table for the migrations that were rolled-back.

To make this full agile, we created a single migration shell-script and then integrated it in our Jenkins build.

Flyway really helped us improve on the deployment & release process, and also enabled better collaboration in maintaining database changes.

Comparison of Database Migration Tools

Database migration is a term for evolving databases in an agile environment. It is basically version control for your database so that releases can be controlled and managed. It has nothing to do with migrating from one database to another. ­čÖé
Martin Fowler and Pramod Sadalge talk in detail about agile database development in this podcast:
http://www.se-radio.net/2012/06/episode-186-martin-fowler-and-pramod-sadalage-on-agile-database-development/

There are many database migration tools available, the following comparison table might help you make a better choice:

db-deploy Flyway Liquibase MyBatis
Supported Types sql java api & sql java api, json, sql, xml, yaml java api & sql
Execution methods ant & command-line ant, command-line, gradle, java api & maven ant, command-line, java api, maven, spring & servlet-context command-line, java and maven
Database support Oracle, SQL Server, MySQL, PostgreSQL, H2, Hsql, Derby & SQLite Oracle, SQL Server, MySQL, PostgreSQL, H2, Hsql, Derby & SQLite Oracle, SQL Server, MySQL, PostgreSQL, H2, Hsql, Derby & SQLite Oracle, SQL Server, MySQL, PostgreSQL, H2, Hsql, Derby & SQLite
Rollback support no partially through repair command yes yes
Versioning  no custom  no  automated [date-time based]
Existing schema support no yes no no
Development activity no updates since May 2009 active active active