Quarkus: Support multiple datasources when using Hibernate with Panache

Created on 7 Jun 2019 · 31Comments · Source: quarkusio/quarkus

There seems to be no way to use the Hibernate with Panache in case you have more than one datasource defined.
This should be possible and supported as one cannot assume that only one datasource will be used by a service.
As a matter of fact, one of our services that we would like to move to Quarkus needs 3 different datasources and due to the simplicity of code, we would like to use Hibernate with Panache.

arepanache kinenhancement pinned triagon-ice

Source

dwamara

👍8

Most helpful comment

@gsmet @emmanuelbernard @FroMage Bumping this one just in case it has been forgotten, just got a client who wants to switch to quarkus from payara and this is something that will be needed.

dwamara on 22 Jan 2020

👍11

All 31 comments

@emmanuelbernard do we already support multiple data-sources in Quarkus?

@dwamara how do you separate your datasources wrt entities? Do you have a separation where each entity has a datasource, but no entity lives in more than one datasource?

FroMage on 11 Jun 2019

@FroMage Quarkus supports multiple datasources (you can easily do it via the config, see https://quarkus.io/guides/datasource-guide#multiple-datasources for more info) but... there is no way to easily configure multiple persistence units in application.properties: it's doable but you have to go back to using a persistence.xml. That's something I want to work on.

In Panache, we need a way to attach an entity to the persistence unit and inject the right entity manager.

gsmet on 11 Jun 2019

👀3 👍3 😕1

So I'm guessing that this sort of marker should be sufficient (once implemented):

@Entity
@PersistenceUnit("mysql")
public class MyEntity extends PanacheEntity {
}

I don't think we need to put it on the equivalent repository because it can use the one declared on the entity: I don't think it makes sense to have a repo that is not using the same persistence unit as its entity.

FroMage on 11 Jun 2019

@gsmet how should my persistence.xml look like? I tried:

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
             http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
             version="2.1">

    <persistence-unit name="default" transaction-type="JTA">
        <jta-data-source>java:/default</jta-data-source>

        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
            <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
        </properties>

    </persistence-unit>

    <persistence-unit name="extra" transaction-type="JTA">
        <jta-data-source>java:/extra</jta-data-source>

        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
            <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
        </properties>

    </persistence-unit>
</persistence>

With:

quarkus.datasource.url=jdbc:h2:tcp://localhost/mem:test
quarkus.datasource.driver=org.h2.Driver
quarkus.datasource.max-size=8
quarkus.datasource.min-size=2

quarkus.datasource.extra.url=jdbc:h2:tcp://localhost/mem:extra
quarkus.datasource.extra.driver=org.h2.Driver
quarkus.datasource.extra.max-size=8
quarkus.datasource.extra.min-size=2

But it throws with:

org.junit.jupiter.api.extension.TestInstantiationException: TestInstanceFactory [io.quarkus.test.junit.QuarkusTestExtension] failed to instantiate test class [io.quarkus.it.panache.PanacheFunctionalityTest]
    at org.junit.jupiter.engine.descriptor.ClassTestDescriptor.invokeTestInstanceFactory(ClassTestDescriptor.java:314)
    at org.junit.jupiter.engine.descriptor.ClassTestDescriptor.instantiateTestClass(ClassTestDescriptor.java:289)
    at org.junit.jupiter.engine.descriptor.ClassTestDescriptor.instantiateTestClass(ClassTestDescriptor.java:281)
    at org.junit.jupiter.engine.descriptor.ClassTestDescriptor.instantiateAndPostProcessTestInstance(ClassTestDescriptor.java:269)
    at org.junit.jupiter.engine.descriptor.ClassTestDescriptor.lambda$testInstancesProvider$2(ClassTestDescriptor.java:259)
    at org.junit.jupiter.engine.descriptor.ClassTestDescriptor.lambda$testInstancesProvider$3(ClassTestDescriptor.java:263)
    at java.util.Optional.orElseGet(Optional.java:267)
    at org.junit.jupiter.engine.descriptor.ClassTestDescriptor.lambda$testInstancesProvider$4(ClassTestDescriptor.java:262)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$prepare$0(TestMethodTestDescriptor.java:98)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.prepare(TestMethodTestDescriptor.java:97)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.prepare(TestMethodTestDescriptor.java:68)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$prepare$1(NodeTestTask.java:107)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.prepare(NodeTestTask.java:107)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:75)
    at java.util.ArrayList.forEach(ArrayList.java:1257)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
    at java.util.ArrayList.forEach(ArrayList.java:1257)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229)
    at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197)
    at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:137)
    at org.eclipse.jdt.internal.junit5.runner.JUnit5TestReference.run(JUnit5TestReference.java:89)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:541)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:763)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:463)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:209)
