This is the first in a series of articles I'll be writing about how to use the Microsoft SQL unit testing framework, tSQLt, in a continuous-integration environment. I'll be using Visual Studio 2017, SQL Server Data Tools (SSDT) for Visual Studio, and Visual Studio Team Services (VSTS) to demonstrate the solution structure and build process.
What is tSQLt, Anyway?
If you're not familiar with tSQLt, you can find out all about it at tsqlt.org. There are downloads for the install package as well as snippets for SQL Prompt (a lovely add-in for SSMS and Visual Studio from Red Gate that helps with some of the more mundane aspects of writing T-SQL code; read more about it here). There's also a helpful tutorial for getting started and documentation on the details of each of the test methods provided within the framework.
I won't be covering how to write tSQLt unit tests in this article. Instead, I'll be focusing on the infrastructure aspects of using tSQLt within a solution and its associated CI/CD pipeline.
The VS Solution Setup
Let's start by setting up the SSDT projects within a Visual Studio solution. For the database sample, I'll be using the WideWorldImporters database, one of the sample databases for SQL Server and Azure that's provided by Microsoft, that was released in 2016. You can find the original WideWorldImporters sample solution here on GitHub if you're interested.
In a typical project, your team will most likely have multiple environments which all need to be the database deployment target at some point in the project's life cycle: Development, QA, Staging, Production, and so forth. To make the deployment process easier, I'll be using SSDT composite projects to separate the tSQLt database objects from my primary database business logic. This way, I can isolate parts I don't want to deploy to every server.
What are SSDT Composite Projects?
Let's start with a baseline - what is an SSDT project? This is a type of Visual Studio project, supported by the SQL Server Data Tools add-in, that describes the SQL database schema - both DML and DDL objects. The advantage this provides should be obvious: the schema changes - along with any changes to the code within the stored procedures and any other scripts - can then be tied to all the helpful things we developers rely upon when we're in a team: source control, work item tracking, branches, pull requests, code review, and on and on.
Composite SSDT projects are projects that each describe just a part of the database. Taken together, the projects then "compose" a single database. Each project may reference the other projects, as needed, to resolve any objects they're using that are defined in the referenced project.
Here's what a part of my solution structure looks like. I have a solution folder, SSDT, that holds four separate projects:
- tSQLt.Framework - this is where the tSQLt framework gets stored. I don't have to refresh the objects here unless another version of tSQLt is released and I want to upgrade.
- WWI.DataLoadSimulation - The WideWorldImporters database comes with some stored procedures that allow you to generate more data. That's not typically the kind of thing you'd want to deploy into a production database, so I've isolated it into its own project. I can then selectively deploy this to any environment as needed.
- WWI.DbSchema - Here are the primary "business logic" portions of the WWI database; orders, customers, employees, etc. I'd need to deploy these to every database in the development pipeline.
- WWI.UnitTests - Here are the unit tests my team will be writing. Because the unit tests will be referring to the schema objects in both the WWI business logic and the tSQLt framework, this project will need to reference the other projects.
Database References for Composite SSDT Projects
If I try to build my solution without adding any database references to the projects that need them, I'll see a lot of SQL71502 warnings (or errors, if you've configured your project to treat warnings as errors) like this:
These can be resolved by adding a database reference within the project that needs it (in this case, WWI.UnitTests, which is where the procedure [UnitTests].[TestCustomerOrders] is defined). The database reference needs to point to where the referenced object is found - in my case, all of objects are defined in the WWI.DbSchema project.
To set up the composite projects' references, I'll need to define the referred database's location as "same database" when I add a database reference to a project that needs it:
I've also checked the "Suppress errors caused by unresolved references in the referenced project", because if the referenced project has a problem, I don't want that to break the build for this project.
Referencing a Dacpac Instead
I could take this one step further. If I have a portion of the database that isn't going to change often, then I can exclude that database project from the build. Any other project that refers to it can instead refer to the dacpac file - that's the file generated by the SSDT build process.
Why would I want to refer to the dacpac file instead of the database project? Each time the dacpac is generated from the project, precious time is consumed. Think about how many times you trigger a rebuild of your solution, and how many times the entire solution is rebuilt on the build server, each time someone makes a source control change, or a build is fired off for any other reason. Here's an opportunity to shorten that rebuild time by bypassing a step that's only needed occasionally.
That's it for now! In the next article in this series, I'll show how to run tSQLt unit tests within Visual Studio.