Using H2 database only for Unit testing

Question

I have a spring boot application standing on Postgres database.

Now I want to use h2 database for Unit testing alone.

Is this right to do? or what is the recommendation

Answer 1

Yes and you should also use H2 as an in memory database as it allows to create a clean data base fast enough, to execute unit tests against and to delete the database fast enough when the test cycle phase was executed.
Creating and deleting a physical database at each build would consume much time and would do your local build slow.

Now, automatic testing should not rely only on H2.
This has some limitations that can create slight different behaviors compared to your target DBMS (PostgreSQL).
You should also create integration tests that uses the target DBMS.
Generally these integration tests should not be executed automatically on the developer build but on a continuous integration environment.

H2 compatibility and limitations :

H2 provides some specific database compatibility modes (for PostgreSQL and many others) but these have multiple corner cases.

It supports not fully the ANSI SQL and specific database features:

Compatibility

All database engines behave a little bit different. Where possible, H2 supports the ANSI SQL standard, and tries to be compatible to other databases. There are still a few differences however:

In MySQL text columns are case insensitive by default, while in H2 they are case sensitive. However H2 supports case insensitive columns as well. To create the tables with case insensitive texts, append IGNORECASE=TRUE to the database URL (example: jdbc:h2:~/test;IGNORECASE=TRUE).

And you could some undetailed information on this page, about specific specific database modes :

Compatibility Modes

For certain features, this database can emulate the behavior of specific databases. However, only a small subset of the differences between databases are implemented in this way. Here is the list of currently supported modes and the differences to the regular mode:

DB2 Compatibility Mode

...

MySQL Compatibility Mode

...

Oracle Compatibility Mode

...

PostgreSQL Compatibility Mode

To use the PostgreSQL mode, use the database URL jdbc:h2:~/test;MODE=PostgreSQL or the SQL statement SET MODE PostgreSQL.

For aliased columns, ResultSetMetaData.getColumnName() returns the alias name and getTableName() returns null. When converting a floating point number to an integer, the fractional digits are not be truncated, but the value is rounded. The system columns CTID and OID are supported. LOG(x) is base 10 in this mode.

Answer 2

I can recommend that. H2 has a kind of compatibility mode to postgres, which makes it quite similar. The only part, where we had problems were the lacking of "common table expressions".

The biggest advantage I see is the in memory db. You can easily start for each test with a blank slate, that is much easier than with any harddisk backed dbms.

As live DB especially when you need to store much data, in my opinion the efficiency is lacking. We had some performance problems in tests with bigger data amounts, like 1000000 records. Because of this you naturally can not do any meaningfull index-optimizations using H2.