How to browse the content of an H2 in-memory database

In this post we'll see how to browse the content of an in-memory H2 database instance. 

As this H2 mode is usually used in unit tests and integration tests, it may come in handy the day a test does not yield the expected results and we need to get a grasp of the data that are handled. 

Method A - H2 TCP server

Starting an H2 TCP server allows to remotely connect to an in-memory database using SQL clients such as Squirrel SQL or Eclipse integrated SQL client.

A TCP server is created through calling createTcpServer() on org.h2.tools.Server. This method
takes an optional String vararg argument that allows to specify options such as the port the server will listen to.

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.h2.tools.Server;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

public class H2MemoryDbBrowserTest {

 private static final String DATABASE_URL = "jdbc:h2:mem:demo;USER=sa;INIT=RUNSCRIPT FROM 'classpath:/scripts/init-db.sql'";
 private static Connection conn;

 @BeforeClass
 public static void initDatabase() throws SQLException {
  conn = DriverManager.getConnection(DATABASE_URL); // creating SQL connection will trigger the DB creation
 }
 
 @AfterClass
 public static void closeDatabase() throws SQLException {
  conn.close();
 }

 @Test
 public void testMethodA() throws SQLException {
  Server server = Server.createTcpServer("-tcpPort", "9999");
  server.start();
  server.stop();
 }
}

 

In this example, the arguments we passed to the createTcpServer() method means the TCP server will be reachable on port 9999.

If we execute this unit test with a breakpoint set after the TCP server has started, we are able to connect to the in-memory DB. To illustrate this, let's connect to it using Squirrel SQL.

Our database has been created with the URL jdbc:h2:mem:demo and a user account whose username is "sa" and has no password. With the TCP server running, our database is remotely accessible with the following URL: jdbc:h2:tcp//localhost:9999/mem/demo.

Connection configuration in Squirrel SQL

Squirrel SQL connected to our in-memory DB

Method B - H2 web server

Another solution consists in starting an H2 web server by calling the static org.h2.tools.Server.startWebServer() method. This method takes an SQL connection as argument. This SQL connection is created by calling java.sql.DriverManager.getConnection() and passing our database's URL as argument.

When the H2 web server starts up, the code execution is suspended and it triggers the opening of a web interface in our default browser:

H2 web server interface

Important note: the suspended code execution will only resume after we explicitly disconnect the web interface by clicking on the button in the upper left corner. Closing either the tab of the browser won't be enough.

Note that the instruction to start the H2 web server can also be executed in the IDE expression evaluation tool. That way, it is even possible to browse the in-memory DB at any point of an executing test without having to first somewhat change the code.

Runnable example

A Maven based project with runnable examples is accessible here