Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

Free Database Unit-Testing for SQL Server Data Tools


By:   |   Last Updated: 2017-12-15   |   Comments (8)   |   Related Tips: More > 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 with SSMS (SQL Server Management Studio) tSQLt with SSDT (SQL Server Data Tools)
The tSQLt unit-tests are managed by the main database The tSQLt unit-tests are managed and maintained as database project
The tSQLt objects required to create and run unit-tests are created in the main database The tSQLt objects required to create and run unit-tests are created in a separate test database other than main database
The tSQLt unit-tests are created in the main database as separate schemas The tSQLt unit-tests are created in test database referencing main database objects
The main database needs to clean-up tSQLt objects and tests when deployed on QA or Production The main database needs no clean-up to be deployed on QA or Production since the tests are managed by a test database
The database (structure) and its unit-tests cannot be put under source control without third party source control system which can be costly solution Both main database and the test database can be version controlled along with creating multiple point in time snapshots without the need for any third party source control solution
Limited deployment options are available Both connected (Publish) and disconnected database (DACPAC) deployments are supported
Not many features like disconnected database development, static code analyses, etc. Static code analysis (analyzing code without executing it), refactoring (renaming database objects), disconnected database development speed up creating, running and managing database objects and their unit-tests

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:

office supplies database

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
main database

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:

new project

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

solution explorer

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

import database

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

solution explorer

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”.

reference data

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

customer data

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

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:

reference data

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:

sql mode

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:

project settings

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:

orders view

Creating OfficeSuppliesSample_Tests database Project based on TSQLT Objects

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

tSQLt Download Button - Description: Download tSQLt version

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:

copyright

Next create a new database project called “OfficeSuppliesSample_Tests”:

solution explorer

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

project settings

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

database

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

office supplies

Setting Target Connection for Test Project (OfficeSuppliesSample_Tests)

target connection string

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:

database reference

Next add the OfficeSuppliesSample_Data reference as follows:

database reference

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.

post deployment

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

build

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:

office supplies

Then write the following code:

CREATE SCHEMA [CustomerTests]
Authorization dbo
GO

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

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:

office supplies

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:

build

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:

startup project

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

microsoft visual studio

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:

output
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.


Last Updated: 2017-12-15


next webcast button


next tip button



About the author
MSSQLTips author Haroon Ashraf

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

View all my tips






Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, December 18, 2017 - 12:25:38 PM - Haroon Ashraf Back To Top

I would like to add a correction to my latest comments:

3. In step "Adding Master Database and Main Database Reference" I think there is mistake and we need to add Database References to OfficeSuppliersSample_Test project (not OfficeSupplierSamples_Data project)

In response to the above comments "This is not the case" should be replaced with

"Yes, this is actually the case, we have to add database reference (OfficeSupplierSamples_Data) in OfficeSupplierSamples_Tests database project as the test project contains tSQLt classes and test cases while it requires to access main database objects to test them and this is done by adding the database reference.

Well spotted! This is a typo which got away un-noticed despite the fact that we do number of revisions before finalising the tip." 

Thank you very much for bringing this to my notice."

 


Monday, December 18, 2017 - 11:07:30 AM - Haroon Ashraf Back To Top

Thank you for your comments.

I am sorry to hear that you faced problems following the steps.

Please see below some suggestions:

1. Built Action of Script.PostDeployment1.sql was not set to PostDeploy. That's why Deploy of my project failed due syntax error in PostDeployemnt script.

The BUILD Action of a Post Deployment script is "PostDeploy" by default unless it is manually changed.
So, if in first place we add a new Post-Deployment Script then no action is required.

2. I had to manualy enable CLR on SQL Server tpo execute tSQLKintClass.sql script

CLR is already enabled in Pre-Deployment script in this Tip so it should work in SSDT while using SQL Server I have already mentioned the two main requirements to install and run tSQLt in my previous Tip and this Tip under the heading "Enabling CLR for tSQLt Test Runs"

