Using H2 as in memory database for unit/component tests instead of MySQL

h2 database

So following my recent post regarding the creation of a component test framework – let’s take a closer look in the use case of using H2 as a lightweight in memory database instead of MySQL, for your unit/component tests.

Benefits of using H2 instead of MySQL

It makes a lot of sense to use H2 in the context of component tests instead of MySQL:

  • You can create and destroy a DB/table/data on the fly – once the tests are done the DB disappears. Compare this to having to rollback manually every change your tests have made to your development/local DB in MySQL. Much more comfortable if all changes just disappear once you terminate the test and you have a fresh copy of the DB in the next run.
  • H2 is much more lightweight than MySQL memory wise
  • H2 can be run as part of the java process
Drawbacks of using H2 database instead of MySQL for component tests
  • H2 syntax is not 1:1 with MySQL syntax. You need to make sure you have enabled MySQL mode to facilitate maximum compatibility. But even if you have done this there are still some differences and missing MySQL functions for H2. However, for most use cases you can take your MySQL file and with minimal changes make it work for your H2 dbms tests (for instance: remove the “collation” parameter and the “Engine” specification you are using).

As for unit tests – using H2 can be a refreshing alternative to the “Where”->”Then” assumptions we make in unit tests. It allows us to actually write some data to the DB and fetch it thus giving us more confidence in our unit tests.

Anyway, from my experience it is a quick and easy alternative to actually launching docker containers with MySQL.

Give it a shot.

Leave A Comment

Please be polite. We appreciate that. Your email address will not be published and required fields are marked