Accessing a remote H2 database in automatic mixed mode

Let's say you have an application running on a server.

Let's say that application uses an H2 database in automatic mixed mode.

And let's say you want to run some SQL queries.

On the server

Download the platform-independent ZIP from the H2 page and extract it into the directory of your choice, which we’ll refer to as h2Dir.

Go to the directory and start the server:

cd h2Dir
java -cp h2*.jar org.h2.tools.Server

The output should look similar to this:

TCP server running at tcp://192.168.123.123:9092 (only local connections)
PG server running at pg://192.168.123.123:5435 (only local connections)
Web Console server running at http://192.168.123.123:8082 (only local connections)
Failed to start a browser to open the URL http://192.168.123.123:8082: Browser detection failed, and java property 'h2.browser' and environment variable BROWSER are not set to a browser executable.

This informs us about something important: that a console server has been started on port 8082.

192.168.123.123 is, of course, a placeholder IP that represents your server's IP address.

On your computer

As mentioned in the H2 server output, it only allows local connections, so if someone tries to access 192.168.123.123:8082, they won’t be able to connect. So how do we access it?

We’ll create a local port forwarding using SSH. Run:

ssh -L 127.0.0.1:8083:192.168.123.123:8082 your_ssh_user@192.168.123.123 -N

You’ve just created an SSH tunnel. Sounds cool, right? Such wow, very internet, you’re quite the hacker!

Essentially, this means that you can now initiate a TCP connection from your computer to port 8082 on the server as if you were on the server, simply by connecting to port 8083 on your computer.

I used 8083 here for clarity, to show that the local port is listed first and the remote port second, but you could use the same port number.

You can now open localhost:8083 in your browser and fill in the connection details.

The key part here is the JDBC URL, with the value

jdbc:h2:~/theDatabaseDir/database;AUTO_SERVER=TRUE

Here, theDatabaseDir is the directory where the H2 database file database.mv.db is located, under the user directory of your_ssh_user.

Want me to go into more detail about this trick? Then click this button like crazy: