How to override the database user that the flyway configuration uses for migration?

[ChrisAtFremtind]

Is there a way to configure flyway to use a different datasource other than the default one?

From the guide of setting up flyway to work with multiple datasources the guide describes using named datasources, but this is to separate the schemas / concern for each datasource, but I can’t see a any configuration properties or examples where the datasource that flyway uses are changed / overridden, it seems it can only implicitly use the same user as the configured named datasource.

The guide contains this:

quarkus.datasource.db-kind=h2
quarkus.datasource.username=username-default
quarkus.datasource.jdbc.url=jdbc:h2:tcp://localhost/mem:default

quarkus.flyway.schemas=DEFAULT_TEST_SCHEMA
quarkus.flyway.locations=db/default/location1,db/default/location2
quarkus.flyway.migrate-at-start=true

What I wished was available was something like this:

quarkus.datasource.db-kind=h2
quarkus.datasource.username=applicationuser
quarkus.datasource.password=applicationuser-password
quarkus.datasource.jdbc.url=jdbc:h2:tcp://localhost/mem:default

quarkus.flyway.schemas=DEFAULT_TEST_SCHEMA
quarkus.flyway.locations=db/default/location1,db/default/location2
quarkus.flyway.migrate-at-start=true

*quarkus.flyway.username=migrationuser
*quarkus.flyway.password=migrationuser-password

*Properties which does not exist.

I have thought out some ways to make this work is

1. Inject the Flyway object into a applicationserviced bean and override the datasource manually. I can’t see a way of injecting the flyway object and keep the default configured migration behavior. It is to my understanding that if you inject and / or do some operations on the flyway object the default migration out of the box is lost.
2. Another way i think might work is to set the desired user to use when establishing a new connection to the database. This also seems like a round about way of doing it.
3. Would using two named datasources one called owner and the other called application work? where only one of them have a flyway configuration enabled?

The application user has only limited access to the database as a security measure.

As an example to how this is achieved in Spring with the same configuration above:

spring:
  datasource:
    url: dbc:h2:tcp://localhost/mem:default
    username: applicationuser
    password: applicationuser-password
    driver-class-name: org.h2.Driver
  flyway:
    user: migrationuser
    password: migrationuser-password

With spring's integration to flyway you can explicitly set the desired user that flyway should use to migrate. 
Is there any plans to make this available as configuration options? Or is there another way to make this work which i don’t see?

[quarkus-bot[bot]]

/cc @cristhiank, @gastaldi, @geoand, @gsmet

[gastaldi]

There is an issue requesting this feature here:

• configure dedicated db user for database migrations: DML-only user for datasource, but DDL user for migration #24639

[ChrisAtFremtind]

Oh, I see. I unfortunately didn't find that issue when i searched for the problem. I see that the issue has been inactive as of march 30th. Should i continue the conversation in that issue and close this?

I made an example project with what i and what the other issue detailed as a workaround. That was defining two datasources where only one has flyway enabled.

Here it is if it is of any interest: https://github.com/ChrisAtFremtind/quarkus-flyway-example

[gastaldi]

I think it's fine to continue the conversation in that issue. Would you like to submit a PR introducing this feature?

[ChrisAtFremtind]

I'm sorry, I do not have time right now to create a PR now with the feature. We also went another direction where we circumvented this problem.