How to setup Flyway migrations for multiple schemas

Question

I'm currently using Quarkus and imported Flyway extension to perform database migrations. Currently it is working fine for the default schema 'public', but it doesn't seem to be able to pick up other .sql files to run.

I currently have folder structure like this: db/food db/public

I store all the .sql files that I want to run for migration into the separate folders, and each folder maps to the schema name in the database (this one is just naming convention for us to keep track easier).

Now for the application.properties file, this is what I have currently:

# common flyway configs for all schemas
quarkus.flyway.enabled=true
quarkus.flyway.migrate-at-start=true
quarkus.flyway.out-of-order=true
quarkus.flyway.repair-at-start=true
quarkus.flyway.ignore-migration-patterns=*:ignored
quarkus.flyway.validate-migration-naming=true
#quarkus.flyway.schemas=public,food

# public schema config
quarkus.flyway.baseline-on-migrate=true
quarkus.flyway.baseline-version=0.9.4
quarkus.flyway.baseline-description=Initial version
quarkus.flyway.schemas=public
quarkus.flyway.locations=db/public

quarkus.datasource.db-kind=postgresql
quarkus.datasource.username=${DB_USER}
quarkus.datasource.password=${DB_PASS}
quarkus.datasource.jdbc.url=${JDBC_URL}

# food schema config
quarkus.flyway.food.enabled=true
quarkus.flyway.food.baseline-on-migrate=true
quarkus.flyway.food.baseline-version=0.9.4
quarkus.flyway.food.baseline-description=Initial version
quarkus.flyway.food.schemas=food
quarkus.flyway.food.locations=db/food

quarkus.datasource.food.db-kind=postgresql
quarkus.datasource.food.username=${DB_USER}
quarkus.datasource.food.password=${DB_PASS}
quarkus.datasource.food.jdbc.url=${JDBC_URL}

However it seems that whenever it is run, it only scans the migrations inside the db/public folder. The other migrations from other folder will not run.

If anyone has any idea, it will be great. Or if anyone needs more details, I can provide.

Answer 1

Found success in specifying the specific schema name as this:

quarkus.datasource.jdbc.url=${JDBC_URL}?currentSchema=public
quarkus.datasource.food.jdbc.url=${JDBC_URL}?currentSchema=food

# this one as well
quarkus.flyway.food.migrate-at-start=true