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:
- You have to look in (up to) 3 places to find out what is happening in a test
- These methods often become a dumping ground for the Arrange portion of a test, containing things that do not pertain to every test in the class
- For the reason above, this can affect test isolation, because the state of one test can be influenced by the needs of another.
In addition to the Setup and TearDown methods, there are also:
- TestFixtureSetup and
- TestFixtureTearDown
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,
- only use the Test pane, to keep the test elements in one place
- Create your own assert procs to raise an error for failed tests
- Put the whole test in a try..catch block so that errors can be rolled back and reported
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:
- The ability to Fake a table
- The ability to Fake a view
- The ability to Fake a function
- The ability to Fake a proc
- Asserts of all kinds
- Asserts for error conditions
- The ability to run tests in a single transaction from the test runner and auto rollback
- Code coverage analysis
- Parameterized Tests
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: db Unit Test, SSDT
