• Flyway
• Unit and integration testing
• Database Testing and Quality

Running Unit and Integration Tests during Flyway Migrations

---

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.

---

The approach I suggest to database unit and integration testing uses Flyway Teams within my Flyway Teamwork PowerShell framework. It draws on three of Flyway’s great strengths:

1. Callback scripts – Flyway supports callbacks, which will execute a script automatically whenever you successfully run of one or more migrations. This callback can execute SQL or Java scripts (community edition) and PowerShell scripts (Teams), This means that we for every successful Flyway migration, we can use a PowerShell callback to execute all appropriate tests.
2. Versioning – Flyway enforces database versioning so, as long as you specify the range of versions for which a test is appropriate, you can write a script that will be executed only if appropriate.
3. Undo scripts – Flyway Team supports use of Undo scripts that we can use to revert to the previous version in a migration fails. This means that you can run a migration in an isolated branch, repeatedly, until it works and passes all the tests that are devised to prove that it works. This practice can only be done in a branch that has had no branches of its own, because by altering previous migrations you alter the past.

I described the basics of how it works in my previous article, Introduction to Testing a Flyway Development. This article focuses on unit and integration tests, and performance tests are covered in the article, Performance Testing Databases with Flyway and PowerShell.

Running automated tests in the Flyway Teamwork framework

Tests are hierarchical in the same way that branches are hierarchical. If you are developing a feature branch that has exotic new functionality, you will want tests that prove that the exotic functionality works, but you’re not in the mood for worrying about anything more prosaic. You just want to test the branch.

At the point of merging with the parent branch, you will want to add tests that prove that your work hasn’t interfered with the functionality of the parent branch. You want to run those tests too, and if the merge works, your tests for the feature need to be added to the parent’s collection of tests. At some point, you may want to run all the tests for the development branch or even the main branch.

To allow all this, my Flyway Teamwork framework now supports the use of a Tests directory that can be added to any branch. The following figure shows a project within the Flyway Teamwork framework, set up for running tests.

We save tests into the Tests folder, each with a filename indicating the range of versions for which the test is applicable. We then need a PowerShell script, which can run as a callback, that retrieves the current version number of database then searches the test locations, provided to it in an array generated by the preliminary.ps1 script, for relevant tests. If there is no Tests directory, it adds one to the main branch as a catch-all where you can add tests that can be used from any branch.

If the Tests directory exists, the PowerShell script executes any tests applicable to the version reached in the Flyway Migrate command. It is up to you as to whether it runs just your branch tests or all of them.

The example: testing a new Search function

In our SQL Server ‘Pubs’ database, the fictional but fractious users are complaining about the difficulty of searching through the Notes table, used to store all the customer complaints and the responses to those complaints, by various employees. This makes it hard to investigate any problems.

The developers have tried to placate the users by adding simple queries like …

… but this isn’t enough, because it is slow, and it doesn’t work for short words because it has no built-in way of defining ‘whitespace’. It is hard to like LIKE.

‘It ought to work just like Google’, mutters the user rep.

We’ve therefore developed a better and much faster search function, called searchNotes, that will look for both strings and words. You can find the migration script to create this search function in search branch of my Pubs Flyway teamwork project.

It will find strings:

You can also search on words that must all be in the note

This will give:

From the customer:

What a time I’ve had with your company. If you are ordering something you actually want or need to arrive on time then go and buy it from somewhere else.. it’s all down to customer service. The management is appalling.

Javier V replied:

Sorry to hear that you had such a bad experience |Thank you for your email although I was obviously extremely concerned to read of your poor experience with our product and offer my sincere apologies. We encountered an issue earlier today that affected a portion of accounts � and it seems like unfortunately yours was one of those affected. We have rectified the underlying problem and you should be up and running again now. Your satisfaction is our number one concern and we promise to do our best to meet your expectations

Devising the tests

Now we need to create some tests that will check that our searchNotes function is working properly. This will have two purposes. Firstly, we need to make sure the first release contains no obvious faults, and once we add the feature, and the associated tests, to production then we ensure that if someone else subsequently makes over-ambitious attempts to improve it, any errors will be picked up when the integration tests are run.

A test for finding single words

The most obvious test is to make sure that the function will find the same number of hits for single words that we know are there.

We write a test routine that will check this. It chooses a random twenty words and searches for them using our table function. We know from the inversion table, WordOccurence, how many hits it should find, so we can make sure it finds the same number. The WordOccurence table is like an index table for the fast search of words in text.

A test for finding phrases

To test a routine effectively, we either need to know the right answer (as we did in the previous article, Introduction to Testing a Flyway Development), or we need to test that we get the same answer via two different algorithms.

This means that we need to test our searchNotes routine against an entirely different way of searching, to make sure the return thew same results. Unfortunately, SQL Server’s alternative (LIKE) doesn’t give the same accuracy because words are separated by one or more whitespace characters or can occur at the start or end of a string. Therefore, a test like this…

…will pass more by luck than science.

