How to configure multiple DataSources for multitenancy in Hibernate

Question

I am trying to add multitenancy to a java application using the separate schema approach as outlined in this webinar

I wanted to know how would I configure multiple data sources via spring perhaps by using properties files and get the data sources from the spring context based on tenant id.

More importantly, though I want to be able to configure my custom connection provider implementation that supports this multitenancy feature to be used by Hibernate instead of the injected ConnectionProvider that it uses by default.

How can i achieve this.

Ballet

Answer 1

Use the AbstractRoutingDataSource. See my answer located @ Multiple Entity Manager issue in Spring when using more than one datasource.

Triumphal

Answer 2

This post demonstrate how to use spring security and AbstractRoutingDataSource to build up Multi-Tenancy SaaS applications. Extend Spring Security to Protect Multi-tenant SaaS Applications

Sosna

Answer 3

The routing can be done by Hibernate via its MultiTenancyConnectionProvider which you can implement 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
        );
    }
}

Assuming each tenant uses its own dedicated DataSource, you can register the individual ConnectionProvider(s) using a utility method like this one:

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
    );
}

You can register a default tenant for admin-related tasks:

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

And for the actual tenants, you could use a method like this one:

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
    );
}

You can change the MysqlDataSource to whatever database you are using.

Then, registering the tenants is as simple as that:

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

The last thing to take into consideration is to provide the MultiTenancyConnectionProvider implementation to Hibernate via the hibernate.multi_tenant_connection_provider configuration property.

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

Defoe

Is it possible to make this approach "dynamic" if I need to provision schema as I onboard a new tenant? The concern is with addTenantConnectionProvider("asia"); I may know all the tenants upfront. – Cryptic

Anything is possible in software development – Defoe

Answer 4

If you want to do multi-tenancy by ConnectionProvider, you'll need a thread-local to provide context. See this brief run-through:

http://literatejava.com/hibernate/multi-tenancy-architecture-with-hibernate/

Slipperwort