I’ve been using tSQLt a lot recently for testing database development. Our development team doesn’t typically place any logic on the database. However, we’ve been working on a logging platform to track how our applications are being used and I built a data warehouse to store this information.
The questions always asked for the logger are who, what, when, where, and why. We wanted to make sure there is not a lot of administration when a new application comes online and starts logging utilization. So, I built a stored procedure for the logger to call, the logger simply passes the application name, metric name, metric value, who performed the task and the date/time of the task.
The stored procedure determines if the application name and metric name are in the warehouse or if they’re new and performs accordingly. It also performs some lookups to determine if the user exists or if his meta data has changed since the last log for the user and takes various steps based on what it finds. Since this is logic on the database it needs to be tested, I added unit tests to my existing database using tSQLt…
In a matter of minutes I was able to create tests to ensure the data warehouse has the appropriate users, roles, and permissions. A couple more minutes and all dependencies the “AddMetric” procedure required are tested to exist and function properly. A coffee break and a couple more minutes “AddMetric” was fully unit tested ensure all the cases it needed to meet were met. A few short minutes later the user logic was tested and passing. All told I spent about 1.5 hours creating my test suite from scratch, three test classes with a total of 16 unit tests and various setup tasks for each class, that includes brewing a pot of coffee.
A few days later the development team determined we needed additional functionality for adding metrics. Here’s were Test-Driven Development came in handy. I added some unit tests to test the new requirements. Obviously, they failed (I hadn’t implemented the code yet). I then added the new feature and ran my tests. A prior test failed!
I’d forgotton about a DML trigger that exsted on a table, adding a new column caused the trigger to stop functioning. But I discovered the problem immediately and I know that existing functionality is not broken after adding the new feature. And … it only took a few minutes to find and resolve the problem thanks to the unit tests I’d already built.