tSQLt Part 1: tSQLt & SSDT

Aim: Include a SQL Server test framework in an existing SSDT project so it can be added to version control & deployed to environments.

Set up the tSQLt framework:
In SSMS, create a new database to hold the tests, e.g. if you plan to run tests against an Accounts database, create a database called Accounts_Tests (Updated: See my note on cross-database testing). Download tSQLt.zip from http://tsqlt.org/downloads/ & unpack. Run SetClrEnabled.sql & tSQLt.class.sql against Accounts_Test to apply the relevant settings & install the framework. You should see new tSQLt objects which form the framework on which the tests run.

1 tSQLt_objects


Set up the tSQLt project in SSDT:
Open the Accounts SSDT solution in Visual Studio & a new SQL Server Database Project called Accounts_Test. Right click the project & import the Accounts_Test database.

2 import_database

You can also use Schema Compare to import the tSQLt objects but the database import is easier in this case as the database contains only tSQLt objects.
Add a pre-deployment script containing the contents of SetClrEnabled.sql. This will ensure the correct settings are applied with every deployment to a new environment. Add a post-deployment script containing

EXEC tSQLt.RunAll

This will run all tests at the end of every deployment.

3 tSQLt_project

Building the project at this point will result in errors because the tSQLt relies on objects in the master database that are not available in the solution.

4 error_output

To resolve the errors, add a new database reference to the project. Choose master as the system database (different database, same server) & set the database name to sys.

5 master_reference


Add a new unit test:
Create a new schema in the Accounts_Test project & add an extended property, e.g.: when testing a stored procedure called dbo.GetCustomerId which takes the CustomerName as input & returns the CustomerId, create the following schema. All tests for dbo.GetCustomerId will be created in that schema.

CREATE SCHEMA [testGetCustomerId];
GO
EXEC sp_addextendedproperty @name = N'tSQLt.TestClass', 
   @value = 1, 
   @level0type = 'SCHEMA', 
   @level0name = [testGetCustomerId];
GO

Add a new stored procedure to test a particular output of dbo.GetCustomerId, e.g.: for a customer with the name “Test Customer 1”, the expected Id is 1. The stored procedure name should be prefixed with “test” to identify it within the framework.

CREATE PROCEDURE [testGetCustomerId].[test Output 1]
AS
BEGIN
   SET NOCOUNT ON;
   
   --Assemble
   DECLARE @expected INT = 1;
   DECLARE @actual INT; 
   
   --Act
   EXECUTE @actual = [Accounts].[dbo].[GetCustomerId] 
      @CustomerName = 'Test Customer 1';
   
   --Assert
   EXEC tSQLt.AssertEqualsString @expected, @actual;
   
END

Publish the project:
Right click the Accounts_Test project & Publish. Set the target database connection to a suitable environment where the Accounts database already exists.

6 publish_tSQLt_project

Create profile so that it can be reused in future & change the file name as appropriate, e.g. local.publish.xml. To deploy, click Generate Script or Publish. Check the message tab to see the result of the test.
If testing first, the test result will be a failure.

7 failing_test

Create the dbo.GetCustomerId stored procedure to satisfy the test requirements & publish again. This time the test passes.

8 tSQLt_testresult

Add the Accounts & Accounts_Test projects to version control (in this case, Git).

9 git commit

Next: I plan to get the SSDT projects building & deploying with TeamCity & Octopus Deploy so my tests will run every time we check in a new change.


Useful links: tSQLt Tutorial & User Guide:

 

Advertisements

One comment

  1. […] tSQLt Part 1: tSQLt & SSDT, I added a post-deployment script to Accounts_Tests which runs all tests at the end of the […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!

%d bloggers like this: