Hibernate Panache extension do not support switching between mutliple datasources

[alex-kovalenko1982]

Task: switch database provider depending on environment. postgresql for test and dev, oracle for prod.

Expectation: hibernate is a great orm tool that also provides abstraction from the database engine, so we expected some simple setting to switch db-kind and connection url via environment variables and everything would work out of the box.

Result: mission seems impossible due to so many obstacles starting with db-kind and many other datasource and hibernate config properties being locked on build-time and thus not available to runtime config.

Anyway, we have followed a guide to configure multiple data sources and activate only one them:

quarkus.hibernate-orm."pg".packages=org.acme.model.shared
quarkus.hibernate-orm."pg".datasource=pg
quarkus.hibernate-orm."pg".database.generation=drop-and-create
quarkus.hibernate-orm."pg".active=false
quarkus.datasource."pg".db-kind=h2
quarkus.datasource."pg".active=false
quarkus.datasource."pg".jdbc.url=jdbc:postgresql:///your_database

quarkus.hibernate-orm."oracle".packages=org.acme.model.shared
quarkus.hibernate-orm."oracle".datasource=oracle
quarkus.hibernate-orm."oracle".database.generation=drop-and-create
quarkus.hibernate-orm."oracle".active=false
quarkus.datasource."oracle".db-kind=oracle
quarkus.datasource."oracle".active=false
quarkus.datasource."oracle".jdbc.url=jdbc:oracle:///your_database

but that is not supported by panache extension despite being mentioned in its documentation as well https://quarkus.io/guides/hibernate-orm-panache#multiple-persistence-units

the problem is that panache requires an entity to be linked to a single persistent unit and we have two of them even though only one is active at a time

[ERROR] Failed to execute goal io.quarkus:quarkus-maven-plugin:3.13.0:build (default) on project <panache-multi-data-sources>: Failed to build quarkus application: io.quarkus.builder.BuildException: Build failure: Build failed due to errors
[ERROR] 	[error]: Build step io.quarkus.hibernate.orm.panache.deployment.PanacheHibernateResourceProcessor#build threw an exception: java.lang.IllegalStateException: Panache entities do not support being attached to several persistence units:
[ERROR] 	- org.acme.model.shared.Employee is attached to: oracle,pg
[ERROR] 	at io.quarkus.hibernate.orm.panache.deployment.EntityToPersistenceUnitUtil.determineEntityPersistenceUnits(EntityToPersistenceUnitUtil.java:56)
[ERROR] 	at io.quarkus.hibernate.orm.panache.deployment.PanacheHibernateResourceProcessor.build(PanacheHibernateResourceProcessor.java:127)

panache extension provides a great support to manage entity related operations so we would like to keep it and not switching back to basic hibernate functionality.

any workarounds to overcome this issue?

[quarkus-bot[bot]]

/cc @FroMage (panache), @gsmet (hibernate-orm), @loicmathieu (panache), @yrodiere (hibernate-orm)

[yrodiere]

Task: switch database provider depending on environment. postgresql for test and dev, oracle for prod.

Fair warning: you'll regret this the very first time you face an Oracle-specific bug. This is a bad idea, you've been warned, now I'll shut up about this :)

---

but that is not supported by panache extension despite being mentioned in its documentation as well https://quarkus.io/guides/hibernate-orm-panache#multiple-persistence-units

To be fair what's mentioned in your link is exactly the limitation you're encountering:

A given Panache entity can be attached to only a single persistence unit.

You can use Panache with multiple persistence units, and even multiple datasources, it's just that each persistence unit must have its own entities.

And yes, this is a known limitation. I don't know if there's an issue for it though, maybe @FroMage would know. In theory the limitation could be removed, but only in repository mode; active records most likely cannot handle this.

any workarounds to overcome this issue?

If:

• this is really just about test/dev/prod
• and the entities are exactly the same in all persistence units
• and you don't need to handle multiple datasources in prod (you always deploy with Oracle)
• and you don't run @QuarkusIntegrationTest tests using failsafe (typically you need this for native tests)

... in that case yes, I think there is a workaround.

IIRC, the application gets rebuilt by Quarkus for tests, dev, and prod, allowing you to update build-time settings such as quarkus.hibernate-orm.datasource in each profile.

So you just don't need multiple persistence units, and a simpler solution would be this:

quarkus.hibernate-orm.database.generation=drop-and-create

quarkus.datasource."pg".db-kind=postgres
quarkus.datasource."pg".jdbc.url=jdbc:postgresql:///your_database

quarkus.datasource."oracle".db-kind=oracle
quarkus.datasource."oracle".jdbc.url=jdbc:oracle:///your_database

%test,dev.quarkus.hibernate-orm.datasource=pg
%test,dev.quarkus.datasource."oracle".active=false
%prod.quarkus.hibernate-orm.datasource=oracle
%prod.quarkus.datasource."pg".active=false

