Unit-Testing Database Code

So I've just read a interesting post on my brother's blog highlighting the problems of running unit tests against databases. I'll let you read the detail, however, in summary, his thoughts on the subject basically center around two thoughts:

1. Code insert and delete statements for every row of data you need for your unit test in the setup and tear-down methods, respectively.
   The problem with this, approach is that there is a high chance of corrupting your DEV database (hopefully you are not running unit tests against QA or production!)


2. Create a test database containing the data you need to run your unit tests with.
   The problem with this solution is that it involves maintenance overhead for the test database; Making sure that it's clean prior to inserting your data and up to date with any new columns/tables/triggers/procedures/etc.

Now, based on my experience, a lot of people fall into this trap when thinking about unit testing against database data, myself included once upon a time. One day, I saw the light when I was introduced to the concept of "mock objects".

According to Wikipedia, mock objects "are simulated objects that mimic the behavior of real objects in controlled ways". So how does this this help with unit testing database code ? - Well simply put, you can replace every call to your database with a mock object, at test time, that returns the data and/or results you'd expect from your database without requiring a database. To make implementation easier, it helps if your database calls are abstracted out by either a database interface layer or a ORM tool; Most modern ORMs have either built-in support for mock objects or can be adapted to be used with mock objects.

The beauty and simplicity of this approach cannot be stated enough. When testing using mock objects one can do things in unit tests that maybe difficult and/or impossible to replicate with a real database, such as testing for timeouts, loss of database connections and bad and/or corrupt data. It also enables your unit tests to run in a environment without a database (ala build server), which can be very useful if you are doing or want to do nightly test builds.

So there you have it folks you can all sleep a little better at night knowing that you no longer have to struggle with unit testing your database code.