Should unit tests use a database connection to test stored procedures? This was the subject of a lively discussion I had with a collegue. At my office we use unit tests in our daily builds to test our software and determine the code quality. My collegue wants to test his stored procedures in these test. In my opinion this isn't a good idea.

Let's start with the criteria for good unit tests as described by Andrew Hunt and David Thomas in Pragmatic Unit Testing in C# with NUnit.

• Automatic (test to be able to run without any extra installation or setup. Testing stored procedures implies some sort of database setup or cleanup before the actual test can be performed)
• Thorough (Thorough testing implies that stored procedures should be tested within these unit tests. On the other hand business objects, in code, can be referenced directly. Stored procedures are indirectly referenced. If the stored procedures should be tested in a thorough test is still debatable)
• Repeatable (Test must be repeatable. This means that the effects of these tests must not intefere with each other)
• Independent (Test must be able to run independently. The database is likely to be shared among the tests. This makes the test rely on a dependent set of objects and much easier to create dependent tests)
• Professional (There's no real issue here with testing stored procedures. The only thing can be performance, because databases are quite slow in comparison to 'pure code' tests)

Looking at these criteria, unit tests can be compared to scientific tests. You want to be able to predict the outcome, therefore you (try to) minimise the variables that influence that outcome. By comparing the real outcome to the predicted outcome you have the proof that something works or doesn't work. By adding a database connection to you unit test you add a great number of variables that you have no control of. Variables like the tcp/ip connection/stack, network device or loopback device, operating system, etc. The prediction of the outcome depends on the correct working of al these variables, making it very hard to predict. This decreases the value of the unit test.

Actually if you test more than the code you write, you are not writing unit tests, but integration tests. A database is another server, even if it's installed on the same hardware. Of course you want to test the stored procedures. You will test these in the integration test that you, as a professional developer, perform. Realize the difference and keep unit tests small and controlable. This way the unit tests have the most value to the development process. If every independent small part is proven to work, the whole is far more likely to work.