Testing SQL Server Code with TST

Automated Testing (unit/integration) is an integral part of any agile development process. However a project with significant logic housed in SQL Server stored procedures or other database code like triggers/functions creates severe constraints to writing unit level tests, especially if they are large, complex and depend on data. We will explore the TST framework and a few ideas for writing and maintaining good tests for database code.

I recently started working for a client that had a lot of stored procedures in their existing project – the procedures contained mission critical code and were written by strong programmers, but were never-the-less painful to change. We had a nice .NET web app sitting on top of all this, which had a well-maintained test suite but when it came to stored procedures we were pretty much flying blind.

And then I had to modify one of the most critical procedures and we just decided that it was too risky to do that without having any tests. So began my search for a way to effectively test Stored Procedures.

Enter TST. TST is an Open source Unit Testing framework specifically meant for testing SQL Server Database Code. It helps in following ways -

1. Has an Assertion Framework for testing Database code – includes asserts for comparing table level data
2. Allows tests to be divided logically into suites
3. Has setup and teardown procedures, as well as the ability to roll back the entire test session

We will explore this with a sample later on.

Also, as useful as TST is, writing tests for database code can still be challenging for several reasons -

1. Code tends to be dependent on data in tables – either setup or transactional data
2. Code depends on other code – stored procedures calling other stored procedures or functions, depending on triggers to do some stuff, etc. There is currently no known way to stub out these dependencies
3. The result of a stored procedure or other database code is often data changes, often in multiple tables

Due to some of these things, the there is a lot of setup needed for testing a behaviour. It was apparent that just using TST was not sufficient, we needed to formulate a way to write the tests in a way that they were easily maintainable. After a couple of false starts, it was BDD that inspired me, with its clean separation of spec definitions and the specs themselves. I will outline this technique of writing good maintainable tests using TST through the rest of this article.

Let us take an example. Download this code sample. Read the readme file for instructions to setup it up.

Now this is the data model -

As you can see there 4 tables (SCHOOLS, CLASSES, STUDENTS and TRANSFERS). Also there is one stored procedure – TRANSFER_STUDENT – whose job is to -

1. Update the student’s class id
2. Enter a record in the transfers table with old and new class ids
3. Mark the student as transferred
4. Return an error if there is any problem

Our job is to now test whether this stored procedure does its job properly.

As you can see, there are three main cases we need to test which are covered in the tests in the sample -

1. Calling the stored procedure with an invalid student_id (but valid to_class_id)
2. Calling the stored procedure with an invalid to_class_id (but valid student_id)
3. Calling the stored procedure with both valid student id and class id

The sample code contains tests for all these three scenarios. Let’s explore the main case, i.e. both input parameters have valid inputs.

Now this is the approach we use for testing -

1. Create prerequisite data
   1. Create a valid school
   2. Create two valid classes
   3. Create a valid student assigned to first class
2. Run the stored procedure with the student_id and second class_id
3. Validate that
   1. Student’s class is updated to point to second class
   2. A transfer record is created
   3. Student is marked as transferred

Now if we try to do everything in a single test, there are several issues -

• The test can become too long and unwieldy
• It will be difficult for a reader to understand exactly what the test is trying to achieve
• we will miss out on reusability of certain steps (for eg create a valid school)

The best way to circumvent this is to create helper Stored Procedures (think BDD spec definitions) that will focus on doing a particular job properly. For instance SQLTestStep_given_valid_school just creates a new school record and returns the school id to the test procedure. There are two types of helpers here, the GIVENs setup the required data and the THENs run the various assertions required. So the test body itself looks really simple, like this -

exec SQLTestStep_given_valid_school @v_school_id out
exec SQLTestStep_given_valid_class @v_school_id, @v_class_id_1 out
exec SQLTestStep_given_valid_student @v_class_id_1, @v_student_id out    
exec SQLTestStep_given_valid_class @v_school_id, @v_class_id_2 out
exec pr_transfer_student @v_student_id, @v_class_id_2, @v_error out,  @v_error_mesg out
exec TST.Assert.Equals 'Error should be false', 0, @v_error
exec TST.Assert.Equals 'Error message should be blank', '', @v_error_mesg
exec SQLTestStep_then_student_should_be_in_class @v_student_id, @v_class_id_2
exec SQLTestStep_then_student_should_be_transferred @v_student_id
exec SQLTestStep_then_should_have_new_transfer @v_student_id, @v_class_id_1,  @v_class_id_2

The first four “given” procedures are setting up the prerequisite data, without depending on any existing data – this is very important, because your same tests might run on different databases (different developer machines, CI build servers, etc.) – as such you always want to setup the data required by the logic under test either in your test, or in a setup method which you can define at the suite level. You can refer to the TST documentation on how to do that.

Lets’ explore what a Given procedure does – for instance, the body of SQLTestStep_given_valid_school looks as follows

CREATE PROCEDURE SQLTestStep_given_valid_school  
      @p_school_id int  out
   as 
 begin 
      insert into schools (school_name) values ('Test_school');  
      set @p_school_id =  SCOPE_IDENTITY();
 end
 GO 

After the given statements, is the actual call to the procedure under test. Note how the given statements have out parameters with data that needs to be passed to the procedure under test.

And finally we have the “then” statements – these helper stored procedures actually run the assertions and check whether the desired effect has been achieved. For e.g. this is how the body of SQLTestStep_then_should_have_new_transfer looks –

 select @v_count = count(1) from transfers 
 where student_id =  @p_student_id and 
 from_class_id = @p_class_id_1 
 and to_class_id =  @p_class_id_2  

 exec TST.Assert.Equals 'New transfer should be created', 1,  @v_count  

