I once had a manager who liked to say, "I want to see checked-in code with tests that prove it doesn't work." Very Yogi Berra-esque.
I'm a big proponent of unit testing my TSQL code. The questions become
- What tools are available for the data professional?
- Which tool is the "best"?
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
- adheres to the AAA (Arrange, Act, Assert) Model for unit testing
- Has a robust assertion engine that is specific to the needs of a data developer. For example, support for asserting that a result set matches the expected result set, down to the column ordinals, names, and datatypes is important.
- Does not require injecting special assertion or debugging code into your stored procedure. We don't need that stuff in our production code.
- Mocking data should be simple.
- Mocking views and stored procs so they return a given output for testing.
- has support for automatically setting up (Arranging) and tearing down test data without being destructive to existing data in the database. So basically I don't need to backup/restore a test database to run my tests. (Never run unit tests on a production system).
- Shouldn't require you to learn another language or tool, such as PoSH to write or run tests. It should just work in SSMS.
- Generates a standard output that is human-readable and automatable so other CI Tools can call it. Bonus points if it can return HTML/XML so I can plug the output into a build automation tool's standard reporting.
- Good documentation and tutorials. Most data professionals have no idea how to start with TDD and unit testing their TSQL code. (You could probably make the same case for Java Jocks too).
Evaluating The Available Tools
|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.
sql server data architecture tsqlt