Caused by: java.lang.ExceptionInInitializerError
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at java.lang.Class.newInstance(Class.java:442)
    at io.quarkus.runner.RuntimeRunner.run(RuntimeRunner.java:129)
    at io.quarkus.test.junit.QuarkusTestExtension.doJavaStart(QuarkusTestExtension.java:236)
    at io.quarkus.test.junit.QuarkusTestExtension.createTestInstance(QuarkusTestExtension.java:302)
    at org.junit.jupiter.engine.descriptor.ClassTestDescriptor.invokeTestInstanceFactory(ClassTestDescriptor.java:299)
    ... 49 more
Caused by: java.lang.RuntimeException: Failed to start quarkus
    at io.quarkus.runner.ApplicationImpl1.<clinit>(Unknown Source)
    ... 58 more
Caused by: javax.persistence.PersistenceException: Unable to locate persistence units
    at io.quarkus.hibernate.orm.runtime.PersistenceUnitsHolder.convertPersistenceUnits(PersistenceUnitsHolder.java:89)
    at io.quarkus.hibernate.orm.runtime.PersistenceUnitsHolder.initializeJpa(PersistenceUnitsHolder.java:62)
    at io.quarkus.hibernate.orm.runtime.HibernateOrmTemplate$4.created(HibernateOrmTemplate.java:86)
    at io.quarkus.arc.runtime.ArcDeploymentTemplate.initBeanContainer(ArcDeploymentTemplate.java:108)
    at io.quarkus.deployment.steps.ArcAnnotationProcessor$build13.deploy_0(Unknown Source)
    at io.quarkus.deployment.steps.ArcAnnotationProcessor$build13.deploy(Unknown Source)
    at io.quarkus.runner.ApplicationImpl1.<clinit>(Unknown Source)
    ... 58 more
Caused by: java.lang.UnsupportedOperationException: Value found for #getJtaDataSource : not supported yet
    at io.quarkus.hibernate.orm.runtime.boot.LightPersistenceXmlDescriptor.verifyIgnoredFields(LightPersistenceXmlDescriptor.java:75)
    at io.quarkus.hibernate.orm.runtime.boot.LightPersistenceXmlDescriptor.<init>(LightPersistenceXmlDescriptor.java:56)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1382)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499)
    at io.quarkus.hibernate.orm.runtime.PersistenceUnitsHolder.convertPersistenceUnits(PersistenceUnitsHolder.java:87)
    ... 64 more

FroMage on 13 Jun 2019

So it won't work globally today for a few reasons:

today we enlist all @Entity classes in the same Scanner that we pass to all persistence units instead of segregating things.
the LightPersistenceXmlDescriptor indeed does not support any reference to datasoruce and therefore one cannot add a reference from a datasource name to the persistence unit starting operation

To be honest I could not find where there is a mapping from the EMF and the datasources generated by agroal.

emmanuelbernard on 13 Jun 2019

I tried with:

<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
             http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
             version="2.1">

    <persistence-unit name="default" transaction-type="JTA">
        <properties>
            <property name="hibernate.connection.driver_class" value="org.h2.Driver" />
            <property name="hibernate.connection.url" value="jdbc:h2:tcp://localhost/mem:test" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
            <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
        </properties>

    </persistence-unit>

    <persistence-unit name="extra" transaction-type="JTA">
        <properties>
            <property name="hibernate.connection.driver_class" value="org.h2.Driver" />
            <property name="hibernate.connection.url" value="jdbc:h2:tcp://localhost/mem:extra" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
            <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
        </properties>

    </persistence-unit>
</persistence>

But then I can't use Arc.container().instance(EntityManager.class) even with a PersistenceContext qualifier, as suggested by JPAResourceReferenceProvider (this resolver is not even called). We are probably missing something in ORM to support multiple persistence units, which needs to be resolved before ORM with Panache can support that.

