9 Jul 2014

Integrating tsqlt with SQL Server Data Tools

As mentioned in a previous post I've recently been working with a SQL Server unit test framework: tsqlt. I would like to share how I managed to integrate the framework and unit test into my project life-cycle.

Unit tests should be treated the same as application code, meaning they should be contained within a project under source control. Being under source control will allow the tests to be maintained and changes tracked when multiple people are helping to write tests.  Also having the unit test in a project means there is a deployment mechanism out-of-the-box.

The tsqlt framework has a few requirement which are as follows
  • CLR is enabled on the SQL Server
  • The framework and the unit test are contained within the same database of the objects which are being tested
  • The database instance is trustworthy
As unit test are required to be contained within the same database as the objects being tested, also due to the way SQL Server Data Tools (SSDT) project validation works, the easiest solution would be to add the unit test and the framework objects into the same project of your application database.

However one question would be raised which is how do you stop the unit test framework and the unit tests getting into the production deployment? The tsqlt framework does offer an approach to this problem by supplying an uninstall store procedure which could be called after deployment to remove the unit tests and the framework. This is more of a workaround rather than solving the problem of not deploying tests and the framework into production.

There is an alternative solution, which is the method I used, to integrate the unit tests into the project life cycle.  The solution is to use SSDT project setup method of composite database project.  The composite database project method, which has been around since visual studio database professionals, is basically having 2 (or more) database projects, where one references the other(s), which both gets deployed into the same database on the same server.

My method to implement composite SSDT database project was as follows:
  • Created an application database project as normal. Which contained all the objects for the application requirements.
  • Create a unit tests database project to contain all my unit tests and supporting objects. This project could also be where tsqlt unit framework code could be stored.  I decided created a separate tsqlt dacpak file by using the following steps:
    • Create an empty database.
    • Execute the tsqlt install script within the empty database.
    • Use SQLPackage with the extract action.
    UnitTestSolutionExplorer
  • Before starting to create my unit test code within my unit to project I had to set up the references.  I created references to the application project and the tsqlt dacpack file but setting database location to be “same database”, which is the same as removing the generated database name and variable.
Adding Database Reference To Application Project From Unit Test ProjectAdd Database References to tsqlt dacpac file From Unit Test Project
Setting up the projects in this method means I can deploy my application project without the unit tests and the framework to all the environments. Then add the unit test to any one of my  environments: development, test or separate unit test server.

The reason for creating a dacpack file was to have a single reusable file for my next projects. And if the unit framework is updated I only have to update a single file.  Also hopefully at some point the SSDTS project will support the use of NuGet. This would mean that I can upload my dacpack to a NuGet server and add the package reference.  Then if I ever updated the server package the project would automatically pick up the new version. (There is a fork within the NuGet project which already allows this: https://nuget.codeplex.com/workitem/2439.)

Here is a link to find out more about composite projects: http://blogs.msdn.com/b/ssdt/archive/2012/06/26/composite-projects-and-schema-compare.aspx.

SQLpackage has a setting which will allow a composite project to deploy its composite references objects at the same time. This allow the unit test project to deploy the unit tests including the application database and the tsqlt framework database objects. However be aware that only one Pre/Post deployment file is executed which is one that is attached to the dacpack listed in the SQL package source file parameter. So the composite references project have post/pre-deployment requirements are not executed.  This can be a problem if your application database has a post-deployment script to load reference data.

A couple of closing notes:

To create a unit test class within SSDTS for tsqlt add the following extended property on the schema which is to become a tsqlt test class: tSQLt.TestClass with a value of 1
e.g:
   1:  EXECUTE sp_addextendedproperty
   2:  @name = N'tSQLt.TestClass'
   3:  , @value = 1
   4:  , @level0type = N'SCHEMA'
   5:  , @level0name = N'MyUnitSchema';

And creating unit test the store procedure must start with "test "

further reading:

5 comments:

Anonymous said...

Visual Studio noob here. Do you have to do anything with the .dacpac after making a reference to it? Does it magically end up in source control somehow, or does it need to be manually added to the project?

After adding the reference, I did a compare between the new tSQLt database project and the database that I created the .dacpac from, and the project wanted to delete all the non-existent objects from the database. Perhaps you can also point me to some pages that explain how to work with .dacpacs as references?

Thanks,
Lee Anne

Steven Wright said...

Hi Lee Anne, You will need to ensure that the tsqlt.dacpac is added into source control.

The way I handle this was to create a Solution folder call Reference then added the tsqlt.dacpac file. This will then allow Visual Studio to download the file when you, or someone else, does a Get Latest at the solution file level.

When Comparing the project against a target database you will need to set the following option: Include Composite Objects which is under General Tab. This will make the compare treat References as part of project and compare them against the target database update/create/delete as appropriate.

For more information about Reference here is a link: http://msdn.microsoft.com/en-us/library/jj684584(v=vs.103).aspx

Regards
Steve

Anonymous said...

Excellent, thank you! Got this set up and working now, and I had forgotten about the 'Include Composite Projects' check since I initially set up my solution.

Thanks,
Lee Anne

Anonymous said...

Hi, I'm trying to export the .dacpac and while that appears to work, when I make the reference, it is complaining that the associated .dll isn't found. Are there extra steps needed to included the CLR stored procedures in the .dacpac?
Thanks!
Greg

Steven Wright said...

Hi Greg
When using SQLPackage to export the tsqlt database there should have been a DLL file eported as well. This DLL file needs to be copied the DACPAC as well.

Regards
Steve