Hibernate database catalog multitenancy

(Last Updated On: August 29, 2018)

Introduction

As I explained in this article, multitenancy is an architectural pattern which allows you to isolate customers even if they are using the same hardware or software components.

There are multiple ways you can achieve multitenancy, and in this article, we are going to see how you can implement a multitenancy architecture using the database catalog as the unit of isolation.

Catalog-based multitenancy database architecture

Catalog-based multitenancy can be achieved using any relational database system. However, since MySQL is one of the most popular RDBMS and because it does drive a distinction between a catalog and a schema, in this article we are going to use MySQL to demonstrate how we can implement a catalog-based multitenancy architecture with JPA and Hibernate.

So, if we run the show databases command in MySQL, we will get the following results:

Database
asia
europe
information_schema
performance_schema
sys

Notice the asia and europe database catalogs. These catalogs are the two tenants we are going to use in our applications. So, if a user is located in Europe, she will connect to the europe database while if the user is located in Asia, she will be redirected to the asia database catalog.

All tenants contain the same database tables. For our example, let’s assume we are using the following users and posts tables:

Domain Model

The aforementioned database tables can be mapped to the following JPA entities:

@Entity(name = "User")
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(
        strategy = GenerationType.IDENTITY
    )
    private Long id;

    private String firstName;

    private String lastName;

    @Column(name = "registered_on")
    @CreationTimestamp
    private LocalDateTime createdOn;

    //Getters and setters omitted for brevity
}
@Entity(name = "Post")
@Table(name = "posts")
public class Post {

    @Id
    @GeneratedValue(
        strategy = GenerationType.IDENTITY
    )
    private Long id;

    private String title;

    @Column(name = "created_on")
    @CreationTimestamp
    private LocalDateTime createdOn;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    private User user;

    //Getters and setters omitted for brevity
}

Hibernate multitenancy configuration

There are 3 settings we need to take care of when implementing a multitenancy architecture with Hibernate:

  • the multitenancy strategy
  • the MultiTenancyConnectionProvider implementation
  • the CurrentTenantIdentifierResolver implementation

Hibernate multitenancy strategy

The Hibernate MultiTenancyStrategy Java enumeration is used to specify what type of multitenancy architecture is being employed. For catalog-based multitenancy, we need to use the MultiTenancyStrategy.DATABASE value and pass it via the hibernate.multiTenancy configuration property:

<property name="hibernate.multiTenancy" value="DATABASE"/>

MultiTenancyConnectionProvider implementation

Now, in order for Hibernate to properly route database connection requests to the database catalog each user is associated to, we need to provide a MultiTenancyConnectionProvider implementation via the hibernate.multi_tenant_connection_provider configuration property:

properties.put(
    AvailableSettings.MULTI_TENANT_CONNECTION_PROVIDER, 
    MultiTenantConnectionProvider.INSTANCE
);

In our example, the MultiTenantConnectionProvider class looks like this:

public class MultiTenantConnectionProvider
        extends AbstractMultiTenantConnectionProvider {

    public static final MultiTenantConnectionProvider INSTANCE =
            new MultiTenantConnectionProvider();

    private final Map<String, ConnectionProvider> connectionProviderMap = 
            new HashMap<>();

    Map<String, ConnectionProvider> getConnectionProviderMap() {
        return connectionProviderMap;
    }

    @Override
    protected ConnectionProvider getAnyConnectionProvider() {
        return connectionProviderMap.get(
            TenantContext.DEFAULT_TENANT_IDENTIFIER
        );
    }

    @Override
    protected ConnectionProvider selectConnectionProvider(
            String tenantIdentifier) {
        return connectionProviderMap.get(
            tenantIdentifier
        );
    }
}

The connectionProviderMap is used to store the Hibernate ConnectionProvider associated with a given tenant identifier. The Hibernate ConnectionProvider is a factory of database connections, hence each database catalog will have its own ConnectionProvider instance.

To register a ConnectionProvider with our MultiTenantConnectionProvider we are going to use the following addTenantConnectionProvider method:

