Saving in-memory H2 database to disk

Question

How can I save/load full embedded h2 in-memory database to some file or directory in binary mode for faster loading.

I want to use this for caching data so I don't have to run all the lines of create table/insert clauses every time.

Answer 1

Instead of using an in-memory database, you could use a regular (persisted) database. You can still use in-memory tables even then (create memory table).

The easiest way to persist a completely in-memory database to disk is to use the SCRIPT TO 'fileName' SQL statement. This will create an SQL script. The data is stored in text form, which doesn't sound like the most efficient solution. However usually the bottleneck is the disk anyway and not formatting / parsing the text.

Another option is to create another database, link the tables with the in-memory database (using create linked table or the link_schema function), and then use create table as select to persist the tables.