SQL Unit Testing reference guide for beginners

In this article, we are going to learn the basics of SQL unit testing and how to write a SQL unit test through the tSQLt framework.

Introduction

Unit testing is a testing technique that allows us to the test functionality of the smallest and programmable part of the software. Our goal to write unit tests is to make sure that every atomic part of the software works as expected. In terms of database development, unit testing helps us to test the functionality of the database’s small units of objects. At this point, this question may appear in your mind:

How can we implement unit tests in SQL Server?

tSQLt is a powerful, open-source framework for SQL Server and we can use this framework to write unit tests for SQL Server.

Installing tSQLt framework

To download the tSQLt framework, we can go to the tSQLt.org website and then download the latest version of the tSQLt framework. In the download file, we can find PrepareServer.sql. Initially, we will execute PrepareServer.sql to enable CLR and installs a server certificate that allows the installation of the tSQLt CLR.

As a second step, we need to execute the tSQLt.class.sql file to install the tSQLt functionalities. After execution of this script, this message will appear thus we can understand tSQLt framework has been installed successfully.

Writing a basic SQL unit test

After learning the benefits and advantages of SQL unit testing, we can start to write our first unit test. First of all, we need to create a test class so that we can gather and organize our tests under this class. In addition, a test class allows us to execute tests as a group that belongs to the test class. A test class can be executed as follows:

EXEC tSQLt.NewTestClass 'unit_test_demo_class';

The tSQLt framework includes a bunch of test methods for the SQL unit testing operations and we can use these methods for different test scenarios.

tSQLt.AsserEquals
tSQLt.AssertEqualsTable
tSQLt.AssertEmptyTable
tSQLt.AssertEqualsString
tSQLt.AssertEqualsTableSchema
tSQLt.AssertLike
tSQLt.AssertNotEquals
AssertObjectDoesNotExist
AssertObjectExists
AssertResultSetsHaveSameMetaData
Fail

Let’s start with the very basic function of the tSQLt framework and thus try to figure out how the tSQLt framework works.

AssertEquals: This method compares the actual output value with the expected value. This method takes three different parameters:

@expected: The expected parameter value specifies the value which we expect after the execution of the test process.

@actual: The actual parameter specifies the value returning from processing during the test.

@message: This parameter is optional and helps to customize the message when the actual and expected values are not equal.

The following scalar-valued function calculates and returns the age of the passed parameter.

CREATE FUNCTION sales.calculating_age (@date_of_birth DATE)

RETURNS INT

BEGIN

DECLARE @age INT

SET @age = DATEDIFF(YEAR, @date_of_birth, GETDATE()) - CASE

WHEN (MONTH(@date_of_birth) > MONTH(GETDATE()))

OR (

MONTH(@date_of_birth) = MONTH(GETDATE())

AND DAY(@date_of_birth) > DAY(GETDATE())

)

THEN 1

ELSE 0

END

RETURN @age

END

Now, we will write a unit test for this function.

CREATE unit_test_demo_class.[test_dbo_calculating_age_function] AS

BEGIN

DECLARE @tested_birth_date AS DATE = '01/01/1998'

DECLARE @expected AS INT = 24

DECLARE @actual AS INT

SELECT @actual = sales.calculating_age('19980101')

EXEC tSQLt.AssertEquals @expected

, @actual

END

If we interpret this function line by line ;

CREATE unit_test_demo_class.[test_dbo_calculating_age_function]

At first, we give a name to our test function. At this point, we need to give more readable name to our test procedures

DECLARE @tested_birth_date AS DATE = '01/01/1998'

In this part of the code, we declare a variable that passed as input to the scalar-valued function as an input.

DECLARE @expected AS INT = 24

We declare the expected value for our SQL unit testing and then assigned it to an expected value which we wait for after the execution of the function.

DECLARE @actual AS INT

SELECT @actual = dbo.calculating_age('19980101')

In this code part, we declare a variable for actual value and assigned the function result to this variable.

EXEC tSQLt.AssertEquals @expected, @actual

Lastly, we compare the expected value to the output of the age-calculating function. Now, we are going to execute our test case and are going to look at the result of the test case.

tSQLt.Run '[unit_test_demo_class].[test_dbo_calculating_age_function]'

The result of the test execution shows us, the age-calculating function passed the test. However, if we change the excepted value of the test case, the output of the test result will like as the following.

Arrange, Act and Assert Patterns in SQL Unit Testing

In unit testing, there is a pattern that is widely used to structure the unit test. This pattern is known as the AAA pattern and all A defines the Arrange, Act, and Assert.

Arrange: In this part of the unit test, the test inputs, and required objects are defined, and also the expected result which we wait for after the execution of the test is defined in this part of the unit test.

Act: This is where we invoke the code we are testing.

Assert: It is the part where the result from the tested method and the result we expect is compared.

Using the AAA pattern is the most effective way to write an effective unit test and we can use this pattern in the SQL unit testing. In the following, we can see how to implement this pattern in our previous unit test.

Isolating the dependencies in SQL unit testing

