Database Projects, SQL Unit Tests, and TeamCity

Continuous Integration is a really good idea, especially when you are working with a team of developers. There are lots of tools out there that can help your team build better quality into your application. TeamCity by JetBrains is one such tool that has been around for awhile; specifically, it is the one that my company uses.

Visual Studio has Database Projects SQL Server Data Tools to address some of the inconsistencies that developers face when using a database as a storage layer; Things like version control and consistent deployment. It also includes SQL unit tests, which fills a huge gap if any of your logic is in the database. Many organizations moved away from logic in the database years ago because it was tricky (at best) to verify logic on the data tier, of course, they were also making changes directly to the database itself... but I digress.

If you are using SQL Server Data Tools, and run your unit tests locally, it is a joy to behold. Your database is automatically deployed when you run the unit tests. I don't recommend using NCrunch to automatically run your SQL unit tests because database deployments can take a while to complete.

Ideally, we should be able to deploy a database and run the SQL unit tests in TeamCity in the same way that we can locally. If you're reading this blog post, you know that is not the case.

Enough background, let's get to my solution. It may not be the best, or even correct way of doing this, but it works, and preserves the local experience.

Prerequisites

Of course you need a Visual Studio Solution. I also assume that you have TeamCity installed, and that you have a TeamCity project that pulls down your solution from your source code repository.

I'm also assuming that you are running a local SQL server database and that your database connections are using "local" or "." (dot) as the database server, and that your CI database server is also installed with the same relative path. If you need to change your unit test database connections to reflect your CI environment, you'll need to use the same techniques you would otherwise use with TeamCity.

Visual Studio Solution

In your visual studio solution, you should have at least two projects:

• Database project
• Unit test project

TeamCity Steps

You need at least three build steps in your team city project:

• NuGet Installer - Installs and updates missing NuGet packages
• Visual Studio (sln) - Microsoft Visual Studio solution (.sln) runner
• Visual Studio Tests - Visual Studio Tests runner

Missing Configuration File

Once you have the solution building, TeamCity will use VSTest to try and run the unit tests. You will see a failure for every SQL test that looks something like this:

An error occurred while SQL Server unit testing settings were being read from the configuration file. Click the test project, open the SQL Server Test Configuration dialog box from the SQL menu, add the settings to the dialog box, and rebuild the project.

This error is basically saying that the app.config file can't be read by VSTest. It was renamed on deployment, and because the tests are being run in the context of VSTest.exe, a configuration file named VSTest.exe.config is expected. FAIL.

Solution

VSTest can use a .testsettings file to tell it to customize various aspects of the unit test execution environment. This file can be used properly deploy your .config file. To do this:

1. In Visual Studio, create a test settings file that enables deployment
1. Add a test settings file file to your solution. It can be anywhere, but I like putting it in the solution root because it is easy to find there. You can do this by selecting Add | New Item and selecting Test Settingsfrom the Test Settings category of the Add New Item dialog.
2. Navigate to the Deployment screen of the Test Settings wizard and check Enable Deployment.
   
3. Close the wizard
2. In TeamCity, reference the new test settings file
1. On the Visual Studio Tests runner Build Step page, click "show advanced options" at the bottom of the page
2.  Select your test settings file from the Run configuration file settings property.

Database Deployment Failure

You will now be greeted by a new error for each of your unit tests:

Database deployment failed. Path '{some path to your database project}.sqlproj' is not a valid path to a database project file.

My approach to getting around this problem isn't very satisfactory to me, but seems to work:

1. Deploy the database project as part of the Visual Studio build
1. In Visual Studio, create a publish.xml file for the database project
1. Open the Publish context menu from the database project
2. Change the target database connection to match the server and database that your unit tests expect
3. Under the Advanced settings, choose Always re-create database from the General tab and Drop objects in target but not in source from the Drop tab. Press OK to close the dialog.
4. Press Create Profile and name it.
2. In TeamCity, change the Visual Studio Solution  runner to publish the database project as part of the build step.
1. Change the Targets property from "Build" or "Rebuild" to "Build Publish" or "Rebuild Publish". I prefer to rebuild, but the important part is to add "Publish"
2. Open the advanced options 
3. add a value to the Command line parameters setting that sets the SqlPublishProfilePath. Mine looks like this: /p:SqlPublishProfilePath="%teamcity.build.checkoutDir%\db\ci.publish.xml"
4. Ensure that the configuration setting is "Release" 
2. Conditionally compile out the code from the SQL unit test project that tries to deploy the database project.
1. In your unit test project, when you added a SQL unit test, Visual Studio added a SqlDatabaseSetup.cs file. This file contains two lines of code that publish and populate the test database. 
2. Add a conditional #if DEBUG around the following code
   

   #if DEBUG
     SqlDatabaseTestClass.TestService.DeployDatabaseProject();
     SqlDatabaseTestClass.TestService.GenerateData();
   #endif
   

Conclusion

This article has demonstrated how to integrate SQL Server Data Tools with a TeamCity continuous integration build without breaking the local database unit test workflow. We added test settings and deployment files to the Visual Studio solution, and conditionally compiled out the deployment steps from the SQL unit test project. We also added TeamCity references to the test settings and deployment files as well as "publish" to the solution build targets.