Hibernate database schema 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 schema as the unit of isolation.

Schema-based multitenancy database architecture

Schema-based multitenancy can be achieved using any relational database system which drives a distinction between a catalog and a schema. In this article, we are going to use PostgreSQL to demonstrate how we can implement a schema-based multitenancy architecture with JPA and Hibernate.

If we are running the following PostgreSQL query in the current database catalog:

select nspname as "Schema"
from pg_catalog.pg_namespace
where nspname not like 'pg_%'; 

PostgreSQL will list the following database schemas:

Database
asia
europe
information_schema
performance_schema
sys

Notice the asia and europe database schemas. These schemas 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 schema while if the user is located in Asia, she will be redirected to the asia database schema.

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
    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
    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 schema-based multitenancy, we need to use the MultiTenancyStrategy.SCHEMA value and pass it via the hibernate.multiTenancy configuration property:

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

MultiTenancyConnectionProvider implementation

Now, in order for Hibernate to properly route database connection requests to the database schema 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 schema 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) {

    PGSimpleDataSource defaultDataSource = (PGSimpleDataSource) database()
    .dataSourceProvider()
    .dataSource();

    Properties properties = properties();

    PGSimpleDataSource tenantDataSource = new PGSimpleDataSource();
    tenantDataSource.setDatabaseName(defaultDataSource.getDatabaseName());
    tenantDataSource.setCurrentSchema(tenantId);
    tenantDataSource.setServerName(defaultDataSource.getServerName());
    tenantDataSource.setUser(defaultDataSource.getUser());
    tenantDataSource.setPassword(defaultDataSource.getPassword());

    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 -> {

    LOGGER.info(
        "Current schema: {}", 
        entityManager.createNativeQuery(
            "select current_schema()")
        .getSingleResult()
    );

    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:

INFO  [main]: SchemaMultitenancyTest - Current schema: europe

LOG:  execute <unnamed>: BEGIN

LOG:  execute <unnamed>: select nextval ('hibernate_sequence')

LOG:  execute <unnamed>: 
insert into users (
    registered_on, 
    firstName, 
    lastName, 
    id
) 
values (
    $1, 
    $2, 
    $3, 
    $4
)

DETAIL:  parameters: 
    $1 = '2018-08-29 09:38:13.042', 
    $2 = 'Vlad', 
    $3 = 'Mihalcea', 
    $4 = '1'

LOG:  execute S_1: COMMIT

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 -> {

    LOGGER.info(
        "Current schema: {}", 
        entityManager.createNativeQuery(
            "select current_schema()")
        .getSingleResult()
    );

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

    entityManager.persist(user);
});

Hibernate will insert it in the asia database schema:

INFO  [main]: SchemaMultitenancyTest - Current schema: asia

LOG:  execute <unnamed>: BEGIN

LOG:  execute <unnamed>: select nextval ('hibernate_sequence')

LOG:  execute <unnamed>: 
insert into users (
    registered_on, 
    firstName, 
    lastName, 
    id
) 
values (
    $1, 
    $2, 
    $3, 
    $4
)

DETAIL:  parameters: 
    $1 = '2018-08-29 09:39:52.448', 
    $2 = 'John', 
    $3 = 'Doe', 
    $4 = '1'

LOG:  execute S_1: COMMIT    

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 -> {

    LOGGER.info(
        "Current schema: {}", 
        entityManager.createNativeQuery(
            "select current_schema()")
        .getSingleResult()
    );

    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 schema:

INFO  [main]: SchemaMultitenancyTest - Current schema: europe

LOG:  execute <unnamed>: BEGIN

LOG:  execute <unnamed>: select nextval ('hibernate_sequence')

LOG:  execute <unnamed>: 
insert into users (
    registered_on, 
    firstName, 
    lastName, 
    id
) 
values (
    $1, 
    $2, 
    $3, 
    $4
)

DETAIL:  parameters: 
    $1 = '2018-08-29 09:43:00.683', 
    $2 = 'High-Performance Java Persistence', 
    $3 = '1', 
    $4 = '2'

LOG:  execute S_1: COMMIT

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 schema-based multitenancy strategy is very suitable for database systems that draw a clear distinction between a database catalog and a schema, like PostgreSQL.

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

4 thoughts on “Hibernate database schema multitenancy

  1. What about connection pooling in multitenancy? Assuming i have catalog strategy or schema strategy. With let’s say a pool size of 10 and 3000 tenants and im reaching the connection limit on SQL Server for example. Any thoughts on this topic?

  2. Has anyone implemented schema based multitenancy with SQL Server? I have tried it with no success. I could not find anything working on Google or Github i this regard. On SO there is one question with no real answer: https://stackoverflow.com/questions/46173352/schema-based-multitenancy-with-sqlserver-and-hibernate and i also found the Microsoft SQL Server JDBC driver does not really implement the setSchema method: https://github.com/Microsoft/mssql-jdbc/blob/dev/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerConnection.java#L5164 .

    1. Use different users which have different default schemas and pass the user to the MultiTenantConnectionProvider.

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.