• Flyway
• Testing migration scripts
• Database Testing and Quality

Test-Driven Development for Flyway Using Transactions

---

This article is part of a series on the requirements and practicalities of database testing, with Flyway:

Also relevant are the series of related articles on Test Data Management.

---

Flyway applies SQL migration scripts within a transaction, whenever possible, and will roll back the whole migration if it receives an error message on its connection to the database. I’ll show how you can exploit this fact by adding unit tests to your migrations that deliberately raise an SQL error message if an assertion test fails, for example with the Raiserror statement, if you’re using SQL Server. I’ll also demonstrate how to use explicit transactions to repeatedly test and roll back complex SQL migrations, such as those that required data migration. Once fully tested you can simply transfer the migration file to the Flyway migration scripts folder for execution.

Running SQL Migrations with Flyway

If you just boldly add your new SQL migration file to Flyway with the silent thought ‘what could go wrong?‘, fate will ensure that something will. The script might execute without error but could easily make an alteration to your database that you subsequently discover doesn’t work properly. The problem is that once Flyway executes that script, it “commits” it, stamping it indelibly into its schema history table. You can’t undo its effects, except by applying a patch, in the form of one or two additional migrations to ‘roll forward’.

Of course, this can cause difficulties when developing a more complex migration. For example, say you need to refactor the Pubs database, adding some tag tables, merging data into the new columns, and then dropping the old TypeList column.

Before letting Flyway “commit” these migration scripts (this could equally well be a single migration), you’d want to work each of the steps one by one and test them. You’d also need to assess various alternative approaches to find the best one. When I first used Flyway, I was a victim of my own bad programming habits. For anyone used to being able to tinker with a SQL Server database, try things out, and rebuild from scratch, it just seemed unnecessarily hard to do it this way, when it looked as if the first time you apply a migration, it became cast as if in stone in the epic order of migration versions.

Development with Flyway doesn’t have to be like that. In fact, it is no easier or harder than doing it the more traditional way, if you use transactions properly.

Flyway and Transactions

With Flyway, if you make a mistake and an error is triggered, then the migration rolls back. Migrations are applied within a transaction whenever possible, and Flyway rolls back the migration if it receives an error message on its connection. You can configure Flyway so that a whole group of migration files roll back.

Imagine that we were foolish enough to attempt to have this code in a migration.

We saved it as the next version in the filename in the project directory and executed a flyway migrate. You’ll expect to see that the insertion will fail with the violation of a UNIQUE constraint on the name column, and so it does:

Flyway : ERROR: Migration of schema [dbo] to version "1.1.10 - TestForTransaction" 
failed! Changes successfully rolled back.
---<blah>--
Message    : Violation of UNIQUE KEY constraint 'UQ__DeleteMe__737584F6888D1BF6'. 
Cannot insert duplicate key in object 'dbo.DeleteMePlease'. The duplicate key 
value is (Phil).

You can search in vain for your DeleteMePlease table and won’t find it. Flyway never even registered the script, and so never incremented the version number in the Flyway schema table; it only does that on a successful migration. This means you can fix and update any failed migration without Flyway objecting to the change.

What if we then fix the code so that it works. Can we test it so that even if it works, we can stop the migration from being ‘committed’?

Any bets? Good. We try the migration once again in Flyway (I provide some simple PowerShell code to try this out, at the end of the article):

Flyway : ERROR: Migration of schema [dbo] to version "1.1.10 - TestForTeansaction" 
failed! Changes successfully rolled back.
Message    : Don't worry. It worked but was deliberately rolled back

Flyway will roll back the migration, and using flyway info, you’ll see that in the schema history table the migration (v1.1.9 in my example) is listed as Pending:

However, at this stage you can alter the contents of that file, and even change its name without causing Flyway to complain.

With SQL Server, you might think that whatever connection to the database you have, the Raiserror function, or any error that occurs, will automatically roll back the transaction. This, however, will only happen in certain circumstances, and to guarantee this you must use a more explicit way of doing it such as TRY…CATCH. Flyway manages this for you. This means that you can use Raiserror more confidently to test out a longer migration in Flyway a digestible bite at a time, checking each section.

Raiserror for Unit Testing

There are many ways that you can use this Raiserror function merely to force Flyway to roll back the migration because you’ve more work to do on it. You can use it on your test code to raise errors if it fails any of your unit tests or assertions. It could be the result of queries or of the execution of a batch.

