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.

---

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.

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.

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.

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.

---

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.

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.

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

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

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:

• http://tsqlt.org/user-guide/tsqlt-tutorial/
• http://tsqlt.org/user-guide/