Using the tSQLt Unit Testing Framework with Visual Studio and VSTS, Part 3

In Part 3 of this series, I'll be discussing how to push unit tests to a test SQL server and run them from a VSTS build.

Earlier in Part 1, we set up a Visual Studio solution with a set of composite SSDT projects in it. Some of these projects are for our business objects. One of these projects holds the tSQLt framework. Another one holds our unit tests. In Part 2 we installed Ed Elliott's tSQLt Test Adapter to run our unit tests from within the Visual Studio IDE.

We'd like to share the code with our colleagues, so we store it in a repository hosted in Visual Studio Team Services (VSTS), the cloud-service counterpart to TFS. Then we create a build definition.

How can the build process run the unit tests?   Fortunately, the VSTS build definition editor lets us configure VSTest for custom test adapters. We'll need to set up the tSQLt Test Adapter somewhere such that it's accessible by VSTS.

Where do I put the tSQLt Test Adapter so VSTS can use it?

There are several options. Each approach has its pros and cons, which I've noted below.

• If you have sufficient security access to your build server, consider placing the test adapter in a folder on the server. Your build definition can reference the hardcoded physical path to the test adapter when it runs the tests.
  
  • PRO: This doesn't live in your source repository; also, any additional repository that uses the same build server can easily use the same test adapter.
  • CON: You may not have security rights to install things to your team's build server. Also, this will get you a static version of the test adapter; if you want to update it, you'll need to do so "by hand" (unlike a VSIX or NuGet package where the updating is largely click - click - you're done).
    
    
    
• Add the test adapter's files to a solution folder within your Visual Studio solution. Your build definition can then reference a relative path to the test adapter (based on the solution folder root) to run the tests.
  • PRO: No security rights to the build server needed; no need to add a NuGet package to a project that doesn't directly use it
  • CON: You'll be checking the test adapter's files in to your source repository and taking up space there. Not much space, but still this is a point to consider. Also (like option #1) this will get you a static version of the test adapter that you'll need to update manually.
    
    
    
• Lastly, you can add the NuGet package for the test adapter to any project within your solution than accepts NuGet packages. Beware: SSDT and ReadyRoll packages cannot have NuGet packages added to them, so the test adapter will need to be included in some other project.
  • PRO: No security rights to the build server needed; no test adapter / dll files need to be stored in source control; simple updates via the NuGet Package Manager
  • CON: You'll have to add a test adapter package to a project that doesn't directly use it, which feels…weird, to say the least, for the minimalists among us.

In the screenshot below, I've taken the last approach by adding a dummy project to my solution, and including the NuGet package for the test adapter in it:

Great! I've got the test adapter available (either on the build server or in the solution). How do I set up the build definition to run the unit tests?

You'll need a VsTest task in your build definition, pointed at the tSQLt test adapter. Take a look at the screenshot below. At a minimum, you'll need to set the following three items:

• the test assemblies - set to point at your SQL files
• the runsettings file (it helps to keep a runsettings file specifically for your build process in your solution)
• the path to custom test adapters - use the relative path for the tSQL test adapter NuGet package

Here's an example of what the output will look like once the build is queued and the VsTest task is executed:

Notice that the build step immediately before the VsTest task was a command-line task, "SqlPackage: Push WWI.UnitTests". This is important - don't forget to push the unit tests to the target SQL server before asking the test adapter to run them.

What if you'd rather use a ReadyRoll project for your SQL development and unit testing? In the next article in this series, I'll show an example project using ReadyRoll Core in Visual Studio 2017 to host the migration scripts for a sample database, along with the tSQLt framework and some example unit tests.

This is the third article in this series. You can find Part 2 - running tSQLt unit tests from the Visual Studio IDE - here.