Getting started with tSQLt

It’s time to take the first steps into Database Unit Testing. As mentioned in my previous blog post, I will use tSQLt, an open-source unit testing framework for databases. Since I’m a big fan of SSDT (SQL Server Data Tools), we will use a Database Project to set up the tSQLt framework.

Download the tSQLt Framework

To download the tSQLt framework, you can use the following url that will bring you to the download page Downloads • tSQLt – Database Unit Testing for SQL Server. Once you downloaded the framework, extract all files into a dedicated folder of your choice.

Once extracted, open the folder and you should have a similar view as what is included in the screenshot below.

Initial Setup

As a first step in the process, we’re going to create a new Database Project, in my case, I will be calling my Database Project AdventureWorksDW_UnitTesting and my solution AdventureWorks.

If you are not sure how to set up a Database Project in Visual Studio from scratch, don’t worry, you can follow the step-by-step data recipe I released a while ago, Getting Started with Database Projects and Version Control

Preparation

In the coming steps, we are going to execute the preparations to be able to generate the Database Project structure later on. Before you continue, make sure you have your newly created Database Project in Visual Studio.

As a first step, we are navigating to the SQL Server Object Explorer. If you can’t see this menu, navigate to the view section in the menu bar, and choose SQL Server Object Explorer.

Navigate to localdb\ProjectModels and open the Databases folder. In this folder, you should be able to find a database with the same name as your Database Project. In my case AdventureWorksDW_UnitTesting.

Right-click, and choose new query. Now, navigate to the Windows Explorer, and go to the location where you extracted the tSQLt framework, and search for the PrepareServer.sql file. Op the file in any Text Editor tool and copy the code into the SQLQuery windows in Visual Studio.

To continue, ensure you are connected to localdb\ProjectModels and your project database is selected as the active connection. Press the play button on the left to execute the query.

The first script will as the name mentions prepare the server by enabling CLR and adding the assembly.

As a next step, we need to copy the next script called tSQLt.class.sql, which you can find in the tSQLt framework folder as well, into the SQLQuery window. Now execute this script in the SQLQuery windows by clicking the Execute button again.

As a result, you should receive a thank you message to install tSQLt, similar to the screenshot below.

Adding the Database Object to the Database Project

We have done all the required preparations, so we can set up our Database Project properly. To successfully set up the Database Project, we navigate to the SQL Server Object Explorer in Visual Studio, search for the localdb\ProjectModels, right-click on the database with the name of our Database Project, and choose Schema Compare.

As a next step, we are going to select our Database Project as the target for the comparison and click on the Compare button the the top left in the SqlSchemaCompare window.

This action can take some time and as soon as the schema comparison has been completed, we can identify all created objects by the scripts that we have executed. Now, we choose the Update button, which will start to create all the scripts in our Database Project.

As soon as the update is finished, we can see that we receive a lot of errors in our Error List in Visual Studio. As you can see in the screenshot below, the errors mention something about “… contains an unresolved reference to an object.”. We are receiving these kinds of errors because a lot of the imported objects make use of objects in the master database.

To solve this issue, we need to add a Database Reference to our Database Project. To do this, we navigate to the Solution Explorer, to our Database Project, and search for the References section. We right-click on “References” and choose “Add Database Reference…”

The Add Database Reference window is opening and in this window, we can choose System database, master. We resolve all the errors by clicking the OK button.

As a result, we now have a base Database Project which includes the tSQLt Framework.

Cooking tip

If you have multiple Database Projects where you want to implement Unit Testing, this is the perfect moment to create an SSDT Project Template. This allows you to create a Database Unit Testing project with tSQLt very easily. This project will allow you to start from the point where we currently finished this data recipe.

In the coming weeks, I will release a data recipe where I will go through the steps to create an SSDT Project Template. (Create a SSDT Project Template based on your Database Project)

What’s next?

In the next data recipe, we will create our very first Test Class and Unit Test.

Enjoy!
Bon appetit!