How can I use unit tests and TDD to test an app that relies mostly on database CRUD operations?

Question

At work, one of my projects is mostly about taking data passed in from an external client and persisting it in a database. It's a Java enterprise app using JPA and most of our logic revolves around CRUD operations.

The majority of our bugs involve JPA in one way or another.

• Example 1: If you click the save button twice, JPA might try to insert the same entity into the database a second time, causing a primary key violation.
• Example 2: You retrieve an entity from the database, edit it and try to update its data. JPA may try to create a new instance instead of updating the old one.

Often the solution is needing to add/remove/change a JPA annotation. Other times it has to do with modifying the DAO logic.

I can't figure out how to get confidence in our code using unit tests and TDD. I'm not sure if it's because unit tests and TDD are a bad fit, or if I'm approaching the problem wrong.

Unit tests seem like a bad fit because I can only discover these problems at runtime and I need to deploy to an app server to reproduce the issues. Usually the database needs to be involved which I consider to be outside the definition of a unit test: These are integration tests.

TDD seems like a bad fit because the deploy + test feedback loop is so slow it makes me very unproductive. The deploy + test feedback loop takes over 3 minutes, and that's just if I run the tests specifically about the code I'm writing. To run all the integration tests takes 30+ minutes.

There is code outside this mold and I always unit test that whenever I can. But the majority of our bugs and the biggest time sinks always involve JPA or the database.

---

There is another question that is similar, but if I followed the advice I'd be wrapping the most unstable part of my code (the JPA) and testing everything but it. In the context of my question, I'd be in the same bad situation. What's the next step after wrapping the JPA? IMO that question is (perhaps) a step to answer my question, but not an answer to it.

Answer 1

One option is to use an in-memory testing database such as H2; it tends to be about about 10x faster than a standard disk-using database, and with lower startup/teardown times.

Whether it will help does largely depend on whether the JPA issues you are having are general enough that they will still fail on different database. Not much point running tests faster if they miss the bulk of the problems.

But if you can do 10 runs with H2 for every one with the full system, it could pay off.

Answer 2

Other people have answered with "Mock out your DB!" - but what's the point in mocking out your DB layer if you actually need to test how it interacts with your code?

What you're looking for is integration tests and/or automated UI tests. You mentioned that the problem happens when:

*If you click the save button twice*

The only way to test for this is to write an automated UI test to click on the button twice. Maybe check out Selenium.

You will probably also need a unit testing DB and for your tests point it towards that. A pain to maintain but welcome to TDD in the real world.

Answer 3

Databases can be very easy to unit test - you need stored procedures and transactions.

This what Microsoft says about Database unit testing. You can also run unit tests against a database, writing your tests in Java or C# by setting up a DB connection, beginning a transaction, write whatever data you want to use for the test to the DB, run the tests and then rollback. No damage to the DB if you were using one you also deployed to and you get fully isolated tests.

Hope this can give you some insight how to do it within your framework.

Answer 4

In the example you give in your question, you can't unit test/TDD your way into the situation of clicking the button twice to cause an error very easily. But what you can unit test is that in the code which is called when you click the button, if you get an exception from the persistence layer you handle it appropriately (by either mocking out the persistence layer or by using an in-memory database as has been suggested in other answers) - either by rethrowing or displaying an error or whatever.

You're right that TDD can start to break down when you need to perform tests that aren't a good fit for a unit test (i.e. integration/system tests) - this formed quite a lot of the discussion in the recent "Is TDD Dead?" debates between Kent Beck, Martin Fowler and David Heinemeier Hansson: http://martinfowler.com/articles/is-tdd-dead/