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 (for instance, stage), and, in queries, it will render the output schema. The climax of the code relies on these settings, as you can see here (the authentication is specific to the Spring Security API):
Authentication auth = SecurityContextHolder
.getContext().getAuthentication();
String authority = auth.getAuthorities().iterator()
.next().getAuthority();
String database = authority.substring(5).toLowerCase();
ctx.configuration().derive(new Settings()
.withRenderMapping(new RenderMapping()
.withSchemata(
new MappedSchema().withInput("development").withOutput(database)))).dsl()
.insertInto(PRODUCT, PRODUCT.PRODUCT_NAME,
PRODUCT.QUANTITY_IN_STOCK)
.values("Product", 100).execute();
Depending on the role of the currently authenticated user, jOOQ renders the expected database name (for instance, `stage`.`product` or `test`.`product`). Basically, each user has a role (for instance, ROLE_STAGE or ROLE_TEST; for simplicity, a user has a single role), and we extract the output database name by removing ROLE_ and lowercase the remaining text; by convention, the extracted text represents the name of the database as well. Of course, you can use the username, organization name, or whatever convention makes sense in your case.
You can test this example in the application named MT for MySQL.
The withInput() method takes the complete name of the input schema. If you want to match the name of the input schema against a regular expression, then instead of withInput(), use withInputExpression(Pattern.compile("reg_exp")) (for instance, ("development_(.*)")).
If you are in a database that supports catalogs (for instance, SQL Server), then simply use MappedCatalog() and withCatalogs(), as in the following example:
String catalog = …;
Settings settings = new Settings()
.withRenderMapping(new RenderMapping()
.withCatalogs(new MappedCatalog()
.withInput("development").withOutput(catalog))
.withSchemata(…); // optional, if you need schema as well
If you don't need a runtime schema and instead need to hardwire mappings at code generation time (jOOQ will always render at runtime, conforming to these settings), then, for Maven, use the following:
<database>
<schemata>
<schema>
<inputSchema>…</inputSchema>
<outputSchema>…</outputSchema>
</schema>
</schemata>
</database>
For Gradle, use the following:
database { schemata { schema {inputSchema = '…'
outputSchema = '…'
}
}
}
Use the following for programmatic:
new org.jooq.meta.jaxb.Configuration()
.withGenerator(new Generator()
.withDatabase(new Database()
.withSchemata(
new SchemaMappingType()
.withInputSchema("...") .withOutputSchema("..."))
)
)
You can see such an example in MTM for MySQL. As you'll see, all accounts/roles act against the database that was hardwired at code generation time (the stage database).
If you are using a database that supports catalogs (for instance, SQL Server), then simply rely on <catalogs>, <catalog>, <inputCatalog>, and <outputCatalog>. For Maven, use the following:
<database>
<catalogs>
<catalog>
<inputCatalog>…</inputCatalog>
<outputCatalog>…</outputCatalog>
<!-- Optionally, if you need schema mapping -->
<schemata>
</schemata>
</catalog>
</catalogs>
</database>
For Gradle, use the following:
database { catalogs { catalog {inputCatalog = '…'
outputCatalog = '…'
// Optionally, if you need schema mapping
schemata {}}
}
}
For programmatic, use the following:
new org.jooq.meta.jaxb.Configuration()
.withGenerator(new Generator()
.withDatabase(new Database()
.withCatalogs(
new CatalogMappingType()
.withInputCatalog("...") .withOutputCatalog("...")// Optionally, if you need schema mapping
.withSchemata()
)
)
)
So far, the development database has a single table named product. This table has the same name in the stage and test databases but let's assume that we decide to call it product_dev in the development database, product_stage in the stage database, and product_test in the test database. In this case, even if jOOQ renders the database name per role correctly, it doesn't render the table's names correctly. Fortunately, jOOQ allows us to configure this aspect via withTables() and MappedTable(), as follows:
ctx.configuration().derive(new Settings()
.withRenderMapping(new RenderMapping()
.withSchemata(
new MappedSchema().withInput("development").withOutput(database)
.withTables(
new MappedTable().withInput("product_dev") .withOutput("product_" + database))))).dsl().insertInto(PRODUCT_DEV, PRODUCT_DEV.PRODUCT_NAME,
PRODUCT_DEV.QUANTITY_IN_STOCK)
.values("Product", 100).execute();
You can check out this example in the application named MTT for MySQL.
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()
.iterator().next().getAuthority();
String database = authority.substring(5).toLowerCase();
DSL.using(
"jdbc:mysql://localhost:3306/" + database,
"root", "root")
.configuration().derive(new Settings()
.withRenderCatalog(Boolean.FALSE)
.withRenderSchema(Boolean.FALSE))
.dsl()
.insertInto(PRODUCT, PRODUCT.PRODUCT_NAME,
PRODUCT.QUANTITY_IN_STOCK)
.values("Product", 100).execute();
}
You can check out this example in the application named MTC for MySQL.
Alternatively, we can instruct jOOQ to remove any schema references from the generated code via the outputSchemaToDefault flag. For Maven, use the following:
<outputSchemaToDefault>true</outputSchemaToDefault>
For Gradle, use the following:
outputSchemaToDefault = true
Since there are no more schema references in the generated code, the generated classes can run on all your schemas:
String database = …;
DSL.using(
"jdbc:mysql://localhost:3306/" + database,
"root", "root")
.insertInto(PRODUCT, PRODUCT.PRODUCT_NAME,
PRODUCT.QUANTITY_IN_STOCK)
.values("Product", 100).execute();
You can test this example in the application named MTCO for MySQL.
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>
</database>
I am sure you have enough experience now to intuit how to write this for Gradle or programmatic, so I'll skip those examples.
Once we run the jOOQ Code Generator, we are ready to execute queries, as follows:
ctx.select().from(DB1.PRODUCTLINE).fetch();
ctx.select().from(DB2.PRODUCT).fetch();
Or, here is a join between PRODUCTLINE and PRODUCT:
ctx.select(DB1.PRODUCTLINE.PRODUCT_LINE,
DB2.PRODUCT.PRODUCT_ID, DB2.PRODUCT.PRODUCT_NAME,
DB2.PRODUCT.QUANTITY_IN_STOCK)
.from(DB1.PRODUCTLINE)
.join(DB2.PRODUCT)
.on(DB1.PRODUCTLINE.PRODUCT_LINE
.eq(DB2.PRODUCT.PRODUCT_LINE))
.fetch();
DB1 and DB2 were statically imported, as follows:
import static jooq.generated.db1.Db1.DB1;
import static jooq.generated.db2.Db2.DB2;
The complete example is available in the application named MTJ for MySQL.
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>
<goal>generate</goal>
</goals>
<configuration xmlns="... jooq-codegen-3.16.0.xsd">
... <!-- MySQL schema configuration -->
</configuration>
</execution>
<execution>
<id>generate-postgresql</id>
<phase>generate-sources</phase>
<goals>
<goal>generate</goal>
</goals>
<configuration xmlns="...jooq-codegen-3.16.0.xsd">
... <!-- PostgreSQL schema configuration -->
</configuration>
</execution>
</executions>
</plugin>
Next, jOOQ generates artifacts for both vendors and we can switch between connections and tables, as follows:
DSL.using(
"jdbc:mysql://localhost:3306/classicmodels",
"root", "root")
.select().from(mysql.jooq.generated.tables.Product.PRODUCT)
.fetch();
DSL.using(
"jdbc:postgresql://localhost:5432/classicmodels",
"postgres", "root")
.select().from(
postgresql.jooq.generated.tables.Product.PRODUCT)
.fetch();
Or, considering that we have already programmatically configured our DataSource objects, we can configure two DSLContext as well (the complete code is available in the bundled code):
@Bean(name="mysqlDSLContext")
public DSLContext mysqlDSLContext(@Qualifier("configMySql") DataSourceProperties properties) {return DSL.using(
properties.getUrl(), properties.getUsername(),
properties.getPassword());
}
@Bean(name="postgresqlDSLContext")
public DSLContext postgresqlDSLContext(
@Qualifier("configPostgreSql") DataSourceProperties properties) {return DSL.using(
properties.getUrl(), properties.getUsername(),
properties.getPassword());
}
You can also inject these two DSLContext and use the one you want:
private final DSLContext mysqlCtx;
private final DSLContext postgresqlCtx;
public ClassicModelsRepository(
@Qualifier("mysqlDSLContext") DSLContext mysqlCtx, @Qualifier("postgresqlDSLContext") DSLContext postgresqlCtx){this.mysqlCtx = mysqlCtx;
this.postgresqlCtx = postgresqlCtx;
}
…
mysqlCtx.select().from(
mysql.jooq.generated.tables.Product.PRODUCT).fetch();
postgresqlCtx.select().from(
postgresql.jooq.generated.tables.Product.PRODUCT).fetch();
The complete code is named MT2DB. If you want to generate the artifacts for only one vendor depending on the active profile, then you'll love the MP application.
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.