in-memory DBs evaluation

Question

Hi guys,

I am trying to increase the overall Integration test execution time and I am currently evaluating various in-memory db solutions. The idea is to have DAOs hit in-mem db during the tests as opposed to hitting a real DB. This is a java app using Hibernate for persistence.

I'd be interested to see your experience with one of these products H2, Derby, HSQLDB, Oracle Berkeley DB.

Some of my concerns are: will in-mem DBs be able to execute stored procedures, custom native sql? Can you selectively choose which one of your services should hit real DB vs in mem DB?

And overall, since this approach involves DB bootstrapping(pre-load/pre-create all tables with data) I am now thinking if it'd be simply easier to just mock out the DAO layer and not even worry about all the unknown problems that in mem DB may bring...

thanks.

Answer 1

Mock out the DAO layer.

Despite what some claim unless you are just using trivial sql the subtle implementation differences and differing feature sets between databases will limit what you can do (stored procedures, views etc.) and also to some extent invalidate the tests.

My personal mocking framework of choice is Mockito. But there are lots that do the job and mocking out the DAO is standard practice so you'll find lots of documentation.

Answer 2

It is bad idea to have different databases for unit-testing and for production.

BTW, testing in real database should be fast, probably you are doing something wrong in your tests.

Answer 3

My suggestion is to test everything, including the DAO layer as you mention. But see if you can test it in pieces. Services, DAOs, UI.

For service layer testing, mock out the DAOs. That way the service layer tests are independent of whether the DAOs are working. If the service layer tests are using DAOs and using a real database then I'd argue that it's not really a Unit test but an Integration test. Although those are valuable too, if they fail it doesn't pinpoint the problem like a Unit test.

For our DAO layer tests we use DbUnit with HSQLDB. (Using Unitils helps if you are using Spring/Hibernate/DbUnit to tie it all together.) Our DAO tests execute nice and quickly (which is important when you have 500+ tests). The memory db schema is built from our schema creation scripts so as a side effect we are testing those as well. We load/refresh a known set of data from some flat files into the memory database. (Compared to when we were using the DEV database and some data would get removed which then broke tests). This solution is working great for us and I would recommend it to anyone.

Note, however, that we are not able to test the DAO that uses a stored proc this way (but we only have one). I disagree somewhat with the poster who mentioned that using different databases is "bad" -- just be aware of the differences and know the implications of doing so.

You didn't mention if you are using Hibernate or not -- that is one important factor in that it abstracts us away from modifying any SQL that may be specific to Oracle or SQLServer or HSQLDB which another poster mentioned.

Answer 4

I just came across Oracle Times Ten in mem db. http://www.oracle.com/technology/products/timesten/index.html

This may seem like possibly the most painless solution. Since no additional mocking/configuration is required. You still have all of your Integration tests intact hitting the DB but now the data is delivered faster. What do you guys think ?