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

The article was originally published on Systango.

--

--

--

London’s leading digital agency Systango offers full service from strategy, scoping to launch & maintenance, innovative campaigns to enterprise infrastructure.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Lead Enrichment using Elasticsearch

Fairwinds Polaris 1.0 — Best Practices for Kubernetes Workloads

Cardano : Benefits of using Haskell (the non-tech summary)

Connecting a GoDaddy Domain to Your Site

How to handle the Dynamic 365 Data Export Service (DES) deprecation and end of life?

What is Python Used For? Why Startups Take Advantage of Python?

GSoC 2021 with SCoRe Lab — Week 10

Bringing flexibility to document requirements with dates and valid period

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
SYSTANGO

SYSTANGO

London’s leading digital agency Systango offers full service from strategy, scoping to launch & maintenance, innovative campaigns to enterprise infrastructure.

More from Medium

Enterprise Application Architect- Part 1

Cloud Development Overview for Non-Cloud Developers

WSO2 API Manager 4 deployment options explained

Testing approaches and Cloud-native microservices