Interesting. I don't have an out-of-the-box solution for MySQL. I used to work with Oracle, and we implemented shared schema multi tenancy through views and SYS_CONTEXT. It worked really well for all clients, not just Java written ones, also PL/SQL, Perl, etc. We actually had 4M "tenants" (each end user was considered a tenant), so shared schema multi tenancy was the only option.
Having said so, while it is possible to implement shared schema multi tenancy with jOOQ through VisitListeners, it is extremely difficult to get right for every single query. It's also very easy to bypass, e.g. by using plain SQL. Compared to that, a schema based solution will be much easier to use, even if it is a bit harder to migrate. A future jOOQ version will offer this as an out of the box feature. I'm currently working on these two features, which are prerequisites for better shared schema multi tenancy: - https://github.com/jOOQ/jOOQ/issues/8012 - https://github.com/jOOQ/jOOQ/issues/1969 It will then be possible to attach predicates to each table through Settings. VisitListeners are much more low level and can implement tons of features, including shared schema multi tenancy, which is why they make doing it much more difficult. I hope this helps, Lukas On Tue, Nov 13, 2018 at 11:13 AM Ian Duffy <[email protected]> wrote: > Hi Lukas, > > Thanks for your response. The database is MySQL. > > Since sending my original email I've been wondering if a database per > tenant would be easier to implement. > We could have a "master" database which contains tenant details. On boot > up, the application creates a data source for each of these tenants. > JOOQ has a resolver for the datasource to use and this gives the tenancy. > My issue with this is the complexity around managing the multiple > datasources and getting liquibase to initialise the database and to run > schema updates in the future. > > Thanks > Ian. > > On Tuesday, 13 November 2018 08:44:31 UTC, Lukas Eder wrote: >> >> Hi Ian, >> >> First off, what database are you using? Because if the database can do >> this for you, then you should probably use its features... >> >> Thanks, >> Lukas >> >> On Mon, Nov 12, 2018 at 7:38 PM <[email protected]> wrote: >> >>> Hi All, >>> >>> Does anyone have an example of a VisitListener that >>> implements shared-schema multi-tenancy? or suggestions of a different >>> approach to do shared-schema multi-tenancy. >>> >>> I've read https://github.com/jOOQ/jOOQ/issues/2682 and >>> https://groups.google.com/forum/#!msg/jooq-user/xIL7_2q1QfU/7qeJ6DqTMxAJ >>> but I do not have a clear idea of how to approach this with JOOQ. >>> >>> >>> https://stackoverflow.com/questions/17324524/row-level-security-implementation-in-jooq >>> provides an interesting approach using an Execution Listener but I'm unsure >>> how to form a generic predicate to work on all tables. >>> >>> Thanks, >>> Ian. >>> >>> -- >>> 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 [email protected]. >>> For more options, visit https://groups.google.com/d/optout. >>> >> -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
