6

I need to separate reader/writer PostgreSQL instances. I have one write replica and several read replicas.

Quarkus supports adding multiple data sources, however it is not clear how to make Panache/Hibernate pick the right one for different queries.

Question: How to configure Quarkus to use different data sources for read/write access?

Sasha Shpota
  • 9,436
  • 14
  • 75
  • 148
  • 1
    Panache with quarkus and hibernate does not support sharing entities between persistence units. When an entity is retrieved its associated to the persistence unit, If you want to use different persistent units with different entities it is totally possible with annotations at the entitiy package level or with the use of the traditional EntityManager, but if you are using Panache i assume that you are not into using the EntityManager if you can avoid it. Is acceptable to you to use different entities for reading and writing, or accessing the entity manager? – Javier Toja Aug 09 '21 at 12:07
  • @karelss thank you. I am not sure I understand completely, what you mean by different entities for reading and writing. Do you mean having several entities for the same table and using them depending on whether it is a read or a write? How then would it work with several read instances? – Sasha Shpota Aug 09 '21 at 13:23
  • 1
    As you can see on the guide https://quarkus.io/guides/hibernate-orm#multiple-persistence-units, Panache entities are bound to a datasource by the association with the package where they reside. You can annotate the package instead of using configuration properties but each package goes to one datasource or the default. What I'm proposing is having two different Entity models. One for queries (dto projections) and reading entities for select queries and other entity model for storing data. You can end with the similar models but in two packages, so you can go to two datasources – Javier Toja Aug 09 '21 at 13:29
  • https://quarkus.io/guides/hibernate-orm#multiple-persistence-units-attaching-model-classes – Javier Toja Aug 09 '21 at 13:37
  • @karelss thank you. Unfortunately, it is not an option in my case. – Sasha Shpota Aug 09 '21 at 15:01
  • 1
    Panache is very restrictive concerning persistence units (just go to `AbstractJpaOperations#persist(Object entity)` and you'll see that it always uses the entity class to find the PU). I don't think it is possible without using different entity classes. – Vinicius Aug 11 '21 at 18:39

1 Answers1

0

We use the following in our properties file to distinguish which DB datasource you need to pick up. Then you separate in different packages code pointing to different datasources.

quarkus.hibernate-orm."db".packages=com.mycompany.outgoing.db
quarkus.hibernate-orm."push".packages=com.mycompany.outgoing.push

I think you´ll need to have subclasses pointing to the same BaseEntityof some sort to distinguish between datasources.

Other option I can think of, that I haven´t used yet, is injecting 2 different EntityManager in your repository class and manage your repository from the EntityManager directly.

@Inject
@PersistenceUnit("db") 
EntityManager entityManager;

@Inject
@PersistenceUnit("push") 
EntityManager entityManagerPush;
Chexpir
  • 1,876
  • 18
  • 33
  • This will not work with panache, which is a base requirement for the question, without panache. To implement this idea which is fine It is possible to share the same entities using the EntityManager merge operation without required being subclass of a shared based entity. – Javier Toja Aug 16 '21 at 07:21
  • It says how to make ...Panache/Hibernate...? – Chexpir Aug 16 '21 at 11:06