We must adopt a better strategy. The following SQL chooses a note at random and grabs a phrase from that note, at random. It then checks to see if the search routine finds that phrase in that note.

OK. We now have enough tests for this demonstration

Normally, we’d have several more tests for a function like this, but we have sufficient for the demonstration.

Creating a Test file

We can put both tests in one script file, called T1.1.12.1-__Check_That_SearchNotes_Works.ps1 and put it in the local Tests directory we’ve created, within the search branch of our project. The T1.1.12.1-__ part of the filename tells the framework that the test is valid all versions from 1.1.12.1 onwards, and the description confirms that it checks that the searchNotes function works.

The test file script looks like this.

Running the test file

Now we just need a PowerShell script that will locate and run our tests, and any other tests that are valid for the database version that’s being checked. If we save the script as an afterVersioned script callback (requires Flyway Teams edition), in the Migrations folder, then we can ensure that the tests automatically run every time a Flyway migration completes successfully.

The final matter to decide is where to put the report. I like to put these test reports along with other reports such as code quality, in a report directory for the version tested. This is provided for you by the PowerShell framework. You’d get this path from this string:

The following figure depicts how the test process works, when running tests automatically as part of a Flyway migration, using a script callback:

Here’s the script that will run the tests. It retrieves the latest database version (using the $GetCurrentVersion script task) and then uses the Run-TestsForMigration script task to locate and run all the test files that are valid for that version.

Both of these tasks are included in the latest version of the DatabaseBuildAndMigrateTasks.ps1 file, which you can find in the Resources folder of my FlywayTeamwork-Pubs project on GitHub. If you look at the code for Run-TestsForMigration, you’ll see that it does no more than the sample code I presented in my previous article on testing.

This is the basis behind the AfterVersioned_TestResults.ps1 callback that you can run the directly in the IDE. The code in the cmdlet Run-TestsForMigration displays the results in the output stream and saves them to disk in the Reports subdirectory for the version of the database tested (as returned by $GetCurrentVersion). In this case, they will be saved in …\Versions\1.1.12.1\Reports\tests.

Every time you run this script, it tests different strings. However, you’ll see results like this:

executing T1.1.12.1-__Check_That_SearchNotes_Works.ps1 (Check_That_SearchNotes_Works)
running the test to check for phrases
1 Found "As much as we rely on" as expected in note 1259
1 Found "on technology and training" as expected in note 1478
1 Found "regret when breakdowns" as expected in note 255
1 Found "and can appreciate your" as expected in note 1602
1 Found "day for the item to be" as expected in note 2224
passed 3 3 Ramona
passed 2 2 Jodi
passed 368 368 which
passed 563 563 Totally
passed 5 5 Maribel
passed 2 2 Natalie
passed 233 233 portion
passed 433 433 I'm
passed 801 801 all
passed 173 173 angry
passed 725 725 obviously
passed 357 357 keep
passed 266 266 happy
passed 1 1 Marty
passed 523 523 buy
passed 117 117 nurse
passed 235 235 let
passed 117 117 basket
passed 775 775 having
passed 1 1 youIn

So, it all passed, which is a relief!

This function is rather different from the typical integration test that compares the result of a process under test with a known correct result for that process; I’ve already shown how to do that standard test against a fixed result in a previous article Introduction to Testing a Flyway Development. Please refer to that article for the PowerShell way of comparing JSON data that hold tabular results.

Running tests when merging

When you pass the index [0] for TestLocations, as we did in the previous example, it runs only the files in the local branch’s Tests folder.

However, you can also run the parent branch’s tests simply by passing the index [1]:

This can be very useful at the point of merge to make sure your changes haven’t causes any hasn’t interfered with the functionality of the parent branch.

If you simply want to run all the tests in the project, you can use:

If a test fails….

Since we’re working in an isolated search branch, supported by its own copy of the database, we can safely undo and rewrite our migrations until every test passes.

If a test fails, we can just ask flyway to reverse the migration. It will use the U1.1.12.1__Search_Functionality.sql. We can then alter the V1.1.12.1__Search_Functionality.sql script to fix the problem and redo the migration. As you extend the migration, you just keep the undo file U1.1.12.1__Search_Functionality.sql up to date so it performs the clean-up adequately.

Summary

Flyway makes testing almost a pleasure. It just needs a little scripting help to make best use of these virtues. Flyway Teamwork supplies the data hashtable and supports the hierarchy that allows you to provide special tests for a branch, without cutting yourself off from more general tests, and makes it practicable to run tests in PowerShell.

So, the ingredient we provide that makes the tests happen in Flyway is a callback script that can also be executed as a normal PowerShell script. This will, in turn, execute every script that it finds in the test directory that is relevant to the version that the database is at. As you add tests, you can test them easily, and add them to the test directory. You can even provide them as a SQL file, but that’s another story that we’ll go into when we describe performance testing.

Tools in this post

Flyway Teams

Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.

Find out more

Redgate Flyway

DevOps for the Database

Find out more