When using Hibernate ORM multitenancy by schema, Flyway scripts only run in the default schema

[iddacosta]

Describe the bug

Multitenant BUG

Flyway BUG

Expected behavior
Schema change dynamic with hibernate in query
Flyway run scripts in all schemas

Actual behavior
Queries only run in default schema

To Reproduce

Steps to reproduce the behavior:

1. cd /docker and run docker-compose up
2. cd /rhino-api and run in dev mode
3. call in postman http://localhost:8080/user and add header x-tenantId

rhino.zip

[quarkus-bot]

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

[yrodiere]

@iddacosta Thanks for your report.

Unfortunately, I suspect the problems you've been experiencing are caused by invalid configuration;
below is a list of what I found.

The resulting, fixed reproducer (which no longer fails) can be found here:
rhino.zip

Problems in the reproducer

Spaces in configuration values

Comma-separated configuration values must be separated by commas, not by commas + space.

So you should fix this:

   flyway:
     migrate-at-start: true
     baseline-on-migrate: true
     baseline-version: 1.0.0
     create-schemas: true
     clean-at-start: true
-    schemas: maripa, bicas, rhino
+    schemas: maripa,bicas,rhino
-    locations: db/migration/maripa, db/migration/bicas, db/migration/rhino
+    locations: db/migration/maripa,db/migration/bicas,db/migration/rhino

Incomplete SQL scripts

The SQL scripts include a reference to table user_account, which is not defined anywhere. I assumed you meant "user", so I replaced every instance of user_account with "user".

Invalid datasource reference

Your configuration references a datasource that doesn't exist:

quarkus:
  datasource:
    db-kind: postgres
    username: postgres
    password: 1234
    jdbc:
      url: jdbc:postgresql://localhost:5432/postgres
      driver: org.postgresql.Driver
  hibernate-orm:
    multitenant: SCHEMA
    multitenant-schema-datasource: postgres # HERE

The name postgres doesn't match any datasource, so Quarkus will not find it.

I removed the line multitenant-schema-datasource: postgres to fix this and use the default datasource.

Duplicate SQL scripts for version

Apparently, Flyway expects exactly one SQL script for each version, so you layout with one script per schema doesn't work.

Just merge your scripts (adding the schema qualifier before references, e.g. "user" becomes "rhino"."user") and put them all in one file.

Duplicate REST endpoints

You have two different REST endpoints for the same path (/user/), so RestEasy ends up picking one at random, which is not what you want.

Give a different name to each endpoint:

     @GetMapping
     public ResponseEntity<List<UserDTO>> findAll() {
         return ResponseEntity.ok().body(userService.findAllUsers());
     }
 
-    @GetMapping
+    @GetMapping("/by-username/")
     public ResponseEntity<UserDTO> findByUsername(@RequestParam("nome") final String nome) {
         try {
             final UserDTO userDTO = userService.findByUsername(nome);
             return ResponseEntity.ok().body(userDTO);
         } catch (UserNotFoundException exception) {
             return handleException(exception);
         }
     }

Using default-schema with schema-based multi-tenancy

Whatever schema your tenant ID select, this bit will override it:

    database:
      default-schema: rhino

So it's not a good idea to set it along with schema-based multi-tenancy. Remove this from your configuration.

"user" table name

"user" is a reserved keyword in postgresql, and attempting to use it in SQL queries will lead to strange results.

You really should escape it:

-@Table(name = "user")
+@Table(name = "\"user\"")
 public class User extends PanacheEntityBase {

Conclusion

Strictly speaking, it seems problems are in the reproducer, not in Quarkus. So I will close this issue as invalid. If you think otherwise, feel free to reopen this issue or to open another one.

However, there are usability problems, and I will open additional issues about those:

1. Space handling in configuration property values is dodgy. I can understand that spaces are meaningful in schema names (though that really feels wrong...), but it seems dangerous that when an entry in locations is not found (because of a leading space), it is simply ignored. Maybe we should at least issue warnings?
2. The fact that setting quarkus.hibernate-orm.database.default-schema overrides the schema defined by the multi-tenancy strategy is very surprising. At the very least, using this setting with schema-based multi-tenancy should trigger an error?