Need for DB migration tool
In today’s world of rapid development needs, version control tools (read: Git, SVN etc.) provide a good way to satisfy our needs of streamlining the development process. It is common for enterprise applications to be developed by a team until you have a Bill Joy working on it. Every now and then, the product owner will keep coming up with new features and modifications to existing features. Incorporating all this requires a lot of structural changes to your project architecture and to the schema involved.
Without version control tools it is hard to imagine the development of great software in a team. Without these tools there will be a lot of chaos in the development teams. Imagine multiple developers working on the same module without a version control tool. It’s hard or may be impossible for you to just imagine such a universe existing.
We have made major progress in the context of version control for our code. But we haven’t made such great progress in the context of version control as far as our db schema migration is concerned. I have personally worked in teams where we have to apply the migration scripts with each deployment. Now imagine a scenario where after deployment on production you find out that some of the features are not working.
You are in a fix as the QA team just tested the build on the staging environment where everything was working like a charm. After going through a debugging session you find out that the culprit here is one of the missing SQL scripts that contained some schema changes that one of your peers has checked in but has called in sick on the day of deployment. All this trouble could have been avoided had you been using a schema migration tool for your project.
Various schema migration tools
There are various schema migration tools like liquibase, flyway (java platform) and db-maintain options available to you.
Today we will take a look at Flyway and its integration in a Spring based application. First lets look at how Flyway works-:
- The first scenario will be when Flyway looks at an empty database. At this point it will try to look for its metadata table by the name of “schema_version”. This table contains all the information of the scripts that have already been run on this database. If this table is not found then it will create this database.
- After the creation of the database, Flyway scans the classpath for all the migrations present and executes them in a specified order (we will talk about the order later).
- At each subsequent deployment, Flyway will make sure that only the scripts that have not been already run will get executed using the history from the metadata table “schema_version”.
- Your metadata table look something like this-:
Using Flyway with a Spring based app
Now that you have got a hang of core working of Flyway lets see its integration with a Spring Application. Following are the steps that are required for this integration-
- First, we need to include the Flyway libraries in our project. This step may differ upon the choice of the build tool that you are using for your project. If you are using Maven as the build tool you need to define the Flyway dependency in your pom.xml so that maven can include the required library. Following is the Maven dependency:
- Now we need to register a bean in the spring application context. We need to make sure that the spring context picks it up as a managed bean. We also need to make sure that as soon as the bean is instantiated, Flyway looks for all the new migrations and if found run them on the database.
Now let’s first understand this bean definition. We define a bean ‘com.myzilla.migration.MyZillaMigration’ and tell Spring to run the method ‘repairAndMigrate’ on successful bean creation. This bean requires three property definitions for its creations:
- locations: This is the path on which all migration scripts are to be scanned for. All the migration scripts will go under this path. Here we will drop all our migration scripts under the path “/META-INF/db”. Flyway will scan this path for the migration scripts.
We need to follow a pattern for the naming of the migration scripts. Lets try to understand it with this example.
The filename consists of the following parts-
- prefix: Configurable, default: V
- version: Dots or underscores separate the parts, you can use as many parts as you like
- separator: Configurable, default: __ (two underscores)
- description: Underscores or spaces separate the words
- suffix: Configurable, default: .sql
The files are executed in the order of the version of the file.
- dataSource: This is the reference of the datasource bean that you provide to the entityManagerFactory bean. You must also make sure that the creation of the entityManagerFactory bean depends on the successful creation of the flyway bean as you would want that all the new db changes must be applied to the db before the instantiation of the entityManager.
Now lets take a look at the class MyZillaMigration.
This class extends the Flyway class and calls the repair and migrate methods of the extended class. The repair method clears all the failed migration entries. I personally call this method before migrate because if one of the previous scripts fails and if I make some changes for running it again then because of the checksum differences the altered script won’t run. For this altered script to run again we need to make sure that its previous entry is deleted from the ‘schema_version’ table. Calling repair before migrate helps me do so.
For a more detailed study please head to the FlyWay official website. Hope you find Flyway useful for your development needs . Happy learning.