Here, as illustration, we have a function that splits a string into words. It is followed by some assertions. If there are no errors in the function, then all the tests pass (as is the case here) and Flyway will happily run and “commit” this migration. However, if any of the assertion tests raise an error then Flyway will roll back the entire migration, and the function will not even be created in SQL Server.

To force an error and try this out just make this alteration in the code and you’ll see plenty of errors coming from the unit tests

Using the same technique, you can add a table or set of tables, import data into it and test it. Only if it passes all its tests is the migration added to the database. You cannot get the same effect using a SQL editor such as SSMS without extra code. Flyway’s transaction management and rollback is simple but very effective.

Transactions without Commitment

Use of Raiserror is the obvious way to run assertion tests on all your functions in Flyway. You could use the same technique to work through a migration, piece by piece, using Flyway to re-run the ‘failed’ migration as many times as you wish. Sure, it will help you to develop code, with lots of tryouts, until you get it right. However, I find I need to be a lot more interactive so we’ll move on now to a technique that allows you a great deal of freedom to try things out, using your favourite IDE to access the database so you can execute the code.

Developing incrementally and interactively within a transaction

To test your migrations without having Flyway commit them, we can simply run the migration code within a transaction for your IDE, such as SSMS. You don’t commit it, ever. You only roll back.

First, set up a new migration file for interactive work in an IDE such as SSMS with its own connection to the database. Your first line must be BEGIN TRANSACTION (never forget to delete this line before you turn your code into a migration file, because Flyway does its own transaction handling). You leave the transaction open while you are testing and modifying your work. If you execute your code and part of it causes an error, you are still in your transaction and can rollback. The code you’ve successfully executed will still exist in your connection, but in your connection only.

Every time you wish to tear-down or revert to the state that the database was in before you started, which should be the current version, you roll back the transaction and as if by magic, all your changes are gone. You must have a line, commented out, and preferably at the end of your code, to make it easy to find, that rolls back the transaction. Before running it, you can if you are in a transaction (SELECT @@TranCount). It returns 1 if you are, and your additions are in place. If 0, then the transaction has been rolled back by an error you haven’t executed the file.

The last line can check whether the objects you’ve created in the code are still there, after you roll back, using a metadata query. In our example, I’ve listed the existing tables.

Whatever happens, you must never leave the transaction open because once you begin the transaction you have effectively sole possession. If you happen to try to run Flyway on the same database when you have that transaction open, it just waits until your transaction is either committed or rolled back.

Once your work is finished and tested and you’ve checked that you’ve rolled back the transaction you must remove the code that begins the transaction, and then you can safely turn your code into a Flyway migration, you. And pass it to Flyway to do the actual migration. Just place the amended file in the Flyway directory, suitably named with the correct version number, and run it within Flyway.

Running Flyway Migrations using PowerShell

To illustrate the process, and ensure that there were no hidden snags, I created a fairly radical revision of our Flyway pubs project. Noticing that there were three places in the old Pubs database where tables contained addresses, in authors, publishers and employees, I decided to replace that with a single address table. This had an advantage for location lookup, but also to be able to specify the type of address. I then decided that one may as well add support for phones, email number and so on. This revision also provides views to eventually replace the old legacy authors, publishers and employee tables. These views enabled me to test the code and the data migration. I reckoned that was enough for a migration.

The file is too large to include in the article, but it is here on GitHub within the Flyway project (the file is V1.1.10_AddAddressesPhonesEtc.sql).

This was all tested with Flyway. In PowerShell I used a reasonably simple method of running Flyway, taken from my article Getting Started with Flyway and SQL Server:

This method of doing the migration unfortunately exposes passwords so isn’t usable for anything other than test purposes. This following method is more robust and extendable but requires the use of a series of script blocks held in a separate file. It is covered in a series of articles:

Conclusion

Flyway consistently uses transactions for migrations unless you configure it at file-level to do otherwise. This is very handy if you wish to try out migrations, perform tests and abort the migration if a test is failed. I’ve illustrated how to so this in this article.

I was surprised how easy it was to develop migration scripts with Flyway by changing my development style very slightly to use transactions and rollbacks to develop code, test it, try out alternatives and so on, before passing the resulting migration file over to Flyway to execute. You really do far better with this technique if you have your own dedicated database to work on. Unfortunately, many of my habits were developed toiling over shared development databases where such behaviour would have caused cries of pain from my co-workers.

Tools in this post

Flyway

DevOps for the Database

Find out more