This is part 4 in my series on continuous deployment of databases. In this post I’ll be having a quick look at FlyWay.
Part 1 covers the logic behind the deployment options.
Part 2 covers SqlPackage.exe.
Part 3 covers ReadyRoll.
VSTS Sql Server Database Deployment Task can be found here.
In my two previous posts I briefly had a look at two tools for SQL Server Continuous Deployment, SqlPackage.exe and ReadyRoll. I wanted to see what is out there for Oracle, MySQL and other databases especially if you have a situation where your application supports multiple database and you want to do Continuous Deployment against all of them. There are plenty commercial applications for SQL and Oracle that integrate directly with build systems and IDEs but not that much for other databases based on my quick google search.
Flyway
I’m going to take a quick look at Flyway, a migration based tool, bearing in mind I work in .NET and some of the features are Java centric. It is an open source project and looking at the commit history on GitHub it has been around for a long time and is supported by an active development community. At the time of writing it supported 18 databases.
You create migrations using SQL script files or Java code based migrations which comes in handy for very complex data manipulation and handling blobs. It is based on convention over configuration so naming folders and files correctly is very important since the version number which controls the deployment sequence is part of the name. There are various ways to execute the migrations for example using the command line tool or calling the Java API directly, this opens up scenarios like including Flyway as part of your application to keep your database and application in sync. There are also plugins for Maven, Gradle and Ant but not much in terms of .NET.
I’ll quickly touch on the main operations used by the application:
Migrate: The meat of the application, it migrates a schema to the required version by determining the migrations that should be run.
Clean: Wipes the destination database clean, used during development and testing.
Info: Shows the migrations applied with execution time and state and which ones are pending.
Validate: Verify the applied migrations match the ones you have locally.
Baseline: Integrate Flyway with an existing database so only newer migrations will be applied.
Repair: Flyway uses a metadata table to keep track of applied migrations, this will the repair the metadata table.
Tooling
It doesn’t have direct integration with Visual Studio or VSTS but it doesn’t look to difficult to roll your own automated deployments with some scripting to call the command line tool. Similar to the SqlPackage.exe you can call the Flyway command line tool from your release management tool to update the target database directly or generate a report of the changes that will be applied in case a DBA has to scrutinise the changes first.
Oracle Side Note
If you want to use Flyway against Oracle or just want to use Visual Studio in general for Oracle database development have a look at Oracle Developer Tools for Visual Studio. Oracle tools is very similar to Sql Server Data Tools and makes is easy to add your Oracle database to source control. You can also use it to compare Oracle schemas and generate change scripts which can be the base for your Flyway migration scripts.
