How to store Flyway H2 migrations' history in another schema than PUBLIC?

Question

Using Flyway with H2 database, I get out of the box all SQL schema migrations history stored in PUBLIC.schema_version.

I would like to store this table in a dedicated SQL schema FLYWAY, like this: FLYWAY.history.

Reasons for doing so are not to pollute H2 console visually when opening PUBLIC tables, and to avoid any namespace collision.

By modifying the property flyway.table, the name for the history table can be changed.

But using flyway.table=FLYWAY.history does not work. The schema FLYWAY is not created and the table PUBLIC.'FLYWAY.history' gets created instead.

How should one tweak Flyway configuration in order to achieve the expected result?

Answer 1

Providing this property solves the problem partially: flyway.schemas=FLYWAY,PUBLIC.

By doing so, the history table will be stored in the schema FLYWAY but all the migrations will be run by default on this schema.

Please refer to http://flywaydb.org/documentation/commandline/migrate.html and look for schemas for more details.

I found 2 issues to this approach, which can be fixed with minor tweaks.

1st issue:

The schema FLYWAY must be existing before any Flyway migration attempt. This can be done in java by using stmt.execute("CREATE SCHEMA IF NOT EXISTS FLYWAY"); and close the database before migration

2nd issue:

All the migrations will run by default on the schema FLYWAY. This can be fixed by modifying each SQL migration file to specifically point to PUBLIC schema. Each file would then contain these statements: create table PUBLIC.PERSON (...);

I've solved my problem at hand, but I'm not fully happy with the fix and the extra manual work. Hopefully someone can come with a better answer (more native way) and less tweaks.