Contents

Setting up SSDT Database Projects and tSQLt

Previously I’ve written about the database unit testing framework tSQLt, you can read about it here, there is also an excellent Pluralsight course by Dave Green (blog | twitter) which you can find here.

In this post I’m going to show you a method of version controlling your database and unit tests with SQL Server Database Projects in SQL Server Data Tools (SSDT).

Setting up the Solution

In my solution I’ve created two SQL Server Database Projects

/setting-up-ssdt-database-projects-and-tsqlt/images/1-the-solution.pngThe first project SimpleDB will represent the actual database project (i.e. the one that is being tested) this will contain all of the tables, stored procedures, functions etc. The second project SimpleDB_Tests will contain all of the tSQLt objects and associated tests.

Once this is done, the next step is to create some database references, right click on References and select Add Database Reference.

The first reference is to SimpleDB, ensure that you select Same Database under the Database Location.

/setting-up-ssdt-database-projects-and-tsqlt/images/2-simpledb-database-reference.pngThe second reference is to the master database, ensure that you enter sys as the Database name.

/setting-up-ssdt-database-projects-and-tsqlt/images/3-master-database-reference.png

Version Controlling tSQLt

The next step is to install the tSQLt framework into your local database, download the latest release from their website and follow the install instructions.

After you have installed tSQLt go back to SSDT and start a new Schema Comparison, select as the source your local development database and then target as your SimpleDB_Tests project.

To make the results easier to understand I like to group the results by schema, you can do this by clicking on the Group results icon and selecting schema.

Exclude everything except the objects in the tSQLt schema and the tSQLt assembly

/setting-up-ssdt-database-projects-and-tsqlt/images/4-tsqlt-schema-compare.png

Then write the results into the SimpleDB_Tests project by clicking the Update button.

/setting-up-ssdt-database-projects-and-tsqlt/images/5-ssdt-tsqlt-schema-objects.png

The next step is to create a Pre Deployment Script, right click on the SimpleDB_Tests project and under Add click Script. This will bring up the Add New Item dialog, click Pre-Deployment Script and give it the name Script.PreDeployment.Setup_tSQLt.sql

Into this script copy and paste the contents from the SetClrEnabled.sql script which is included with tSQLt.

Follow the above step, but this time add a Post Deployment Script and add the following

1
EXEC tSQLt.RunAll

This script will be executed each time the SimpleDB_Tests project is deployed and will run all of the tSQLt tests.

Adding Unit Tests

There are two ways you can go about adding unit tests.

The first and probably easiest is to create them in SQL Server Management Studio, you can read about how to do that in my previous post on tSQLt.

After you have created a new test class and some tests just simply use Schema Compare in SSDT to sync the changes into the project - just make sure you don’t include any of the objects that belong to the main database project. Only objects related to your test should go into the _Tests project!

The second way is to create everything inside of SSDT yourself.

If you do this then notice that when the tSQLt.NewTestClass proc is used to generate a test schema it adds an extended property, so make sure you do the same.

1
2
3
4
5
6
CREATE SCHEMA [stp_create_new_person]
    AUTHORIZATION [dbo];
GO
 
EXECUTE sp_addextendedproperty @name = N'tSQLt.TestClass', @value = 1, @level0type = N'SCHEMA', @level0name = N'stp_create_new_person';
GO

Writing a test is actually very easy

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
CREATE PROCEDURE [stp_create_new_person].[test Check that a person is created]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
  --Assemble
  DECLARE @expected int = 1
  EXEC tSQLt.FakeTable 'Person', 'dbo'
 
  --Act
  EXEC stp_create_new_person @first_name = 'bob', @last_name = 'brown', @email = '[email protected]'
  DECLARE @actual int = (SELECT COUNT(*) FROM dbo.Person)
 
  --Assert
    EXEC tSQLt.AssertEquals @expected = @expected, @actual = @actual, @message = 'Actual didn''t match expected'
 
END

Notice that since the reference to the SimpleDB was set up as ‘same database’ the test can just refer to the Person table and stp_create_new_person stored procedure directly.

Deploy and Test

Once you’ve made some changes, (e.g. adding new tests, changing stored procs etc…) it is time to deploy and test.

To do this we will be deploying the SimpleDB_Tests project. Right click on SimpleDB_Tests in the solution explorer and click Publish. You will need to set the Target Database Connection and ensure that the database name given doesn’t already exist.

/setting-up-ssdt-database-projects-and-tsqlt/images/6-publish-database-dialog.png

Then click Generate Script (or if you are brave you can just go ahead and click Publish).

Once the script has been generated you can give it a quick glance over then click the Play icon to execute it.

If you clicked Generate Script (instead of Publish) then you will see the status being updated in the messages section. Here you can see that one of my tests passed and the other failed so the status of the publish is ‘completed with errors’

/setting-up-ssdt-database-projects-and-tsqlt/images/7-publish-results-messages.png

If instead you clicked Publish you will see the status in the Data Tools Operations section being updated. Here you can see that it says ‘an error has occurred’

/setting-up-ssdt-database-projects-and-tsqlt/images/8-data-tools-operations.png

To see the details click on the View Results link. This will bring up the script that was executed and a very similar Messages section will appear with the following error

/setting-up-ssdt-database-projects-and-tsqlt/images/9-failed-message.png

Some Helpful Tips

If you use Schema Compare a lot to keep your projects in sync, then I suggest saving a schema compare file into each of your database projects with it setup to either, ignore the test objects (for your main database project e.g. SimpleDB) or all the main database objects (this would be for your test project e.g. SimpleDB_Tests). This streamlines the process of keeping your projects in sync.

Likewise save the Publishing Profile, unless you enjoy entering in the connection details over and over again (you can do this from the Save Profile As button on the Publish Database dialog).

One other idea that I have, but haven’t tried yet is having a post deployment script to drop the Tests database after all the tests have been executed.

🍪 I use Disqus for comments

Because Disqus requires cookies this site doesn't automatically load comments.

I don't mind about cookies - Show me the comments from now on (and set a cookie to remember my preference)