2

in our team recently the question was raised if using h2db for integration tests is a bad practice/should be avoided if the production environment relies on a different database engine, in our case MySQL8.

I'm not sure if I agree with that, considering we are using spring boot/hibernate for our backends.

I did some reading and came across this article https://phauer.com/2017/dont-use-in-memory-databases-tests-h2/ stating basically the following (and more):

TL;DR

Using in-memory databases for tests reduce the reliability and scope of your tests. Your application’s SQL may fail in production against the real database, although the h2-based tests are green.

They provide not the same features as the real database. Possible consequences are:

  • You change the application’s SQL code just to make it run in both the real and the in-memory database. This may result in less effective, elegant, accurate or maintainable implementations. Or you can’t do certain things at all.
  • You skip the tests for some features completely.

As far as I can tell for a simple CRUD application with some business logic all those points does'n concern me (there some more in the article), because hibernate wraps away all the SQL and there is no native SQL in the code.

Are there any points that i am overlooking or have not considered that speak against an h2db? Is there a "best practice" regarding the usage of in-memory db for integration tests with spring boot/hibernate?

Jakob
  • 778
  • 3
  • 9
  • 25
  • Of course, Most of the InMemory databases are not 100% compatible with enterprise database technologies and we can't relay on test results as stated on the blog. We do both Integration testing though InMemory DB and TDD with mock dao. Yes database as docker container while testing is makes more sense as long as NO licensing and challenges in running on CD pipeline. – Lovababu Padala Jun 29 '21 at 14:47
  • I would stay with h2 only when doing academic project or testing new frameworks for testing like junit4/junit5, spock, mockito or asserj. When dealing with read project you'll encounter problems like writing your own query with @Query. There is high probability that they won't run on h2. Good and adwanced example here is recursive CTE. In my project it didn't work on h2 so we had to migrate our test database. Beside that you may have problems with character encoding or lack of configuration. – Piotr Solarski Jun 29 '21 at 14:53
  • Thank you guys for your answers, now I know why "opinion based questions" are being closed: it's rather difficult to mark a "correct" answer. Nevertheless I feel I have to, so I chose the one from Michal, not because the one coming from Sakibul is wrong or bad, in the end it was " - it may not stay like that forever." that convinced me. Hope you are fine with that. – Jakob Jul 01 '21 at 06:41

2 Answers2

7

I'd avoid using H2 DB if possible. Using H2DB is good, when you can't run your own instance, for example if your company uses stuff like Oracle and won't let you run your own DB wherever you want (local machine, own dev server...).

Problems with H2DB are following:

  1. Migration scripts may different for H2DB and your DB. You'll probably have to have some tweaks for H2DB scripts and MySQL scripts.

  2. H2DB usually doesn't provide same features, like real RDBMS, you degrade the DB for using only SQL, you won't be able to test store procedures, triggers and all the fancy stuff that may come handy.

  3. The H2DB and other RDBMS are different. Tests won't be testing the same thing, you may get some errors in production that won't appear in your tests.

Speaking of your simple CRUD application - it may not stay like that forever.

But go ahead with any approach you like, it is best to get your personal experience yourself, I got burned on H2DB too often to like it.

Michal Krasny
  • 5,434
  • 7
  • 36
  • 64
4

I would say it depends on the scope of your tests and what you can afford for your integration tests. I would prefer testing against an as close as possible environment to my production environment. But that's the ideal case, in reality that might not be possible for a varied reasons. Also, expecting hibernate to abstract away low level details perfectly is also an ideal case, in reality the abstraction may be giving you a false sense of security.

If the scope of your tests is to just test CRUD operations, an in-memory tests should be fine. It will perform in that scope quite adequately. It might even be beneficial reducing time of your tests, as well as some degree of complexity. It wont detect any platform/version/vendor specific issues, but that wasnt the scope of the test anyways. You can rather test those things in a staging environment before going to production.

In my opinion, it's now easier than ever to create a test environment as close as possible to your production environment using things like docker, CI/CD tools/platform also support spinning up services for that purpose. If this isn't available or too complicated for your use case, then the fallback is acceptable.

From experience, I had faced failures related to platform/version/vendor specific issues when deploying to production though all my tests against in-memory database went green. It's always better to detect these issues early and save a lot of recurrent development time and most importantly your good night sleep.

Sakibul Alam
  • 1,731
  • 2
  • 21
  • 40