Database code unit testing tips

Question

I'm working on developing a big 3-tier application.

Database has a lot of code (stored procedures, functions, triggers, etc). I'm looking for a valid approaches how to implement unit testing for the database code.

• "Should I use a separated database to run unit tests?"
• "How should I implement stored procedures to be ready for unit tests?"

Any help, tips, tricks are welcome!

Answer 1

You can also check tool - TST - TSQL Test Tool. Tool is available in codeplex http://tst.codeplex.com/

For DB test automation below parameters pass for TST - TSQL unit

• Ability to run suite of test cases
• Ability to run individual test case
• Handling Assertions without any extra coding
• Data Driven Testing can be implemented; this approach involves QA writing TSQL Code

Answer 2

I would recommend that you use the Database unit testing features of Visual Studio 2008 Database Professional aka "Data Dude"

There is a great in-depth blog post about it here, which includes a full step by step walkthrough.

Answer 3

"Should I use a separated database to run unit tests?"

Yes! Effective testing requires control of the environment.

Answer 4

Try DBFit. I am sure you are going to love it. You can do ATDD for your database the same way you can do it for you application.

The following link will give a lot of information on how to get going including some background on Fitnesse and Dbfit (follow the links in the post).

http://gojko.net/fitnesse/dbfit/

Answer 5

I posted this on SO a while back. There was a series of articles on closing the loop holes in your databae testing. In the example in the article given He used C# and NUnit. Close those Loopholes - Testing Stored Procedures So this may be of assistance. I have done similar things writing small execution wrappers in C# that do very little but execute the data layer.

Answer 6

I assume you are referring to automated unit tests rather than tests performed by hand. I approach automated unit tests as a means to an end rather than an end in themselves. Some developers get a lot of value out of automated unit tests (I did when I was a developer). Others write great, high-quality code without resorting to automation. You know your own work well enough to know where you are likely to introduce bugs. If you think you are likely to write buggy stored procedures, functions, or triggers, by all means write some unit tests. Otherwise I might test at the persistence layer instead. (Or not at all -- again, you can judge for yourself.)

Another reason to unit test at the persistence layer would be if you expect the database to be queried manually, i.e. by typing in some ad-hoc SQL.