Spring Boot & Postgres - Multi-Tenancy & Routing

calendar Jul 28, 2024 · 4 min read · jdbc multi-tenancy liquibase  ·
Share on: linkedin copy

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.

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

There are 2 approache to implement multi-tenancy

  1. AbstractMultiTenantConnectionProvider - Handles Hibernate session factory connections for different tenants.
  2. 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```

References

https://spring.io

https://vladmihalcea.com/database-multitenancy/

https://vladmihalcea.com/hibernate-database-schema-multitenancy/

https://vladmihalcea.com/read-write-read-only-transaction-routing-spring/

comments powered by Disqus