Is it bad practice to use h2db for integration test in a spring boot context?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

Just to add to these answers, but with the ease of setting up test containers I don't really see the need/advantage to using h2 for integration tests. most pipelines also have docker runtime out the box..

I would even go so far as to say that mocking your repository calls is about as valuable as using h2 for integration tests since h2 just doesn't provide enough exactness to a real database to really trust the results.