private void addTenantConnectionProvider(
        String tenantId, 
        DataSource tenantDataSource, 
        Properties properties) {
        
    DatasourceConnectionProviderImpl connectionProvider = 
        new DatasourceConnectionProviderImpl();
    connectionProvider.setDataSource(tenantDataSource);
    connectionProvider.configure(properties);
    
    MultiTenantConnectionProvider.INSTANCE
    .getConnectionProviderMap()
    .put(
        tenantId, 
        connectionProvider
    );
}

We are using the JDBC DataSource to build a Hibernate DatasourceConnectionProviderImpl which is further associated with a given tenant identifier and stored in the connectionProviderMap.

For instance, we can register a default DataSource which is not associated with any tenant like this:

addTenantConnectionProvider(
    TenantContext.DEFAULT_TENANT_IDENTIFIER, 
    defaultDataSource, 
    properties()
);

The default DataSource is going to be used by Hibernate when bootstrapping the EntityManagerFactory or whenever we don’t provide a given tenant identifier, which might be the case for the administration features of our enterprise system.

Now, to register the actual tenants, we can use the following addTenantConnectionProvider utility method:

private void addTenantConnectionProvider(
        String tenantId) {
    
    DataSourceProvider dataSourceProvider = database()
    .dataSourceProvider();

    Properties properties = properties();

    MysqlDataSource tenantDataSource = new MysqlDataSource();
    tenantDataSource.setDatabaseName(tenantId);
    tenantDataSource.setUser(dataSourceProvider.username());
    tenantDataSource.setPassword(dataSourceProvider.password());

    properties.put(
        Environment.DATASOURCE,
        dataSourceProxyType().dataSource(tenantDataSource)
    );

    addTenantConnectionProvider(
        tenantId, 
        tenantDataSource, 
        properties
    );
}

And our two tenants will be registered like this:

addTenantConnectionProvider("asia");
addTenantConnectionProvider("europe");

CurrentTenantIdentifierResolver implementation

The last thing we need to supply to Hibernate is the implementation of the CurrentTenantIdentifierResolver interface. This is going to be used to locate the tenant identifier associated with the current running Thread.

For our application, the CurrentTenantIdentifierResolver implementation looks like this:

public class TenantContext {

    public static final String DEFAULT_TENANT_IDENTIFIER = "public";

    private static final ThreadLocal<String> TENANT_IDENTIFIER = 
        new ThreadLocal<>();

    public static void setTenant(String tenantIdentifier) {
        TENANT_IDENTIFIER.set(tenantIdentifier);
    }

    public static void reset(String tenantIdentifier) {
        TENANT_IDENTIFIER.remove();
    }

    public static class TenantIdentifierResolver 
            implements CurrentTenantIdentifierResolver {

        @Override
        public String resolveCurrentTenantIdentifier() {
            String currentTenantId = TENANT_IDENTIFIER.get();
            return currentTenantId != null ? 
                currentTenantId : 
                DEFAULT_TENANT_IDENTIFIER;
        }

        @Override
        public boolean validateExistingCurrentSessions() {
            return false;
        }
    }
}

When using Spring, the TenantContext can use a RequestScope bean that provides the tenant identifier of the current Thread, which was resolved by an AOP Aspect prior to calling the Service layer.

To provide the CurrentTenantIdentifierResolver implementation to Hibernate, you need to use the hibernate.tenant_identifier_resolver configuration property:

properties.setProperty(
    AvailableSettings.MULTI_TENANT_IDENTIFIER_RESOLVER, 
    TenantContext.TenantIdentifierResolver.class.getName()
);

Testing time

Now, when running the folloiwng test case:

TenantContext.setTenant("europe");

User vlad = doInJPA(entityManager -> {

    User user = new User();
    user.setFirstName("Vlad");
    user.setLastName("Mihalcea");

    entityManager.persist(user);

    return user;
});

Hibernate is going to insert the User entity in the europe tenant:

Connect mysql@localhost on europe using TCP/IP
Query   SET character_set_results = NULL
Query   SET autocommit=1
Query   SET autocommit=0
Query   insert into users (registered_on, firstName, lastName) values ('2018-08-16 09:55:08.71', 'Vlad', 'Mihalcea')
Query   select last_insert_id()
Query   commit
Query   SET autocommit=1
Quit    

