SQL Unit Testing Using tSQLt: Part 1


Unit…what now?

Other practitioners of code-slinging, such as .NET folks, have been unit testing their code for many moons now. We SQL folks, however, rarely seem to do this, at least in my experience. Several job roles ago, I had to use unit testing on a daily basis. It was heavily integrated into our workflow via the CI/CD pipelines we used

Ultimately, if you checked some code in, you made sure you had the associated unit tests written for it. As with many things of this nature, initially, it felt like a chore; an unnecessary extra that needed thinking about all of a sudden.

One day, however, a unit test caught an error in one of my procedures (Dear reader, I was just as shocked as you might be.) The unit test caught an edge-case bug in my code, something that basic smoke testing would never have found.
From that day onward, I have been a passionate advocate for unit testing.

Better egg on one’s face than omelettes in production.


Unit Testing Defined

While it may seem obvious to some, a loose definition of ‘unit testing’ would be:

Testing the smallest part (unit) of one’s application code. Providing some standard inputs and preempting its >output(s) to validate the unit’s functionality

GeeksforGeeks have a comprehensive guide here regarding what unit testing is actually comprised of.


So what is tSQLt?

tSQLt is an open-source framework used for the purpose 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 your tests independent (and idempotent), thus minimizing cleanup work.
  • Create unit tests directly 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 can it help me?

The main benefit tSQLt has given me is assurance in stored procedure changes I have made.

It’s enabled me to have confidence that the core functionality of a procedure has remained the same after I have made changes to it. The ability to totally isolate the stored procedure’s functionality and verify it has been a huge support for me over the years. Paired with the great Redgate tool SQL Test, it’s the ultimate comfort blanket for code noodlers™.


Looking Forward

I am once again working in an environment where I know tSQLt is going to help me.

I currently have several projects on the go where I can see the need for unit testing. So, with that said, I’m dusting off the cobwebs of my tSQLt install to get to know my old friend again. I do hope you come along for the journey.

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, covering a number of its functions and features as we go.

Thanks for reading!