3. In step "Adding Master Database and Main Database Reference" I think there is mistake and we need to add Database References to OfficeSuppliersSample_Test project (not OfficeSupplierSamples_Data project)

This is not the case, we have to add database reference (OfficeSupplierSamples_Data) in OfficeSupplierSamples_Tests database project as the test project contains tSQLt classes and test cases while it requires to access main database objects to test them and this is done by adding the database reference

4. Also, I suppose, that in script EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='BasicTests' @level0name should be set to 'CustomerTests', because schema 'BasicTests' doesn't exist.

That is correct. However, creating SCHEMA Object would suffice here.

5. I can't reproduce step "tSQLt Design-Time Test Automation Scenario", because after changing name of AddCustomer procedure to AddCustomer2, build is still successed. 

If BUILD does not fail even after chaning the name of stored procedure then please make sure you have followed step-3 properly.

Please check the following:

  1. "Multiple startup projects” option under Solution > Common Properties > Startup Project has been selected.
  2. Deploy the project(s) by pressing F5

I hope this helps.

The things that we learn by resolving issues have their own benefits in the long run.

Thank you once again for your comments.

 


Monday, December 18, 2017 - 8:04:45 AM - Dariia Back To Top

 Thank you for interesting information!

 

Would like to inform you that I faced some problems during reproducing steps in this article:

 

1. Built Action of Script.PostDeployment1.sql was not set to PostDeploy. That's why Deploy of my project failed due syntax error in PostDeployemnt script.

2. I had to manualy enable CLR on SQL Server to execute tSQLtClass.sql script

3. In step "Adding Master Database and Main Database Reference" I think there is mistake and we need to add Database References to OfficeSuppliersSample_Test project (not OfficeSupplierSamples_Data project)

4. Also, I suppose, that in script EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='BasicTests' @level0name should be set to 'CustomerTests', because schema 'BasicTests' doesn't exist.

5. I can't reproduce step "tSQLt Design-Time Test Automation Scenario", because after changing name of AddCustomer procedure to AddCustomer2, build is still successed. 

 


Friday, December 15, 2017 - 12:14:59 PM - Haroon Ashraf Back To Top

Thank you for your kind remarks.

Please stay in touch with MSSQLTips as more tips about testing are on their way.

 

 


Friday, December 15, 2017 - 12:01:15 PM - Adeela Back To Top

 

 Very precise and to the point helpful steps


Friday, December 15, 2017 - 11:31:43 AM - ExesMan Back To Top

Thank you so much! Very interesting!

 

 


Friday, December 15, 2017 - 10:34:13 AM - Haroon Ashraf Back To Top

Thanks for your comments and asking a very good question.

Some of the benefits of using tSQLt instead of another testing framework/environment without considering cost benefits are as follows:

1.     Database unit-tests are written in the same language (T-SQL) in which database objects are written which makes the database development and testing (unit-testing) easier, flexible and efficient

2.     tSQLt unit-tests do not rely on the data of the database under test rather they are written as self-contained tests 

3.     tSQLt unit-tests isolate objects from other dependencies including data and other objects, for example, if you have a Procedure which adds customer data to a table and then calls another procedure to validate customer details then unit-test for the Procedure which adds customer data to a table will not be affected by other procedure which validates customer details

4.     tSQLt unit-tests are easier to write, modify and maintain since they can be grouped into difference classes and each group can be tested independently 

5.     tSQLt unit-tests in the form of Database Test Project within SSDT (SQL Server Data Tools) helps your development team to detect bugs earlier in the form of BUILD Failures

6.     More importantly tSQLt Framework offers advanced database unit-testing capabilities such as cross database testing

And finally tSQLt is recommended by many SQL experts and is an underlying testing framework of some of the very famous third party testing tools. :)


Friday, December 15, 2017 - 6:17:35 AM - ExesMan Back To Top

What is the advantage of using tSQLt instead of a test environment or a new instance without considering cost benefit? Tks!

 


Learn more about SQL Server tools