Test-Driven Development for Flyway Using Transactions
When you are using Flyway, you can easily adopt test-driven development practices that will allow you to test your database migration script, to make sure it works exactly as you intended, before you even let Flyway execute it.
This article is part of a series on the requirements and practicalities of database testing, with Flyway:
- Testing Databases: What’s Required?
- Planning a Database Testing Strategy for Flyway
- Test-Driven Development for Flyway Using Transactions
- Basic Functional Testing for Databases using Flyway
- Running Unit and Integration Tests during Flyway Migrations
- Performance Testing Databases with Flyway and PowerShell
- Running Structured Database Tests in Flyway
- Running Database Assertion Tests 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.
1 2 3 4 5 6 7 |
CREATE TABLE DeleteMePlease (id INT IDENTITY, Name NVARCHAR(60) UNIQUE); INSERT INTO DeleteMePlease (Name) VALUES ('Phil'); INSERT INTO DeleteMePlease (Name) VALUES ('Hypotenuse'); INSERT INTO DeleteMePlease (Name) VALUES ('Factor'); INSERT INTO DeleteMePlease (Name) VALUES ('Phil'); INSERT INTO DeleteMePlease (Name) VALUES ('Hypotenuse'); INSERT INTO DeleteMePlease (Name) VALUES ('Factor'); |
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’?
1 2 3 4 5 6 7 8 9 |
CREATE TABLE DeleteMePlease (id INT IDENTITY, Name NVARCHAR(60) UNIQUE); INSERT INTO DeleteMePlease (Name) VALUES ('Phil'); INSERT INTO DeleteMePlease (Name) VALUES ('Hypotenuse'); INSERT INTO DeleteMePlease (Name) VALUES ('Factor'); INSERT INTO DeleteMePlease (Name) VALUES ('Shadrak'); INSERT INTO DeleteMePlease (Name) VALUES ('Meshek'); INSERT INTO DeleteMePlease (Name) VALUES ('Abednego'); RAISERROR('Don''t worry. It worked but was deliberately rolled back',18,1) |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
CREATE OR ALTER FUNCTION [dbo].[SplitStringToWords] (@TheString NVARCHAR(MAX)) /** Summary: > This table function takes a string of text and splits it into words. It takes the approach of identifying spaces between words so as to accommodate other character sets Author: Phil Factor Date: 27/05/2021 Examples: - SELECT * FROM dbo.SplitStringToWords ('This, (I think), might be working') - SELECT * FROM dbo.SplitStringToWords('This, must -I assume - deal with <brackets> ') Returns: > a table of the words and their order in the text. **/ RETURNS @Words TABLE ([TheOrder] INT IDENTITY, TheWord NVARCHAR(50) NOT NULL) AS BEGIN DECLARE @StartWildcard VARCHAR(80), @EndWildcard VARCHAR(80), @Max INT, @start INT, @end INT, @Searched INT, @ii INT; SELECT @TheString=@TheString+' !', @StartWildcard = '%[^'+Char(1)+'-'+Char(64)+'\-`<>{}|~]%', @EndWildcard = '%['+Char(1)+'-'+Char(64)+'\-`<>{}|~]%', @Max = Len (@TheString), @Searched = 0, @end = -1, @Start = -2, @ii = 1 WHILE (@end <> 0 AND @start<>0 AND @end<>@start AND @ii<1000) BEGIN SELECT @start = PatIndex (@StartWildcard, Right(@TheString, @Max - @Searched) COLLATE Latin1_General_CI_AI ) SELECT @end = @start + PatIndex ( @EndWildcard, Right(@TheString, @Max - @Searched - @start) COLLATE Latin1_General_CI_AI ); IF @end > 0 AND @start > 0 AND @end<>@start BEGIN INSERT INTO @Words(TheWord) SELECT Substring(@THeString,@searched+@Start,@end-@start) END SELECT @Searched = @Searched + @end, @ii = @ii + 1; END; RETURN; END; GO /* So now we give the assertion tests */ IF EXISTS( SELECT * FROM dbo.SplitStringToWords ('This, (I think), might be working') EXCEPT SELECT * FROM OpenJson('[{"o":1,"w":"This"},{"o":2,"w":"I"},{"o":3,"w":"think"}, {"o":4,"w":"might"},{"o":5,"w":"be"},{"o":6,"w":"working"}]') WITH ( TheOrder int '$.o', TheWord NVarchar(80) '$.w')) RAISERROR('The first assertion test failed',18,1); IF EXISTS( SELECT * FROM dbo.SplitStringToWords ('Yan, Tan, Tether, Mether, Pip, Azer, Sezar') EXCEPT SELECT * FROM OpenJson('[{"o":1,"w":"Yan"},{"o":2,"w":"Tan"},{"o":3,"w":"Tether"},{"o":4,"w":"Mether"}, {"o":5,"w":"Pip"},{"o":6,"w":"Azer"},{"o":7,"w":"Sezar"}]') WITH ( TheOrder int '$.o', TheWord NVarchar(80) '$.w')) RAISERROR('The second assertion test failed',18,1); IF EXISTS( SELECT * FROM dbo.SplitStringToWords ('This!!! is a <very> {cautious} test') EXCEPT SELECT * FROM OpenJson('[{"o":1,"w":"This"},{"o":2,"w":"is"},{"o":3,"w":"a"}, {"o":4,"w":"very"},{"o":5,"w":"cautious"},{"o":6,"w":"test"}]') WITH ( TheOrder int '$.o', TheWord NVarchar(80) '$.w')) RAISERROR('The Third assertion test failed',18,1); |
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
1 2 3 4 5 6 7 |
INSERT INTO @Words(TheWord) --SELECT Substring(@THeString,@searched+@Start,@end-@start) -- to force an error try commenting out the line above -- and uncommenting this next line below SELECT Substring(@THeString,@searched+@Start+1,@end-@start) --to make the tests fail END |
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.
1 2 3 |
SELECT @@TranCount --ROLLBACK TRANSACTION --SELECT object_schema_name(object_id)+'.'+name FROM sys.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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Set-Alias Flyway 'C:\ProgramData\chocolatey\lib\flyway.commandline\tools\flyway-7.3.1\flyway.cmd' -Scope local $ProjectFolder = 'PathToTheScripts $Server = 'MyServer' $Database = 'MyDatabase'; $Password = 'MyPassword' $UserID = 'MyUID' $port = '1433' if ($userID -eq '') { $FlyWayArgs = @("-url=jdbc:sqlserver://$($Server):$port;databaseName=$Database;integratedSecurity=true"; <# provide server and password #> )<# the migration folder #> } else {<# just to make things easier to see and document, we'll splat the parameters to FlyWay via a hashtable, but it could just as easily be do as a conventional command line#> $FlyWayArgs = @("-user=$UserID"; <# you only need this and password if there is no domain authentication #> "-password=$Password"; <# Normally, you'd have a routine to provide this dynamically #> "-url=jdbc:sqlserver://$($Server):$port;databaseName=$Database"; <# provide server and password #> "-locations=filesystem:$ProjectFolder")<# the migration folder #> } $FlyWayArgs += @("-locations=filesystem:$ProjectFolder",<# the migration folder #> "-placeholders.projectDescription=a trial database to demonstrate and try out some features of Flyway", "-placeholders.projectName=Pubs" ) Flyway migrate @FlywayArgs |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
#create an alias for the commandline Flyway, Set-Alias Flyway 'C:\ProgramData\chocolatey\lib\flyway.commandline\tools\flyway-7.3.1\flyway.cmd' -Scope local try { $executablepath = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition) } catch { $executablepath = '' } #didn't like that so remove it if ($executablepath -eq '') { $executablepath = "$(If ($psISE) # null if at the commandline { Split-Path -Path $psISE.CurrentFile.FullPath } Else { $global:PSScriptRoot })" } if ([string]::IsNullOrEmpty($ExecutablePath)) { $ExecutablePath = $pwd } .("$executablepath\DatabaseBuildAndMigrateTasks.ps1") $DatabaseDetails=@{ 'server'='TheServer'; #the name of your sql server 'database'='TheDatabase'; #the name of the database 'version'='TheVersionNumber'; #the version 'ProjectFolder'='' 'project'='TheProjectName'; #the name of your project 'uid'='MyUserID' #optional 'pwd'='MyPassword' #only if you use a uid 'locations'=@{}; # for reporting file locations used 'problems'=@{}; # for reporting any problems 'warnings'=@{}} # for reporting any warnings $VerbosePreference = 'continue' Process-FlywayTasks $DatabaseDetails @( $FetchOrSaveDetailsOfParameterSet, $FetchAnyRequiredPasswords, $FormatTheBasicFlywayParameters #deal with passwords ) try {Flyway migrate $DatabaseDetails.FlyWayArgs} catch { $ErrorMessage = $_.Exception.Message $FailedItem = $_.Exception.ItemName $DatabaseDetails.Problems.exceptions += "Flyway failed with $FailedItem : $ErrorMessage" } |
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.