Shall I open another issue @emmanuelbernard ?

FroMage on 13 Jun 2019

Yes open an issue please.

emmanuelbernard on 13 Jun 2019

@FroMage Just FYI, making this work on the ORM side is on my list for as soon as we fixed some HV issues and the HV + ORM integration.

gsmet on 13 Jun 2019

Cool. Then this depends on #2835

FroMage on 13 Jun 2019

I would like to help on this. I finish my ticket on jni :)

Dufgui on 6 Jul 2019

@dwamara how do you separate your datasources wrt entities? Do you have a separation where each entity has a datasource, but no entity lives in more than one datasource?

I only saw your question now, no entity lives in more than one datasource.

dwamara on 6 Jul 2019

@Dufgui I'm planning to work on this one as it's a touchy one. Also we need to wait for @machi1990 's work about moving as much configuration as possible to runtime as it will conflict.

gsmet on 7 Jul 2019

ok, i will do the #3014 but I want increase my level ;o) and get more touchy enhancement.

Dufgui on 7 Jul 2019

Any news on this one? We have the gateway API from one of our applications that can't go live without it, unless we don't go with Quarkus on this one, what will be a bummer.

dwamara on 27 Aug 2019

@gsmet @emmanuelbernard Just wanted to bump this one up as it seems to have been forgotten and I quite desperately need it.

dwamara on 13 Sep 2019

@dwamara It's not forgotten, just not yet on the top of my list. I'll see if I can prioritize it a bit more.

gsmet on 13 Sep 2019

BTW: we need quite a lot of infrastructure work done before: support multiple PU in the ORM config for instance.

gsmet on 13 Sep 2019

👍4

BTW: we need quite a lot of infrastructure work done before: support multiple PU in the ORM config for instance.

@gsmet I was planning to give this a go and then was stuck on this :-(

machi1990 on 13 Sep 2019

@machi1990 well, even if you miss the multiple PU stuff, maybe you can try to work on the infrastructure work for Panache? The main issue is that we need to inject the proper entity manager.

Obviously, you wouldn't be able to test it until we have the multiple PU support coming but it would help to have it ready.

gsmet on 13 Sep 2019

@machi1990 well, even if you miss the multiple PU stuff, maybe you can try to work on the infrastructure work for Panache? The main issue is that we need to inject the proper entity manager.

Nice. Thanks for the heads up. I'll slide in your DM a little later to talk about this :-)

machi1990 on 13 Sep 2019

@gsmet @emmanuelbernard another project fell on my lap today that would need this. Until it's done, do you have any alternative to suggest? Or should I just skip quarkus on this one plain and simple?

dwamara on 8 Oct 2019

👍1

Until this is implemented, we decided to build intermediate Microservices for each of the databases that will be called and return JSON. It's a bit cumbersome as we now have to add multiple remote calls for each database operation we would like to have.
Still waiting on this one though.

dwamara on 19 Oct 2019

I have worked on mongo multi datasource. I can handle this one too if needed ?

dufoli on 20 Oct 2019

dwamara on 22 Jan 2020

👍11

Not forgotten. But the current efforts in the data area are about some bug fixes to stabilize the platform and another area around reactive access.

emmanuelbernard on 7 Feb 2020

😕2

This would be a crucial feature for us to move away from Wildfly.

chrisgleissner on 1 Mar 2020

👍1

Hi, I want to move from Spring boot to quarkus, but as I understood for now there is no way to switch datasources in runtime as in spring? As I see there is a way to configure multiple datasources, but there is no mechanism to switch them in run time as in Spring as described here with MultiRoutingDataSource. This is crucial feature for my client as we are using 6 databases.

Myslyvchuk on 1 May 2020

Hi,

I need this feature for a specific project I'm working on.
Which quarkus version should it be released?

luszczynski on 26 Jun 2020

It's currently aimed for 1.8 https://github.com/orgs/quarkusio/projects/5
But that's an aim, it's always difficult to prioritize and stick to things with so many demands

emmanuelbernard on 26 Jun 2020

👍5 👀4 ❤1

This was delivered, right @FroMage @loicmathieu @gsmet ?