How to create SaaS style multi tenant web app with Spring Boot 2 ,Spring Security 5 and MySQL

  Reading Time:

Prerequisites

  • Java 8
  • Spring Boot 2
  • MySQL

Overview

We are going to see how to build JPA Multi Tenancy in Spring boot 2 and use Flyway for DB migration

We are going to have create a database in MySQL for each tenant so for Tenant 1 it would be DB tenant_1_db and for Tenant 2 it would be tenant_2_db

We will connect to the right database during runtime based on the information stored in Tenant Context  ThreadLocal variable

We will be resolving the database during runtime and if a new tenant signs up then we create a new database on the fly in MySQL

Now lets see how to create the application

 

Creating master database

Master database is the place where we store the tenant to database mapping information and user to tenant mapping information . When a user login in to the application we check for tenant information in the master database

Master database has two tables

  1. Tenant
  2. User_Tenant

We will be using flyway to create these tables

The SQL to create the tables are given below


CREATE TABLE IF NOT EXISTS `tenant` (
  `tenant_id` bigint(20) NOT NULL,
  `database_name` varchar(255) DEFAULT NULL,
  `enabled` bit(1) DEFAULT NULL,
  `tenant_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`tenant_id`),
  UNIQUE KEY `UK_database_name` (`database_name`),
  UNIQUE KEY `UK_tenant_name` (`tenant_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `user_tenant` (
  `user_name` varchar(255) NOT NULL,
  `enabled` bit(1) DEFAULT NULL,
  `tenant_id` bigint(20) NOT NULL,
  PRIMARY KEY (`user_name`),
  KEY `FK_tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



 

Next we add the properties to application.properties file to connect to Master database

database.host = localhost
database.port =  3306
database.username = root
database.password = root
database.driverClass = com.mysql.cj.jdbc.Driver

# Master tenant information stored under this database
database.name = master

# Database connection information 
spring.master.datasource.jdbcUrl = jdbc:mysql://${database.host}:${database.port}/${database.name}?createDatabaseIfNotExist=true&verifyServerCertificate=false&useSSL=false&requireSSL=false&useUnicode=yes&characterEncoding=UTF-8
spring.master.datasource.driverClassName = ${database.driverClass}
spring.master.datasource.username = ${database.username}
spring.master.datasource.password = ${database.password}
spring.master.datasource.connectionTimeout=60000
spring.master.datasource.idleTimeout=600000
spring.master.datasource.maxLifetime=1800000
spring.master.datasource.maximumPoolSize=5
spring.master.datasource.minimumIdle=2
spring.master.datasource.poolName=master-db-pool

 

Create the master database config class to create the Master datasource

import javax.sql.DataSource;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

@Configuration
public class MasterDatabaseConfig {

	@Bean("masterConfig")
	@ConfigurationProperties(prefix = "spring.master.datasource")
	public HikariConfig hikariConfig() {
		return new HikariConfig();
	}

	@Bean(name = "masterDataSource")
	public DataSource masterDataSource() {
		return new HikariDataSource(hikariConfig());
	}

}

 

Configure flyway to migrate Master datasource . All migration files are placed under 'classpath:db/migration/master'

import javax.sql.DataSource;
import org.flywaydb.core.Flyway;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MasterDatabaseMigrationConfig {

	@Autowired
	@Qualifier("masterDataSource")
	public DataSource masterDataSource;

	@Bean
	public Flyway flyway(DataSource theDataSource) {
		Flyway flyway = Flyway.configure().dataSource(masterDataSource).locations("classpath:db/migration/master").baselineOnMigrate(true)
				.outOfOrder(true).load();
		flyway.repair();
		flyway.migrate();
		return flyway;
	}
}

 

Next we need to create the TenantResolver to find the tenant id for current user or current context . We don't use JPA to query Master database instead we rely on Spring JdbcTemplate to query the Master DB . The JPA is used only to query tenant database .

The reason is because JPA initialization process needs to be aware of the tenant datasource and to know that we need to resolve the tenant so it will become a cyclic call in spring so to avoid it we use JdbcTemplate that directly connects via Master datasource.

import javax.annotation.PostConstruct;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class TenantResolver {

	private JdbcTemplate jdbcTemplate;

	@Autowired
	@Qualifier("masterDataSource")
	public DataSource masterDataSource;

	@PostConstruct
	private void init() {
		jdbcTemplate = new JdbcTemplate(masterDataSource);
	}

	public String findDataBaseNameByTenantId(Long tenantId) {
		if (tenantId == null)
			return null;
		try {
			return jdbcTemplate.queryForObject("SELECT database_name FROM tenant WHERE tenant_id = ?", String.class, tenantId);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public String findDataBaseNameByUsername(String username) {
		if (username == null)
			return null;

		try {
			return jdbcTemplate.queryForObject(
					"SELECT t.database_name FROM user_tenant ut INNER JOIN tenant t on t.id = ut.tenant_id WHERE ut.user_name =  ?",
					String.class, username);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}

	}

	public Long findTenantIdByUsername(String username) {
		if (username == null)
			return null;
		try {
			return jdbcTemplate.queryForObject(
					"SELECT t.tenant_id FROM user_tenant ut INNER JOIN tenant t on t.id = ut.tenant_id WHERE ut.user_name =  ?", Long.class,
					username);

		} catch (EmptyResultDataAccessException e) {
			return null;
		}

	}

}

Creating tenant database

Now lets create the tenant aware datasource

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import com.appngeek.saas_multi_tenant_demo.repo.util.DBUtil;
import com.appngeek.saas_multi_tenant_demo.repo.util.TenantContext;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class TenantAwareDataSource extends AbstractRoutingDataSource {

	private Map<Object, DataSource> resolvedDataSources = new HashMap<>();

	@Autowired
	@Qualifier("masterDataSource")
	public DataSource masterDataSource;

	@Autowired
	@Qualifier("masterConfig")
	private HikariConfig hikariConfig;

	@Autowired
	private TenantDatabaseMigrationService tenantDatabaseMigrationService;

	@Autowired
	private TenantResolver tenantResolver;

	@Override
	public void afterPropertiesSet() {
		super.setDefaultTargetDataSource(masterDataSource);
		super.setTargetDataSources(new HashMap<>());
		super.afterPropertiesSet();
	}

	@Override
	protected Object determineCurrentLookupKey() {
		return TenantContext.getCurrentTenant();
	}

	@Override
	protected DataSource determineTargetDataSource() {
		Long tenantId = (Long) determineCurrentLookupKey();
		if (tenantId == null)
			return masterDataSource;

		DataSource tenantDataSource = resolvedDataSources.get(tenantId);
		if (tenantDataSource == null) {
			tenantDataSource = createDataSourceForTenantId(tenantId);
			tenantDatabaseMigrationService.flywayMigrate(tenantDataSource);
			resolvedDataSources.put(tenantId, tenantDataSource);
		}

		return tenantDataSource;
	}

	private DataSource createDataSourceForTenantId(Long tenantId) {
		String tenantDatabaseName = tenantResolver.findDataBaseNameByTenantId(tenantId);
		if (tenantDatabaseName == null)
			throw new IllegalArgumentException("Given tenant id is not valid : " + tenantId);

		HikariConfig tenantHikariConfig = new HikariConfig();
		hikariConfig.copyStateTo(tenantHikariConfig);
		String tenantJdbcURL = DBUtil.databaseURLFromMYSQLJdbcUrl(hikariConfig.getJdbcUrl(), tenantDatabaseName);
		tenantHikariConfig.setJdbcUrl(tenantJdbcURL);
		tenantHikariConfig.setPoolName(tenantDatabaseName + "-db-pool");
		return new HikariDataSource(tenantHikariConfig);
	}

}
public class TenantContext {
	
    private static ThreadLocal<Long> currentTenant = new ThreadLocal<>();

    public static void setCurrentTenant(Long tenantId) {
        currentTenant.set(tenantId);
    }

    public static Long getCurrentTenant() {
        return currentTenant.get();
    }
}
import java.net.URI;

public class DBUtil {

	public static String databaseURLFromMYSQLJdbcUrl(String url, String newDbName) {
		try {
			String cleanURI = url.substring(5);

			URI uri = URI.create(cleanURI);
			return "jdbc:" + uri.getScheme() + "://" + uri.getHost() + ":" + uri.getPort() + "/" + newDbName
					+ "?createDatabaseIfNotExist=true&verifyServerCertificate=false&useSSL=false&requireSSL=false&useUnicode=yes&characterEncoding=UTF-8";
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

}

 

Configure the Tenant Aware datasource

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

@Configuration
public class TenantDatabaseConfig {

	@Bean(name = "tenantAwareDataSource")
	@Primary
	public DataSource tenantAwareDataSource() {
		return new TenantAwareDataSource();
	}

}

 

We need to initialize the tenant datasource when it is created and we would do that with flyway , flyway can initialize and migrate the tenant database . All migration files are placed under 'classpath:db/migration/tenant'


import javax.sql.DataSource;

import org.flywaydb.core.Flyway;
import org.springframework.stereotype.Service;

@Service
public class TenantDatabaseMigrationService {

	public Boolean flywayMigrate(DataSource tenantDataSource) {
		try {
			Flyway flyway = Flyway.configure().dataSource(tenantDataSource).locations("classpath:db/migration/tenant")
					.baselineOnMigrate(true).outOfOrder(true).load();
			flyway.repair();
			flyway.migrate();
		} catch (Exception e) {
			return false;
		}

		return true;
	}
}

 

Lets test our code

We can test it by creating an simple REST API end point to create tenant specific data

Create tenant specific JPA model class called TenantSpecificData.java

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import lombok.ToString;

@AllArgsConstructor
@NoArgsConstructor
@Data
@EqualsAndHashCode(callSuper = false)
@ToString
@Entity
@Table(name = "tenant_specific_data")
public class TenantSpecificData {

	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	private Long id;

	@Column(name = "current_tenant_id")
	private Long currentTenantId;

	@Column(name = "username")
	private String username;

	@Column(name = "sample_data")
	private String sampleData;

}

 

Create a repository for TenantSpecificData model called TenantSpecificDataRepository.java

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import com.appngeek.saas_multi_tenant_demo.domain.TenantSpecificData;

@Repository
public interface TenantSpecificDataRepository extends JpaRepository<TenantSpecificData, Long> {

	List<TenantSpecificData> findByUsername(String username);

	List<TenantSpecificData> findByCurrentTenantId(Long currentTenantId);

}

 

Create a REST controller called TenantSpecificDataController.java with HTTP POST method

@RestController
public class TenantSpecificDataController {

	@Autowired
	private TenantSpecificDataRepository tenantSpecificDataRepository;

	@Autowired
	private TenantResolver tenantResolver;


	/**
	 * Push data to specific tenant db based on tenantId passed as path variable
	 * @param tenantId
	 * @param username
	 * @param tenantSpecificString
	 * @return
	 */
	@PostMapping("/api/create/data/{tenantId}/{username}")
	@ResponseStatus(HttpStatus.CREATED)
	public TenantSpecificData createTenantSpecificData(@PathVariable Long tenantId, @PathVariable String username,
			@RequestBody String tenantSpecificString) {
		TenantContext.setCurrentTenant(tenantId);
		TenantSpecificData tenantSpecificData = new TenantSpecificData();
		tenantSpecificData.setCurrentTenantId(tenantId);
		tenantSpecificData.setUsername(username);
		tenantSpecificData.setSampleData(tenantSpecificString);

		return tenantSpecificDataRepository.save(tenantSpecificData);
	}

}

The above api POST : "/api/create/data/{tenantId}/{username}" is called with 'tenantSpecificString' as sample data in body . The data goes into the respective tenant db since we set the tenant id as the current tenant context

TenantContext.setCurrentTenant(tenantId);

 If we connect to our database and query for the databases created , we would see three databases

  1. Master database

2. Tenant_1 database

2. Tenant_2 database

Summary

We just saw how to configure SaaS style multi tenant web app with Spring Boot 2 ,Spring Security 5 and MySQL

Code available on GITHUB

How to Deploy an Express.js Application to AWS Lambda

In this post, we will learn how to set up a simple Express API application powered by an AWS Lambda function. We will use Serverless Framework...

Create custom spring AOP annotation for caching in Spring boot

Lets see how to add custom annotation to cache data to redis , we do have spring @Cacheable but we will not use it because it doesn't...

App & Geek   Never miss a story from App & Geek, get updates in your inbox.