Writing unit tests for SQL Server

As a full stack software engineer, I’m used to switching between different languages and technologies. One day I can be deep in some server-side C#, the next I’m looking at execution plans from a SQL Server query, the next debugging JavaScript in Chrome. A lot of concepts can be shared between each type of development — good coding practices apply to each, even if they can look slightly different.

One aspect that has troubled me in the past, however, has been the approach to unit testing. The principles of unit testing are the same regardless of the language or technology, but sometimes we’re limited by what tools are available to us to do this testing.

For instance, unit testing in C# is really well established, and it’s generally expected that any C# developer can write unit tests for their work. It’s integrated into CI builds, and there are plenty of libraries and frameworks to support these tests, like MSTest, NUnit, xUnit, Moq, FluentAssertions, etc. That’s great. And looking at JavaScript, we’re also now in the position where it’s really easy to write good, clear unit tests, using libraries like Jasmine, Mocha, Jest, Puppeteer, etc. Getting NodeJS involved makes JS testing really easy.

Then, we move onto SQL Server. It seems this area of unit testing hasn’t matured as much yet as testing in other languages. Yes, there are tools available, but there aren’t too many options, and some can be pretty tricky to get your head around. Take tSQLt for instance; it’s well established, and does provide everything you need for writing tests for SQL (including mocking and common setups), but there’s a steep learning curve to it. For a start, all the tests are written in tSQL, so even though it means the tests and the objects under test are using the same language, it can be quite hard for developers who are more used to C#/JS unit testing to adopt testing in this way.

This is why I developed DBConfirm (https://github.com/Bungalow64/DBConfirm) — I wanted a way to write tests for SQL Server in the same way that I write the rest of my tests. I wanted to use C#, I wanted all my tests to live in the same solution without involving extra tools, I wanted them to run as part of a CI build really easily, but mostly, I wanted it to be super-easy for new developers to write and understand the tests.

And I wanted it to be free for commercial use, and open-source, so that anyone can use it.

For something as fundamental as testing, I didn’t want to introduce anything that would be a blocker for people using it, if they thought it would benefit their team and their project. And it’s something that I can use myself, when I’m working with teams on different projects, without having to worry about licensing.

So my aims for DBConfirm were:

• to be free for anyone to use, for personal and commercial use
• to work fine for tiny projects, as well as huge, enterprise-level projects
• for the tests look like normal C# unit tests, as much as possible
• to be really easy to set test data up
• to be really easy to get working in a Continuous Integration (CI) build
• to have minimal framework-specific code and setup steps
• to have everything hosted on GitHub, and packages provided by NuGet

So, hopefully, that’s what I’ve done. Here’s an example test, using MSTest, which executes a stored procedure (called dbo.AddUser), and verifies that the user has been added:

There’s only a couple of things you need to set up to get this working (configuring the connection string to point to the database you’re testing, and make sure the test class inherits from the DBConfirm base class). That’s like a 30-second job, so you can spend the rest of the time actually writing your tests.

The full documentation is at https://www.dbconfirm.com, and covers everything from how to set these projects up, to writing and maintaining the tests. I’d recommend hitting the quickstart first, then maybe the walkthrough to see how the whole process fits together with a sample database, going from adding the project to writing tests, setting up test data, and hooking it up to a CI build in Azure DevOps.

So if you want an easier way to unit test your SQL Server code, then feel free to give DBConfirm a go.

Developing and maintaining this project is something I’m really passionate about, so keep a look out for the new features I’ve got lined up to be released over the next few months, and if you find that you can’t test something how you want, then please give me a shout and we’ll see what we can do. Your best bet is to raise it as an issue on GitHub, and we’ll discuss what needs to be done.

Thanks guys — I hope this can help you and your projects in some way.