Multi-tenancy indicates an approach in software development where a single application simultaneously serves multiple clients. These clients are also called tenants. This approach is very common in Software as a Service (SaaS) solutions.

The challenge of such systems is the isolation of the data between the tenants: each client should only see its information and mustn’t have access to other client’s information.

Multi-tenant approaches

There are 3 main approaches to isolate data between clients:

• separate database
• separate schema
• partitioned data

Let’s take a look at them even if you know the idea right from the name.

Separate database

In this case each tenant’s data is kept in a physically separated database. This ensures that the application can access only this database so the integrity is provided.

An approach for this solution is to set up a database connection pool for each tenant and to select the pool based on the tenant’s identifier associated with the current logged in user.

Separate schema

In this approach we only have a single database but the client’s data are stored in separate database schemas.

For this approach there are mainly two solutions how you can achieve this:

The first solution is just like the previous approach: connection pools are created for each schema and the pool is chosen based on the current user’s tenant identifier.

The second solution would point to the database itself with a default schema and alter the database schema per connection with the SET SCHEMA SQL command (or something similar if the database allows it). In this case a single connection pool would serve all tenants but before using this connection it would be altered to the right schema using the user’s tenant identifier.

Partitioned data

With this approach all data is kept in a single database schema. The data between tenants is partitioned by a discriminator value which can range from a single column to a complex SQL formula.

With this approach there would be again one connection pool but this time every SQL query has to be altered to reference the discriminator value of the tenant.

Multi-tenancy and Hibernate

To set-up a multi-tenant connection with Hibernate you have to start at the Session:

Session session = sessionFactory.withOptions().tenantIdentifier( yourTenantIdentifier ).openSession();

As you can see, when opening the session you provide your tenant identifier. In this case you have to additionally provide a MultiTenancyStrategy to let Hibernate know which strategy to look for in the database (or create if you enable schema management with Hibernate):

• NONE: This is the default, it throws an exception if you open your session with tenantIdentifier
• SCHEMA
• DATABASE
• DISCRIMINATOR: Hibernate plans to support this strategy starting with version 5.

If you do not provide a tenant identifier for other strategies than NONE, you get an exception. When using a strategy other than NONE you have to specify a MultiTenantConnectionProvider.

These strategies can be set with the hibernate.multiTenancy setting in your Hibernate configuration (in the example’s case it is the hibernate.cfg.xml file).

MultiTenantConnectionProvider

To use one of the two available multi-tenancy providers we need to configure the MultiTenantConnectionProvider. In this particular case this means we have to implement it ourselves.

In the example application to this article I’ve added the very basic implementations of the interface.

Yes, there are two implementations because the SCHEMA and DATABASE strategies need separate handling.

The MultiTenantConnectionProviderImpl class is used for the DATABASE strategy.

The MultiTenantConnectionProviderWithDbPoolImpl class is used for the SCHEMA strategy.

The only problem with the DatabasePool implementation is that it uses H2 database and H2 does not know the USE SQL command so we encounter an exception when we try to run the application.

Note on tenant ids

If you are using a database use a String as the tenant ID (unless you use the DISCRIMINATOR strategy) because schemas have to have a textual name and if you provide a number (even as a String in the CurrentTenantIdentifierResolverImpl) then you get a database exception.

Schema update

When you are using Multi-tenancy with your Database, Hibernate schema update is no option. This is because it would only update the default schema when starting the application — and if you switch to another one it might not be updated.

This means you have to manually create the schemas you want to use and add the required tables to those schemas. Alternatively a migration tool like Liquibase or FlyWay could help.

The script below shows how to create a schema and the BOOKS table in it.

create schema if not exists example;
use example;

 create table Books (
 id bigint generated by default as identity (start with 1),
 title varchar(255),
 isbn varchar(255),
 authors varchar(255),
 published_date date(8),
 primary key (id)
 );

Conclusion

We have seen it is not impossible to have multi-tenancy in our application however currently it requires some coding to set up the right connection pools and mappings between tenants and you have to take care of your databases, schemas and tables.