I'm a big proponent of unit testing my TSQL code. The questions become
First off, there are a lot of unit testing tools that handle database testing, but are not necessarily TSQL unit testing tools. An example would be DbFit. This tool allows your Fit/Fitnesse tests to actually execute against your database. This is cool, and valuable, but really doesn't help me unit test a stored procedure. Another example is psunit. This tool allows you to write unit tests for your PoSH code. I've seen lots of people, myself included use psunit as their basic assertion framework to test tsql. Again, this is cool, but it isn't a tool designed specifically for testing tsql. Frankly, any tool that follows the "Arrange/Act/Assert" model can be retrofit to call a database and unit test your SQL.
So, for this blog post, I'm not going to include unit testing tools that only peripherally touch the database, I only want to compare unit testing tools that specifically focus on TSQL. A very strong argument can be made that db unit tests should be done in something like DbFit so that the db unit tests are stored and executed with the Java tests and code. This is exactly how DataDude handles this. I understand the rationale, I am simply questioning whether this is always the best approach. In many organizations the db experts are not the Java experts, so keeping the tests separate makes a lot of sense in some circumstances too. Further, I want the best tsql unit testing tool, not the best unit testing tool for my development environment that also handle tsql.
What Makes a Good TSQL/database TDD Tool
These are just my opinions
Evaluating The Available Tools
The only two tools I've found that are specifically unit testing tools for tsql and stored procedures are TSQLUnit and TSQLT.
|Open Source (so it's easy to look at the source code and extend it for your needs)||Yes||Mostly. The table assertion functionality is in CLR code that is not open source. The rest of the product is written entirely in tsql. You are using tsql to test tsql.|
|Mocks for data||Yes||Yes. tsqlt.FakeTable|
|Stored proc and view mocking||No||Yes. tsqlt.SpyProcedure. This is very ingenious.|
|Has the ability to "arrange" and "cleanup" test data||Yes||Yes|
|Can compare result sets||Yes||Yes|
|Test output to XML/HTML||No||Yes|
|tests can be written and executed entirely in SSMS||No, requires python||There is even a plugin helper available from RedGate.|
|support from industry vendors||Not that I'm aware of||RedGate's release of SQLTest is fully supported|
|Documentation||kinda sparse||Lots of documentation and tutorials.|
|Support (and on-going viability)||Doesn't seem to have much new development lately||The two authors actively respond to bug and feature requests. (full disclosure...I was a co-worker with one of the authors)|
|Deployment||test code is in dbo schema with your production objects||Uses separate schemas that map closely to test classes, just like Java. They are only deployed if your tests are run. If you follow the test class creation pattern in their tutorial you can run tests and you will have no remaining testing artifacts in your database.|
Clearly I think the world of TSQLT. Even if you do not do TDD or don't understand unit testing, you really should download the tsqlt code and peruse it. I know that I learned quite a bit about transact-sql by looking through their code. It's just totally ingenious. If you are a database guy and are not familiar with TDD or unit tests then try using it when you write your next stored proc. Your unit tests will likely be amateurish but your code will be much easier to maintain and will have fewer bugs. In a few years you'll look back and laugh at your early unit tests. That's ok, it's a journey.
Dave Wentzel CONTENT
sql server data architecture tsqlt