Andy Swan has set me thinking about database test strategies. I’ve been following the same general strategy for a few years now; it is handy not to have to think about the meta issues, and concentrate on the task in hand, but every now and again it is good to have a rethink.

Andy is arguing for a “black box” approach to the database testing. Rather than fall for the fallacy of best practice by arguing one approach over another, I’ll try and consider the advantages, disadvantages, and applicability of different approaches. I will ignore another approach, mocking the database, for the moment (except for saying that I agree with Andy that it is not usually a good idea).

I’ll just steal the examples from Andy’s blog entry.

Black Box

public void testAdd()

{

  Expenses e = new Expenses();

  assertEquals( 0, e.getNumEnties() );

  e.add( “description”, “23.50” );

Expenses other = new Expenses();
assertEquals( 1, other.getNumEntries() );
}

Check database Contents

public void testAdd()

{

  Expenses e = new Expenses();

  e.add( “description”, “23.50” );

  // check database table has been updated

}

I usually write database checking and test helper code to aid database access during tests, and compare an in-memory model of the table contents with that in the database.

Advantages of Black Box over Checking Database Contents

An obvious advantage of the Black Box approach is that it is, by definition, implementation independent. You can change the table name and field names without changing the test. You could even decide to change the persistence mechanism to the file system or LDAP or Object database without changing the test.

Another advantage is the you do not need to access the database in your test code. Even with testing data access code, this adds an additional level of complexity to the Check Database Contents approach.

Advantages of Checking Database Contents

Not everything that goes in always comes out – it is quite usual for data to be written to the database that are never read by the application. This could be audit data, or data used for MIS reporting; this could consist of entire tables or housekeeping columns; you’ll need to read it to test the write.

Conversely, not everything that comes out goes in – it is even more common for data to be read by the application that are written by something else, for example user security details; something will need to write the data to test the read.

Some direct database access will often be needed for tearing down the tests: delete requirements are rarer than create, read and update; it is very rare for production code to require a delete all my data method.

With Black Box, you need to code more before you get to green – you need to write both the read and write code before your code gets to green. Also you have more code to search for bugs – if the test fails it could be in the read or the write.

Conclusion
I may have been too dogmatic in always taking the Check The Database approach when writing to a database. The appeal of The Black Box approach is its simplicity; my experience is that the database structure changes more rarely than the code and when it does the domain model needs to change anyway. What put me off Black Box is my preference for keeping the amount of code being exercised by a single test small.

I will probably give the The Black Box approach a try, bearing in mind that it is only applicable when there is symmetry between reads and writes.