Dynamic Multi Tenancy possible?

[mrclrchtr]

Could you please assist me in identifying the optimal approach to implement Multi-Tenancy in my database using Spring and jOOQ?

My objective is to dynamically extract the tenant_id from a JWT and integrate it into my database queries.

Additionally, I aim to ensure that new tenants can be seamlessly and dynamically added.

What best practices and concepts should be considered when implementing these requirements, and how can they be effectively implemented in jOOQ?

I tried it with policy:

@Bean
  fun configuration(): DefaultConfigurationCustomizer {
    return DefaultConfigurationCustomizer { defaultConfiguration: DefaultConfiguration ->
      defaultConfiguration.set(
        DefaultPolicyProvider()
          .append(
            AREA,
            AREA.TENANT_ID.eq(1)
          )
      )
    }
  }

This raises the question of whether the ID can be set dynamically here.

In addition, the approach unfortunately did not work with the following insert:

  fun insert(area: AreaRequest): Area {
    return dsl.insertInto(AREA)
      .set(AREA.NAME, area.name)
      .returning()
      .fetchSingleInto(Area::class.java)
  }

Log:

2024-04-17T15:48:59.929+02:00 DEBUG 14802 --- [nio-8080-exec-6] org.jooq.tools.LoggerListener            : Executing query          : insert into "public"."area" ("name", "tenant_id") select "t"."name", "t"."tenant_id" from (select ?, ?) as "t" ("name", "tenant_id") where "t"."tenant_id" = ? returning "area"."area_id", "area"."name", "area"."tenant_id"
2024-04-17T15:48:59.932+02:00 DEBUG 14802 --- [nio-8080-exec-6] org.jooq.tools.LoggerListener            : -> with bind values      : insert into "public"."area" ("name", "tenant_id") select "t"."name", "t"."tenant_id" from (select 'A1', cast(null as int)) as "t" ("name", "tenant_id") where "t"."tenant_id" = 1 returning "area"."area_id", "area"."name", "area"."tenant_id"
2024-04-17T15:48:59.936+02:00 DEBUG 14802 --- [nio-8080-exec-6] org.jooq.tools.LoggerListener            : Fetched result           : +-------+----+---------+
2024-04-17T15:48:59.936+02:00 DEBUG 14802 --- [nio-8080-exec-6] org.jooq.tools.LoggerListener            :                          : |area_id|name|tenant_id|
2024-04-17T15:48:59.936+02:00 DEBUG 14802 --- [nio-8080-exec-6] org.jooq.tools.LoggerListener            :                          : +-------+----+---------+
2024-04-17T15:48:59.936+02:00 DEBUG 14802 --- [nio-8080-exec-6] org.jooq.tools.LoggerListener            : Fetched row(s)           : 0

No database entry is created.

Many thanks for the great tool and the support.

[lukaseder]

What best practices and concepts should be considered when implementing these requirements, and how can they be effectively implemented in jOOQ?

jOOQ isn't really opinionated about "best practices" (assuming these are either about Spring or kotlin or both). jOOQ's SPIs are designed to support any such "best practice"

This raises the question of whether the ID can be set dynamically here.

Of course. Every jOOQ query is dynamic, including such predicates provided by yourself. You can provide AREA.TENANT_ID.eq(myId). You can configure jOOQ in any way you want. While the DefaultPolicyProvider helps implement very simple PolicyProvider implementations, you can always also implement the interface yourself to get access to context info. Alternatively, don't expose the Configuration as a globally accessible @Bean, but create it once per tenant, in whichever way you like using Spring or not.

In addition, the approach unfortunately did not work with the following insert:

Of course not! You have to provide the TENANT_ID to your insert, otherwise, you cannot insert your record (with a NULL tenant ID). I'm assuming this is precisely what you want to achieve, though you might have overlooked the omission of tenant ID in your INSERT?

You can see how this works in the generated query:

insert into "public"."area" ("name", "tenant_id")
select "t"."name", "t"."tenant_id" from (
  select 'A1', cast(null as int) -- You didn't provide the TENANT_ID here!
) as "t" ("name", "tenant_id") 
where "t"."tenant_id" = 1        -- Hence, this predicate is NULL (effectively FALSE)
returning "area"."area_id", "area"."name", "area"."tenant_id"

[mrclrchtr]

Thank you very much for the quick reply and advice.

but create it once per tenant

If I understand it correctly, this would mean that I have to know all tenants at the start, which unfortunately will not be the case. It should be possible to add new tenants dynamically.

Regarding the INSERT: I thought (probably naively) that with the policy there would be an easy way to provide all queries with a tenant_id without having to adapt the individual implementations of the queries. So also INSERTs.

However, in my scenario it is probably the best and easiest to understand solution to provide the individual queries with tenant_id.

[lukaseder]

If I understand it correctly, this would mean that I have to know all tenants at the start, which unfortunately will not be the case. It should be possible to add new tenants dynamically.

That's not really what this means. You can pass any value to the TENANT_ID.eq(myId) predicate, including from the context. You just have to make such context available to your PolicyProvider.

The reason why you're probably thinking that you'll have to hard-code things is because you're exposing jOOQ Configuration as a Spring @Bean, which is a singleton instantiated at application startup time. But you don't have to do this. In the simplest case, just create a new Configuration for every query. Here's an example of creating a Configuration for every query:

val custom = configuration().derive(new MyPolicyProvider());
custom.select(...).from(...).fetch();

You probably don't have to do this either. There's probably a middle ground in your case as in any case.

Regarding the INSERT: I thought (probably naively) that with the policy there would be an easy way to provide all queries with a tenant_id without having to adapt the individual implementations of the queries. So also INSERTs.

jOOQ's policy implements something similar to PostgreSQL's policy. A Policy is just a combination of Table -> Condition. This information cannot possibly generate values for inserts.

However, you could do this with client side stored computed columns:
https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/codegen-database-forced-types-computed/

It's a different concept, which could work with policies, of course. I'll think about writing a blog post about this combination soon, it would probably be useful for others as well.

[mrclrchtr]

Many thanks for the tips. That looks very interesting. I'll have to take a closer look.

A blog post would be great.