Spring Boot & Postgres - Multi-Tenancy & Routing

Jul 28, 2024 · 4 min read · jdbc multi-tenancy liquibase ·

Share on:

Overview

Spring JPA implementation with multi-tenancy and routing.

Github: https://github.com/gitorko/project101

Postgres Multi-Tenancy

Multi-tenancy is an architectural pattern that allows you to isolate customers even if they are using the same hardware or software components.

Catalog-based - Each region gets its own database
Schema-based - Single database but different schema for each region
Table-based - Single database, single table but a column identifies the region.

There are 2 approache to implement multi-tenancy

AbstractMultiTenantConnectionProvider - Handles Hibernate session factory connections for different tenants.
AbstractRoutingDataSource - Handles other aspects of data source routing in the application, such as switching data sources for non-Hibernate use cases.

Code

 1package com.demo.project101.config;
 2
 3import java.util.HashMap;
 4import java.util.Map;
 5import javax.sql.DataSource;
 6
 7import com.zaxxer.hikari.HikariConfig;
 8import com.zaxxer.hikari.HikariDataSource;
 9import org.springframework.beans.factory.annotation.Qualifier;
10import org.springframework.context.annotation.Bean;
11import org.springframework.context.annotation.Configuration;
12import org.springframework.core.env.Environment;
13import org.springframework.orm.jpa.JpaTransactionManager;
14import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
15import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
16import org.springframework.transaction.PlatformTransactionManager;
17
18@Configuration
19public class DataSourceConfig {
20
21    @Bean(name = "defaultDataSource")
22    public DataSource defaultDataSource(Environment env) {
23        HikariConfig config = new HikariConfig();
24        config.setJdbcUrl(env.getProperty("spring.datasource.url"));
25        config.setUsername(env.getProperty("spring.datasource.username"));
26        config.setPassword(env.getProperty("spring.datasource.password"));
27        config.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
28        config.setMaximumPoolSize(Integer.parseInt(env.getProperty("spring.datasource.hikari.maximum-pool-size")));
29        config.setMinimumIdle(Integer.parseInt(env.getProperty("spring.datasource.hikari.minimum-idle")));
30        config.setIdleTimeout(Long.parseLong(env.getProperty("spring.datasource.hikari.idle-timeout")));
31        config.setMaxLifetime(Long.parseLong(env.getProperty("spring.datasource.hikari.max-lifetime")));
32        config.setConnectionTimeout(Long.parseLong(env.getProperty("spring.datasource.hikari.connection-timeout")));
33        return new HikariDataSource(config);
34    }
35
36    @Bean(name = "entityManagerFactory")
37    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
38            @Qualifier("routingDataSource") DataSource routingDataSource, Environment env) {
39        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
40        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
41        factory.setDataSource(routingDataSource);
42        factory.setPackagesToScan("com.demo.project101.domain");
43        factory.setJpaVendorAdapter(vendorAdapter);
44        factory.setJpaPropertyMap(hibernateProperties(env));
45        return factory;
46    }
47
48    @Bean(name = "transactionManager")
49    public PlatformTransactionManager primaryTransactionManager(
50            @Qualifier("entityManagerFactory") LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory) {
51        return new JpaTransactionManager(primaryEntityManagerFactory.getObject());
52    }
53
54    private Map<String, Object> hibernateProperties(Environment env) {
55        Map<String, Object> properties = new HashMap<>();
56        properties.put("hibernate.hbm2ddl.auto", env.getProperty("spring.jpa.properties.hibernate.hbm2ddl.auto"));
57        properties.put("hibernate.dialect", env.getProperty("spring.jpa.properties.hibernate.dialect"));
58        properties.put("hibernate.show_sql", env.getProperty("spring.jpa.properties.hibernate.show_sql"));
59        return properties;
60    }
61}
62
63

 1package com.demo.project101.config;
 2
 3import java.util.List;
 4import javax.sql.DataSource;
 5
 6import jakarta.annotation.PostConstruct;
 7import liquibase.exception.LiquibaseException;
 8import liquibase.integration.spring.SpringLiquibase;
 9import org.springframework.beans.factory.annotation.Autowired;
10import org.springframework.beans.factory.annotation.Qualifier;
11import org.springframework.beans.factory.annotation.Value;
12import org.springframework.context.annotation.Configuration;
13
14@Configuration
15public class LiquibaseConfig {
16
17    @Autowired
18    @Qualifier("defaultDataSource")
19    private DataSource dataSource;
20
21    @Value("${app.tenants}")
22    private List<String> tenants;
23
24    @PostConstruct
25    public void applyLiquibase() throws LiquibaseException {
26        for (String tenant : tenants) {
27            SpringLiquibase liquibase = new SpringLiquibase();
28            liquibase.setDataSource(dataSource);
29            liquibase.setDefaultSchema(tenant);
30            liquibase.setChangeLog("classpath:db/changelog/db.changelog.yaml");
31            liquibase.afterPropertiesSet();
32        }
33    }
34}
35

 1package com.demo.project101.config;
 2
 3import java.util.HashMap;
 4import java.util.List;
 5import java.util.Map;
 6import javax.sql.DataSource;
 7
 8import com.zaxxer.hikari.HikariConfig;
 9import com.zaxxer.hikari.HikariDataSource;
