Releasing a product without testing it is generally accepted as an unforgivable sin in the software development world. And frankly, there really isn't an excuse not to test when we have whole suites of testing tools available to us – tools which allow us to examine our applications for speed and functionality, and perform unit tests.

What are unit tests?

Unit tests are used to check if small units of code (modules which may be called a number of times by an application) are functioning as you would expect them to. This testing is done as part of the development process, and a unit test will check that the code being tested meets a specification, while a library of unit tests together will check the functions expected of the application. The process is usually automated, which allows the library of tests to be re-run frequently, allowing us to find bugs earlier, and in smaller units of code, which are easier to debug. It also helps us to think in a modular fashion. Often unit tests are included in code reviews, both as a means of demonstrating that the requirements are met, and to help explain the requirement. They are frequently combined with a continuous integration system, to regression test changes, and help speed up and minimize the risk from refactoring. Unit testing can also form the basis of documentation – as the functional requirements should tie to unit tests, and vice versa, allowing you to see which requirements are met by the developed code.

Testing database code

In order to make unit testing effective, you need to isolate from anything that’s not code, so that you know whether you have a correctly performing unit, or a consequence of a different failure. This can be difficult for databases. We're used to thinking about large sets of related data for a database and testing accordingly for performance. But with unit tests we're looking at small sets of data, producing tests that will run quickly, and testing function not speed of execution. Once we have unit tests for our databases, we can harness the same benefits in terms of regression testing and refactoring our database code that we do for application code. The key to getting repeatable unit tests, which work whichever order they are called in, is to work out how you want to isolate from your dependencies. For those running SQL Server 2012, SQL Server Data Tools (SSDT) allows you to create unit tests. But if you're not on SQL Server 2012, or you prefer to develop in SQL Server Management Studio, there are still options available. My personal favorite is tSQLt, an open source SQL Server database unit testing framework. I have found that the extensive mocking and assertion frameworks cover almost all of my needs (and allow me to roll my own if I have a really bespoke requirement). You can also use tSQLt within SSDT, and there’s a nice walkthrough of how this can work in this blog post by Ken Ross. If tSQLt doesn’t suit your requirements, other frameworks are available. The important point being you should unit test your code in such a way that it can become part of your workflow, so it will bring value and advance confidence in your development.

Isolation

It’s worth mentioning that you can’t properly unit test your database from the application code that’s calling it. In that instance you’re not really unit testing either the application module or the database stored procedure, function or view in isolation, but rather as a group together. This challenges the belief that you should be testing the smallest unit of code that is testable in each case. That said, it's better to have tests like this than no modular testing at all, but it’s worth being aware of all the potential dependencies such testing inherently has. I’ve heard it said that it's a good idea to standardize upon the same framework for unit testing databases as you do for application code, but we need to think about how we isolate from dependencies. We shouldn’t forget that the need for a unit test to connect to a database (as opposed to utilizing a connection that’s already set up, and is therefore not part of the code being tested) can create a fresh dependency, and potential cause of failure. Ultimately, this can be made to work, but in my experience you will get a better result by using a tool that you are comfortable with, and codes in your language of choice (for me, as a database developer, that language is T-SQL). Each tool stores the tests in its own way, and for database unit testing there’s a bit of a debate as to the merits of storing unit tests within the database being developed. Some believe tests shouldn’t be in the same place as production code to avoid unit tests accidentally coming into contact with production data. But personally, I like the fact that my unit tests are stored with my database, as I view them as integral to the database’s development. I believe this makes it simpler to source control the two together, while still being easy to remove unit tests before deployment. Whatever your preference of tool, if you aren’t already unit testing your databases, you should consider it. The confidence that a good suite of unit tests gives you is really instrumental in giving you the assurance that your database will support the application by working as expected, and that it can be updated without the worry that existing functionality has been inadvertently broken.