I’m trying to set up Flyway to work with H2 in a spring boot 3 r2dbc app (Flyway is working when I connect to mysql instead). In application.yml I have:
spring:
r2dbc:
url: r2dbc:h2:mem:///~/db/
name: testdb
username: sa
password:
flyway:
url: jdbc:h2:mem:///~/db/testdb
user: ${spring.r2dbc.username}
password: ${spring.r2dbc.password}
baseline-on-migrate: true
Then I have in `V1_1__create_users_and_posts_tables.sql`:
CREATE TABLE IF NOT EXISTS users
(
id bigint NOT NULL AUTO_INCREMENT,
username varchar(255) NOT NULL DEFAULT '',
email varchar(255) NOT NULL DEFAULT '',
phoneNumber varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS posts
(
id bigint NOT NULL AUTO_INCREMENT,
userId bigint NOT NULL,
title varchar(255) NOT NULL DEFAULT '',
description varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (id)
);
It works but it creates a PUBLIC schema:
Database: jdbc:h2:mem:///~/db/testdb (H2 2.2)
Flyway upgrade recommended: H2 2.2.224 is newer than this version of Flyway and support has not been tested. The latest supported version of H2 is 2.2.220.
Schema history table "PUBLIC"."flyway_schema_history" does not exist yet
Successfully validated 2 migrations (execution time 00:00.009s)
Creating Schema History table "PUBLIC"."flyway_schema_history" ...
Current version of schema "PUBLIC": << Empty Schema >>
Migrating schema "PUBLIC" to version "1.1 - create users and posts tables"
Migrating schema "PUBLIC" to version "2.1 - create images table"
Successfully applied 2 migrations to schema "PUBLIC", now at version v2.1 (execution time 00:00.003s)
But when I call one of my api endpoints, I see this error:
Caused by: io.r2dbc.spi.R2dbcBadGrammarException: Table "USERS" not found (this database is empty);
I tried changing the testdb name for PUBLIC in my application file and creating the tables under the PUBLIC schema but I still get the same error. Any ideas anyone ? (The repo is here:
https://github.com/danygiguere/spring-boot-3-reactive-with-kotlin-coroutines)