Unit…What?
Whilst those .Net folks have been unit testing their code for many moons now, we SQL folks have been falling behind.
Up until about 12 months ago, I hadn’t even heard of the idea that one could unit test SQL code. What will they think of next?
Over the past 6 months or so, unit testing within SQL server has become an almost daily occurrence for me as we now have it included in our CI / CD build pipeline. So in short, you check something in, then you make sure there are associated unit tests for the object you have ammended.
As with many things of this nature, initially, it felt like a chore; an unnecessary extra that needed thinking about all of sudden.
However, one day it caught an error in one of my procedures (I know, you are surprised about this trusted reader). The unit test caught an edge case bug in my code. I would never have found this bug with basic smoke testing and so I was an instant convert.
Better egg on my face, than omelets in production.
What is Unit Testing?
Feels like an obvious question, huh? Well, a loose definition would be –
Testing the smallest part (unit) of one’s application code. Providing some standard inputs and preempting its output(s) to validate that ‘unit’s’ functionality
What is tSQLt?
tSQLt is an open-source framework used for the purposes of unit testing SQL Server code. APIs are also available making it possible to integrate nicely into CI (Continuous Integration) services such as TFS and Azure DevOps.
Some key features and benefits:
- Write tests in T-SQL – an obvious benefit. No need to learn additional or external tools
- Tests are all run within transactions keeping our tests independent and minimizing clean up work
- Create unit tests grouped by Schema – create dedicated tests for specific areas of your product/code base and run those independently
- Provides the functionality of being able to ‘mock’ tables and stored procedures allowing for easier testing of code functionality
- Totally free to use
- Completely customizable
Great! But how does this help you?
Disclaimer: I can only speak for how it has benefited me here, duh.
The main benefit tSQLt has given me is confidence in stored procedure changes I have made. It enabled me to have confidence that the core functionality of a procedure has remained the same after I have performance-tuned it (tinkered).
The ability to totally isolate the stored procedure’s functionality and verify it has been a great tool for me over the past twelve months in my work. Paired with the great tool SQL Test provided by Redgate and it’s a comfort blanket I refuse to give up now.
Show Me The Money
In the next post, we shall go through a full demo using the bare mechanics of the framework. I plan to publish a few posts regarding tSQLt which will demonstrate more of its functions and features as we go.
Thanks for reading!
