Hibernate ORM - Database multitenancy example from documentation does not work

[omasseau]

Describe the bug

Hi,

I'm trying to set up multitenancy using the DATABASE approach, as described here : https://quarkus.io/guides/hibernate-orm#multitenancy but it seems it is not working.

Here is my configuration :

quarkus.hibernate-orm.database.generation=none

quarkus.hibernate-orm.multitenant=DATABASE

quarkus.datasource.a.db-kind=postgresql
quarkus.datasource.a.username=a
quarkus.datasource.a.password=a
quarkus.datasource.a.jdbc.url=jdbc:postgresql://localhost:5432/a

quarkus.datasource.b.db-kind=postgresql
quarkus.datasource.b.username=b
quarkus.datasource.b.password=b
quarkus.datasource.b.jdbc.url=jdbc:postgresql://localhost:5432/b

But I'm getting this error at startup :

io.quarkus.runtime.configuration.ConfigurationException: The Hibernate ORM extension could not infer the dialect for persistence unit '<default>'. When using database multi-tenancy, you must either configure a datasource for that persistence unit (refer to https://quarkus.io/guides/datasource for guidance), or set the dialect explicitly through property 'quarkus.hibernate-orm.dialect'.

The error is not very clear and I cannot find any reference to 'quarkus.hibernate-orm.dialect' in https://quarkus.io/guides/datasource

Expected behavior

No response

Actual behavior

No response

How to Reproduce?

No response

Output of uname -a or ver

Microsoft Windows [Version 10.0.19045.4529]

Output of java -version

21.0.3

Quarkus version or git rev

3.12.1

Build tool (ie. output of mvnw --version or gradlew --version)

Apache Maven 3.9.6

Additional information

No response

[quarkus-bot]

/cc @gsmet (hibernate-orm), @yrodiere (hibernate-orm)

[yrodiere]

Hello. Thanks for reporting.

The error is not very clear and I cannot find any reference to 'quarkus.hibernate-orm.dialect' in https://quarkus.io/guides/datasource

The message:

When using database multi-tenancy, you must either configure a datasource for that persistence unit (refer to https://quarkus.io/guides/datasource for guidance), or set the dialect explicitly through property 'quarkus.hibernate-orm.dialect'.

The useful part in your case:

either configure a datasource for that persistence unit

... i.e.: set quarkus.hibernate-orm.datasource to one of your datasources, it doesn't matter which as long as it's one you intend to use in Hibernate ORM.

or set the dialect explicitly through property 'quarkus.hibernate-orm.dialect'.

... i.e.: quarkus.hibernate-orm.dialect=PostgreSQL.

I tend to agree that the part about "configuring a datasource" is confusing; this should be talking about "setting property X to one of the datasources" instead, or maybe not be mentioned at all...

I cannot find any reference to 'quarkus.hibernate-orm.dialect' in https://quarkus.io/guides/datasource

That's the wrong documentation, you should look into the one you mentioned initially, i.e.: https://quarkus.io/guides/hibernate-orm#database-approach

... and that documentation is indeed wrong, we need to fix it. The suggested configuration does not set the datasource nor the dialect, so it would not work. I'll send a PR soon to fix this.

It doesn't help that multi-tenancy quickstarts are broken: #41759 . Let's try to address that as well...

[omasseau]

@yrodiere
Hello, In the meantime I've managed to make it work by adding this line :

quarkus.datasource.db-kind=postgresql

But is has the side effect of starting a Postgres dev service container for nothing :)
I will try using quarkus.hibernate-orm.datasource or quarkus.hibernate-orm.dialect=PostgreSQL as you mentionned.
(EDIT: Indeed it seems better than using quarkus.datasource.db-kind=postgresql, as it does not create a postgres container)

Note also that after making the datasources work, I've noticed that the flyway configuration is wrong in the documentation. I think it should be :

quarkus.flyway.base.locations=classpath:database/default
quarkus.flyway.base.migrate-at-start=true

quarkus.flyway.mycompany.locations=classpath:database/mycompany
quarkus.flyway.mycompany.migrate-at-start=true

Maybe you should also name the folder 'database/default' to 'database/base', because it seems a bit weird and confusing not having the folder name maching the datasource name.

To resume currently this works for me, with a datasource named 'a' and a datasource named 'b' :

quarkus.hibernate-orm.database.generation=none

quarkus.hibernate-orm.multitenant=DATABASE

quarkus.hibernate-orm.datasource=a

quarkus.datasource.a.db-kind=postgresql
quarkus.datasource.a.username=user_a
quarkus.datasource.a.password=password_a
quarkus.datasource.a.jdbc.url=jdbc:postgresql://localhost:5432/db_a

quarkus.datasource.b.db-kind=postgresql
quarkus.datasource.b.username=user_b
quarkus.datasource.b.password=password_b
quarkus.datasource.b.jdbc.url=jdbc:postgresql://localhost:5432/db_b

quarkus.flyway.a.locations=database/a
quarkus.flyway.a.migrate-at-start=true

quarkus.flyway.b.locations=database/b
quarkus.flyway.b.migrate-at-start=true

[omasseau]

I'm adding a little tip here for anyone who comes across this and who would like to use a schema approach but declare 2 datasources (in order to be able to have dedicated users for each schema, which is better for security in a multitenancy approach. The SCHEMA approach with a unique user for all schemas is not recommended in terms of security - Maybe this could be added to the documentation :)) :

• Use a single database
• Create two schemas in your database (lets say schema 'a' and schema 'b')
• Create two users (lets say 'user_a' and 'user_b')
• Set 'user_a' as the owner of schema 'a' and set 'user_b' as the owner of schema 'b'.

In quarkus jus use quarkus.hibernate-orm.multitenant=DATABASE and declare 2 datasources like in the example of the post above, but just have the 2 datasources point on the same jdbc.url.

-> Each user being the owner of its dedicated schema (schema 'a' for user 'user_a' and schema 'b' for user 'user_b'), the datasource will automatically be in the right schema when connecting to the database for each user.