Unit Testing and Databases

Recently I’ve been refactoring some DB access code. Basically unraveling the DB code that is sprinkled throughout the code into a separate layer. Testing this code is challenging and honestly tedious. I’ve been using a couple of different methods for testing. This is Java code, so for some testing I’ve been using a library called Mockrunner. This lib provides a number of components to assist in unit testing, but the only one I’ve been using is the JDBC module.  The key to this module is a mock JDBC driver. By using this module, you first prepare the driver by creating exceptions or mock ResultSets with the results that you expect for given SQL queries. Then you run your code to be tested. Finally you can query the driver to ensure that the expected SQL calls occurred with the parameters you expected, along with checking that the tested code property processed the mock results and/or exceptions.The major advantage to this method is there is no need for an actual database. It also allows causing exceptions to occur predictably to test error handling. Forcing exceptions from a real DB can be difficult, if not impossible. But since it isn’t a real DB, there’s no way to know if the SQL being used is actually valid or that the mock ResultsSets you create are actually what the SQL would result in. It is also very precise work getting everything setup and then confirmed. Another problem is that if you don’t get things setup just right, you can end up with errors that simply don’t occur with a real driver – the main one being getting a null back from a query when a real DB would give you an empty ResultSet.The second method I use is, unsurprisingly, using a real DB. Though in this case I’m using an in-memory H2 Database. Actually a coworker started using this with our code, and I’ve just been expanding on it a bit. Since it’s a real DB, it results in a more accurate test. This is a bit clearer to set up, at least to me. Though it does still take quite a bit of prep. The big difference is that with Mockrunner you have to think in terms of the ResultSets you expect, whereas with a real DB you think in terms of the initial data tables. One method I use to simplify setup in some cases is to use an XML based definition of the DB tables with some helper methods to load the XML. At the very least it makes the data to be used in the tests easier to enter and read.I’m sure I’m not mentioning anything new here to anyone who has done any DB code testing. To be honest, this post is probably more of a rant. To me, DB code is some of the most important to test. Even though a lot of it is almost boilerplate and architecturally very similar from project to project, I find it’s still very easy to introduce hard-to-find bugs into DB code. Regardless of the test methodology used, it takes a lot of setup just to run a single test. And often so much happens in that code (just the necessary boilerplate of DB access) confirmation of results can be just as long and tedious.So, what other methods can be used to simplify DB testing, without simplifying it to the point of worthlessness?

This entry was posted in General, Unit Testing. Bookmark the permalink.

Leave a Reply