H2 database restore to particular state for testing

Question

We use H2 database to execute tests. To isolate each test from another one, the database schema and basic data-setup is dropped and re-created before each test.

Is it possible to create a restore-point after the first setup of the database and restore before each test the data of this point?

SCRIPT just creates a sql-file with all tables and datas. Not a big difference to our own initialization.

Question database restore to particular state for testing is the same, just for Oracle and Postgres.

Answer 1

An old question, but I find it is still relevant. AFAIK there is no restore-point support.

Here is a simple, yet fast approach to backup/restore.

Create a backup prior to running the first test:

Connection conn = DriverManager.getConnection("jdbc:h2:mem:myDatabase;DB_CLOSE_DELAY=-1;LOG=0");
Statement stat = conn.createStatement();
stat.execute("SCRIPT TO 'memFS:myDatabase.sql'");
stat.close();
conn.close();

Restore after each test:

Connection conn = DriverManager.getConnection("jdbc:h2:mem:myDatabase;DB_CLOSE_DELAY=-1;LOG=0");
Statement stat = conn.createStatement();
stat.execute("DROP ALL OBJECTS");
stat.close();
conn.close();

conn = DriverManager.getConnection("jdbc:h2:mem:myDatabase;DB_CLOSE_DELAY=-1;INIT=runscript from 'memFS:myDatabase.sql';LOG=0");
conn.close();

Note that SHUTDOWN command turned out to be faster than DROP ALL OBJECTS, but it caused some issues (connection pool unable to reestablish connection).

I would not say the above approach is slow, far from it. But with a large database and thousands of tests there is still room for improvement as the method above takes some time. I managed to achieve a few times faster backup/restore (~15ms for a DB with ~350 tables) manually composing a script performing TRUNCATE TABLE, ALTER SEQUENCE and do the INSERT of all initial data (needs SET REFERENTIAL_INTEGRITY FALSE for cleanup/restore procedure to be really fast). The code is cumbersome but was worth the effort.