Test-driven database development

Test-driven development is a known approach in software development, where you write the tests first before you write your production code. Using this approach will result in writing better test coverage and better-quality code. Test-driven database development (TDDD) is a term that is less well known. But it is basically the same, only for database development.

I use tSQLt, a open source database unit testing framework for SQL Server, in my TDDD approach. Let’s start with a quick tutorial for using tSQLt.

Implementing the framework

1. First download the tSQLt framework
   https://tsqlt.org/
2. Unzip the file
3. Execute the file: PrepareServer.sql
4. Execute the file: tSQLt.class.sql in the database you want to install the framework to
   

Creating your first test

Before creating your first test we need a test class. This is a database schema specially for your tests.

EXEC tSQLt.NewTestClass 'TestClass';

Now it’s time to create our first test. In this case I want to test the function subtract.

CREATE OR ALTER PROCEDURE TestClass.[test_subtract]
AS
BEGIN
    DECLARE @actual int;
    DECLARE @expected int;
    DECLARE @total int = 100;
    DECLARE @minus int = 20;
     SELECT @actual = MySchema.subtract(@total, @minus);
    SET @expected = 80;   --(total - minus)
    EXEC tSQLt.AssertEquals @expected, @actual;
END;

Executing tests

There are three ways to execute tests.

-- Run all tests in your database
EXEC tSQLt.RunAll;
-- Run all tests in test class 'TestClass'
EXEC tSQLt.Run 'TestClass';
-- Run 1 test
EXEC tSQLt.Run 'TestClass.test_substract';

The execution results will be show as in the screenshot below

Nice to know

FakeTable
The tSQLt function “tSQLt.FakeTable” can be used to simulate a table. The function creates an empty snapshot of the table. Your can insert, update or delete records in a table without affecting the actual records in the table. This way you always know the expected outcome of a test.

I usually create several procedures with different scenarios that I can use in my tests. In my scenario’s I use the FakeTable function and fill it with predefined dataset.

AssertEqualsTable
With the “tSQLt.AssertEqualsTable” function you can check if 2 tables are the same.

ExpectException
It is also recommended tot test your exceptions. Therefore, we can use “tSQLt.ExpectException”.

RunAll
It is possible to use “tSQLt.RunAll” in your CI/CD pipeline. The execution results can be added to (for example) Azure DevOps test results.