Policies for Database-Wide Tenant-Check (Configuration-Setup)

[Bernd Huber]

Hello all,

i would like to use the Policies Feature in Jooq-Express, 

and tested it in the Trial-Version.

It is working fine, 

but i found some small things i want to ask about.

I have a "ConfigurationFactory" (in my quarkus-app) that should create the Jooq-Configuration only once (for performance-reasons).

I used to do it like this:

public class Database1ConfigurationFactory {

    private Configuration configuration;
    public Configuration getConfiguration() {
        if (configuration == null) {
            ...
            configuration = new DefaultConfiguration()
            ...
         }
     }
}

But now, when i also add the Policies for the Tenant Check, i need to pass the tenantId to the getConfiguration() function, so now the function suddendly needs to return unique Configuration instances, for each tenantId.

I fear that this will negatively affect my performance.

It would look like this:

public class Database1ConfigurationFactory {

    private Configuration configuration;
    public Configuration getConfiguration(Integer tenantId) {
        ...
        configuration = new DefaultConfiguration()
        configuration.set(new DefaultPolicyProvider()
             .append(CUSTOMER, CUSTOMER.CUSTOMER_ID.eq(tenantId))
             ...
         );
         ...
         return configuration
     }
}

am i implementing this correctly (as intended) or is there another way to apply the policies afterwards (maybe in a listened, like the executionlistener or something similar?)

greetings, Bernd

[Lukas Eder]

What performance impact are you fearing, specifically? I mean, the Configuration hosts the reflection cache for those into(Class<?>) calls, which would then be repeated per tenant. But other than that, what specific measurements have you taken to think that a Configuration is costly (per tenant)? How many tenants do you have?

A future version of jOOQ will look into improving the PolicyProvider SPI to be context aware, see:

https://github.com/jOOQ/jOOQ/issues/16311

https://groups.google.com/g/jooq-user/c/TT9PbfQlG8w/m/o5VE8vyGAQAJ?pli=1

In the meantime, you could pass tenant IDs to your policy provider using ThreadLocals, for example, or some other way to set variables like a tenant into some "scope".

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9dfec149-8533-4280-bf09-86a2a38b72e2n%40googlegroups.com.

[Bernd Huber]

I think it should be only very small (neglectable) performance impacts.

So im totally fine with how i did it in the example, but just want to mention it.

i guess i could also hold a hashmap with key=tenantId -> value=configuration in my "ConfigurationFactory",
to get them from this hashmap or create them if not existing. But i think i wait till the performance needs to be optimized,

because currently everything is very fast.

The tenantes we will have should be well below 5000 Tenants (most likely they will be more like 500 max)

so im totally fine with how its now, and good to know there will be a PolicyProvider SPI in the future.

Greets, Bernd