In the previous article SQL unit testing with the tSQLt framework for beginners in this series, we covered tSQLt framework essentials and installation. we also demonstrated basic SQL unit testing through the tSQLt framework. In this article, we will continue to explore SQL unit testing and we will also discuss the benefits of SQL unit testing. Additionally, we will learn how we can use the tSQLt framework in a SQL Server Database Project.

According to my own experience, if you ask the developers that why they care about writing unit tests, the general answer will be something along the lines of “We don’t have enough time to write unit tests.”

Unfortunately, this type of excuse can’t be acceptable because if we experience an error or bug in the production releases, we must spend more time to fix it. No doubt, error fixing time is more expensive and painful than writing unit tests. For this reason, if we want to eradicate this type of problem, we must provide our database designs with unit tests so that we can be confident with our database code. Certainly, unit test helps us to minimize errors and handle them in the early stages of the development process so that the production errors or bugs will be decrease. In the below list, you can find some suggestions about writing SQL unit testing so that you can improve our unit test quality.

Some suggestions about SQL unit testing:

• Make sure each SQL unit test is checking only one indusial functional part
• Don’t use more than one assert expression in the SQL unit test
• To isolate the behavior of the SQL unit test from dependencies
• Give proper and understandable names to the SQL unit tests
• Create documentation about SQL unit test cases and this documentation must include all possible input and output parameters

SQL Server Database Project

If we ever decided to design a new database project in Visual Studio, our first template choice will be a SQL Server Database Project because it allows us to create a new database design very easily with the help of the visual designer tools and we can also import database schema from existing database or data-tier application (.dacpac) files.

Note: Dacpac is a zipped file which includes the database objects definitions in it so that we can import, export and upgrade the databases.

In the database project, we can find and add various database objects to our database design and we can also publish it to an on-premise SQL Server Instance or Azure SQL.

Now we will jump into the demonstration …

Create a new SQL Server Database Project

In this part, we will configure the required settings in order to integrate tSQLt framework and database project and then create a SQL Server Database Project. However, first of all, we need to install the tSQLt Test Adapter for Visual Studio 2017. This adapter allows us to run tSQLt framework unit tests in database projects, so that we can evaluate the results of the unit test behaviors and results in the Test Explorer tab. We will download tSQLt Test Adapter for Visual Studio 2017 and then we will install it. In the downloaded tSQLtTestAdapter file we will click Open.

And then, VSIX Installer will ask you which tools you want to install in tSQLtTestAdapter. We will check Microsoft SQL Server Data Tools for Visual Studio 2017 (SSDT) and Visual Studio Enterprise 2017 (2) and then click the Install button. It will add a new extension to Visual Studio.

If our installation is successful, we will see the following image:

Now, we will launch Visual Studio 2017 (SSDT) and then click the Tools menu and select Extensions and Updates.

In this screen, we will check out the installation of the tSQLtTestAdapter for Visual Studio.

Now, we will add a new SQL Server Database project so that we can design a new database. This database includes only one scalar-valued function and then we will test it in the main screen of the Visual Studio. Click the File menu and then select Project.

We will select SQL Server Database Project and specify a location for the project. We will give TestDbProject name to it.

Right-click in the TestDbProject and select New Item… and after selecting we will add scalar-valued function whose name is CalculateTaxAmount.

We will change the function script with the following codes.

Right-click in the database project and select Publish.

In the publish screen, we will configure the target database connections and then we will publish the project.

We can track the status of the published project in the Data Tools Operations tab

When we connect to the target SQL Server, we can find out our database which we published as a TestDbProject name.

Configure database project for tSQLt framework

In the following steps, we will create a new database and we will use this database in order to create and run a unit test case. A question might arise in your mind that “Why we separate the projects and use separated databases?” Actually, if we place unit test and database design into the same project, we have to put in extra effort to separate unit test cases during production of the publishing operations, so, for this reason we will separate database project and test project. Now, we will connect to target server which we published in the TestDbProject and then create a new database. After that, we will install tSQLt framework.

• Execute the following script and create a new database.
  1 2 3 4 5 6 7 8 9 10 11 12

  USE  master GO EXEC sp_configure 'clr enabled', 1; RECONFIGURE;   GO DROP DATABASE IF EXISTS TestDbProject_UnitTest GO CREATE DATABASE  TestDbProject_UnitTest GO ALTER DATABASE TestDbProject_UnitTest SET TRUSTWORTHY ON; GO

• Download the tSQLt framework and execute the tSQLt.class.sql query file in the TestDbProject_UnitTest database finally you will see the thanks message and tSQLt framework version information
• We will create a new test class through the following query
  1	EXECUTE tsqlt.NewTestClass 'TestUClass'

  Note: For detailed information about the tSQLt framework installation, see SQL unit testing with the tSQLt framework for beginners

Now, we will return into Visual Studio and add a new database project. This project will include our unit test cases. Right click in the solution and then select New Project…

We will import TestDbProject_UnitTest database to this project. Right click into TestDbProject.UnitTest project and select Import and then Database…

In the Import Database screen, we will make a connection to TestDbProject_UnitTest

database and click the Start button.

And then Import Database screen will appear.

Our solution will look like the following illustration:

If you try to build the database project, you will face the following error:

We will click the References and then select the Add Database Reference

In the Add Database Reference screen, we will master database as a reference because some of the tSQLt objects are related to master database tables or views.

We will create test setting file which includes only the following settings and then will save as

the testlocal.runsettings name. Afterward, we will locate this file into the project folder.

<?xml version=”1.0″ encoding=”utf-8″?>
<RunSettings>
<TestRunParameters>
<Parameter name=”TestDatabaseConnectionString” value=”server=localhost;initial catalog= TestDbProject_UnitTest;integrated security=sspi” />
</TestRunParameters>
</RunSettings>

In this step, we will change the test setting file and set this new file which we created in the previous step. Click the Test menu and then click to Select Test Setting File and then select the testlocal.runsettings file.

Run tSQLt unit tests in database project:

In this part, we will add a new unit test to TestDbProject.UnitTest and it automatically found by tSQLt extension. We will add a new stored procedure which includes the unit test case.

We will change the stored procedure with the following query. The significant point about this step is that stored procedure name and query file name must be same.

And the finally test tax amount unit test appears in the test explorer so that we can run all tSQLt unit test.

In this step, we will add a Post-Deployment Script file because we want to enable TRUSTWORTHY option after the publishing process so that we don’t experience any error when we run unit test cases.

We will add the following query to Pre–Deployment Script file.

Finally, we will publish our unit test project to the target server and then we enable to run the unit tests. Right-click into TestDbProject.UnitTest and then select Publish. Configure to target server and database and then click the Publish button.

In the test explorer, we can find out the unit test so that we can run the test. Click the Run All in order to run unit tests.

Conclusion

In this article we discussed benefits of the SQL unit testing and we also integrated the tSQLt framework to SQL Server Database Project with the help of the tSQLt Test Adapter for Visual Studio 2017. I want to add a notice about tSQLt test adapter, this adapter is an open source project so that we can access the project code in GitHub.

Table of contents

• 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

Latest posts by Esat Erkec (see all)

• SQL Performance Tuning tips for newbies - April 15, 2024
• SQL Unit Testing reference guide for beginners - August 11, 2023
• SQL Cheat Sheet for Newbies - February 21, 2023