Use a Database Migration Tool (Flyway) for your Evolving project

When you are doing development on application that has a database schema that will be changing over time you need to have a good way to manage those changes, and if you are working with team that needs to have those changes to avoid issues during their development you might want to think about a Data base migration Tool.

Before a Database migration Tool we used to send email to a distribution list of developers and DBAs to make sure the new DDL or database DML were executed to support new code.

I'll focus on Flyway which is the one I have used for my latest project.

Flyway is an open-source database migration tool. It strongly favors simplicity and convention over configuration.

I'll try to summarize flyway workflow for migration:
It needs to have a directory where all the .sql files that will be executed on a database
It will need the driver for your database
The schema and the credentials for the user to run the .sql files
Having the above when, a migration can be done, here is an example of what migration is with flyway:

As you can see there is a database table called Schema_version

That table is created by flyway to save version of each migration, and a version is the representation of the .sql scripts that have been executed since the baseline, here is how this table looks like

Once you have all your .sql files ready for a migration you can run migration on flyway and everything will be executed for you without.
Here is how the file system looks like:

Now that a migration process is understood, the next step is to store all the.sql into a source code repository, there is a great article about this at http://www.jeremyjarrell.com/using-flyway-db-with-distributed-version-control/

There are more tasks rather than migration such as (clean,info,validate,baseline,repair).

Flyway comes with several EXECUTION MODES

• Command-line
• API
• Maven
• Gradle
• Ant
• SBT

I'll used ANT for my project, but feel free to use the Execution mode that fits better on your project

With Ant is as easy as configure a build.xml for your database migration, here is an example of how easy is to do it.

Once this is done, it is as easy as just Run the ant Task and the Migration will happen for the files over your configured location.

This is a great tool, spend like 4 hours to do a POC, and you will realized that it was worth.