Free Database Unit-Testing for SQL Server Data Tools

By: Haroon Ashraf   |   Updated: 2017-12-15   |   Comments (8)   |   Related: > Testing

Problem

As a database developer I would like to implement an advanced and cost-effective database unit testing framework that can help my team in early bug detection and does not require context switching from SQL to any other tool/language and is suitable for the following scenario: when all my database development work is done through SSDT (SQL Server Data Tools).

Solution

The solution is to get your databases equipped with tSQLt and start writing and running unit tests for your database(s) without even leaving the Visual Studio IDE provided the preferred database development toolset in your organization is SSDT (SQL Server Data Tools).

Recap: Answering Some Basic Questions about tSQLt

Some of the basic things that may come to mind before tSQLt implementation have already been answered in detail in my previous tip, in case you missed it a quick recap is as follows:

• Why tSQLt for advanced database unit-testing?
• Writing tSQLt unit-tests is same as writing T-SQL scripts so no context switching is needed you stay within SQL.
• Other benefits include isolation, transactions, continuous integration and cross-database objects testing.
• How is tSQLt Testing Framework Cost-Effective?
• tSQLt is an open-source project so it is free to use commercially and it is also underlying unit-testing framework behind many third party visual database testing tools.
• Apart from the cost-effectiveness and support for advanced database unit-testing, tSQLt framework architecture has been optimized to use for TDD (Test-Driven Development).

Recap: Creating and Running tSQLt Tests Generally

In my previous tip the steps to create and run tSQLt tests steps are mentioned. In short a tSQLt unit-test is ultimately a test class which is actually a schema which helps us to group relevant tests together while calling a test class or all the tests in all test classes is simply calling a stored procedure so nothing new to learn apart from T-SQL.

Implementing tSQLt Framework with SSDT vs SSMS

The steps to implement tSQLt unit-testing with SSDT are different than SSMS implementation due to the following reasons:

tSQLt Implementation Scenario

In order to implement tSQLt consider the following scenario:

• You want to implement tSQLt as your database unit-testing framework while all your database development, debugging, testing, deployment and refactoring is done through SSDT (SQL Server Data Tools).

Setup: Database Unit-Testing with tSQLt using SSDT

Now that your team has finally decided to use tSQLt for advanced database unit-testing you are going to start implementing the testing framework on your database(s) which are managed through SSDT.

According to tSQLt.ORG the tSQLt Test Framework is compatible with SQL 2005 (Service Pack) and all versions above it.

Using OfficeSuppliesSample Database Project (Mimicking Transactional Database Project)

We are using OfficeSuppliesSample database project to mimic main transactional database which contains a simple version of customer-product-order scenario for office supplies as follows:

Apart from the above three objects (tables) there is a procedure to add customer and view to see the orders.

Note: The database (OfficeSuppliesSample) was originally created in previous tip to mimic traditional online sales systems in a simplest manner can also be referenced in upcoming tips, so please feel free to get familiar with it.

Please download and run the source code below to create and populate OfficeSuppliesSample database:

The database is ready to be used now.

Writing tSQLt Unit-Tests against OfficeSuppliesSample_Data

The best way to implement tSQLt Testing Framework to write unit-tests against transactional database project (which results into OfficeSuppliesSample_Data database) is to have another database project containing unit-tests.

So we are looking forward to have the following database projects:

1. OfficeSuppliesSample_Data
2. OfficeSuppliesSample_Tests

Creating OfficeSuppliesSample Database Project (Mimicking Transactional Database Project)

Since SSDT (SQL Server Data Tools) uses naturally declarative database development, we begin with a database project followed by a database in a sandboxed environment.

SSDT provides complete isolation from changes done by other developers by providing a fully functional version of the main database in the form of a Project with both connected and disconnected development flavors that takes database development, testing, refactoring and deployment to next phase.

So the first thing that needs to be done (keeping in mind SSDT is used for database development) is to create OfficeSuppliesSample Database Project and we can easily simulate it by creating the project from the database, however, please bear in mind either you create a database with basic objects and then create project from it or begin with database project and start adding objects both ways are acceptable.

Start a new solution and create a new SQL Database Project as shown:

Now right click on OfficeSuppliesSample database project and click on Import and then click on Database…

Next Connect to OfficeSuppliesSample database and click Start and then click Finish.

Next check the database project in the solution explorer it must have been populated with OfficeSuppliesSample database objects.

Scripting OfficeSuppliesSample Data in Database Project

Reference data can be added to the project in the form of scripts to be centrally managed by a Post Deployment Script.

If we debug the project to deploy changes the debug database (OfficeSuppliesSample_Data) will have no data.

In order to create and populate OfficeSuppliesSample_Data database please create a new folder “Reference Data”.

Unzip the scripts below and put them under “Reference Data” folder using windows explorer:

Now In the solution explorer right click on “Reference Data” folder select “Add” and then select “Existing Item…”:

Next select all the scripts in the “Reference Data” folder (you recently unzipped) and as a result they will become part of the project:

Before these scripts can run we have to make sure that SQLCMD Mode has been turned on which can be achieved by clicking on the SQLCMD Mode in any script pane in the top right:

Creating and Populating OfficeSuppliesSample_Data database from Project

You can set your Debug Database as Local Dev SQL database by checking Properties of your database project and then under “Debug Tab” pointing to your desired SQL instance:

