Chapter 17: Multitenancy in jOOQ

Sometimes, our applications need to operate in a multitenant environment, that is, in an environment that operates on multiple tenants (different databases, different tables, or generally speaking, different instances that are logically isolated, but physically integrated). In this chapter, we will cover some common use cases of integrating jOOQ in a multitenant environment based on the following agenda:

• Connecting to a separate database per role/login via the RenderMapping API
• Connecting to a separate database per role/login via a connection switch
• Generating code for two schemas of the same vendor
• Generating code for two schemas of different vendors

Let's get started!

Technical requirements

The code for this chapter can be found on GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter17.

Connecting to a separate database per role/login via the RenderMapping API

Connecting to a separate database per role/login is a classical use case of multitenancy. Commonly, you have a pillar database (let's call it the development database) and several other databases with the same schema (let's call them the stage database and the test database). All three databases belong to the same vendor (here, MySQL) and have the same schema, but they hold data for different roles, accounts, organizations, partners, and so on of the application.

For simplicity, the development database has a single table named product. This database is used for generating jOOQ artifacts, but we want to execute the queries depending on the current role (currently logged in user) against the stage or test databases.

The key to such implementation relies on juggling with the jOOQ RenderMapping API. jOOQ allows us to specify at runtime an input schema (for instance, development) and an output schema...

Connecting to a separate database per role/login via a connection switch

Another quick solution for connecting to a separate database per role/login consists of switching to the proper connection at runtime. In order to accomplish this task, we have to suppress the jOOQ default behavior of rendering the schema/catalog name. This way, we don't risk connecting to database A but get database B rendered in front of our tables, and so on. In other words, we need unqualified names.

jOOQ allows us to turn off rendering the schema/catalog name via the withRenderSchema(false) and withRenderCatalog(false) settings. The following example connects to the database having the same name as the role of the logged in user and suppresses rendering the schema/catalog names:

Authentication auth = SecurityContextHolder
     .getContext().getAuthentication();
if (auth != null && auth.isAuthenticated()) {
   String authority = auth.getAuthorities...

Generating code for two schemas of the same vendor

Consider two schemas of the same vendor named db1 and db2. In the first schema (db1), we have a table named productline, and in the second schema (db2), we have a table named product. Our goal is to generate the jOOQ artifacts (to run the jOOQ Code Generator) for these two schemas of the same vendor (here, MySQL) and to execute queries against one or another, and even join these two tables.

Basically, as long as we don't specify any input schema, jOOQ generates code for all the schemas it can find. But since we want to instruct jOOQ to work only on the db1 and db2 schemas, we can do it as follows (here, for Maven):

<database>
 <schemata>
   <schema>
    <inputSchema>db1</inputSchema>
   </schema>
   <schema>
    <inputSchema>db2</inputSchema>
   </schema>
 </schemata...

Generating code for two schemas of different vendors

Consider two schemas of different vendors – for instance, our classicmodels schema for MySQL and PostgreSQL. Our goal is to generate the jOOQ artifacts for both schemas and execute queries against one or another.

Considering a Maven-based application, we can accomplish this task by using two <execution> entries, for the flyway-maven-plugin plugin and the jooq-codegen-maven plugin. Here is the skeleton code for jooq-codegen-maven (the complete code is available in the bundled code):

<plugin>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId> 
  <executions>
    <execution>
      <id>generate-mysql</id>
      <phase>generate-sources</phase>
      <goals>
    ...

Summary

Multitenancy is not a regular task but it is good to know that jOOQ is quite versatile and allows us to configure multiple databases/schemas in seconds. Moreover, as you just saw, the jOOQ + Spring Boot combo is a perfect match for accomplishing multitenancy tasks.

In the next chapter, we talk about jOOQ SPI.