Notice the europe database identifier in the MySQL log.

Assuming other user logs in and is associated with the asia tenant:

TenantContext.setTenant("asia");

When persisting the following User entity:

doInJPA(entityManager -> {

    User user = new User();
    user.setFirstName("John");
    user.setLastName("Doe");

    entityManager.persist(user);
});

Hibernate will insert it in the asia database catalog:

Connect mysql@localhost on asia using TCP/IP
Query   SET character_set_results = NULL
Query   SET autocommit=1
Query   SET autocommit=0
Query   insert into users (registered_on, firstName, lastName) values ('2018-08-16 09:59:35.763', 'John', 'Doe')
Query   select last_insert_id()
Query   commit
Query   SET autocommit=1
Quit    

When switching back to the europe tenant and persisting a Post entity associated with the vlad User entity we have previously saved into the database:

TenantContext.setTenant("europe");

doInJPA(entityManager -> {

    Post post = new Post();
    post.setTitle("High-Performance Java Persistence");
    post.setUser(vlad);
    entityManager.persist(post);
});

Hibernate will execute the statements to the europe database catalog:

Connect mysql@localhost on europe using TCP/IP
Query   SET character_set_results = NULL
Query   SET autocommit=1
Query   SET autocommit=0
Query   insert into posts (created_on, title, user_id) values ('2018-08-16 10:02:15.408', 'High-Performance Java Persistence', 1)
Query   select last_insert_id()
Query   commit
Query   SET autocommit=1
Quit    

Cool, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Implementing a multitenancy architecture with Hibernate is fairly easy, yet very powerful. The catalog-based multitenancy strategy is very suitable for database systems that don’t draw a clear distinction between a database catalog and a schema, like MySQL or MariaDB.

Subscribe to our Newsletter

* indicates required
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence, 
  • a 10% discount coupon for my book. 
Get the most out of your persistence layer!

Advertisements

6 thoughts on “Hibernate database catalog multitenancy

  1. Hi Vlad,

    Thanks for writing about it.

    Just a question though, i am currently trying to work on Database Level Multi-tenancy, However, we need to add a Master database as primary data source and the rest of tenants will be added dynamically. I am using Spring Boot and Data Jpa (with hibernate). Any idea if we can make this approach more dynamic and add databases on run time without restarting the application.

      1. Thanks Vlad.

        I used the Connection Provider approach with tenant resolver. Now my use case got a bit more interesting. Under each Client Tenant, one client could have multiple databases, while the tenant resolver only provide one tenant id, i am looking for a approach to resolve multiple databases, with each to be used in their respective Repository (Spring data JPA).

        Do you think this can be used with same approach ? The challenge for me at the moment is, how to provide datasource dynamically to Repository class. At the moment, its resolved by packaging convention.

  2. Hello Mr. Mihalcea,

    thank you for the very informative article.

    I am into implementing dynamic multitenancy in a JEE environment (Wildfly 14 with PostgreSQL). By dynamic I mean I will create a tenant’s resources (DataSource, database schema, etc.) on runtime.

    I’ve run onto a show-stopper with Wildfly which isn’t capable of de-registering a DataSource resource without restarting itself (sigh…) Which means when a tenant should be de-registered the server should restart, which is unacceptable.

    So I’d like to ask for your opinion about the viability of a possible approach to workaround Wildfly’s shortcoming, especially with regard to connection pooling:
    a. get a DataSource from the JDBC driver itself and never register it with the application server.
    b. Use this DataSource as input to the Hibernate ConnectionProvider as your article suggests.
    c. Provide my interface for changing the connection pool characteristics since it will not be available from the server’s management interface.

    Do you think I can have my cake and eat it too? 🙂

    Your contributions all these years are much appreciated,

    Vagelis

    1. I haven’t used Wildfly or Java EE application servers for a very long time, so I cannot advise you in that particular area. However, as long as you have all DataSource objects in place, you should be able to pick the right one. Another alternative is Discriminator- based multitenancy. I’ll write about it in the following weeks.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.