The question of where to install tSQLt is probably the most common question I get when I talk about unit testing T-SQL, so much so that I thought that it would be a good topic for a blog. I mention tSQLt in the title, but this covers all unit test code for T-SQL.
So to be more specific:
Where do you install unit tests and unit tests frameworks for SQL Server, which databases should have that code in them?
If we look at what databases might exist in a development process:
- Local developer database
- CI database on a build server
- Test database for testers
- QA database for user acceptance testing
- Prod mirror or Pre-Production database for a production standby database
- Production database
If we have tests written in tSQLt and the tSQLt framework itself then where do you install that and those tests and execute those tests?
TLDR: The answer is quite simple, in these two database types:
- Local developer database
- CI database on a build server
Not TLDR:
The first thing to say is, what are unit tests?
There are two top goals for unit tests:
The first is that unit tests are small pieces of code that validate that other small pieces of code work successfully.
The second goal is that unit tests guard against other developers breaking the actual pieces of code covered by the tests - you might well be the other developer in this case so I would always recommend not getting too worked up about this :).
In the list of databases, these two types are the only database types that are private and the type of databases that can typically be thrown away on a whim.
A local developer database should sit on the developer's machine and allow a developer to deploy and test their code locally without affecting anyone else. I have worked on projects with this setup, and it is easily the best to work with as a developer.
If you aren't able to have your database locally but have to either use a remote database or even worse, a shared database for testing, then you will likely get hit by other peoples breaking changes or other people debugging their code in some way blocking you from working.
When you have a shared database you have to coordinate all sorts of things like the best time to deploy and that itself is a massive time sink.
The second type of database, one for a CI build is typically only used for a single build at a time. Because a SQL Server database is, mostly, free to create on an existing server, there is no need to share CI databases. When you think about the fact that you can use Local DB to quickly and cheaply spin up a new clean, empty, fresh, happy database, why wouldn't you use this unless there was an excellent reason not to?
It is in these private databases that we can do things like deleting all the data in a table, setting all the dates of birth to a developer's favourite date or create a long-running transaction that blocks out all of the other users.
Once we move further right in our list of environments, we are less likely to want to do things such as deleting all the data in a table, and once we hit production, it is critical we don't leave test code like that in the database to be accidentally called.
Tests for a production database should be a series of smoke tests which can operate in and around the existing live production calls and data so they do not take extra transactions than the standard database does and they certainly don't modify data, other than their own.
These smoke tests are also likely to be carried out via the application, so a deploy happens and a set of application tests are typically executed to validate that the deploy has been a success - this is a long way from where we use unit tests to validate and guard code against breaking changes.
Anyway, enjoy!
