Master Multi‑Tenant Architecture in Spring Boot 3.3: Schemas, Separate DBs

Environment: SpringBoot 3.3.0

1. Introduction

Multi‑tenant architecture allows a single application instance to serve multiple customers (tenants). It is common in SaaS solutions, and isolating each tenant’s data, customizations, etc., is a key challenge. Three typical implementations are described below.

1.1 Separate Database

Each tenant has its own physical database instance. JDBC connections are created per tenant, usually by defining a separate connection pool and selecting the pool based on the tenant identifier.

Advantages

High data isolation and security.

Database can be tuned and scaled per tenant.

Backup and restore are relatively simple.

Disadvantages

Higher cost because each tenant requires a dedicated database.

Potential waste of hardware resources if a tenant uses only a fraction of the database.

1.2 Separate Schema

All tenants share a single database instance but each tenant uses a distinct schema. Two ways to obtain a JDBC connection are described: (1) point the connection directly to the schema, or (2) connect to the default schema and issue a SET schema command at runtime.

Advantages

Lower database cost because tenants share the same instance.

Data isolation remains high as each tenant has its own schema.

Disadvantages

Possible resource contention and performance bottlenecks between schemas.

Backup and restore become more complex, needing per‑schema operations.

1.3 Partitioned (Discriminator) Data

All tenant data resides in a single schema and a dedicated partition column distinguishes tenants. A single connection pool serves all tenants, but every SQL statement must include the tenant column in its WHERE clause.

Advantages

Lowest cost because all tenants share one database and schema.

Potentially higher query efficiency as data is in one table.

Disadvantages

Lowest isolation level, raising security concerns.

Application logic must enforce correct isolation and access control.

Backup and recovery are complex because all tenant data is intertwined.

2. Practical Implementation

2.1 Partitioned Data Example

Entity definition:

<code>@Entity
@Table(name = "t_person")
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private Integer age;
    @TenantId
    private String tenantId;
}
</code>

The @TenantId annotation marks the column used for tenant discrimination; Hibernate automatically adds the tenant filter to queries.

DAO and Service:

<code>// DAO
public interface PersonRepository extends JpaRepository<Person, Long>, JpaSpecificationExecutor<Person> {}

// Service
@Service
public class PersonService {
    private final PersonRepository personRepository;
    public PersonService(PersonRepository personRepository) {
        this.personRepository = personRepository;
    }
    public List<Person> persons() {
        return this.personRepository.findAll();
    }
}
</code>

Controller:

<code>@GetMapping("")
public List<Person> persons() {
    return this.personService.persons();
}
</code>

Tenant identifier resolver (ThreadLocal based):

<code>public class TenantIdResolver implements CurrentTenantIdentifierResolver<String> {
    private static final ThreadLocal<String> CURRENT_TENANT = new ThreadLocal<>();
    public void setCurrentTenant(String currentTenant) {
        CURRENT_TENANT.set(currentTenant);
    }
    @Override
    public String resolveCurrentTenantIdentifier() {
        return Optional.ofNullable(CURRENT_TENANT.get()).orElse("default");
    }
    @Override
    public boolean validateExistingCurrentSessions() {
        return true;
    }
}
</code>

Web interceptor to read the tenant ID from request headers:

<code>@Component
public class TenantIdInterceptor implements HandlerInterceptor {
    private final TenantIdResolver tenantIdResolver;
    public TenantIdInterceptor(TenantIdResolver tenantIdResolver) {
        this.tenantIdResolver = tenantIdResolver;
    }
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        String tenantId = request.getHeader("x-tenant-id");
        tenantIdResolver.setCurrentTenant(tenantId);
        return true;
    }
}
</code>

Hibernate configuration to plug in the resolver:

<code>spring:
  jpa:
    properties:
      hibernate:
        tenant_identifier_resolver: com.pack.tenant.config.TenantIdResolver
</code>

Running the application shows SQL statements automatically containing the tenant_id condition.

2.2 Separate Database Example

Define multiple data sources in configuration:

<code>pack:
  datasource:
    defaultDs: ds1
    config:
      ds1:
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/tenant-01
        username: tenant01
        password: xxxooo
        type: com.zaxxer.hikari.HikariDataSource
      ds2:
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/tenant-02
        username: tenant02
        password: oooxxx
        type: com.zaxxer.hikari.HikariDataSource
</code>

Routing data source implementation:

<code>public class PackRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.get();
    }
}

public class DataSourceContextHolder {
    private static final ThreadLocal<String> HOLDER = new InheritableThreadLocal<>();
    public static void set(String key) { HOLDER.set(key); }
    public static String get() { return HOLDER.get(); }
    public static void clear() { HOLDER.remove(); }
}
</code>

Bean configuration to create the routing data source:

<code>@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource dataSource(MultiDataSourceProperties properties) {
        PackRoutingDataSource dataSource = new PackRoutingDataSource(properties.getDefaultDs());
        Map<Object, Object> targetDataSources = new HashMap<>();
        properties.getConfig().forEach((key, props) -> {
            targetDataSources.put(key, createDataSource(props, HikariDataSource.class));
        });
        dataSource.setTargetDataSources(targetDataSources);
        return dataSource;
    }
    private static <T> T createDataSource(PackDataSourceProperties properties, Class<? extends DataSource> type) {
        return (T) properties.initializeDataSourceBuilder().type(type).build();
    }
}
</code>

Web interceptor to set the current tenant’s data source:

<code>@Component
public class TenantIdInterceptor implements HandlerInterceptor {
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        String tenantId = request.getHeader("x-tenant-id");
        DataSourceContextHolder.set(tenantId);
        return true;
    }
}
</code>

With these components, the application can dynamically switch between tenant‑specific databases at runtime.