Stop Unit Testing Database Code

Writing tests that use an actual database is hard.


Now that this has been established, let’s have a look at a blog post by Marco Behler, in which he elaborates on various options when testing database code, with respect to transactionality. Testing database transactions is even harder than just testing database code. Marco lists a couple of options how to tweak these tests to make them “easier” to write.

One of the options is:

3. Set the flush-mode to FlushMode.ALWAYS for your tests

(note, this is about testing Hibernate code).

Marco puts this option in parentheses, because he’s not 100% convinced if it’s a good idea to test different behaviour from the productive one. Here’s our take on that:

Stop Unit Testing Database Code

By the time you start thinking about tweaking your test setup to achieve “simpler” transaction behaviour (or worse, use mocks for your database), you’re pretty much doomed. You start creating an alternative system that heavily deviates from your productive system. This essentially means:

  • that results from tests against your test system have (almost) no meaning
  • that your tests will not cover some of the most complex aspects of your productive system
  • that you will start spending way too much time on tweaking tests rather than implementing useful business logic

Instead, focus on writing integration tests that test your business logic on a very high level, i.e. on a “service layer” level, if your architecture has such a thing. If you’re using EJB, this would probably be on a session bean level. If you’re using REST or SOAP, this would be on a REST or SOAP service level. If you’re using HTTP (the retro name for REST), it would be on an HTTP service level.

Here are the advantages of this approach:

  • You might not get 100% coverage, but you will get the 80% coverage for those parts that really matter (with 20% of the effort). Your UI (or external system) doesn’t call the database in the quirkiest of forms either. It calls your services. Why would you test anything other than your services?
  • Your tests are very easy to maintain. You have a public API to your UI (or external system). It’s formal and easy to understand. It’s a black box with well-defined input and output parameters, which makes it easy to read / write tests

Databases are stateful. Obviously

What you have to do is let go of this idea that your database will ever participate in a “unit” (as in “unit” test). Units are pretty stateless, and thus it is very easy to write mutually independent unit tests for functional algorithms, for instance.

Databases couldn’t be any less stateless. The whole idea of a database is to manage state. And that’s very complicated and completely opposite to what any unit test can ever model. Many of the most meaningful state transitions span several database interactions, or even transactions, or maybe even services. For instance, it may be important that the CREATE_USER service invocation be immediately followed by an invocation of CHANGE_PASSWORD. You can only integration-test that on a service layer. Don’t believe it? What if CREATE_USER depends on an external LDAP system? Or complex security logic in stored procedures? Your integration test’s got that covered.


Writing tests that use an actual database is hard.

Yes. That won’t change. But your perception may. Don’t try to tweak things around this fact. Create a well-known test database. Reset it between tests. And write integration tests on a very high level. The 20/80 cost/benefit ratio will leave you no better choice.

Stay tuned for another blog post on this blog about how we integration-test the jOOQ API against 16 actual RDBMS

12 thoughts on “Stop Unit Testing Database Code

  1. Even in-memory db testing alienates the tests from prod data but it’s not that hard to branch them to system integration tests against the prod db as long as those are run on Jenkins as opposed to dev stations.

    1. I can see the benefit of replacing one RDBMS by another for the sake of dev-speed, but the risks of testing stuff against an entirely different storage model or transaction model is significant. I like your point of at least performing the “system integration tests” (e.g. performed during nightly builds) against the actual productive RDBMS. I guess that’s an acceptable pragmatic compromise.

        1. ;-) That’s not what I’m talking about. I know JDBC is a good API to interface with all RDBMS. I’m really talking about the differences between the RDBMS themselves, and the impact these differences have upon the whole system. Of course, this doesn’t matter too much to a 10-table-Hibernate app…

  2. I always considered a test that crossed system boundaries to be at least an integration test, possibly a functional test. By definition, unit tests cannot hit the database (or any other system).

    1. Exactly. But all the tweaking and mocking of system parts originates from the unit testing mind-set. I was particularly intrigued by that other article’s idea of tampering with transaction behaviour for the sake of test “optimisation” :-)

  3. So far, unit testing with H2 and Liquibase has been fairly painless. Test data can be formatted as a CSV file, and tagged with the “test” context. Wiring SpringLiquibase around the data source has worked well.

Leave a Reply