Challenges with Spring R2DBC Migrations Using Flyway

For prototyping and testing, the H2 database is a quick and easy solution. To use Flyway with R2DBC (Reactive Relational Database Connections), the following considerations are critical.

1. Using the “In Memory” H2 Database will create two instances, and hence the Flyway and R2DBC will not be able to find the schemas.
2. Must use the file or other modes for the R2DBC and Flyway configurations to work.

Observations

Upon applying the in-memory database configuration

spring.r2dbc.url=r2dbc:h2:mem:///examples;DATABASE_TO_UPPER=FALSE;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL;

spring.flyway.enabled=true
spring.flyway.url=jdbc:h2:mem:///examples;DATABASE_TO_UPPER=FALSE;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL;
spring.flyway.baseline-on-migrate=true

Errors

Table "tablename" not found (this database is empty); SQL statement:
SELECT table.* FROM table [42104-224]

Corrections

Apply the file-based database configuration to fix the table selection challenge.

spring.r2dbc.url=r2dbc:h2:file:////tmp/h2db/examples;DATABASE_TO_UPPER=FALSE;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL;

spring.flyway.enabled=true
spring.flyway.url=jdbc:h2:file:////tmp/h2db/examples;DATABASE_TO_UPPER=FALSE;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL;
spring.flyway.baseline-on-migrate=true

References

1. H2 Database URL Overview