A Flyway step template for Octopus Deploy to migrate databases, with a drift check

tl;dr

I’ve built a Flyway Migrate step template for Octopus Deploy. Allows you to easily migrate your Flyway project with Octopus Deploy. It also includes a drift check for the most popular database platforms using the Redgate comparison engines.

It’s available on the Octopus Deploy library here:

Flyway Migrate step template for Octopus Deploy

For more detail about what it is, how it works and how to use it please read on.

Contents

 

What is Flyway?

Flyway, built by Axel Fontaine (b|t), is a popular open-source migrations-based project format for Database Lifecycle Management (DLM). It’s a very simple tool that enables users to save a set of migration scripts and to automate the execution of them in sequence against target databases. It’s cross-platform, supporting most of the popular database platforms.

Flyway’s usage is growing exponentially and they are expecting to hit 1m downloads in 2015. On the ThoughtWorks technology radar it rates very highly:

TW

Back to top

What is the drift check?

The template includes a feature to manage a very common DLM problem: drift. This is where someone (or something) makes a change outside of your normal process. Perhaps it is a dev or manager with too much access to production? Perhaps it is a DBA who needed to fix a thing in a hurry? In either case drift causes problems.

I’ve often ranted about the pros and cons of the migrations and state based approaches to DLM, but neither handle drift particularly well. For migrations based tools like Flyway, DbUp and ReadyRoll (among others) the problem is that the change is not detected. If a manual change is made to production and not copied into source control the production database will become out of sync with development and test machines. A migrations based approach will typically ignore this difference and carry on regardless. Over time environments become more and more inconsistent.

Environment inconsistency is one of the biggest problems with delivering software. If anyone has ever told you “it works on my machine” you’ll probably appreciate that. If your production database is different from your test database you cannot trust your tests. If your source code is not consistent how can developers be confident that what they built will work in production?

Back to top

How does the drift check work?

You’ll need to provide a ‘shadow’ database. This is a database that you are happy for Octopus/Flyway to clean (delete all the objects) and rebuild each time you deploy.

We’ll use the Flyway “info” command to determine the version that the target database thinks it is up to. Then we’ll use the Flyway “clean” and “migrate” commands to rebuild the shadow database up to that point. You now have two databases that should contain identical schema objects. If they do not match your target database has drifted.

In order to determine if the target and shadow databases are in sync we’ll call your Redgate comparison tool. Redgate offers three different schema comparison tools:

You’ll provide the path to the command line for the appropriate comparison tool and then we’ll use it to check that the target and shadow databases are in fact in sync. If they are, fantastic, the deployment will continue as normal. If they are not in sync the deployment will fail, informing you that the drift check has failed.

Back to top

What to do if the drift check fails

If you have drift you have two options:

  1. If the change is undesirable: roll it back.
  2. If the change is desirable: Add the changes to source control and promote them through your pipeline.

Either of these actions should bring your environments back in line.

The step template will help you whichever path you wish to take. The Redgate comparison tool will produce one or two artifacts depending on you RDBMS: driftReport.html and (for MySQL) undoDrift.sql.

The report will tell you exactly what differences were found. For MySQL, the undoDrift.sql can be executed against the target database to bring it back into line. (Watch out for DROP TABLE commands etc!)

You can also point your Redgate comparison tool at the shadow and target databases to produce migration scripts in either direction that can be used to roll back drift (if the changes are undesirable) or to correct for it in your Flyway project (if the changes are desirable). Remember, if the changes are desirable, to also add this script to the Flyway “schema_version” table on the target databases and mark this script as deployed so that you don’t try to make the changes a second time next time you deploy!

Back to top

How do I use the step template?

First of all go to the Octopus Deploy Library and select the Flyway migrate template. Follow the instructions to add this template to your Octopus Deploy server.

You need to use NuGet to package your Flyway project. If you are using Octopus Deploy this is probably already second nature to you and ideally you have this happening automatically using a build server like TeamCity, Jenkins or VSO. You’ll need to include the full contents of the FlyWay project in your NuGet package.

Once you can create packages and Octopus can pick them up open the process tab of your Octopus project. Add a step to your Octopus Deploy project to deploy your NuGet package to the target machine that hosts your database.

Next add a “Flyway migrate” step:

1-stepTemplate
As normal you’ll need to provide a name and a role for the step. Then you’ll need to add the following details about your Flyway project:

  • The name of the step you used to deploy the package. (Perhaps your Octopus project involves deploying more than one NuGet package? Which package contained your Flyway project?)
  • The relative path to Flyway.cmd. (Perhaps your Flyway project was in a sub-directory of you NuGet package? If not leave this blank.)
  • (If you use any directories for your SQL scripts, other than the default /sql directory, list them here exactly as you would if you were calling Flyway from the command prompt with the –locations switch.

Next you’ll need to provide the connection string for the target database as well as the username and password. You can use variable substitution for all these fields if you wish and of course you can encrypt the password. Make sure you enter the URL in the exact format specified by Flyway or on the documentation for this step template in order to be sure that the template can correctly determine the database and server names etc:

2-stepTemplate
You can stop here if you like and start deploying with Flyway right away. This much does not require any commercial licencing at all (other than your Octopus licence).

If, however, you wish to run a drift check select the radio button. You’ll also need to provide the path to your Redgate command line tool as demonstrated in the screenshot below.

The Redgate comparison tools all come with a free trial period but you will need to buy an appropriate licence once the trial expires. You’ll also need to provide connection details for a shadow database which will be used for the drift check (as explained above).

3-stepTemplate

Once you’ve done that you are ready to deploy with the added security and confidence that you will be alerted if any drift has occured.

Back to top

FlySQL – a Flyway and Redgate hybrid for MySQL

While I was building this step template a few of my colleagues were building “FlySQL”. FlySQL is a tool for Flyway users with MySQL databases that allows them to auto generate all their upgrade scripts at the click of a button. Once the script is generated the user can edit it should they wish.

This tool allows MySQL developers to build their Flyway projects much more efficiently and reliably. ou can read about it here:

Introducing FlySQL, a Flyway and Redgate hybrid that takes the pain out of database migrations

We are also planning to release this with a free community licence. Stay tuned for more info!

Back to top

Alex – you built it wrong!

If you find a bug in my code or you want me to change something leave a comment here or on the Octopus community library or tweet me and I’ll take a look.

  3 comments for “A Flyway step template for Octopus Deploy to migrate databases, with a drift check

  1. Wayne
    May 15, 2017 at 5:28 pm

    JSON for Octopus script doesn’t copy to clipboard. Is it available?

  2. Alex Yates
    May 15, 2017 at 5:31 pm

    I think that’s a browser issue. I’ve experienced it before.

    #WorksOnMyMachine with Chrome running on Win10.

    Can you expand to see the raw JSON?

  3. Alex Yates
    May 15, 2017 at 5:35 pm

    In the meantime:

    https://gist.github.com/Alex-Yates/325801823395b6fb87eb99b233f4cb1c (Up to date as of 20170515)

    I make no promises to maintain that gist so if this comment is old when you read it please use the version on Octopus Deploy library instead (if possible).

Leave a Reply

Your email address will not be published. Required fields are marked *