This article talks about multiple ways to install tSQLt for SQL unit testing database projects managed through SQL Server Data Tools (SSDT).

Additionally, the readers of this article are going to understand and implement different methods to integrate the SQL unit testing framework with SQL database projects.

This article highlights the importance of using the SQL unit testing framework right from the beginning of a database development project.

Importance of using tSQLt with database projects

In the world of state-based database development, SQL database project is the most widely chosen option, and when it comes to choosing the SQL unit testing framework with it then tSQLt is the most compatible and suitable option.

For those of you who are familiar with these projects already know that as per state-based development you are only required to define (create) a database project and update it when required without worrying about the underlying change script as this is entirely handled by the system on your behalf.

However, using a sharp SQL unit testing framework, like tSQLt, right from the beginning is always a good idea to maintain an inherited (by default or right from the start) database development and testing strategy.

If you make a habit of integrating your database projects with tSQLt at the beginning of the projects then it is easier to build, test and refactor (change) the projects to meet business and technical requirements.

In this article, we are going to go through three ways by which tSQLt can be installed with the database projects.

Overview of methods

There are multiple ways (methods) by which we can install tSQLt with SQL database projects including the following:

1. Create a New Project method
2. Import Database method
3. Import Script method

Let us now explore these methods one by one.

Method 1: Create a New Project

We can create a tSQLt framework (objects) as a new project side by side with our existing SQL database project to start writing SQL unit tests.

In order to use this method we need to first create a tSQLt database by running the following script against the master database in SSMS (SQL Server Management Studio):

Download tSQLt from the tSQLt.org website and locate tSQLt.class.sql or click the file below and run it against the tSQLt database we have just created:

A successfully installed tSQLt framework should show the following message:

Now, open SQL Server Data Tools (SSDT) in Visual Studio and create a new SQL database project named ToyShopWays under the solution named TSQLT with SQL Database Project:

Once the project is created successfully please refer to it as your main database project for which you are going to install the tSQLt framework.

Go to the SQL Server Object Explorer and connect to the SQL instance where you created the tSQLt database earlier.

Right-click on tSQLt database and click Create New Project…:

Please create a new project named ToyShop_Tests and select the same source (repo) folder where your solution is placed and click Start after unchecking Import referenced logins as shown below:

We need to add the main database project reference to the test project where tSQLt has been installed so that we should be able to create SQL unit tests for our main database.

Next, right click on the References node under ToyShop_Tests project and click Add Database Reference… as follows:

Select Same database option under Database location for ToyShopwWays database project and click OK:

Since tSQLt refers to many sys functions in order to work properly so we need to add master database reference to the test project as well.

Please add master database reference as shown below:

That’s it, you can now comfortably run SQL unit tests against your main database project:

Build the solution and then Press F5 to deploy changes to your debug database and dry run tSQLt tests by running the following script against ToyShop_Tests debug database:

The output is as follows:

All looks good.

Method 2: Import Database

Another way to integrate tSQLt with your SQL database project is by importing the database into your solution as a database project. This method requires the same steps to create the tSQLt database used in the previous method.

Please either create a fresh (database) project and solution or remove the previously created test project in the existing solution to implement the second method in order to avoid any confusion.

We assume we are back to the scenario where the main database project ToyShopWays was created under a solution:

Now right-click on the solution and add a new database project called ToyShop_Tests2:

Next, right-click on the newly created database project and click on Import followed by clicking Database… as follows:

Connect to the tSQLt database created earlier and click Start after you uncheck Import referenced logins option:

The test project is almost ready for SQL unit testing:

Please add ToyShopWays and master database references to the project just like the way it was done in the previous method.

Build and deploy the project followed by test running SQL unit tests:

Since there are not any SQL unit tests written yet so we won’t be able to see any results but it shows that the tSQLt framework is working fine.

Method 3: Import Script

There is another way to add the SQL unit testing framework to the database project and that is by importing the tSQLt script.

You can directly import tSQLt from a script thereby skipping the steps of setting up a tSQLt database before creating a tSQLt database (test) project.

Please be careful as you must not remove the previously created test project (ToyShop_Tests), just like we did (removed) in the previous method because we need to extract the script from the previous project before it can be removed.

Right-click on ToyShop_Tests2 project and click on Publish.

Once you see the Publish Database dialogue box please click on Edit… :

Select the debug database server and type in ToyShop_Tests3 in the Database Name section followed by clicking OK as shown below:

Click Generate Script:

Copy the generated script into a file named ToyShop_Tests3.tsqlt.sql and save it in an accessible folder.

Now you can remove the previously created test project (ToyShop_Tests2) or start off with a new database project to use the import script method.

Create a new test project named ToyShop_Tests3:

Right-click ToyShow_Tests3, click Import and then click Script as shown below:

Select the script we just extracted from the previous project and finally click Finish:

Add ToyShopWays database reference in the same way you added this reference in the previous methods.

However, Add master database reference to the test project by checking the Suppress errors check box as shown below:

Right-click on the project and click Build to see if it builds successfully or not:

If your build output is similar to the one shown above then you have successfully installed tSQLt for SQL unit testing your database project.

You can deploy the project and dry run tSQLt tests against the debug database to get ready for real development and testing work.

Please go through the following articles to proceed further with writing and running SQL unit tests:

1. SQL Unit testing with the tSQLt framework and SQL Server Database Project integration
2. Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing
3. Three Standard SQL Unit Tests you can write against any Stored Procedure

A word of advice

Now that you are aware of all the three methods to integrate tSQLt with your SQL database project, it is up to you to find out which one you are more comfortable with, provided it suits your professional and business requirements.

Please remember that the import script method has an edge over the others once you got it stored due to a number of reasons:

1. You do not need to create and rely on a tSQLt database
2. You can put the script itself under source control to speed up the installation steps
3. It is easily editable by simply changing the name of the desired test database, however, you have to be careful with the rest of the attributes in the script because it is machine-generated

Since, we suppressed the unresolved reference errors for the import script method it may be possible for a bug to find its way as compared to the other methods but then on the other side, after all, it is the machine-generated form of the same original script which other methods are using.

Table of contents

• Author
• Recent Posts

Haroon Ashraf

Haroon’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.

He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award.

He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.

His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).

He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential.

View all posts by Haroon Ashraf

Latest posts by Haroon Ashraf (see all)

• SQL Machine Learning in simple words - May 15, 2023
• MySQL Cluster in simple words - February 23, 2023
• Common use cases of SQL SELECT Distinct - February 2, 2023