H2 Database "support"

In a project currently underway we had the challenge of connecting to a H2 database, which is used for testing and development before deployment into production when a more traditional database system is then used. As we do not, yet, have a native JDBC plugin it looked like we might end up with CLI based scripts running under toolkit, but that is less than ideal as we want to build a production ready solution with only the database connection details changing.

After some reading - having never seen H2 before - it turns out that H2 supports a limited subset of the PostgeSQL network protocol. Result!

The changes required in this particular instance were to change the H2 process from using the Console entypoint (I am NOT a Java person, so terminology may be way out) to using Server instead - and the Server allows PostgreSQL options, as per: Server

[-pg]				Start the PG server
[-pgAllowOthers]	Allow other computers to connect - see below
[-pgDaemon]			Use a daemon thread
[-pgPort <port>]	The port (default: 5435)

In this case, changing the command line from something like:

java -cp h2*.jar org.h2.Console -tcp -tcpAllowOthers -web -webAllowOthers ...


java -cp h2*.jar org.h2.Server -tcp -tcpAllowOthers -web -webAllowOthers -pg

was enough to then make queries on port 5435.

There are limitations and the documentation warns it’s not production ready, but since this is for development and testing that all good.

Note you do NOT need to install an ODBC layer as hinted in their docs (H2 - Advanced - ODBC Driver) but it is worth reading for the limitations.

If anyone else has already done this and has real world knowledge of the limitations please chime in.

1 Like