We have set up our tSQLt Database Project in the previous data recipe, Create a SSDT Project Template based on your Database Project. Now it’s time to dive into the wonderful world of tSQLt Unit Testing. In the meantime, I have added my data warehouse to my SSDT Solution and added this project as a Database Reference to my Unit Testing Database Project. If you are unsure how to do this, you can find all the information you need in my previous data cookbook which you can access via the following link: Getting Started With Database Projects & Azure DevOps.
Recipe Overview
Preparation time: < 10 minutes
Ingredients
- tSQLt Database Project
- Visual Studio
Create a tSQLt Test Class
When working with tSQLt, all unit tests must be created within a specific test class. So as a first step, we will create our first test class.
I have opened my Unit Testing Database Project and we need to navigate to the SQL Server Object Explorer. We search for localdb\ProjectModels, right-click on the Unit Testing database and choose New Query.
To create a test class, we can use a tSQLt Stored Procedure called tSQL.NewTestClass. With the code sample below, I created a new test class called CoreUnitTest.
EXECUTE tSQLt.NewTestClass 'CoreUnitTest'
After creating the test class, we can take a look at what this procedure has done for us. So let’s import the test class by using SqlCompare. If you don’t know how to use SqlCompare in SSDT, feel free to take a look at my previous data recipe where we used SqlCompare to do the initial setup of our Unit Testing Database Project.
If we open the created object (CoreUnitTest), we can see that the executed Stored Procedure created a schema with the mentioned name. And, added an extended property which identifies it as a test class.
Creating a Unit Test
We have created our test class so the time has come to create our first unit test. For this unit test, we are going to check if the AdventureWorksDW database has the expected collation.
To create our first unit test, we are going to add a new Stored Procedure called Test_DatabaseCollation in our Unit Testing Database Project. I created a new folder where I will add all my Core Unit Tests. In the Stored Procedure, we are going to use the following code:
CREATE PROCEDURE CoreUnitTest.Test_DatabaseCollation
AS
-- Assemble
DECLARE @actual VARCHAR(50);
DECLARE @expected VARCHAR(50);
-- Act
SET @expected = (SELECT CONVERT(VARCHAR(50), SERVERPROPERTY('Collation')));
SET @actual = (SELECT CONVERT(VARCHAR(50), DATABASEPROPERTYEX('[$(AdventureWorksDW)]', 'Collation')))
-- Assert
EXEC tSQLt.AssertEquals @expected, @actual;
GO
Explaining the code for the Unit Test
If we take a look at the code that we used above, we can identify 3 sections: Assemble (or Prepare), Act, and Assert. These sections typically are part of every unit test.
First, we need to prepare everything for the unit test. In this example, we don’t have anything to prepare but this would be the place where to prepare your dummy data for the unit test.
To continue, we need to define the Act phase, this is the place where you want to apply the logic that needs to be executed to get the final result. In this case, retrieving the server collation and the database collation.
Finally, a Unit Test ends with the assert phase. In this case, we are validating if the database collation is the same as the server collation.
Important to notice as well is that the Stored Procedure (Unit Test) is created in the CoreUnitTest schema. By doing this, tSQLt can identify that this Stored Procedure is a Unit Test.
Executing the Unit Test
We can use another default Stored Procedure from the tSQLt Framework to execute our Unit Test. The T-SQL code that we are using:
EXEC tSQLt.RunAll
If we execute this code on the Unit Testing Database, we get the following result:
If a Unit Test fails, you will get a similar result as shown in the screenshot below:
Enjoy!
Bon Appétit!