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?
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:
Migration scripts may different for H2DB and your DB. You'll probably have to have some tweaks for H2DB scripts and MySQL scripts.
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.
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.