10import lombok.extern.slf4j.Slf4j;
11import org.springframework.beans.factory.annotation.Qualifier;
12import org.springframework.beans.factory.annotation.Value;
13import org.springframework.context.annotation.Bean;
14import org.springframework.context.annotation.Configuration;
15import org.springframework.core.env.Environment;
16import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
17
18@Configuration
19@Slf4j
20public class RoutingConfig {
21
22    @Value("${app.tenants}")
23    private List<String> tenants;
24
25    @Bean(name = "routingDataSource")
26    public DataSource routingDataSource(@Qualifier("defaultDataSource") DataSource defaultDataSource, Environment env) {
27        Map<Object, Object> targetDataSources = new HashMap<>();
28        for (String tenant : tenants) {
29            targetDataSources.put(tenant, createTenantDataSource(tenant, env));
30        }
31        AbstractRoutingDataSource routingDataSource = new AbstractRoutingDataSource() {
32            @Override
33            protected Object determineCurrentLookupKey() {
34                return TenantContext.getCurrentTenant();
35            }
36        };
37        routingDataSource.setDefaultTargetDataSource(defaultDataSource);
38        routingDataSource.setTargetDataSources(targetDataSources);
39        return routingDataSource;
40    }
41
42    public DataSource createTenantDataSource(String tenant, Environment env) {
43        log.info("New Connection: {}", tenant);
44        HikariConfig config = new HikariConfig();
45        config.setJdbcUrl(env.getProperty("spring.datasource.url") + "?currentSchema=" + tenant);
46        config.setUsername(env.getProperty("spring.datasource.username"));
47        config.setPassword(env.getProperty("spring.datasource.password"));
48        config.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
49        config.setMaximumPoolSize(Integer.parseInt(env.getProperty("spring.datasource.hikari.maximum-pool-size")));
50        config.setMinimumIdle(Integer.parseInt(env.getProperty("spring.datasource.hikari.minimum-idle")));
51        config.setIdleTimeout(Long.parseLong(env.getProperty("spring.datasource.hikari.idle-timeout")));
52        config.setMaxLifetime(Long.parseLong(env.getProperty("spring.datasource.hikari.max-lifetime")));
53        config.setConnectionTimeout(Long.parseLong(env.getProperty("spring.datasource.hikari.connection-timeout")));
54        return new HikariDataSource(config);
55    }
56}

 1package com.demo.project101.config;
 2
 3public class TenantContext {
 4
 5    private static final ThreadLocal<String> CURRENT_TENANT = new ThreadLocal<>();
 6
 7    public static String getCurrentTenant() {
 8        return CURRENT_TENANT.get();
 9    }
10
11    public static void setCurrentTenant(String tenant) {
12        CURRENT_TENANT.set(tenant);
13    }
14
15    public static void clear() {
16        CURRENT_TENANT.remove();
17    }
18}

 1package com.demo.project101.controller;
 2
 3import java.util.List;
 4
 5import com.demo.project101.config.TenantContext;
 6import com.demo.project101.domain.Customer;
 7import com.demo.project101.service.CustomerService;
 8import lombok.RequiredArgsConstructor;
 9import org.springframework.web.bind.annotation.GetMapping;
10import org.springframework.web.bind.annotation.PostMapping;
11import org.springframework.web.bind.annotation.RequestBody;
12import org.springframework.web.bind.annotation.RequestHeader;
13import org.springframework.web.bind.annotation.RequestMapping;
14import org.springframework.web.bind.annotation.RestController;
15
16@RestController
17@RequestMapping("/customer")
18@RequiredArgsConstructor
19public class CustomerController {
20    final CustomerService customerService;
21
22    @GetMapping
23    public List<Customer> getAll(@RequestHeader("X-TenantID") String tenantId) {
24        try {
25            TenantContext.setCurrentTenant(tenantId);
26            return customerService.findAll();
27        } finally {
28            TenantContext.clear();
29        }
30    }
31
32    @PostMapping
33    public Customer saveCustomer(@RequestHeader("X-TenantID") String tenantId, @RequestBody Customer customer) {
34        try {
35            TenantContext.setCurrentTenant(tenantId);
36            return customerService.save(customer);
37        } finally {
38            TenantContext.clear();
39        }
40    }
41}

Postman

Import the postman collection to postman

Postman Collection

Setup

 1# Project101
 2
 3Spring Boot & Postgres - Multi-tenancy & Routing
 4
 5### Version
 6
 7Check version
 8
 9```bash
10$java --version
11openjdk version "21.0.3" 2024-04-16 LTS
12```
13
14### Postgres DB
15
16```
17docker run -p 5432:5432 --name pg-container -e POSTGRES_PASSWORD=password -d postgres:14
18docker ps
19docker exec -it pg-container psql -U postgres -W postgres
20CREATE USER test WITH PASSWORD 'test@123';
21CREATE DATABASE "test-db" WITH OWNER "test" ENCODING UTF8 TEMPLATE template0;
22grant all PRIVILEGES ON DATABASE "test-db" to test;
23```
24
25Schema 
26
27```bash
28
29CREATE SCHEMA america;
30CREATE SCHEMA asia;
31
32select nspname as "Schema"
33from pg_catalog.pg_namespace
34where nspname not like 'pg_%';
35```
36
37```bash
38docker stop pg-container
39docker start pg-container
40```
41
42### Dev
43
44To run the backend in dev mode.
45
46```bash
47./gradlew clean build
48./gradlew bootRun
49```