Geeks With Blogs


Dylan Smith ALM / Architecture / TFS

I was reading a post on Rob Caron's blog where he links to a few posts by Sachin Rekhi (a program manager from the Data Dude team).

There are a number of posts about how the new database unit testing features work, and he walks you through implementing a database unit test.  My opinion is that the database unit testing functionality isn't very valuable as it stands today.  It appears targetted to allow you to test a very limited set of conditions: the row count, a scalar value returned, a empty/non-empty result set, and/or the execution time.

I don't know about the rest of you guys, but if I'm writing unit tests against my stored procedure, testing that the row count equals what I expect is not nearly good enough to ensure that the stored proc is doing what I expect.  An example Sachin gives is testing a [Ten Most Expensive Products] stored procedure, and he checks to see that the row count equals 10.  If I was writing a unit test against a Ten Most Expensive Products stored procedure, the most important thing to test for me would be that the 10 products being returned actually match the 10 most expensive products in the database.  I would test this by having some code to initialize the database with a known set of sample data, then execute the stored proc testing to make sure that the 10 product id's returned are what I expected.

Don't get me wrong I like the idea of writing unit tests against stored procedures.  In fact I do this today in some cases.  However, I do it through code, so that I can actually iterate through the resultset and check the data using assert statements.  If I could do this testing at the SQL level and eliminate the data access layer from my test, then all the better.  But I really think that there are more tools needed in the database unit testing feature-set to really deliver on the value proposition.


Posted on Monday, July 17, 2006 12:30 PM | Back to top

Comments on this post: Database Unit Testing - Not quite there yet...

# re: Database Unit Testing - Not quite there yet...
Requesting Gravatar...

I totally agree with you that the simple example I provide of testing the number of rows returned is very limited and I don't expect that to be the bulk of the kinds of tests you would want to do. I just wanted to provide a simple example. Next time I post a walk through, I'll try to show you something more realistic :)

As you mention, getting the database to a known state is the first important thing to do. We provide a test data generation tool and integration with unit testing to simplify that whole process. You should try it out yourself.

I also agree that the set of test conditions we provide definitely does NOT constitute all the level of test you will want to do. However, we have designed the system in such a way that you can easily perform your own test validation either in SQL or more importantly, in C# or VB. If you right-click on a db unit test class and select view code, you will see the C# or VB code that we have generated. You will see an Execute command that returns to you a executionresult object. That contains the dataset that is returned. You can perform any verification logic that you wish over that dataset and then cause an assert when the expected condition is not met. In this way, you can do any kind of validation you can dream of.

Sachin Rekhi
Program Manager
Team Edition for Database Professionals
Left by Sachin Rekhi on Jul 17, 2006 2:31 PM

# re: Database Unit Testing - Not quite there yet...
Requesting Gravatar...
Hey Sachin,
My experience with DB Unit tests is limited to just the articles I've read and the demo's I've seen. I was not aware until now that the DB unit tests are actually implemented very similar to a regular application unit test, only with a bunch of extra code that is genned for you and access to some extra framework classes.

I have no doubt that the automatic deployment of the database will be a huge boon for developers writing unit tests that hit against a database.

I think maybe I just need to shift my thinking. Based on the articles I've read and demo's I've seen, I had believed that a DB unit test is a significantly different beast than a regular unit test. It seems a better way to think about it is that a DB unit test is just a regular unit test with an automatic database-deployment performed via genned code.

If I think about it in that context (and if I am in fact correct), then it makes it easier for me to identify the benefits I can expect from DB unit tests. The benefit being it will gen the plumbing code for you (similar to the code I talked about in this post: only more powerful), allowing you to focus on the test-specific code such as the verification od the data.

For me, it was just a matter of having that "Aha!" moment when I realized that a DB unit test is nothing more than a regular unit test, with some extra genned code.

Am I thinking along the right track here Sachin?

Left by Dylan on Jul 18, 2006 10:37 AM

Your comment:
 (will show your gravatar)

Copyright © Dylan Smith | Powered by: