I’m trying to set up Flyway to work with H2 in a s...

# spring

[dany giguere]

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)

[hho]

Please take a look at the channel topic. Your problem doesn't seem specific to Kotlin at all.

[kqr]

and spring 🙂.
Anyway, why you have different urls for flyway and access?

[dany giguere]

Flyway doesn’t work over r2dbc. Si I’m using jdbc for the migrations but r2dbc for the app. I do the same for my dockerized mysql db but it works. It’s probably a problem that many people working with r2dbc/reactivity (and h2) ran into.

[kqr]

I am not referring to r2dbc vs jdbc

[Robert Jaros]

You could check https://github.com/nkonev/r2dbc-migrate

[dany giguere]

Thanks @Robert Jaros I’ll try it tonight 🙂