Since the data scripts have been kept simple, we are checking the deployment option “Always re-create database” so that rerunning the data scripts should not result in any key violation.

It is important to set the Target Platform the same as your SQL Server Instance version as in our case it is SQL 2012:

Press F5 to deploy changes to the debug database and see OfficeSuppliesSample_Data (debug database) getting populated.

Now using SQL Server Object Explorer view data navigate to OfficeSuppliesSample_Data and right click on “dbo.OrdersView” under Views and then click on “View Data” to see the tables populated by the post deployment script:

Creating OfficeSuppliesSample_Tests database Project based on TSQLT Objects

The first step to implement tSQLt is to download it from tSQLt.ORG.

Please see the attached tSQLt zip file download which was done quite a while ago, but still matches the latest version available on website:

Create a temporary database “TSQLT_Temp” to hold the tSQLtClass.sql script output that generates tSQLt objects:

Create Database TSQLT_Temp
   

Then run the tSQLtClass.SQL script in the TSQLT_Temp database to create tSQLt objects:

Next create a new database project called “OfficeSuppliesSample_Tests”:

Set the Target Platform the same as that of main database:

Then right click on OfficeSupplieSample_Tests and click on the “Import from Database” option:

Then connect to the TSQLT_Temp database and get all tSQLt objects from this database into the project (OfficeSuppliesSample_Tests):

Setting Target Connection for Test Project (OfficeSuppliesSample_Tests)

Adding Master Database and Main Database Reference

Since the test database is going to refer to the main database objects for testing we have to add the following database references:

1. Master Database
2. OfficeSuppliesSample_Data

Right click on OfficeSuppliesSample_Tests project and select “Add Database Reference” then the master database as follows:

Next add the OfficeSuppliesSample_Data reference as follows:

Enabling CLR for tSQLt Test Runs

One of the requirements for tSQLt tests to run successfully is to make sure that CLR is enabled at the Server Level which can be easily done by creating a Pre-Deployment script using the following code:

/*
Pre-Deployment Script Template
--------------------------------------------------------------------------------------
This file contains SQL statements that will be executed before the build script.
Use SQLCMD syntax to include a file in the pre-deployment script.
Example:      :r .\myfile.sql
Use SQLCMD syntax to reference a variable in the pre-deployment script.
Example:      :setvar TableName MyTable
               SELECT * FROM [$(TableName)]
--------------------------------------------------------------------------------------
*/
 
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO 

Automating tSQLt Test Runs for Debug Database

All the tSQLt tests can be run by the following script:

EXEC tSQLt.RunAll
   

In order to automate the running of tests as soon as the project is debugged we need to put it into a Post-Deployment script.

Now Debug the OfficeSuppliesSample_Tests database project to see tSQLt blank tests run:

Creating Tests: Database Unit-Testing with tSQLt using SSDT

Now both main and test database projects along with their databases are ready and it’s time to write and run unit-tests.

Create a new folder “Tests” in OfficeSuppliesSample_Tests project and then create “CustomerTests” sub-folder under the “Tests” folder.

Real Time Scenario for Adding New Customer Test

Now keeping in mind the main tables of the OfficeSuppliesSample, what if you have been asked to test these basic requirements:

• In your system (database) an admin must be able to add a new Customer.
• At a very basic level we need to make sure the following things:
  • Customer object (table) exists
  • AddCustomer object (stored procedure) exists

Create a New Test Class (CustomerTests)

Let's create a Test Class first by creating a schema under the CustomerTests folder of the test project:

Then write the following code:

CREATE SCHEMA [CustomerTests]
Authorization dbo
GO

EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='CustomerTests'
   

Create a test to check if Customer table exists

Create a unit-test by creating a new stored procedure under CustomerTests schema with the following code:

CREATE PROCEDURE [CustomerTests].[test to check Customer table exists]
AS
BEGIN
   EXEC tSQLt.AssertObjectExists 'dbo.Customer'
END 
   

Create a test to check if AddCustomer procedure exists

Next create another stored procedure in the test project to check that the AddCustomer procedure exists as shown below:

The script to create a test to check if the Product table exists is as follows:

CREATE PROCEDURE [CustomerTests].[test to check AddCustomer procedure exists]
AS
BEGIN
   EXEC tSQLt.AssertObjectExists 'dbo.AddCustomer'
END
GO 
   

Running All Tests

Making sure the test project is selected and press F5 to start debugging:

We can see above that all tests have passed.

tSQLt Design-Time Test Automation Scenario

It is easier to achieve test automation at design time for the developer by simply linking a database project with a test project that means debugging the database project should result in a BUILD error if tests fail.

This can be achieved by choosing the “Multiple startup projects” option under Solution > Common Properties > Startup Project as shown below:

Let's change the procedure “AddCustomer” to “AddCustomer2” and then deploy the changes to the debug database by pressing F5:

There is no need to start the unit-tests separately as they run automatically when the main database project is debugged and this results into a BUILD failure if the tests do not pass.

Now rename the stored procedure “AddCustomer2” to “AddCustomer” and debug the main project again to see all goes well:

Next Steps

• Create a fresh database project and test the project from scratch and then add objects to the main database project and simple tests to the test database project and see how it goes.
• Create some solid unit tests to check functionality of the objects such as adding a new customer, product or order.
• Explore the tSQLt official website to develop basic and advanced skills of creating and running tSQLt tests.
• Please keep watching MSSQLTips.com as more tips about tSQLt are on their way.

About the author

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips