Multiple independent H2 databases within one JVM

Question

Is it possible to start up and shut down multiple H2 databases within a JVM?

My goal is to support multi-tenancy by giving each user/account their own database. Each account has very little data. Data between the accounts is never accessed together, compared, or grouped; each account is entirely separate from the others. Each account is only accessed briefly once a day or a few times a month. So there are few upsides to housing the data together in a single database, and some serious downsides.

So my idea is that when a user logs in for a particular account, that account’s database is loaded. When that user logs out, or their web app session (Vaadin app) times out, that account’s database is closed, it's data flushed to storage, and possibly a backup performed. This opening and closing would be happening for any number of databases in parallel.

Benefits include minimizing the amount of memory in use at any one time for caching data and indexes, minimizing locking and other contention, and allowing for smooth scaling.

I'm new to H2, so I'm not sure if its architecture can support this. I'm asking for a denial or confirmation of this capability, along with any tips or caveats.

Answer 1

Yes it is possible to do so. Each database will contain its own mini environment, no possible pollution between databases.

You could for example use a jdbc url based on the user id or login from the user:

• jdbc:h2:user1 in H2 1.3.x embedded mode
• jdbc:h2:./user1 in H2 1.4.x embedded mode
• jdbc:h2:tcp://localhost/user1 in tcp mode

You can use any naming convention for the database name, provided your OS allows it: user1, user2, etc... or truly the name of the login.

Tips:

• use the server mode rather than the embedded mode, allowing for same user multiple connections from multiple sessions/hosts
• have a schema migrator (like flyway) to initialize each newly created db
• ensure you manage name collisions at the top level of your app, and possibly store these databases and corresponding logins in a dedicated database as well

Caveats:

• do not use a connection pool as connections will be difficult to reuse
• You must make sure IFEXISTS=TRUE is not used on the server
• avoid using tweaks on the jdbc url, like turning LOG=0, UNDO_LOG=0, etc...
• I do not know if you'll have a limitation from your OS or the JVM on how many db files could be opened like this.
• I do not know if such setting can be tweaked from the manual pages. I could not find one.

Please refer to H2 manual in doubts of url parameters.