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
- First download the tSQLt framework
- Unzip the file
- Execute the file: PrepareServer.sql
- 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;
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
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.
With the “tSQLt.AssertEqualsTable” function you can check if 2 tables are the same.
It is also recommended tot test your exceptions. Therefore, we can use “tSQLt.ExpectException”.
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.