So as you see, it is quite possible to write maintainable tests for stored procedures, along with reusable Test steps, using some of the techniques used in BDD. It may look very tedious, but in practice it is not – once you have your test steps created, they start acting as your very own DSL for writing tests – over time a lot of test steps start getting reused. One thing you need to do is be careful about adding unnecessary test steps if existing ones can be reused by just adding new nullable parameters.

When writing the test itself, it’s quite useful to start by writing your given..when.. then statements in plain english first, before actually using the test steps and (if required) creating them.

Now, how do I run the tests?

Running the tests is simple –

 exec TST.Runner.RunAll @TestDatabaseName =  'TST_USAGE_SAMPLE' 

where the @TestDatabaseName is the database under test.

How do I run a single test?

There are several run methods that TST provides. For instance -

exec TST.Runner.RunTest 'fc_mdt',  'SQLTest_pr_transfer_student_checks_invalid_student' 

will run only one test. We can also break our tests into suites (by following naming convention SQLTest_<suitename>#<testname> and then running

exec TST.Runner.RunSuite 'fc_mdt', <suitename>

will run the tests only in that particular test suite.

What do the results look like when the tests pass? When the fail?

This is the sample output when everything looks great –

Suite: Anonymous. Tests: 3. Passed: 3. Ignored: 0. Failed: 0  
Test: SQLTest_pr_transfer_student_checks_invalid_class. Passed 
Test: SQLTest_pr_transfer_student_checks_invalid_student. Passed 
Test: SQLTest_pr_transfer_student_valid_student_transfer. Passed
Start: 14:58:49. Finish: 14:58:52. Duration: 2916 miliseconds. 
Total suites: 1. Total tests: 3. Test passed: 3. Test ignored: 0. Test  failed: 0.
 
TST Status: Passed

Let’s say we had forgotten to mark the student record as transferred i.e. set TRANSFERRED = 1 in the Student record. This is how our tests results would look–

Suite: Anonymous. Tests: 3. Passed: 2. Ignored: 0. Failed: 1 
Test: SQLTest_pr_transfer_student_checks_invalid_class. Passed 
Test: SQLTest_pr_transfer_student_checks_invalid_student. Passed 
Test: SQLTest_pr_transfer_student_valid_student_transfer. Failed 
    Failure: Assert.Equals failed. [Student should be marked transferred] Test value: 1 (int). Actual value: 0 (bit)
Start: 15:03:06. Finish: 15:03:06. Duration: 100 miliseconds.  
Total suites: 1. Total tests: 3. Test passed: 2. Test ignored: 0. Test  failed: 1.
 
TST Status: Failed

The more detailed you make your assertion messages the better it is to debug issues and regressions.

How do I incorporate the tests into my build process?

There are multiple ways to do this but the simplest one is to update your runTests.sql like so –

declare @TestSessionpassed bit;
exec TST.Runner.RunAll @TestDatabaseName = 'fc_mdt', @TestSessionpassed =  @TestSessionpassed out
IF @TestSessionpassed = 0
BEGIN
RAISERROR ('Stored Procedure Tests Failed',11, 1)
END;

and then creating adding MSBuild steps to your build file to run this script, something like below –

<ItemGroup>

<TSTTests Include="$(Scripts)\runTests.sql" />
</ItemGroup>
…
<Target Name="RunTSTTests" DependsOnTargets=”Build”>
<Message Text="Test Connection: $(ConnectionString)"/>
<MSBuild.ExtensionPack.SqlServer.SqlExecute TaskAction="Execute"  Retry="true" UseTransaction="false" Files="@( TSTTests)"  ConnectionString="$(ConnectionString)" CommandTimeout="1000"/>
</Target>

(MSBuild Extension pack provides more than 400 useful build tasks, loggers and TaskFactories which cover a wide range of applications, including working with SQL Server, which we are doing here).

Now whenever the RunStoredProcedureTests step is run this will automatically execute the stored procedure tests and also show the results in the console output of the build runner. The RAISEERROR will fail your build if any of the tests fail so that you can have a look and see what’s gone wrong.

Note that this itself does not deploy your database code changes, you have to use a migration framework like migratordotnet to version your database code and add a migration step to your build process before the tests are run – explaining how to do that is outside the scope of this article but you can take a look at the following example.

The bonus with using TST is that it rolls back everything so the database state is pretty much unaltered as far as your data is concerned. No matter how many times you run it. However watch out for -

• Identity values will be lost since ROLLBACK does not get you these id values back
• Explicit transaction handling within the stored procedures will interfere with TST’s own transactions – if you have to have transaction handling in your stored procedures, you should disable TST auto-rollback and write manual cleanup procedures
• TST tests are data heavy – so if the same database is used for multiple purposes (for instance automated as well as manual testing) you might get issues such as resource deadlocks that will cause false failures

However even with the above drawbacks, I think TST helps make T-SQL code code more robust, and splitting these tests into Test steps makes them more maintainable. You can also write tests for testing functions and triggers using the same technique. And you can break them up into Test suites by following the TST conventions. It does become difficult to test code that is highly coupled (a stored procedure calling two other stored procedures, which in-turn might call other procedures) but if you try to focus your testing around the logic of this procedure, it’s still possible to have some useful tests rather than no tests at all.

The TST documentation is quite detailed, so please refer to it on details such as how to write Setup/Teardown logic, organize your tests into Test Suites, write different assert statements and more.

And now you should be all set to start writing your own database tests! What do you think?

Acknowledgements: Special Thanks to Deep Shah who actually figured out how to integrate these tests into the build when we first started using TST.  

About the Author

Roopesh Shenoy is a Software Developer and an enterpreneur focussed on using technology in Education. He also does consultancy for .NET and Mono-based projects. He blogs here and tweets under the handle @sqlhorror.