Phil Factor

8 November 2023

• Flyway
• Database Testing and Quality

Running Database Assertion Tests with Flyway

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.

What are database assertion tests?

Conventional assertion tests, on applications, will check on simple matters of data integrity. However, in relational databases, such checks are built into the system. We use constraints to protect data consistency and integrity, and to manage the basic rules that define the relationships. We use triggers to enforce the more complex conditions governing relationships. We usually test our data integrity logic directly after a build, by running unit tests using procedural SQL code. Assertion tests for relational databases are more geared to ensuring that business processes are handled correctly.

Every time we change a database component that participates in a business process, we must re-run the assertion tests to check if the result is still correct. If these tests are automated, it is usually safer to just re-run them routinely as part of integration testing.

With an assertion, we confidently claim that a query result is what it should be. In real life, we will have a standard result that is agreed by all the participants in the team to be the correct result of a process, given a particular set of inputs. I’ve usually worked closely with company accountants to do this sort of work: they don’t know the computing side, but they have a heightened understanding of what is, and isn’t, correct!

Running assertion tests in Flyway

I’ve described previously how to run Arrange-Asset-Act-Teardown tests in the Flyway Teamwork Framework. I described the Arrange, Act, and Teardown sections of an AAAT test file, but I was a bit reticent on the details of the Assert section of the test file. This article remedies my neglect.

In our assertion tests, we specify the SQL to run. To check whether a result is correct, we obviously need a ‘correct result’, from a JSON file that we specify. We want it in JSON because this data format is well-supported nowadays in most RDBMSs.

In the next section, I’ll show how to run automated assertion tests using the full test facilities of the Flyway Teamwork framework, but let’s start by just simulating a simple test run. We use the Execute-SQLStatement cmdlet to execute the SQL we want to test and save a JSON version of the result. We then run the same query again and use a supporting Cmdlet, Compare-Resultsets, to compare the JSON result sets. These cmdlets work for all supported RDBMSs.

We’ll save the ‘correct result’ to the location of our tests, because, let’s say, we want to make a change to the query and be sure we get the same result:

Note that, for a SQL Server call using this cmdlet, there should be no final semicolon to the statement. This is removed in the actual AAAT test execution for SQL Server.

The Flyway Teamwork framework supports a series of test locations that you can set up within the project. This means that there is always a list of your test directories available to you within a PowerShell script. If you create a test directory named Tests, Flyway Teamwork will find it.

Now that we have the standard result saved, in the current test directory, the next step is to compare this saved result with the results of running the new version of our query. Here, we’ll just run the same query again, and this time make it the TestResult. It will, hopefully, be the same. We then compare the two:

We run it and there are no surprises:

We checked 1000 records and all were the same

Well, that’s fine but what about if there were differences in a real test? We’ll go into the MyTest.json file, holding the correct result, and change just one record, altering a book’s title slightly. We then re-run the test:

for row 0 the values for the title column,  Democratizing Scrutiny as Aphoristically Constructed Mediators. and  Demonizing Scrutiny as Aphoristically Constructed Mediators. don't match

Using an annotated script to run automated database assertion tests

At some point, this will get too fiddly to manage. It becomes easier to use an annotated script to provide a list of assertion checks that need to be run. In the Flyway Teamwork framework, we can run a single annotated SQL file (AAAT file) that can be checked in an IDE, saved to the test directory, and then run by the Framework. I’ve described the basics of how this works in my previous article, Running Structured Database Tests in Flyway.

Here is a simple example, which is essentially the Assert section of a complete AAAT test file. We list the queries we want to test and in the block header for one, we specify a JSON file against which to verify the result:

Save the above assertion test file to the Tests sub-directory, for the branch under test. In this example, we’re testing the develop branch. We save the file with an ‘A‘ prefix and the start and end versions for which the test is valid from versions 1.1.10 to current version. I also saved JSON files with the correct results for each of the queries in the script, as described previously. Each file must have the name specified in the ‘Compare with…’ block comment of the assertion test script (you may need to remove the warning from the PricePerTye.json file before running the tests):

Now we use the Run-AnnotatedTestScript cmdlet, which will parse the test file, execute each of these queries (using the Execute-SQLStatement cmdlet) and compare their results against the results stored in the JSON files stored in the same directory (using Compare-Resultsets):

This will then report any differences between the two result sets:

A Flyway report has been generated here: <…PathTo…>\Pubs\Branches\develop\report.html
For OrdersPerTitlePerCompany, We checked 21 records and all were the same.
For OrdersPerTitleAndBuyers, We checked 21 records and all were the same.
For PricePerType, We checked 1000 records and all were the same.
For BookByPrice, We checked 1000 records and all were the same.

Conclusions

Testing isn’t an intrinsically exciting activity. It is important to make it as convenient as possible and requiring as little human interaction as possible. If your database is properly provided with all the possible constraints and triggers, you need only use Assertion testing for business processes. I know from experience how essential these tests are, and can remember how unexpected an assertion failure was, and how much grief an assertion test can prevent.