5 ways to database testing heaven

30th August 2011

Many applications are backed by persistent storage in the form of a database. When writing tests for our application, it is important not to forget to include behaviour surrounding interactions with the database. However, writing these tests is often easier said than done. There are common pitfalls, and here I suggest some sensible ways to avoid them. However, the subject matter is rather dry, but hopefully the kittens will help.

Midge cat and computer

Write tests that only cover database interactions

Testing how your application interacts with the database are, by definition, integration tests. However, many teams make the mistake of testing much more of the application than necessary when testing database interactions. Often, supporting frameworks, such as Spring or Rails, encourage this behaviour. Falling into this trap leads to slow and brittle tests.

For example, when testing the various permutations of a search operation in a web application, you do not need everything from the controller down wired into your test. Merely instantiate your database access objects and pass them a database connection. You will find that your tests will be quicker and less brittle. Then you can write many more permutations testing your search queries.

Cats love linux

Wrap your tests in transactions

Most of the commonly used relational databases support transactions. Transactions are used in applications when an atomic unit of work must be either applied completely or rolled back. They are also used to provide isolation around changes, when a number of applications are connected to a given database. In both of these cases, the intended goal is to commit these changes. We can use transactions in our test to ensure the reverse. We can use transactions to ensure that any changes to data that are made are rolled back.

This can have a profound effect on your ability to write tests. You no longer need any code to perform data clean up at the end of a test. You no longer need to understand every possible side effect of the code under test. And most importantly, you’ll never accidentally leave something behind, that could interact with and break another test.

Working From Home

Don’t couple yourself to your baseline data

When writing tests that interact with the database, it is likely that you’ll need to use a baseline of data to get those tests running. A baseline is very handy. It means that each test doesn’t have to set up a large amount of extraneous data in order to run. This makes your tests much more readable as there is much less noise in them. It also makes them run more quickly if you only set up this baseline once during a test run.

However, it is very easy to end up with a test that is tightly coupled to this baseline. This can make it very difficult to diagnose test failures, as the data driving the test is unlikely to be visible in your test case. Moreover, it makes your tests much more brittle. Any changes to the baseline can impact a large number of tests.

To avoid these pain points, always make sure that any data that you make an assertion against, is set up by your test. Do this even if the baseline contains a data set that you could use, as that baseline could change under your feet at any time.


Use triangulation

Often when writing applications that interact with a database, it’s likely that you’ll be using some form of framework. Object relational mapping tools such as Hibernate and ActiveRecord are very powerful in their ability to map database table columns to fields in objects, as well as having many other very useful functions. However, it is useful to test that these mappings have been done properly, particularly if you are in the (unfortunate) situation that your database is acting as an integration point between a number of other applications.

In this case, it is important to remember to test using some form of triangulation. If you persist and rehydrate an object using the same mappings, then your tests would never catch a defect around mapping a field to the wrong column. In these cases, it’s advisable to persist and fetch data using two different mechanisms in your test. Using a little plain old SQL in a Hibernate mapping test won’t cost you a great deal in readability and will help prove that your objects are being hydrated and persisted properly.


Get your business logic out of the database

We’ve spoken a great deal about the pitfalls of database interaction testing. Even if you’re doing all the good things suggested here, you still might find that you’re experiencing a great deal of pain. This may simply be because you have too much business logic pushed into the database.

Stored procedures, named queries, etc. can be great from a performance point of view, but with them you’ll need more database interaction testing to support them.

Database tests are still harder to write, maintain and run. If possible, shifting your business logic to your application will make it much more accessible to your whole team and cheaper to maintain. Often considerations of performance are premature optimisations, and having code that is easier to maintain is also easier to optimise if the need genuinely arises.

blog comments powered by Disqus