You won't find that in the documentation though (at least I hope so), because as stated above, it's a bad practice.

[alex-kovalenko1982]

Thanks for the reply @yrodiere ,

I have simplified a bit the use case to make it easier to grasp from the first glance and by that lost some important meaning.
The full story is following.
We are migrating an enterprise solution from wildfly to quarkus. Our final delivery is a set of applications each packed into a docker image. End user decides on which database these applications will be running. In enterprise world it is still often a choice between some major rdbms providers: oracle, postgres, mssql. It means in the build time we do not know which DB engine will be used.

In our legacy wildlfy solution db-driver for hibernate could have been configured in runtime via ENV variables. Having quarkus being a highly configurable framework, we expected the same simplicity in this aspect as well.

Meanwhile, found another guide for maven tooling which suggests to build an application multiple times.
https://quarkus.io/guides/maven-tooling#maven-multi-build

This sounds like a better solution that allows to avoid a bit messy config for multiple data sources you described above. On the other hand, assuming we build our application for every supported DB (postgres, oracle, mssql), our docker images will be 3 times thicker repeating the same app structure, ui and libs just for the sake of one config property.
Or we could build 3 separate docker images for each of our applications, i.e. my-app-oracle:1.0.0, my-app-mssql:1.0.0, my-app-postgres:1.0.0 but in this case a lot of config mess shall be added to k8s helm charts or docker compose yaml to correctly select images reflecting selected database. And when the delivery contains e.g. 20 apps then we need to deliver eventually 60 images.

Long story short, to avoid all that extra build and config effort
is it possible to move quarkus.datasource.db-kind to the runtime config in the future releases or will it stay in build-time?

[yrodiere]

In our legacy wildlfy solution db-driver for hibernate could have been configured in runtime via ENV variables. Having quarkus being a highly configurable framework, we expected the same simplicity in this aspect as well.

Note Quarkus is (supposedly) an opinionated stack, and as such does not aim to provide configuration of every single aspect.

Long story short, to avoid all that extra build and config effort
is it possible to move quarkus.datasource.db-kind to the runtime config in the future releases or will it stay in build-time?

It is not. As you can imagine, this option is set at build time for a reason. Moving it to runtime would mean moving a lot of initialization code to runtime, increasing the image size for native builds, increasing startup time, and maybe even leading to new errors in native mode (caused by limitations at runtime in native mode).
That would be detrimental for a majority of users who don't need this flexibility.

With the current version of Quarkus, if you need to change the db-kind at runtime, and you can't use multiple persistence units (which is the usual solution when not using Panache), then the only solutions available to you are:

• Build one container image per database (what you mentioned), which makes things less practical,
• Add a re-augmentation step (which is essentially a partial rebuild) to your container's startup, which makes images larger and startup slower.

In the short term, we might be able to provide support for a single entity in multiple persistence units with Panache provided you don't use the active record pattern but repositories instead. I would suggest you file a feature request (GitHub issue) if you're interested.

In the longer term, we could try to come up with a solution that allows users to define a finite set of "alternatives" for a given PU/datasource, allowing you to pick one alternative at runtime. In theory this could provide better performance (startup time, image size) than reaugmentation, but it would also be less flexible, and harder to maintain in Quarkus itself, so I'm not sure others in the team would buy the idea.

[alex-kovalenko1982]

thanks again for the quick answer @yrodiere

In the short term, we might be able to provide support for a single entity in multiple persistence units with Panache provided you don't use the active record pattern but repositories instead. I would suggest you file a feature request (GitHub issue) if you're interested.

I have a feeling that supporting multiple persistence units for a database driver switch task is kind of misuse of the pattern. Even if Panache extension supports multiple persistence units in repository mode there is no confidence that all the other extensions can work also with such a workaround. For instance, liquibase or config-jdbc may also have some issues with switching persistence units in runtime.

At the moment, in order to keep startup time as low as possible, we should accept the fact that we have to build either multiple quarkus-apps into a docker image and choose the relevant to the selected database app on container startup or even a dedicated image per database as mentioned before.

In the longer term, we could try to come up with a solution that allows users to define a finite set of "alternatives" for a given PU/datasource, allowing you to pick one alternative at runtime. In theory this could provide better performance (startup time, image size) than reaugmentation, but it would also be less flexible, and harder to maintain in Quarkus itself, so I'm not sure others in the team would buy the idea.

Here is just a wish: would be great if such possibility exists and it should not be mixed with multiple persistence units pattern. As you proposed, it should be a single persistent unit with some pre-defined db-kind alternatives and only one of them can be active at a time.

[yrodiere]

I finally took the time to file an issue: #43656

It's a big one though, this will take time -- if it even happens. Fingers crossed!