Cloning Tests in SSDT

This post might, more appropriately, be named "using tests effectively in SSDT," but I'm going with the cloning thing because that's probably what you think the problem is and what led you to read this post.  Let's start with some unit test basics and then get into what MSTest has to offer for database developers.

What's in a Unit Test Framework

Unit Test Frameworks, like NUnit and MSTest, contain the following special methods:
SetUp and TearDown methods execute once before and after each and every test method in a class, respectively.  It's a little bit magical, because the framework handles calling these methods, rather than each test calling them explicitly.  In fact, it is probably better to avoid them if you can, for a few reasons:
In addition to the Setup and TearDown methods, there are also:
These methods run only once before or after the whole test set in the class.  Again, it is kind of a confusing behavior because the code is separate from the tests and nothing calls it explicitly.  Additionally, there are probably not to many sensible Use cases for either of these methods.  

Frameworks also contain Assert methods, which are methods that test a condition and throw an exception if it is not met, i.e. "expected was 'x', but the actual was 'y'."

What MSTest has for Database Development

When you create a new SqlDatabase Test in a test project, Visual Studio will add the template code, which amounts to a windows form for SQL to be entered and a resource file where that SQL is stored.  The win form looks like this:


Note, the dropdown for pre-test and post-test.  These are run before and after a given test (NOT like SetUP and TearDown which run before all tests).  If you dig deeper, you will find "Common Scripts", which has "Test Initialize" and "Test Cleanup" methods.  These latter ones do act the same as SetUp and TearDown.  Why reinvent the testing terminology?  You got me.

So let's deal with one thing right off the bat, never use Pre-Test or Post-Test, it just makes it a pain to see what a test does, jumping from window to window.  Furthermore, if your test throws an exception, the Post-Test method is never called.  That's right, you might put clean up steps there and now you're database is left in an inconsistent state.  Additionally, other tests might fail because of this inconsistent state.  It's really a poor design that should be done away with.

Now for the real setup and teardown methods (initialize, cleanup), these should also not be used because they are hidden as well and for another good reason - we are testing a database.  We have transactions, dammit, and we're going to use them.  Calling all these different methods throws the use of transactions out the window, while this is the easiest way to clean up after a database test.  You can truncate tables and do as you like in a transaction and rollback the database to a consistent state.  

MSTest also includes that funky window at the bottom of the page called "Test Conditions."  These are really asserts.  They are configured in a properties window and you cannot easily replicate them from test to test.  In fact, they are a pain to configure anyway and they hide the assert, altogether.  Plus, they encourage multiple asserts which steers you clear of good unit testing methodology of only testing one thing.

So instead,

The final template should be something like this:

BEGIN TRY
       BEGIN TRAN;
              --Arrange
              --Code to setup data for a test

              --Act
              --Execute proc, function or select from view

              --Assert
              --Raiserror if values do not match

       ROLLBACK;
END TRY

BEGIN CATCH
       ROLLBACK;
       --code to report an exception here

END CATCH

Cloning

So how do you "clone" a test?  At this point, you can just copy paste into a new test.  It's really that simple, because you've kept it that simple and not used all those cumbersome windows.

Thinking Further

What you get for MSTest is a low barrier to entry.  Anyone comfortable with SQL can crank out tests pretty quickly, writing SQL in some window and self-asserting their tests.  What you will find missing is a bit of important framework code:

In essence, you get a test runner and the means to quickly put your code into a continuous build process.  But what was that quote... "You ask for a banana, instead you get a gorilla holding a banana and you get the whole jungle too."  There are loads of ways to hurt yourself with this poor templating.  The truth is, hardly anyone writes automated database unit tests, and this is not an area that will receive much attention until more of us do so.

For a more serious approach to database unit testing, check out tSQLt by Sebastian Meine and Dennis Lloyd (which is free).  Redgate has made a test runner for this suite that can easily be incorporated into a CI process (the tool costs a nominal amount, but like anything redgate, provides huge value).

Mark Wojciechowicz

Labels: ,