SaaS Support Multi-Tenancy Model With Azure And Hibernate
Software as a Service (SaaS) supported multi-tenancy model is nowadays the preferred distribution architecture with the widespread adoption of the cloud. And one of the challenges that come along, is handling multi-tenancy. This case study takes you through the challenges we faced and how we tackled them.
Building a SaaS application involves handling multiple tenants, from a single instance of your service. For example, slack or JIRA. Different clients create their instance on these services.
The challenge of handling multiple clients is keeping the client’s data isolated from each other. The simplest way we can think of is having a separate database for each client. And this seemed like a straightforward solution. But when we started detailing this solution we realised the challenges it brings like:
- Spinning up a new tenant database on the fly for the new client
- A way to manage these databases and store their metadata
- A dynamic way to identify the tenant and connect to the database during the API call
- Managing the database connection pools and migrations
Let’s see how we solved each one of these problems with a different key.
SaaS support multi-tenancy model with-
- Microsoft Azure, a cloud provider to handle the database
- Hibernate library to handle the connection management
- Flyway library to handle migration
Database Management – Microsoft Azure SQL Server
When we talk about SQL based relational databases, there are a lot of restrictions involved in their creation. They are very opinionated and controlled database management systems. They follow a sequence of steps right from their creation to schema initialisation. Based on our research with different cloud providers, Microsoft Azure stood out for us.
The factors which lead to Azure’s selection were:
- On the fly database creation
- Database creation and management from Java code
- Flexible costing with elastic pool
And these were the things we were looking for. It simplified a number of things for us. Let’s dive a little into the code to understand what we did.
Database Creation
Creating a new database requires procuring a database server from the Azure portal. Once that is done, you just connect to the server, and create as many databases as you need, from the code.
The server also allows us to use the elastic pool, a service to improve server resource utilisation among multiple databases. Read more about elastic pools here.
Let’s take a look at some of the components of database management:
- ShardMapManager database: This is an Azure SQL server component which manages different tenant database with the help of a unique ID associated with each database.
- Shard manager scripts: Shard map manager related database scripts, used while creation of shard map manager.
- Global database: This acts as our central database, keeping the metadata about the clients and their databases.
- Tenant database: A client database.
- Schema script: This is the database schema initialisation script.
These components allow us to create and connect to tenant databases. Let’s see what we will need in our Java code.
Maven dependency
<dependency>
<groupId>com.microsoft.azure</groupId>
<artifactId>elastic-db-tools</artifactId>
<version>1.0.0</version>
</dependency>
Once you have the dependency in your classpath, you’ll get access to shard management APIs provided by azure.
Creating the database
Creating the database involves:
- Initialising the shard map manager (which involves creating one if not present)
- Create the database
- Execute schema initialisation scripts
- Create a mapping of this database in shard map manager
Following is the code snippet for above steps:
public Status provisionNewTenant(String databaseName, Boolean isGlobal, int databaseKey) {
ShardMapManager shardMapManager;
String shardMapManagerServerName = ”sharMapMgr”;
String shardMapManagerDatabaseName = “dbName”;
String shardMapManagerConnectionString = getConnectionString(shardMapManagerServerName, shardMapManagerDatabaseName);
//create shard map manager database
if (shardMapManager == null) {
shardMapManagerConnectionString = createDatabase(shardMapManagerServerName, shardMapManagerDatabaseName);
shardMapManager = createOrGetShardMapManager(shardMapManagerConnectionString);
}
//create requested database
sqlDatabaseUtil.createDatabase(shardMapManagerServerName, databaseName);
//execute schema initialisation script based on database type, global or tenant
if (isGlobal) {
sqlDatabaseUtil.executeSqlScript(shardMapManagerServerName, databaseName, properties.getGlobalDbInitializerScript());
} else {
sqlDatabaseUtil.executeSqlScript(shardMapManagerServerName, databaseName, properties.getTenantDbInitializerScript());
//create shard map pointing for this database
shardMap.createPointMapping(databaseKey, systangoShard);
return Status.TENANT_CREATED;
}
Database creation and schema initialisation
The important part of above code snippet is creating the database, the createDatabase() method. Let’s take a look at the method here:
public String createDatabase(String server,
String dbName) {
Connection conn = null;
String connectionString = getConnectionString(server, “master”);
String dbConnectionString = “”;
try {
conn = DriverManager.getConnection(connectionString);
String query = “SELECT CAST(SERVERPROPERTY(‘EngineEdition’) AS NVARCHAR(128))”;
try (Statement stmt = conn.createStatement()) {
ResultSet rs = stmt.executeQuery(query);
if (rs.next()) {
if (rs.getInt(1) == 5) {
query = String.format(“CREATE DATABASE %1$s (EDITION = ‘%2$s’)”, bracketEscapeName(dbName), properties.getDbEdition());
stmt.executeUpdate(query);
dbConnectionString = getConnectionString(server, dbName);
while (!isDatabaseOnline(DriverManager.getConnection(dbConnectionString), dbName)) {
TimeUnit.SECONDS.sleep(5);
} else {
query = String.format(“CREATE DATABASE %1$s”, bracketEscapeName(dbName));
stmt.executeUpdate(query);
dbConnectionString = getConnectionString(server, dbName)
} catch (SQLException ex) {
printExceptionToConsole(ex);
} finally {
connFinally(conn);
return dbConnectionString;
One important parameter to note here is database edition. This is another Azure parameter which decides how powerful your database would be.
Database Connection Management – Hibernate Multi-Tenancy
Another crucial aspect we had to take care of was connecting to the appropriate database on the fly. Our SaaS supported multi tenancy model is exposed to all of our clients. This meant that we had to identify the client and connect to their database on the fly. But at the same time, we had to connect to the global database as well which had the metadata of all the clients.
There are two aspects to this: identifying the client and connecting to their database.
Identifying the client
We used request headers to identify the client. We made our clients send a particular header in all the requests. This header had the unique ID of each client. Once we received the header in one of our filters, we would save the tenant ID in a Thread Local variable. This allowed us to pass the tenant ID to hibernate a multi-tenancy provider.
Making the connection
The second aspect is to make a connection to the client database. To manage the multi-tenant connections, we used hibernate’s multi-tenant connection provider. Hibernate provides this feature by injecting the tenant connections details.
To connect to the global database, we created a separate session factory with global connection details and injected the same wherever needed.
This way we were able to connect to two databases in one API, the global one and any one of the tenant databases.
Multi-tenancy implementation
Hibernate multi-tenancy provider needs two interface implementations and an ‘entityManagerFactory’ bean to inject these implementations.
Interface – CurrentTenantIdentifierResolver
This interface contains the implementation to fetch the tenant related information.
@Override
public String resolveCurrentTenantIdentifier() {
String tenantId = null;
if (ThreadLocalUtil.getTenantId() != null) {
tenantId = ThreadLocalUtil.getTenantId();
} else {
tenantId = globalTenantId;
return tenantId;
Interface – MultiTenantConnectionProvider
This interface provides the implementation to get a connection object for a particular tenant.
@Override
public Connection getConnection(String tenantId) throws SQLException {
Connection conn = null;
try {
conn = sqlDatabaseQueryManager.getConnection(Integer.parseInt(tenantId));
} catch (Exception e) {
logger.error(“error: “, e)
return conn;
entityManagerFactory bean
This bean injects hibernate properties with multi-tenancy providers.
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
LocalContainerEntityManagerFactoryBean emfBean = new LocalContainerEntityManagerFactoryBean();
emfBean.setPackagesToScan(“com.your.domain.package”);
emfBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
Map<String, Object> jpaProperties = new HashMap<>();
jpaProperties.put(Environment.MULTI_TENANT,
MultiTenancyStrategy.DATABASE);
jpaProperties.put(Environment.MULTI_TENANT_CONNECTION_PROVIDER,
multiTenantConnectionProvider);
jpaProperties.put(Environment.MULTI_TENANT_IDENTIFIER_RESOLVER,
currentTenantIdentifierResolver);
jpaProperties.put(Environment.DIALECT,
properties.getHibernateDialect());
jpaProperties.put(Environment.DRIVER,
properties.getDriverClass());
emfBean.setJpaPropertyMap(jpaProperties);
return emfBean;
This is how you can manage connections to multiple tenants.
Database Migrations – Flyway
As we progress with our SaaS supported multi tenancy model application, we keep adding new features and update existing ones. This impacts not just the code, but also the database schema. Database migration is one way to manage schema versioning. With multiple databases in the picture, things start to get complex.
We needed a way to maintain different schema definitions for different databases. In our application, we maintained only two definitions, one for the global database and another for all the tenants. This kept the things in control for us.
We used the SaaS supported multi tenancy model with a flyway library to handle the migrations. It’s seamless with spring boot, but as our use case was a custom one, we had to implement FlywayMigrationStrategy and provide our custom implementation.
Maven dependency
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
<version>5.0.7</version>
</dependency>
Migrating multiple tenants
public class MultiTenantMigrationStrategy implements FlywayMigrationStrategy{
@Override
public void migrate(Flyway flyway) {
flyway.migrate();
flyway.setLocations(properties.getFlywayTenantLocations());
List<ClientDetails> clientDetailsList = clientDetails.findAll();
if (clientDetailsList == null) {
return;
for (ClientDetails clientDetails : clientDetailsList) {
String connectionString = sqlDatabaseManager.getConnectionString(properties.getDbServerURL(), clientDetails.getTenantName());
//set the migration definitions folder location
flyway.setDataSource(connectionString, dbUserId(), dbPassword());
flyway.migrate();
}
Along with the implementation, you also need to specify the location where migration files are kept. This can either be done using properties file or from the code itself. In our case, we used both. The properties one for global database and custom path for tenant database files.
Building a SaaS supported multi-tenancy application will lead you into a variety of situations and handling a multi-tenant database could be one of them. With this case study, we hope that some of your questions were answered.
Systango, one of the prime product engineering and development companies in the UK that have framed a number of applications on SaaS supported multi-tenancy model. Do you want to create your next web application on multi-tenant architecture or on any other such viral technology? Let’s get into touch by scheduling a call.
We are here to hear from you!
