Esat Erkec
tSQLt Test Adapter for Visual Studio VSIX Installer

SQL Unit testing with the tSQLt framework and SQL Server Database Project integration

March 25, 2019 by

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.

SQL Unit testing - tSQLt Test Adapter for Visual Studio 2017 installation

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.

SQL Unit testing - tSQLt Test Adapter for Visual Studio VSIX Installer

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

SQL Unit testing - tSQLt Test Adapter for Visual Studio success message.

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

SQL Unit testing - Check up the extensions in Visual Studio

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

SQL Unit testing - Extensions and Updates list in 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.

SQL Unit testing - How to add SQL Server Database Project?

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

SQL Unit testing - How to find  SQL Server Database Project?

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

SQL Unit testing - Add Scalar-valued function to database project

We will change the function script with the following codes.

SQL Unit testing - Create Scalar-valued function in database project

Right-click in the database project and select Publish.

SQL Unit testing - Publish SQL Server Database Project to SQL Server instance

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

SQL developer unit testing - Database project publish settings.

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

SQL developer unit testing - Data Tools Operations publish success message.

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

SQL developer unit testing - List of SQL Server databases

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.

  • 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

    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…

SQL developer unit testing - Add new database project.

SQL developer unit testing - Add SQL Server Database Project

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

SQL developer unit testing - Import database from SQL Server into database project

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

database and click the Start button.

SQL developer unit testing - Import database settings

And then Import Database screen will appear.

SQL developer unit testing - Import database summary

Our solution will look like the following illustration:

SQL developer unit testing - Solution view of the database design and tSQLt framework.

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

SQL developer unit testing - Error list of the database project

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

SQL developer unit testing - 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.

SQL developer unit testing - Add master database as a reference

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>

SQL developer unit testing - Unit test config file

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.

SQL developer unit testing - How to select test file in Visual Studio.

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.

SQL developer unit testing - Add a new stored procedure to database project

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.

SQL developer unit testing - Create a new tSQLt unit test case in database project

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

SQL developer unit testing - Unit test and naming rules

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.

SQL developer unit testing - Add a new Post-Deployment  Script to database project

We will add the following query to PreDeployment Script file.

SQL developer unit testing - Add a new setting to Post-Deployment  Script

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.

SQL developer unit testing - tSQLt test publish settings.

SQL developer unit testing - Publish result of the database project.

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.

SQL developer unit testing - Run All unit tests in Test Explorer

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

SQL Unit Testing with SSDT (SQL Server Data Tools)
SQL unit testing with the tSQLt framework for beginners
SQL Unit testing with the tSQLt framework and SQL Server Database Project integration
SQL unit testing best practices
How to use fake tables in SQL unit testing?
How to use fake functions with SQL unit testing?
SQL Unit Testing Mocking Stored Procedures
SQL Unit Testing: Working with exceptions

Esat Erkec

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
Esat Erkec
625 Views