h2 In memory database private mode - unable to access console

Question

Mine is a Mule 4 application ( 4.4 ) and to perform munit test am using a H2 in memory database. So earlier was using the jdbc url as :

<property name="url" value="jdbc:h2:mem:empdb;DB_CLOSE_ON_EXIT=FALSE;MODE=ORACLE;
INIT=RUNSCRIPT FROM 'classpath:sql/h2init.sql';TRACE_LEVEL_FILE=4;TRACE_LEVEL_SYSTEM_OUT=3;" />

However the issue with this was within the same Munit suite had multiple Munits and it was complaining since on every test due to h2init.sql being executed twice against the same database. It fails the second time as the tables already exist.

Above quote is actually copied from here but it exactly explains the issue encountered. So used the solution suggested by @peterB which was to use "in memory private" connection mode as below:

<property name="url" value="jdbc:h2:mem:;DB_CLOSE_ON_EXIT=FALSE;MODE=ORACLE;
INIT=RUNSCRIPT FROM 'classpath:sql/h2init.sql';TRACE_LEVEL_FILE=4;TRACE_LEVEL_SYSTEM_OUT=3;" />

Notice that empdb is removed from jdbc url and things are working fine now

However this is now leading to a different issue - which is earlier I could access the H2 console via : http://localhost:8082/ and login

Now when I try it fails with :

A file path that is implicitly relative to the current working directory is not allowed in the database URL "jdbc:h2:mem". Use an absolute path, ~/name, ./name, or the baseDir setting instead. [90011-224] 90011/90011 (Help)

The jdbc url am trying is : jdbc:h2:mem:

In console logs when running munits can see that my munit is connecting fine with db :

INFO  2024-03-03 11:39:00,665 [[MuleRuntime].uber.03: h2database: database opening mem: (build 224)
INFO  2024-03-03 11:39:00,715 [[MuleRuntime].uber.03: h2database: database opened mem:

Was reading up H2 documentation: https://www.h2database.com/html/features.html

In-Memory Databases For certain use cases (for example: rapid prototyping, testing, high performance operations, read-only databases), it may not be required to persist data, or persist changes to the data. This database supports the in-memory mode, where the data is not persisted.

In some cases, only one connection to a in-memory database is required. This means the database to be opened is private. In this case, the database URL is jdbc:h2:mem: Opening two connections within the same virtual machine means opening two different (private) databases.

Sometimes multiple connections to the same in-memory database are required. In this case, the database URL must include a name. Example: jdbc:h2:mem:db1. Accessing the same database using this URL only works within the same virtual machine and class loader environment.

To access an in-memory database from another process or from another computer, you need to start a TCP server in the same process as the in-memory database was created. The other processes then need to access the database over TCP/IP or TLS, using a database URL such as: jdbc:h2:tcp://localhost/mem:db1.

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1. This may create a memory leak, when you need to remove the database, use the SHUTDOWN command.

So the only issue is would like to open up the console in browser and view state of table records while I am in the middle of a Munit in debug mode - how is that possible ?

From above documentation am not sure if h2 In memory database private mode is possible / not possible ?

Answer 1

Unnamed private in-memory databases aren't shared between different connections, every connection has an own independent database. When you need only one connection, you can use this type of databases, but it will not be possible to use H2 Console or any other tool to inspect it, because H2 Console (or other tool) with the same URL will get an own (empty) private database.