Transactions around database tests

1st April 2010

A database is probably the most common integration point found in software development. Therefore, testing that integration point is key. Ensuring that your queries work and that object relational mapping is configured correctly is key to making sure that the database schema maps to what your application expects.

The best way to test this is to write tests around your repositories to test that queries return expected results, and that your objects are loaded and persisted as expected.

For example:

public void shouldPersistBook() {
BookRepository repository = new BookRepository();
Book book = new BookBuilder()
.title(“War of the Worlds”)
.author(“H. G. Wells”)

List<Record> records = executeQuery(“SELECT * FROM book WHERE title = ‘War of the Worlds’”);

assertTrue(records.size() == 1);

However, there is a problem with this. After running this test, the database will have been modified. Assuming that title is the primary key for this table, this test will fail the second time it is run. An even worse scenario is that the test could pass even if the save method did nothing.

Therefore, it is sensible to make all tests occur within a transaction. This can be implemented in the setup and tear down of any database tests:

public void setUp() {
executeStatement(“START TRANSACTION”);

public void tearDown {

This of course only works if your repositories do not use any DDL statements that update the schema, or call commit explicitly. However doing either of these things is very bad, particularly the first, as your application should never be making schema changes. This makes your schema volatile and breaks any transactional wrapping your application may wish to enforce.

blog comments powered by Disqus