Dynamic database connection switching and application multitenancy with database routing

by Emil Korladinov

Introduction

Projects with  requirements to dynamically switch connections at runtime  and exchange data with different datasources along with multi-tenat capabilities are very common. 

For a project I worked on I compiled a solution using Spring Framework, Spring Data Jpa, Hibernate and Aspect Oriented Programming  with Spring (SpringAOP). 

Here  I'd like to share my solution with all of you.

Implementation

Basic idea is to wrap unit of work into a transaction and to exexute it against preconfigured databases(datasources). Transactions are demarcated using standard Spring annotation-based transaction management. Since annotations are used to mark code which will use database routing a new annotation is introduced: @DatabaseRouting which is meta annotated with Spring @Transactional. Information for the currently used tenant id is kept in a ThreadLocal variable on a per-thread basis. On the database side a special DataSource is used - DatabaseRoutingDatasource which extends Spring's AbstractRoutingDataSource. 

Working horse of the solution is aspect with an @Around pointcut. Second annotation, applied on method parameter is used to convey information for chosen datasource down to Spring transaction.

Annotations

Two annotations are used:

@DatabaseRouting is method level annotation marking method which uses database routing to dynamically select database connection and exchange data. Methods that have this anotation are wrapped in transaction. This annotation is matched by an around aspect which sets current connection discriminator (tenantId), proceeds with methd execution and resets connection to default.

This annotation is defined as follows:

@TenantId is parameter level annotation, used as a vehicle for required connection discriminator (tenantId) to the processing aspect. Parameter with this annotation is not used for business logic in the method body, but removing it from the method signature with break processing and method will be executed on the default connection.

ThreadLocals

TenantIdHolder is standard Java ThreadLocal containing current value of tenantId.

Datasources

RoutingDataSource Extends AbstractRoutingDataSource from spring, overriding method for key lookup (tenantId), linking it with TenantIdHolder.

Aspects

DatabaseRoutingAspect

This is the working horse of the solution. Purpose of this aspect is twofold - First to extract tenantId from the method arguments through annotated parameter on the method signature; and second execute business logic of the method and reset connection to default.

  Sources can be found on GitHub