The Smart Way To Test Your Data Access Code

A very important factor to determine before writing any unit test, is what its scope should be. In other words a unit test should be written with clear objectives in mind, as to what it’s going to test, and anything outside this scope should be ignored as far as possible. This means that if we would like to verify business rules applied to data, then we should not test anything else. This includes the low level data access methods responsible for returning and transforming the data from the database.

Mocks come in extremely handy in this scenario. They can be used to return generated data from my data access methods that reflect an ideal situation where result-sets are 100% correct. However this creates an overly artificial test setup, which completely ignores the role of data access methods. Mistakes in the underlaying queries also go unnoticed until its too late.

Testing data access code, and including them in the scope of unit tests is a very cumbersome process. Hence I give them very low priority in my unit tests.

The most straightforward option is to follow the following process:

  1. Insert generated objects with the expected initial state.
  2. Fetch the inserted objects from the database, and execute business logic operations on them.
  3. Update the objects.
  4. Re-fetched them again, and delete the returned objects to clean-up the database.

With this approach problems start when unit tests fail midway, and bits and pieces of the broken objects’ state remain scattered across the development database. Over time it gets messy, and often the broken data affects other queries and unit tests. This means that we lose the isolated characteristic of a good unit test, since one unit test can cause unwanted effects in other unit tests through the central database. Very bad indeed.

So what can we do to solve this problem? Luckily Hendry Luk has a whole series on this very problem and how we can solve it. He suggests the following smart ways to test data access code:

  1. In-memory database using Sqlite
  2. Transaction rollback
  3. Restorable file-based DB
  4. Preset state with NDBUnit
  5. Fake DB provider with Linq