Use mocks – use only unit tests and mock the data-access layer, assuming the DAO-to-database communication works

Use a real database deployed somewhere (either locally or on a test environment). The hard part is making sure it’s always in a clean state.

Use end-to-end/functional tests/bdd/UI tests after deploying the application on a test server (which has a proper database).

None of the above is without problems. Unit tests with mocked DAOs can’t really test more complex interactions that rely on a database state. Embedded databases are not always available (e.g. if you are using a non-relational database, or if you rely on RDBMS-specific functionality, HSQLDB won’t do), or they can be slow to start and this your tests may take too long supporting. A real database installation complicates setup and keeping it clean is not always easy. The coverage of end-to-end tests can’t be easily measured and they don’t necessarily cover all the edge cases, as they are harder to maintain than unit and integration tests.

I’ve recently tried a strange approach that is working pretty well so far – stubbing the database. It is applicable more to key-value stores and less to relational databases.

In my case, even though there is embedded cassandra, it was slow to start, wasn’t easy to setup and had subtle issues. That’s why I replaced the whole thing with an in-memory ConcurrentHashMap.

Since I’m using spring-data-cassandra, I just extended the CassandraTemplate class and implemented all the method in the new StubCassandraTemplate, and used it instead of the regular one in the test spring context. The stub can support all the key/value operations pretty easily and you can have a bit more complicated integration tests (it’s not a good idea to have very complicated tests, of course, but unit tests can either be too simple or too reliant on a lot of mocks). Here’s an excerpt from the code:

Cassandra supports some advanced features like CQL (query language), which isn’t as easy to stub as key-value operations like get and put, but in fact it is not that hard. Especially if you do not rely on complicated where clauses (and this is a bad practice in Cassandra anyway), it’s easy to parse the query with regex and find the appropriate entries in the ConcurrentHashMap.

Key-value stores are a good candidate for this approach, as their main advantage – being easy to scale horizontally – is not needed in an integration test scenario. You simply need to verify that your code correctly handles interactions with the database in terms of what it puts there and what it gets back. The exact implementation of that interaction – whether it’s in-memory or using a binary protocol, may be viewed as out of scope.

Note that these tests do not guarantee that the application will work with a real database. They only guarantee that it will behave properly if the database behaves the same way as an in-memory key-value data structure. Which is normally the assumption, but isn’t always true – e.g. the database can impose additional constraints that your stub implementation doesn’t have. Cassandra, for example, doesn’t allow WHERE queries for non-indexed columns. If you don’t take that into account, obviously, your test will pass, but your application will break.

That’s why you’d still need end-to-end tests and possibly some real integration tests, but you can cover most of the code with a simple in-memory stub and only do some “sanity” full integration tests.

This doesn’t mean you should always stub your database, but it’s a good option in your testing toolbox to consider.