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

 

Contents

 

What is Flyway?

Flyway is a popular open-source migrations-based project format for Database Lifecycle Management (DLM) built by Axel Fontaine (b|t). 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 SQL Server, MySQL, Oracle, Postgres and many others.

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 FlySQL

Over the last couple of weeks I’ve worked with a small team to create a new tool to improve the experience of developing MySQL databases with Flyway. We called it FlySQL. We plan to release it with a free community edition and a paid edition.

You can find it on our website here. << This link might not work immediately but should do within a few days.

For those who know Redgate tools really well, the best way to describe FlySQL is as a tool a bit like ReadyRoll for MySQL, built on FlyWay with the engine from Redgate MySQL Compare and the UI from Source Control for Oracle.

However, since most people won’t know some or any of those tools in detail let me put that another way…

Back to top

What problem does FlySQL solve?

Most developers on most database platforms like to work directly on a development database. This often allows developers to benefit from intellisense tools and immediate feedback about whether they’ve got the syntax right. It’s also easier to test if the code compiles or does what the developer expects.

Once the developer has the database in the state they want, when using a migration tool such as Flyway, they then need to create a migration script. This means they effectively have to write the same code all over again but this time in the form of a migration script that will handle the deployment without breaking anything.

It’s very easy at this stage to make a mistake. For example, the developer might forget to include one of their changes or they might forget to handle a dependency properly. The upgrade script might perform poorly. In addition to the risk of human error, it takes time – so much time – to write these scripts manually.

Back to top

How does FlySQL solve the problem?

FlySQL is a tool built on top of Flyway that allows developers to get the benefit of working directly on a development database without the need to write upgrade scripts manually. It harnesses the Redgate MySQL comparison engine to generate the .sql scripts automatically. This saves developers time and it reduces the number of errors in migration scripts, hence allowing you to deliver faster and more reliably.

If the developer does not like the migration script generated by the Redgate tool they are free to edit it, for example to handle table re-names, modifications to data or new NOT NULL columns.

You can start and stop using FlySQL whenever you like. If you start using FlySQL there is no commitment to keep using it in the future. It simply helps you use Flyway and if you stop using FlySQL you’ll be back to using vanilla Flyway exactly like before.

Back to top

A worked example

Right, let’s see it in action.

  1. Download FlySQL from here and install it. (Link will go live a few days after this post.)
  2. Open FlySQL from the start menu. It will load in its own window.
  3. Click “Open project…” and select your Flyway project. FlySQLWorkedExample1
  4. That’s it – you are set up. If you compare your migration scripts in your flyway project they should match up with what you see in the FlySQL window. FlySQLWorkedExample2
  5. Now make some changes on your dev database and click “Create new migration…”
  6. You’ll be presented with a list of all the objects that have been modified. FlySQLWorkedExample3
  7. Select the changes you want to add to your Flyway project and click “Generate script”. Give the script a name. You don’t need to provide the version number, we will do that for you with a timestamp. FlySQLWorkedExample4
  8. Click “Create migration” and then check your Flyway project. You should find a new script ready for you to deploy. If you wish you can read the script and modify it. FlySQLWorkedExample5

And that’s it. We hope that you find this process much easier, quicker and more reliable than writing your scripts manually.

Back to top

Deployment with Octopus deploy

I didn’t actually cut any code on FlySQL myself. I worked on something to help you deploy your Flyway projects, regardless of whether you used FlySQL to create them or not, and regardless of whether you use MySQL, SQL Server, Oracle or any other database platform that Flyway supports.

Octopus Deploy is a release management tool built by a great team of people in Australia headed up by Paul Stovell (b|t). It’s effectively a glorified PowerShell scheduler with a really nice UI. The point is that it visualises and automates your deployments, providing a central source for logging and auditing. It’s so nice it makes deployments not only easy, but also kind of fun!

Octopus Deploy can natively do a bunch of things very easily but running your Flyway project isn’t one of them. This means that up until now users of both Octopus and Flyway have had to maintain their own PowerShell scripts.

However, the team at Octopus Deploy do maintain a community library where users can share their own custom deployment step templates. So I built one:

Flyway migrate

The cool thing about this plug-in is that it doesn’t just handle the deployment – it also checks for drift. Drift is one of the biggest challenges with DLM. It is when a database is modified outside the normal process. For example, that hotfix on production, or that time you forgot to deploy that one object with the rest of the release. The problems drift causes can be summed up in a few words: “It works on my machine.”

If you would like to find out more about the step template I’ve written a separate post all about it here.

Back to top

Get involved!

While we think FlySQL already offers a huge amount of value, it is still very early days. Our industry is full of stories of really great products that never worked out. With this in mind, if you like the look of FlySQL and would like us to keep working on it please use it, tell your friends and colleagues about it and, most importantly, tell us what you think.

Have you used it?

Did it do what you expected?

Was it useful?

Do we support your use-case? (If not why not?)

What should we do next?

You can leave comments below or you can reach the product manager, David Atkinson (b|t), or me on Twitter.
Back to top

Leave a Reply

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