Isolating SQL unit testing from the dependencies will help to develop more robust and less fragile unit tests because the main purpose of SQL unit testing is to test the functionality of the smallest part of the database objects. At this point, we need to fake (mock) the dependent objects which we want to test. Mocking is the creation of fake versions of the objects that the code depends on while testing the code. The reason this is done is to do the testing at the true unit level when doing unit testing. In the tSQLt framework, we can use the following functionalities to isolate the dependencies:

ApplyConstraint
ApplyTrigger
FakeFunction
FakeTable
RemoveObject
RemoveObjectIfExists
SpyProcedure

Writing an advanced SQL unit test

When we look at the following procedure, it returns the total sales amount of the customers according to their ages. However, this procedure has two dependencies, the first one is related to the foreign key constraint and the second on
e is including a scalar-valued function.

CREATE TABLE sales.customer (

customer_id INT PRIMARY KEY

, customer_name VARCHAR(100)

)

CREATE TABLE sales.sales_transactions (

sales_id INT PRIMARY KEY

, customer_id INT FOREIGN KEY REFERENCES sales.customer(customer_id)

, sales_amount INT

)

CREATE PROCEDURE sales.calculate_customer_sales_amount @customer_name AS VARCHAR(100)

, @customer_birth_date AS DATE

DECLARE @customer_id AS INT

DECLARE @customer_age AS INT

SELECT @customer_id = c.customer_name

FROM sales.customer c

WHERE c.customer_name = @customer_name

SELECT @customer_age = sales.calculating_age(@customer_birth_date)

IF @customer_age > 25

BEGIN

SELECT SUM(sales_amount)

FROM sales.sales_transactions st

WHERE st.customer_id = @customer_id

END

ELSE

BEGIN

SELECT SUM(sales_amount) * 0.5

FROM sales.sales_transactions st

WHERE st.customer_id = @customer_id

END

Under this circumstance, we need to avoid these two dependencies to write a more robust unit test. The FakeTable function of the tSQLt framework helps us to create an empty version of the specified table without constraints. So that, we can insert any value into it during the execution of the test. The other method we will use will be FakeFunction because the age-calculation function gets a date parameter as input and affects the result of the procedure according to the customer’s age. First, we are creating a new test class.

EXEC tSQLt.NewTestClass 'unit_test_sales';

In this step, we need to create a mock of the function that calculates the age because our result set is changing according to the result of this function. So, we can ensure that the function will always return a constant value.

CREATE FUNCTION unit_test_sales.Fake_calculating_age (@date_of_birth DATE)

RETURNS INT

BEGIN

RETURN 15

END

After all these explanations our unit test design will look as below.

CREATE PROCEDURE [unit_test_sales].[test calculation_of_the_sales_amount]

BEGIN

DECLARE @random_birthdate AS DATE

EXEC tSQLt.FakeFunction 'sales.calculating_age'

, 'unit_test_sales.Fake_calculating_age';

EXEC tSQLt.FakeTable 'sales.sales_transactions';

EXEC tSQLt.FakeTable 'sales.customer';

INSERT INTO sales.customer (customer_id, customer_name)

VALUES (1, 'Unit Test Customer')

INSERT INTO sales.sales_transactions

VALUES (1,1,10)

INSERT INTO sales.sales_transactions

VALUES (1, 1, 20)

DROP TABLE IF EXISTS unit_test_sales.expected

DROP TABLE IF EXISTS unit_test_sales.actual

CREATE TABLE unit_test_sales.expected (sales_amount INT)

INSERT INTO unit_test_sales.expected

VALUES (13)

CREATE TABLE unit_test_sales.actual (sales_amount INT)

INSERT INTO unit_test_sales.actual

EXECUTE sales.calculate_customer_sales_amount @customer_name = 'Unit Test Customer' , @customer_birth_date = @random_birthdate

EXEC tSQLt.AssertEqualsTable 'unit_test_sales.expected'

, 'unit_test_sales.actual';

END

The result of the unit test will not pass the test.

tSQLt.Run '[unit_test_sales].[test calculation_of_the_sales_amount]'

Now we will tackle the SQL unit testing code details line by line according to the AAA pattern and make a comprehensive explanation.

Arrange: In this part of the unit test, we faked the age calculation function, so that we enable it to return a hard-coded value and we designed the SQL unit test according to the certain value. Besides this, we created the faked versions of the customer and sales_customer tables because this table should be empty during the unit test and the procedure worked on the defined rows in the unit test. We also created the actual and expected tables to populate the expected row and actual rows to make a comparison at the end of the unit test.

Act: In here, we executed the procedure which we want to test and populate the returning result set into the actual table.

Assert: In this last part of the unit test, we compare the actual and expected results

When we look at the bit

The “<” sign shows the row exists in the expected table but does not exist in the actual table
The “>” sign shows the row exists in the actual table but does not exist in the expected table
The “=” sign shows the row already exists in the actual and expected tables

Summary

In this article, we learned basics of the SQL unit testing and how to write a SQL unit test using the tSQLt framework. Then, we highlighted the fundamental benefits of writing unit tests which are below:

Improves the quality of the code
Helps to find out the bugs before the production deployments
Accelerate the deployment velocity

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec

Introduction

Installing tSQLt framework

Writing a basic SQL unit test

Arrange, Act and Assert Patterns in SQL Unit Testing

Isolating the dependencies in SQL unit testing

Writing an advanced SQL unit test

Summary

Related posts: