Database Unit Testing + Continuous Integration

Problem:
In the current mob I work in, we deal with an application that contains complex logic in the database (i.e. Business Rules). When bugs appear we first take time to understand its logic and why this scenario caused it to misbehave. After applying a fix, we test the new logic against the scenario that brought up this bug. Although this may fix the initial problem, we are unaware if this new change has broken anything else.

For example, we optimized a Stored Procedure that determined how many transactions it takes to balance out a redemption and mark those transactions as ‘used’. Originally it used a cursor to loop around each transaction until it reached the redemption amount. But this took minutes (especially for big customers redeeming large amounts) and would timeout, so we optimized. We brought it down from minutes to seconds, a major improvement. Everything seemed fine until months later we noticed an edge case that caused the Stored Procedure to not mark these transactions as ‘used’. Fixing these accounts took a couple of days and this was where we decided, we need Database Unit Testing.

tSQLt:
What is tSQLt? tSQLt is an open source database unit test framework for Microsoft SQL Servers. This framework allows you to implement unit tests in T-SQL. Some major features that tSQLt has are:
1. Tests are wrapped in transactions: independent and reduces cleanup
2. Tests can be grouped within schema: allows you to organize your tests
3. Ability to fake tables and views and to create stored procedure spies: removing constraints and dependencies.

How to Install tSqlt:
1. Download and extract the zip folder
2. Execute SetClrEnabled.sql file on database
3. Execute tSQLt.class.sql file on database
4. Done!

I won’t go into much details about tSQLt here, but there are many resources available. This post is mainly about how we integrated automated Database Unit Tests into our Continuous Integration.

Learn more about tSQLt:
Homepage
Pluralsight
YouTube

Using DB unit tests with Continuous Integration:
We use ThoughtWorks GOCD as our Continuous Integration and Delivery service. We had to figure out a way to implement Database Unit Tests in our application’s pipeline, and it came up with the following steps:
1. Install tSQLt to Database
2. Install Tests (checked into Source Control, 1 script file per StoredProc/Function/View)
3. Run tests

Since the tSQLt framework and tests are just scripts that we need to execute on the database, we created a PowerShell script that goes into a folder and executes all .sql files into the database. Below is the PowerShell Script we developed:

Download file – ImportScripts

We saved the PowerShell script in a shared location that can be reached from any of our agents. Setting up a task for steps 1 and 2 can be done with the format seen from the image above.

Now that the tSQLt framework and db unit tests are in the database you must execute them by running the stored procedure tSQLt.RunAll. Below is another script we created to run a stored procedure from a database

Download File – RunStoredProcedure

Conclusion:
Now that we have Database unit tests integrated in our CI pipeline, whenever a change is required to this complex database we always include unit tests. This gives us the confidence to make changes and have instant feedback that we did not break any expected behavior. When fixing a bug and adding unit tests we can make sure this bug never gets back into our code.

Thanks Dexter!