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.
Bazm-e-Code 