[Advanced Spring Boot] Multi-Tenancy and Soft Delete with Spring Boot and Hibernate

A SaaS catalogue serves several customers from one deployment. Each customer, a tenant, sees its own products and never another tenant's, even though the same code, the same connection pool and often the same tables serve all of them. The isolation has to hold for every query the application sends: the repository methods, the JPQL written by hand, the bulk updates, and the native SQL someone adds later. Soft delete is the same kind of problem. A deleted row stays in the table with a flag, and every read has to skip it, so it is one more predicate that must reach every statement.

This article builds both on one product API. It resolves the tenant from a request, separates tenants three ways (a discriminator column with Hibernate's @TenantId, a schema per tenant and a database per tenant), adds PostgreSQL row-level security underneath, and then compares Hibernate's @SoftDelete with the older @SQLDelete and @SQLRestriction pair. The examples use Spring Boot 4.1.1 and Java 21 against PostgreSQL 18.

The first two sections set up the strategies and the lab; the rest follows a request from the header to the SQL, strategy by strategy, then turns to soft delete, and ends with how to choose.

Multi-tenancy strategies: discriminator column, schema per tenant, database per tenant

The three strategies differ in where the boundary between tenants sits.

Discriminator column. All tenants share the tables, and every row carries a tenant_id. The boundary is a predicate: each statement must say where tenant_id = ?. Hibernate's @TenantId appends it.
Schema per tenant. Every tenant has its own copy of the tables in its own PostgreSQL schema, acme.products and globex.products, in one database. The boundary is the connection's search_path: the same SQL reads different tables depending on which schema the connection points at.
Database per tenant. Every tenant has its own database, so its own connections. The boundary is the connection pool the request borrows from.

Discriminator column, schema per tenant and database per tenant side by side: where the tenant boundary sits and what separates the data

Row-level security is not a fourth strategy. It is PostgreSQL enforcing the discriminator predicate itself, under the application, so a query that forgets the predicate still sees one tenant.

The lab: a multi-tenant catalogue and how the outputs were produced

The project came from Spring Initializr with web, data-jpa, postgresql, flyway and validation:

Bash

curl -s "https://start.spring.io/starter.zip?type=gradle-project&language=java&bootVersion=4.1.1&javaVersion=21&groupId=com.example&artifactId=demo&name=demo&packageName=com.example.demo&dependencies=web,data-jpa,postgresql,flyway,validation" -o demo.zip

PostgreSQL 18 runs in Docker on host port 5512, and the application on port 8212:

Bash

docker run -d --name sba-a12-pg --memory 512m -e POSTGRES_USER=demo -e POSTGRES_PASSWORD=demo -e POSTGRES_DB=demo -p 5512:5432 postgres:18

The discriminator version of the catalogue has a tenants table and a tenant_id column on categories and products. A SKU is unique per tenant, not globally, so both tenants can sell a KB-01:

src/main/resources/db/migration/V1__create_catalog.sql

create table tenants (
    id   varchar(30)  primary key,
    name varchar(100) not null
);
 
create table categories (
    id        bigint generated by default as identity primary key,
    tenant_id varchar(30) not null references tenants (id),
    name      varchar(60) not null,
    unique (tenant_id, name)
);
 
create table products (
    id          bigint generated by default as identity primary key,
    tenant_id   varchar(30)    not null references tenants (id),
    name        varchar(120)   not null,
    sku         varchar(40)    not null,
    price       numeric(10, 2) not null,
    category_id bigint         not null references categories (id),
    unique (tenant_id, sku)
);
 
create index products_category_id_idx on products (category_id);

V2__seed_tenants.sql inserts two tenants, acme and globex. Acme has the categories Keyboards (id 1) and Mice (2) and four products, ids 1 to 4: KB-01 89.00, KB-02 59.00, MS-01 24.50 and MS-02 49.90. Globex has Keyboards (3) and Monitors (4) and three products, ids 5 to 7: its own KB-01 at 35.00, MN-01 229.00 and MN-02 159.00. Seven rows in all, four of them Acme's.

src/main/resources/application.properties

spring.application.name=demo
server.port=8212
spring.datasource.url=jdbc:postgresql://localhost:5512/demo
spring.datasource.username=demo
spring.datasource.password=demo
spring.jpa.open-in-view=false
spring.jpa.hibernate.ddl-auto=validate
logging.level.org.hibernate.SQL=DEBUG
spring.mvc.problemdetails.enabled=true

Each strategy needs a different mapping of the same entity, so the lab keeps one Gradle subproject per variant, all sharing the tenant, repository and web code from one source directory. An application needs only one of them:

Tree

demo/
├── shared/src/main/java/com/example/demo/   tenant filter and resolver, repository, service, controller
├── discriminator/   @TenantId, row-level security
├── softdelete/      @TenantId + @SoftDelete
├── sqldelete/       @TenantId + @SQLDelete and @SQLRestriction
├── schema/          a schema per tenant
└── database/        a database per tenant

Repository calls ran in an ApplicationRunner behind a lab profile, which turns the web server off, sets logging.pattern.console=%m%n so each log line is only its message, and switches on org.hibernate.orm.jdbc.bind=TRACE for the bound values. Each block starts with the call after >>>, then Hibernate's statements, then the result: a product prints as id:tenant:SKU price. An exception prints as !!! lines, one per cause. A reset profile cleans the database with Flyway before each run, so every run starts from the seed rows above.

Resolving the tenant from a request header

Before any strategy can apply, the application has to know which tenant the current request belongs to, and hand that to Hibernate. The lab reads it from an X-Tenant-Id header.

A TenantContext holder and a servlet filter

The holder is a ThreadLocal, because a servlet request runs on one thread from the filter to the repository:

src/main/java/com/example/demo/tenant/TenantContext.java

package com.example.demo.tenant;
 
public final class TenantContext {
 
    private static final ThreadLocal<String> CURRENT = new ThreadLocal<>();
 
    private TenantContext() {
    }
 
    public static void set(String tenantId) {
        CURRENT.set(tenantId);
    }
 
    public static String get() {
        return CURRENT.get();
    }
 
    public static void clear() {
        CURRENT.remove();
    }
}

The filter rejects a request without the header or with an unknown tenant, sets the holder, and clears it in finally. Errors thrown by a filter never reach a @RestControllerAdvice, so it writes the ProblemDetail itself with the application's Jackson 3 JsonMapper:

src/main/java/com/example/demo/tenant/TenantFilter.java

package com.example.demo.tenant;
 
import java.io.IOException;
import java.net.URI;
 
import jakarta.servlet.FilterChain;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
 
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ProblemDetail;
import org.springframework.stereotype.Component;
import org.springframework.web.filter.OncePerRequestFilter;
 
import tools.jackson.databind.json.JsonMapper;
 
@Component
public class TenantFilter extends OncePerRequestFilter {
 
    public static final String HEADER = "X-Tenant-Id";
 
    private final TenantRegistry tenants;
    private final JsonMapper jsonMapper;
 
    public TenantFilter(TenantRegistry tenants, JsonMapper jsonMapper) {
        this.tenants = tenants;
        this.jsonMapper = jsonMapper;
    }
 
    @Override
    protected boolean shouldNotFilter(HttpServletRequest request) {
        return !request.getRequestURI().startsWith("/api/")
                || request.getRequestURI().startsWith("/api/admin/");
    }
 
    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response,
                                    FilterChain chain) throws ServletException, IOException {
        String tenantId = request.getHeader(HEADER);
        if (tenantId == null || tenantId.isBlank()) {
            reject(request, response, "Missing " + HEADER + " header.");
            return;
        }
        if (!tenants.exists(tenantId)) {
            reject(request, response, "Unknown tenant '" + tenantId + "'.");
            return;
        }
        TenantContext.set(tenantId);
        try {
            chain.doFilter(request, response);
        } finally {
            TenantContext.clear();
        }
    }
 
    private void reject(HttpServletRequest request, HttpServletResponse response, String detail)
            throws IOException {
        ProblemDetail problem = ProblemDetail.forStatusAndDetail(HttpStatus.BAD_REQUEST, detail);
        problem.setTitle("Invalid tenant");
        problem.setInstance(URI.create(request.getRequestURI()));
        response.setStatus(HttpStatus.BAD_REQUEST.value());
        response.setContentType(MediaType.APPLICATION_PROBLEM_JSON_VALUE);
        jsonMapper.writeValue(response.getOutputStream(), problem);
    }
}

/api/admin/** is left out on purpose: the platform endpoint that creates tenants, later in the article, has no tenant. The known tenants come from the tenants table, loaded once the application has started:

src/main/java/com/example/demo/tenant/TenantRegistry.java

@Component
public class TenantRegistry {
 
    private final JdbcClient jdbc;
    private final Set<String> tenantIds = ConcurrentHashMap.newKeySet();
 
    public TenantRegistry(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }
 
    public boolean exists(String tenantId) {
        return tenantIds.contains(tenantId);
    }
 
    public Set<String> all() {
        return Set.copyOf(tenantIds);
    }
 
    @EventListener(ApplicationStartedEvent.class)
    public void refresh() {
        tenantIds.addAll(jdbc.sql("select id from public.tenants").query(String.class).list());
    }
}

⚠️ A tenant header is for this lab only. Any client can send X-Tenant-Id: globex and read Globex's data. In production the tenant comes from the authenticated principal, typically a claim in the access token, and the filter reads it from the SecurityContext after authentication; article 13 sets up the resource server that provides that token. Everything after the filter stays the same.

With the discriminator setup that the next section builds, the four cases over HTTP:

Bash

curl -i -s -H 'X-Tenant-Id: acme' http://localhost:8212/api/products
curl -s -H 'X-Tenant-Id: globex' http://localhost:8212/api/products
curl -i -s http://localhost:8212/api/products
curl -i -s -H 'X-Tenant-Id: initech' http://localhost:8212/api/products

Http

HTTP/1.1 200 
Content-Type: application/json
Content-Length: 332
Date: Fri, 18 Sep 2026 07:18:17 GMT
 
[{"id":1,"sku":"KB-01","name":"Mechanical keyboard","price":89.00,"category":"Keyboards"},{"id":2,"sku":"KB-02","name":"Compact keyboard","price":59.00,"category":"Keyboards"},{"id":3,"sku":"MS-01","name":"Wireless mouse","price":24.50,"category":"Mice"},{"id":4,"sku":"MS-02","name":"Gaming mouse","price":49.90,"category":"Mice"}]

JSON

[{"id":5,"sku":"KB-01","name":"Office keyboard","price":35.00,"category":"Keyboards"},{"id":6,"sku":"MN-01","name":"27-inch monitor","price":229.00,"category":"Monitors"},{"id":7,"sku":"MN-02","name":"24-inch monitor","price":159.00,"category":"Monitors"}]

Http

HTTP/1.1 400 
Content-Type: application/problem+json
Content-Length: 105
Date: Fri, 18 Sep 2026 07:18:17 GMT
Connection: close
 
{"detail":"Missing X-Tenant-Id header.","instance":"/api/products","status":400,"title":"Invalid tenant"}

Http

HTTP/1.1 400 
Content-Type: application/problem+json
Content-Length: 103
Date: Fri, 18 Sep 2026 07:18:17 GMT
Connection: close
 
{"detail":"Unknown tenant 'initech'.","instance":"/api/products","status":400,"title":"Invalid tenant"}

The same URL returned four products for Acme and three for Globex. Tomcat added Connection: close to both 400 responses on its own.

What happens when the ThreadLocal is not cleared?

A common first version sets the tenant when the header is there and relies on something later to fail when it is not. It never clears the holder:

src/main/java/com/example/demo/tenant/TenantFilter.java

    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response,
                                    FilterChain chain) throws ServletException, IOException {
        String tenantId = request.getHeader("X-Tenant-Id");
        if (tenantId != null) {
            TenantContext.set(tenantId);
        }
        chain.doFilter(request, response);
    }

Tomcat reuses its request threads. To make the reuse certain, the run limited Tomcat to one thread with --server.tomcat.threads.max=1, and ProductService.findAll logs the tenant it sees. A Globex request, then a request with no header at all:

Bash

curl -s -H 'X-Tenant-Id: globex' http://localhost:8212/api/products
curl -i -s http://localhost:8212/api/products

The response to the second request, the one without a header:

Http

HTTP/1.1 200 
Content-Type: application/json
Content-Length: 256
Date: Fri, 18 Sep 2026 07:18:44 GMT
 
[{"id":5,"sku":"KB-01","name":"Office keyboard","price":35.00,"category":"Keyboards"},{"id":6,"sku":"MN-01","name":"27-inch monitor","price":229.00,"category":"Monitors"},{"id":7,"sku":"MN-02","name":"24-inch monitor","price":159.00,"category":"Monitors"}]

The server log for both requests, without the lookups of their two categories:

Text

2026-09-18T14:18:44.074+07:00  INFO 12703 --- [demo] [nio-8212-exec-1] c.example.demo.product.ProductService    : findAll for tenant globex
2026-09-18T14:18:44.097+07:00 DEBUG 12703 --- [demo] [nio-8212-exec-1] org.hibernate.SQL                        : select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ?
2026-09-18T14:18:44.098+07:00 TRACE 12703 --- [demo] [nio-8212-exec-1] org.hibernate.orm.jdbc.bind              : binding parameter (1:VARCHAR) <- [globex]
2026-09-18T14:18:44.134+07:00  INFO 12703 --- [demo] [nio-8212-exec-1] c.example.demo.product.ProductService    : findAll for tenant globex
2026-09-18T14:18:44.135+07:00 DEBUG 12703 --- [demo] [nio-8212-exec-1] org.hibernate.SQL                        : select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ?
2026-09-18T14:18:44.135+07:00 TRACE 12703 --- [demo] [nio-8212-exec-1] org.hibernate.orm.jdbc.bind              : binding parameter (1:VARCHAR) <- [globex]

The anonymous request got Globex's catalogue with a 200. Both requests ran on http-nio-8212-exec-1, and the second one found the first one's value still in the ThreadLocal. With 200 threads instead of one, the same thing happens whenever the next request on that thread skips set, which makes it intermittent rather than absent. The filter above does two things against it: it rejects a missing header before the chain runs, and it clears the holder in finally, so no value outlives its request. The same two requests against that filter, still on one thread, gave 200 and then 400 with "Missing X-Tenant-Id header.".

Is the tenant available on an @Async thread?

No. @Async runs the method on a thread of Spring's task executor, which has its own, empty ThreadLocal:

src/main/java/com/example/demo/product/ProductService.java

    @Async
    public CompletableFuture<Long> countAsync() {
        log.info("countAsync for tenant {}", TenantContext.get());
        return CompletableFuture.completedFuture(products.count());
    }

Bash

curl -s -H 'X-Tenant-Id: acme' http://localhost:8212/api/products/count-async

Text

2026-09-18T14:19:00.266+07:00  INFO 13379 --- [demo] [         task-1] c.example.demo.product.ProductService    : countAsync for tenant null
2026-09-18T14:19:00.278+07:00 DEBUG 13379 --- [demo] [         task-1] org.hibernate.SQL                        : select count(*) from products p1_0 where p1_0.tenant_id = ?
2026-09-18T14:19:00.278+07:00 TRACE 13379 --- [demo] [         task-1] org.hibernate.orm.jdbc.bind              : binding parameter (1:VARCHAR) <- [_none_]

Acme has four products; the answer was 0, with a 200 and no error. On task-1 the holder was empty, and the resolver of the next section turned that into a tenant id that matches no rows. Copying the value onto the executor's threads with a TaskDecorator is part of article 17 on @Async and executors; until then, read the tenant on the request thread and pass it as an argument.

Discriminator column multi-tenancy with @TenantId

Hibernate 6.0 added @TenantId, and in Hibernate 7.4.5 it is the whole discriminator strategy: one annotated attribute per entity and a resolver that says which tenant is current.

Mapping @TenantId and a CurrentTenantIdentifierResolver

The entity gets three lines. Category gets the same three:

src/main/java/com/example/demo/product/Product.java

package com.example.demo.product;
 
import java.math.BigDecimal;
 
import org.hibernate.annotations.TenantId; 
 
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
 
@Entity
@Table(name = "products")
public class Product {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    @TenantId
    @Column(name = "tenant_id", nullable = false, length = 30) 
    private String tenantId; 
 
    @Column(nullable = false, length = 120)
    private String name;
 
    @Column(nullable = false, length = 40)
    private String sku;
 
    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal price;
 
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "category_id", nullable = false)
    private Category category;
 
    protected Product() {
    }
 
    public Product(String name, String sku, BigDecimal price, Category category) {
        this.name = name;
        this.sku = sku;
        this.price = price;
        this.category = category;
    }
 
    // getters and setPrice; no setter for tenantId
}

The resolver answers one question, which tenant is current, and Hibernate asks it every time a session opens:

src/main/java/com/example/demo/tenant/TenantIdentifierResolver.java

package com.example.demo.tenant;
 
import java.util.Map;
 
import org.hibernate.cfg.MultiTenancySettings;
import org.hibernate.context.spi.CurrentTenantIdentifierResolver;
 
import org.springframework.boot.hibernate.autoconfigure.HibernatePropertiesCustomizer;
import org.springframework.stereotype.Component;
 
@Component
public class TenantIdentifierResolver
        implements CurrentTenantIdentifierResolver<String>, HibernatePropertiesCustomizer {
 
    public static final String NO_TENANT = "_none_";
 
    @Override
    public String resolveCurrentTenantIdentifier() {
        String tenantId = TenantContext.get();
        return tenantId != null ? tenantId : NO_TENANT;
    }
 
    @Override
    public boolean validateExistingCurrentSessions() {
        return false;
    }
 
    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put(MultiTenancySettings.MULTI_TENANT_IDENTIFIER_RESOLVER, this);
    }
}

NO_TENANT is what Hibernate gets when no request has set a tenant, for the reason shown below.
validateExistingCurrentSessions concerns Hibernate's own getCurrentSession(), which a Spring application does not use, so false.
customize is the explicit wiring: HibernatePropertiesCustomizer beans edit the properties Boot passes to Hibernate, and hibernate.tenant_identifier_resolver takes an instance. In this stack it is also redundant. Spring Framework 7's HibernateJpaVendorAdapter hands Hibernate a SpringBeanContainer, and Hibernate 7.4.5 asks that container for a CurrentTenantIdentifierResolver bean when the property is missing; with customize emptied, the run below bound acme exactly the same. The customizer stays because it is visible.

Spring Data prepares derived queries while it creates the repositories, and that opens a Hibernate session on the main thread, where no request has set a tenant. The first version of the resolver returned TenantContext.get() as it was, null, and the application did not start:

Text

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'productRepository' defined in com.example.demo.product.ProductRepository defined in @EnableJpaRepositories declared on DataJpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: SessionFactory configured for multi-tenancy, but no tenant identifier specified
Caused by: org.springframework.data.repository.query.QueryCreationException: Cannot create query for method [ProductRepository.findByPriceLessThan(java.math.BigDecimal)]; SessionFactory configured for multi-tenancy, but no tenant identifier specified
Caused by: org.hibernate.HibernateException: SessionFactory configured for multi-tenancy, but no tenant identifier specified

A value that is not a tenant keeps startup working and fails closed later: it matches no tenant_id, and an insert with it failed on products_tenant_id_fkey with Key (tenant_id)=(_none_) is not present in table "tenants". The @Async run above is what it looks like in practice, an empty result instead of an exception.

Examples written for Hibernate 5 set hibernate.multiTenancy=SCHEMA and implement raw, non-generic interfaces. Hibernate 7.4.5 has neither the property nor the MultiTenancyStrategy enum it named: the strategy follows from the mapping. @TenantId on an attribute means a discriminator, and a registered MultiTenantConnectionProvider means schemas or databases.

The tenant from the X-Tenant-Id header travels through the filter, the ThreadLocal holder and the resolver, and becomes either a tenant_id predicate or a schema switch in the SQL

The SQL for find, insert, update and delete

The lab runner sets TenantContext to acme and calls the repository:

Text

>>> findById(1) as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
    Optional[1:acme:KB-01 89.00]
>>> findById(5) as acme, 5 belongs to globex
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [5]
binding parameter (2:VARCHAR) <- [acme]
    Optional.empty
>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? order by p1_0.id
binding parameter (1:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90] (4 rows)
>>> save(new Product("Vertical mouse", "MS-03", 39.00, Mice)) as acme
insert into products (category_id,name,price,sku,tenant_id) values (?,?,?,?,?)
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [Vertical mouse]
binding parameter (3:NUMERIC) <- [39.00]
binding parameter (4:VARCHAR) <- [MS-03]
binding parameter (5:VARCHAR) <- [acme]
    8:acme:MS-03 39.00
>>> update price of 1 to 95.00 as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
update products set category_id=?,name=?,price=?,sku=? where id=?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [Mechanical keyboard]
binding parameter (3:NUMERIC) <- [95.00]
binding parameter (4:VARCHAR) <- [KB-01]
binding parameter (5:BIGINT) <- [1]
    1:acme:KB-01 95.00
>>> deleteById(8) as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [8]
binding parameter (2:VARCHAR) <- [acme]
delete from products where id=?
binding parameter (1:BIGINT) <- [8]

findById carries and p1_0.tenant_id = ?. Hibernate implements @TenantId as a filter named _tenantId, defined with applyToLoadByKey, so it reaches loads by primary key as well as queries. Globex's product 5 came back as Optional.empty, which the controller turns into a 404, the same answer as an id that does not exist.
The insert bound acme for tenant_id although the code never set it. The value comes from the resolver when the row is inserted.
The update and the delete have only where id=?, and tenant_id is missing from the set list. Hibernate marks the attribute as not updatable, so a tenant cannot be changed, and it sends updates and deletes by primary key. That is safe only because the entity reached the session through the filtered select first.

Setting the tenant by hand to another value is refused before any SQL:

Text

>>> save a product whose tenantId is set to globex, as acme
!!! org.springframework.dao.DataIntegrityViolationException: assigned tenant id differs from current tenant id [globex != acme] for entity com.example.demo.product.Product.tenantId
!!! org.hibernate.PropertyValueException: assigned tenant id differs from current tenant id [globex != acme] for entity com.example.demo.product.Product.tenantId

JPQL, derived queries, Specifications, paging and bulk updates

The repository is shared by every variant in the lab. Two of its methods, deleteInBulkBySku and findAllNative, come back in the soft-delete sections:

src/main/java/com/example/demo/product/ProductRepository.java

public interface ProductRepository extends JpaRepository<Product, Long>, JpaSpecificationExecutor<Product> {
 
    List<Product> findByPriceLessThan(BigDecimal price);
 
    @Query("select p from Product p where p.category.name = :category order by p.price")
    List<Product> findInCategory(String category);
 
    @Modifying
    @Query("update Product p set p.price = p.price * :factor where p.sku like :skuPattern")
    int reprice(String skuPattern, BigDecimal factor);
 
    @Modifying
    @Query("delete from Product p where p.sku = :sku")
    int deleteInBulkBySku(String sku);
 
    @Query(value = "select * from products where price >= :min order by id", nativeQuery = true)
    List<Product> findPricedAtLeastNative(BigDecimal min);
 
    @Query(value = "select * from products where tenant_id = :tenantId order by id", nativeQuery = true)
    List<Product> findAllNative(String tenantId);
}

Every kind of query, as acme. The Specification is price >= 50, and the bulk update raises every KB- price by 10 percent, a pattern that matches a Globex product too:

Text

>>> findInCategory("Keyboards") as acme (JPQL)
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? where p1_0.tenant_id = ? and c1_0.name=? order by p1_0.price
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:VARCHAR) <- [acme]
binding parameter (3:VARCHAR) <- [Keyboards]
    [2:acme:KB-02 59.00, 1:acme:KB-01 89.00] (2 rows)
>>> findByPriceLessThan(50) as acme (derived)
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and p1_0.price<?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:NUMERIC) <- [50]
    [3:acme:MS-01 24.50, 4:acme:MS-02 49.90] (2 rows)
>>> findAll(priceAtLeast(50)) as acme (Specification)
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and p1_0.price>=?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:NUMERIC) <- [50]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00] (2 rows)
>>> findAll(PageRequest.of(0, 2)) as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? order by p1_0.id offset ? rows fetch first ? rows only
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:INTEGER) <- [0]
binding parameter (3:INTEGER) <- [2]
select count(p1_0.id) from products p1_0 where p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00] totalElements=4
>>> count() as acme
select count(*) from products p1_0 where p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
    4
>>> reprice("KB-%", 1.10) as acme (bulk @Modifying)
update products p1_0 set price=(p1_0.price*?) where p1_0.sku like ? escape '' and p1_0.tenant_id = ?
binding parameter (1:NUMERIC) <- [1.10]
binding parameter (2:VARCHAR) <- [KB-%]
binding parameter (3:VARCHAR) <- [acme]
    2
>>> prices of KB-01 per tenant, read with JdbcClient
    [acme:KB-01 97.90, globex:KB-01 35.00] (2 rows)

The JPQL join got the predicate twice: c1_0.tenant_id = ? inside the on clause for the joined Category, and p1_0.tenant_id = ? in the where. Every entity with @TenantId that appears in a query is filtered, joined or not.
The derived query, the Specification, the page and its count query all start their where with the tenant predicate. The code that builds them did not change.
The bulk update is the one worth checking by hand, because bulk statements skip the persistence context and, as Basics 32 showed for auditing, entity callbacks. Hibernate 7.4.5 still appended and p1_0.tenant_id = ?: it updated two rows, Acme's KB-01 went from 89.00 to 97.90, and Globex's KB-01 stayed at 35.00.

What bypasses @TenantId: native queries and JdbcClient

Hibernate does not parse native SQL, so it cannot add a predicate to it, and JdbcClient never passes through Hibernate at all:

Text

>>> findPricedAtLeastNative(0) as acme (native @Query)
select * from products where price >= ? order by id
binding parameter (1:NUMERIC) <- [0]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90, 5:globex:KB-01 35.00, 6:globex:MN-01 229.00, 7:globex:MN-02 159.00] (7 rows)
>>> JdbcClient select as acme
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90, 5:globex:KB-01 35.00, 6:globex:MN-01 229.00, 7:globex:MN-02 159.00] (7 rows)

Seven rows for a request that should see four, and the native query even materialized Globex's rows as managed Product entities in Acme's session. Every native query in the discriminator variant needs its own where tenant_id = :tenantId, like findAllNative, and nothing checks that it has one. That is the gap row-level security closes later in the article.

A foreign key does not know about tenants

getReferenceById returns a proxy without a select, so the tenant filter never sees it. Saving an Acme product with Globex's category 3:

Text

>>> save with category 3, which belongs to globex, as acme
insert into products (category_id,name,price,sku,tenant_id) values (?,?,?,?,?)
binding parameter (1:BIGINT) <- [3]
binding parameter (2:VARCHAR) <- [Cross-tenant keyboard]
binding parameter (3:NUMERIC) <- [1.00]
binding parameter (4:VARCHAR) <- [KB-98]
binding parameter (5:VARCHAR) <- [acme]
    8:acme:KB-98 1.00

The foreign key checked only that category 3 exists. Making the tenant part of the key closes it:

src/main/resources/db/migration/V3__tenant_safe_foreign_key.sql

alter table categories add constraint categories_id_tenant_uk unique (id, tenant_id);
 
alter table products drop constraint products_category_id_fkey;
alter table products add constraint products_category_tenant_fk
    foreign key (category_id, tenant_id) references categories (id, tenant_id);

The mapping keeps @JoinColumn(name = "category_id"); the database now compares both columns. The same save after V3:

Text

>>> save with category 3, which belongs to globex, as acme
insert into products (category_id,name,price,sku,tenant_id) values (?,?,?,?,?)
binding parameter (1:BIGINT) <- [3]
binding parameter (2:VARCHAR) <- [Cross-tenant keyboard]
binding parameter (3:NUMERIC) <- [1.00]
binding parameter (4:VARCHAR) <- [KB-98]
binding parameter (5:VARCHAR) <- [acme]
HHH000247: ErrorCode: 0, SQLState: 23503
ERROR: insert or update on table "products" violates foreign key constraint "products_category_tenant_fk"
  Detail: Key (category_id, tenant_id)=(3, acme) is not present in table "categories".
!!! org.springframework.dao.DataIntegrityViolationException: could not execute statement [ERROR: insert or update on table "products" violates foreign key constraint "products_category_tenant_fk" ...
!!! org.hibernate.exception.ConstraintViolationException: could not execute statement [ERROR: insert or update on table "products" violates foreign key constraint "products_category_tenant_fk" ...
!!! org.postgresql.util.PSQLException: ERROR: insert or update on table "products" violates foreign key constraint "products_category_tenant_fk" ...

Schema per tenant with a MultiTenantConnectionProvider

In the schema variant the boundary moves out of the SQL. public keeps only the tenants table, and each tenant gets a schema with its own categories and products, without a tenant_id column:

src/main/java/com/example/demo/product/Product.java

    @TenantId
    @Column(name = "tenant_id", nullable = false, length = 30) 
    private String tenantId;

src/main/resources/db/tenant/V1__create_catalog.sql

create table categories (
    id   bigint generated by default as identity primary key,
    name varchar(60) not null unique
);
 
create table products (
    id          bigint generated by default as identity primary key,
    name        varchar(120)   not null,
    sku         varchar(40)    not null unique,
    price       numeric(10, 2) not null,
    category_id bigint         not null references categories (id)
);
 
create index products_category_id_idx on products (category_id);

The migration lives in db/tenant, not db/migration, because it runs once per tenant schema; the Flyway section below wires that up. public gets V1__create_tenants.sql with the tenants table and the rows acme and globex, and the lab loaded the same products as before into acme.products and globex.products.

A MultiTenantConnectionProvider that switches the schema

Hibernate asks a MultiTenantConnectionProvider for a connection for tenant X and gives it back when the session is done. This one borrows from the application's HikariCP pool and points the connection at the tenant's schema:

src/main/java/com/example/demo/tenant/SchemaPerTenantConnectionProvider.java

package com.example.demo.tenant;
 
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;
 
import javax.sql.DataSource;
 
import org.hibernate.cfg.MultiTenancySettings;
import org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider;
import org.hibernate.service.UnknownUnwrapTypeException;
 
import org.springframework.boot.hibernate.autoconfigure.HibernatePropertiesCustomizer;
import org.springframework.stereotype.Component;
 
@Component
public class SchemaPerTenantConnectionProvider
        implements MultiTenantConnectionProvider<String>, HibernatePropertiesCustomizer {
 
    private final DataSource dataSource;
 
    public SchemaPerTenantConnectionProvider(DataSource dataSource) {
        this.dataSource = dataSource;
    }
 
    @Override
    public Connection getAnyConnection() throws SQLException {
        return dataSource.getConnection();
    }
 
    @Override
    public void releaseAnyConnection(Connection connection) throws SQLException {
        connection.close();
    }
 
    @Override
    public Connection getConnection(String tenantId) throws SQLException {
        Connection connection = getAnyConnection();
        connection.setSchema(tenantId);
        return connection;
    }
 
    @Override
    public void releaseConnection(String tenantId, Connection connection) throws SQLException {
        connection.setSchema("public");
        releaseAnyConnection(connection);
    }
 
    @Override
    public boolean supportsAggressiveRelease() {
        return false;
    }
 
    @Override
    public boolean isUnwrappableAs(Class<?> unwrapType) {
        return false;
    }
 
    @Override
    public <T> T unwrap(Class<T> unwrapType) {
        throw new UnknownUnwrapTypeException(unwrapType);
    }
 
    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put(MultiTenancySettings.MULTI_TENANT_CONNECTION_PROVIDER, this);
    }
}

connection.setSchema rather than a hand-written set search_path, for two reasons. PostgreSQL's server log, with log_statement = 'all', shows what pgjdbc 42.7.13 sends for it:

Text

2026-09-18 07:25:02.274 UTC [383] LOG:  execute <unnamed>: SET SESSION search_path TO 'globex'
2026-09-18 07:25:02.275 UTC [383] LOG:  execute <unnamed>: select current_schema()

The first statement is the driver's translation, with the tenant id sent as a quoted string literal instead of pasted into the SQL as an identifier. The second is HikariCP reading the schema back: its connection proxy intercepts setSchema, records the new value and marks the connection dirty, which the next subsection turns out to depend on. A set search_path executed as a statement reaches the database the same way, but the pool never learns about it. Either way the path holds only the tenant's schema, so an unqualified name no longer finds anything in public; that is why TenantRegistry writes public.tenants.

The series keeps spring.jpa.hibernate.ddl-auto=validate, and here it stopped the application:

Text

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema validation: missing table [categories]

Hibernate validates through getAnyConnection(), which points at public, and public has no categories. Flyway is what keeps the tenant schemas identical in this variant, so validation is switched off:

src/main/resources/application.properties

spring.jpa.hibernate.ddl-auto=validate 
spring.jpa.hibernate.ddl-auto=none

The same repository, run as each tenant:

Text

>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 order by p1_0.id
    [1:KB-01 89.00, 2:KB-02 59.00, 3:MS-01 24.50, 4:MS-02 49.90] (4 rows)
>>> findAll() as globex
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 order by p1_0.id
    [1:KB-01 35.00, 2:MN-01 229.00, 3:MN-02 159.00] (3 rows)
>>> findById(1) as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id=?
binding parameter (1:BIGINT) <- [1]
    Optional[1:KB-01 89.00]
>>> findById(1) as globex
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 where p1_0.id=?
binding parameter (1:BIGINT) <- [1]
    Optional[1:KB-01 35.00]
>>> findPricedAtLeastNative(0) inside a transaction as acme (native @Query)
select * from products where price >= ? order by id
binding parameter (1:NUMERIC) <- [0]
    [1:KB-01 89.00, 2:KB-02 59.00, 3:MS-01 24.50, 4:MS-02 49.90] (4 rows)
>>> JdbcClient select inside a transaction as acme
    acme: 4
>>> count() with no tenant
select count(*) from products p1_0
HHH000247: ErrorCode: 0, SQLState: 42P01
ERROR: relation "products" does not exist
  Position: 22
!!! org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [ERROR: relation "products" does not exist ...
!!! org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [ERROR: relation "products" does not exist ...
!!! org.postgresql.util.PSQLException: ERROR: relation "products" does not exist ...

The SQL has no tenant predicate at all, and the same statement with the same id returned a different row for each tenant. The native query, which leaked seven rows in the discriminator variant, returned only Acme's four, and so did JdbcClient inside the transaction: under JpaTransactionManager it runs on the session's connection, which the provider had pointed at acme. Without a tenant, the sentinel _none_ became a schema that does not exist, and the query failed instead of returning something.

The connection-reuse trap: a pooled connection keeps the last tenant's schema

releaseConnection resets the schema before the connection goes back to the pool. The version without that line looks finished, because every tenant request sets the schema anyway:

src/main/java/com/example/demo/tenant/SchemaPerTenantConnectionProvider.java

    @Override
    public void releaseConnection(String tenantId, Connection connection) throws SQLException {
        connection.setSchema("public"); 
        releaseAnyConnection(connection);
    }

Tenant requests are fine. The problem is everything else that borrows from the same pool without going through the provider: JdbcClient outside a transaction, a scheduled job, Flyway at runtime, a library with its own queries. The lab asked for the schema and the PostgreSQL backend process id, so that the physical connection is visible, before and after a Globex request:

Text

>>> JdbcClient, before any tenant: select current_schema()
    backend 353, schema public
>>> findAll() as globex, then the backend it ran on
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 order by p1_0.id
    [1:KB-01 35.00, 2:MN-01 229.00, 3:MN-02 159.00] on backend 353, schema globex
>>> JdbcClient, no tenant: select current_schema()
    backend 353, schema globex
>>> JdbcClient, no tenant: select count(*) from tenants
!!! org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select count(*) from tenants]
!!! org.postgresql.util.PSQLException: ERROR: relation "tenants" does not exist ...

Backend 353 served Globex and was then handed, still pointing at globex, to code that knew nothing about tenants. Here the result was an error, because tenants exists only in public. A table name that exists in every tenant schema, products, would instead have returned Globex's rows. HikariCP keeps a per-thread list of the connections a thread used last, which is why the same backend came back on the same thread; across 200 request threads it becomes intermittent.

HikariCP can reset the schema itself, but only in one combination. The same run with each variant, the set search_path one written as statement.execute("set search_path to " + tenantId):

How `getConnection` switches	Reset in `releaseConnection`	`spring.datasource.hikari.schema`	Next borrower of backend N saw
`connection.setSchema(tenantId)`	none	not set	`globex`; `tenants` does not exist
`connection.setSchema(tenantId)`	none	`public`	`public`; `count(*)` = 2
`set search_path to …` as a statement	none	`public`	`globex`; `tenants` does not exist
`connection.setSchema(tenantId)`	`connection.setSchema("public")`	not set	`public`; `count(*)` = 2

HikariCP 7.0.2 restores the schema on return only if the connection's schema was changed through setSchema, which sets its dirty bit, and a default schema is configured; Boot leaves spring.datasource.hikari.schema unset. A statement that changes search_path is invisible to the pool in every case. So use setSchema, reset it in releaseConnection, and treat hikari.schema=public as a second net rather than the fix.

Flyway migrations for every tenant schema

Boot's Flyway migrates public before JPA starts. Each tenant schema needs the db/tenant migrations too, before any request reaches it, so the lab migrates them from a FlywayMigrationStrategy, which Boot calls in place of its own migrate() and before the EntityManagerFactory is built:

src/main/java/com/example/demo/tenant/TenantSchemaMigrator.java

@Component
public class TenantSchemaMigrator {
 
    private static final Logger log = LoggerFactory.getLogger(TenantSchemaMigrator.class);
 
    private final DataSource dataSource;
    private final JdbcClient jdbc;
 
    public TenantSchemaMigrator(DataSource dataSource) {
        this.dataSource = dataSource;
        // not the JdbcClient bean: that one waits for Flyway, and this class runs inside Flyway's migration
        this.jdbc = JdbcClient.create(dataSource);
    }
 
    public void migrateAll() {
        jdbc.sql("select id from public.tenants order by id").query(String.class).list()
                .forEach(this::migrate);
    }
 
    public void migrate(String tenantId) {
        MigrateResult result = Flyway.configure()
                .dataSource(dataSource)
                .schemas(tenantId)
                .locations("classpath:db/tenant")
                .load()
                .migrate();
        log.info("Tenant {}: {} migration(s) applied", tenantId, result.migrationsExecuted);
    }
}

src/main/java/com/example/demo/tenant/TenantFlywayConfig.java

@Configuration
class TenantFlywayConfig {
 
    @Bean
    FlywayMigrationStrategy migratePublicThenEveryTenant(TenantSchemaMigrator tenants) {
        return flyway -> {
            flyway.migrate();
            tenants.migrateAll();
        };
    }
}

The comment in the constructor is from a failed start: with the JdbcClient bean injected, startup stopped with Requested bean is currently in creation: Is there an unresolvable circular reference or an asynchronous initialization dependency?, because Boot makes the JdbcClient bean wait for Flyway while Flyway was waiting for this class. .schemas(tenantId) gives each tenant its own flyway_schema_history inside its schema and lets Flyway create the schema when it is missing. Starting against an empty database, with the log lines cut to their message:

Text

Migrating schema "public" to version "1 - create tenants"
Successfully applied 1 migration to schema "public", now at version v1 (execution time 00:00.003s)
Creating schema "acme" ...
Creating Schema History table "acme"."flyway_schema_history" ...
Current version of schema "acme": null
Migrating schema "acme" to version "1 - create catalog"
Successfully applied 1 migration to schema "acme", now at version v1 (execution time 00:00.003s)
Tenant acme: 1 migration(s) applied
Creating schema "globex" ...
Creating Schema History table "globex"."flyway_schema_history" ...
Current version of schema "globex": null
Migrating schema "globex" to version "1 - create catalog"
Successfully applied 1 migration to schema "globex", now at version v1 (execution time 00:00.003s)
Tenant globex: 1 migration(s) applied

A second start printed Schema "acme" is up to date. No migration necessary. for each tenant. The loop runs inside startup, one schema after another, so startup time grows with the number of tenants, and an exception from any one schema's migration leaves the strategy and stops the application; with hundreds of tenants the loop usually moves to a deployment step of its own.

Adding a tenant at runtime

A new customer is a row in public.tenants, a migrated schema and a refreshed registry. The endpoint lives under /api/admin/, which the tenant filter skips:

src/main/java/com/example/demo/tenant/TenantAdminController.java

@RestController
@RequestMapping("/api/admin/tenants")
class TenantAdminController {
 
    record CreateTenantRequest(@NotBlank @Pattern(regexp = "[a-z][a-z0-9_]{1,29}") String id,
                               @NotBlank String name) {
    }
 
    private final JdbcClient jdbc;
    private final TenantSchemaMigrator migrator;
    private final TenantRegistry registry;
 
    TenantAdminController(JdbcClient jdbc, TenantSchemaMigrator migrator, TenantRegistry registry) {
        this.jdbc = jdbc;
        this.migrator = migrator;
        this.registry = registry;
    }
 
    @PostMapping
    ResponseEntity<CreateTenantRequest> create(@Valid @RequestBody CreateTenantRequest request) {
        jdbc.sql("insert into public.tenants (id, name) values (?, ?)")
                .params(request.id(), request.name())
                .update();
        migrator.migrate(request.id());
        registry.refresh();
        return ResponseEntity.created(URI.create("/api/admin/tenants/" + request.id())).body(request);
    }
}

The @Pattern keeps a tenant id a plain lowercase identifier, since it becomes a schema name. Like the tenant header, this endpoint has no authentication in the lab. Before, during and after:

Bash

curl -s -H 'X-Tenant-Id: initech' http://localhost:8212/api/products
curl -i -s -H 'Content-Type: application/json' -d '{"id":"initech","name":"Initech"}' http://localhost:8212/api/admin/tenants
curl -i -s -H 'X-Tenant-Id: initech' http://localhost:8212/api/products

JSON

{"detail":"Unknown tenant 'initech'.","instance":"/api/products","status":400,"title":"Invalid tenant"}

Http

HTTP/1.1 201 
Location: /api/admin/tenants/initech
Content-Type: application/json
Transfer-Encoding: chunked
Date: Fri, 18 Sep 2026 07:26:00 GMT
 
{"id":"initech","name":"Initech"}

Http

HTTP/1.1 200 
Content-Type: application/json
Content-Length: 2
Date: Fri, 18 Sep 2026 07:26:00 GMT
 
[]

The migration in the server log, three of its lines:

Text

2026-09-18T14:26:00.043+07:00  INFO 19683 --- [demo] [nio-8212-exec-2] o.f.core.internal.database.base.Schema   : Creating schema "initech" ...
2026-09-18T14:26:00.065+07:00  INFO 19683 --- [demo] [nio-8212-exec-2] o.f.core.internal.command.DbMigrate      : Migrating schema "initech" to version "1 - create catalog"
2026-09-18T14:26:00.073+07:00  INFO 19683 --- [demo] [nio-8212-exec-2] c.e.demo.tenant.TenantSchemaMigrator     : Tenant initech: 1 migration(s) applied

No restart: the request thread created the schema, and the next request for initech got an empty catalogue instead of a 400.

Hibernate 7.1 TenantSchemaMapper: the schema switch without a provider

Hibernate 7.1 added an incubating shortcut for exactly this provider, hibernate.multi_tenant.schema_mapper. With a TenantSchemaMapper, Hibernate keeps using the ordinary connection provider, calls setSchema on each connection it acquires, and restores the schema the connection had before it releases it:

src/main/java/com/example/demo/tenant/TenantSchemas.java

@Component
public class TenantSchemas implements TenantSchemaMapper<String>, HibernatePropertiesCustomizer {
 
    @Override
    public String schemaName(String tenantId) {
        return tenantId;
    }
 
    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put(MultiTenancySettings.MULTI_TENANT_SCHEMA_MAPPER, this);
    }
}

With this bean in place of SchemaPerTenantConnectionProvider, the same reuse run:

Text

>>> JdbcClient, before any tenant: select current_schema()
    backend 426, schema public
>>> findAll() as globex, then the backend it ran on
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 order by p1_0.id
    [1:KB-01 35.00, 2:MN-01 229.00, 3:MN-02 159.00] on backend 426, schema globex
>>> JdbcClient, no tenant: select current_schema()
    backend 426, schema public
>>> JdbcClient, no tenant: select count(*) from tenants
    2

The per-tenant queries returned the same rows as with the provider, and the missing tenant failed with the same relation "products" does not exist. It is marked @Incubating, so its contract may still change; the provider is the stable SPI.

Database per tenant: one connection pool per tenant

The routing mechanics, choosing a DataSource per call with AbstractRoutingDataSource and keeping reads and writes apart, are the subject of article 10. Hibernate has its own hook for the tenant case: AbstractDataSourceBasedMultiTenantConnectionProviderImpl asks for a DataSource per tenant id. This one keeps a HikariCP pool per tenant, created on first use, with Flyway run against it:

src/main/java/com/example/demo/tenant/DatabasePerTenantConnectionProvider.java

@Component
public class DatabasePerTenantConnectionProvider
        extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl<String>
        implements HibernatePropertiesCustomizer, DisposableBean {
 
    private final DataSource controlDataSource;
    private final TenantRegistry registry;
    private final String urlTemplate;
    private final String username;
    private final String password;
    private final Map<String, HikariDataSource> pools = new ConcurrentHashMap<>();
 
    public DatabasePerTenantConnectionProvider(DataSource controlDataSource, TenantRegistry registry,
                                               @Value("${app.tenants.url-template}") String urlTemplate,
                                               @Value("${spring.datasource.username}") String username,
                                               @Value("${spring.datasource.password}") String password) {
        this.controlDataSource = controlDataSource;
        this.registry = registry;
        this.urlTemplate = urlTemplate;
        this.username = username;
        this.password = password;
    }
 
    @Override
    protected DataSource selectAnyDataSource() {
        return controlDataSource;
    }
 
    @Override
    protected DataSource selectDataSource(String tenantId) {
        if (!registry.exists(tenantId)) {
            throw new IllegalStateException("No database for tenant '" + tenantId + "'");
        }
        return pools.computeIfAbsent(tenantId, this::createPool);
    }
 
    private HikariDataSource createPool(String tenantId) {
        HikariDataSource pool = new HikariDataSource();
        pool.setPoolName("tenant-" + tenantId);
        pool.setJdbcUrl(urlTemplate.formatted(tenantId));
        pool.setUsername(username);
        pool.setPassword(password);
        Flyway.configure().dataSource(pool).locations("classpath:db/tenant").load().migrate();
        return pool;
    }
 
    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        hibernateProperties.put(MultiTenancySettings.MULTI_TENANT_CONNECTION_PROVIDER, this);
    }
 
    @Override
    public void destroy() {
        pools.values().forEach(HikariDataSource::close);
    }
}

spring.datasource.url points at a control database, dbcontrol, which holds tenants, and app.tenants.url-template=jdbc:postgresql://localhost:5512/tenant_%s names each tenant's database. The lab ran findAll() once per tenant, waited five seconds, then counted the server's connections per database in pg_stat_activity. With Acme and Globex:

Text

>>> findAll() as acme
tenant-acme - Starting...
tenant-acme - Added connection org.postgresql.jdbc.PgConnection@6eb06667
tenant-acme - Start completed.
Database: jdbc:postgresql://localhost:5512/tenant_acme (PostgreSQL 18.6)
Successfully validated 1 migration (execution time 00:00.002s)
Current version of schema "public": 1
Schema "public" is up to date. No migration necessary.
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku from products p1_0 order by p1_0.id
    [1:KB-01 89.00, 2:KB-02 59.00, 3:MS-01 24.50, 4:MS-02 49.90] (4 rows)
>>> connections per database, from pg_stat_activity
    [dbcontrol=10, tenant_acme=10, tenant_globex=10] (3 rows)
>>> total client connections
    30

The native findPricedAtLeastNative(0) returned Acme's four rows here too, from tenant_acme. Thirty connections for two tenants and a control database. A HikariCP pool defaults to maximumPoolSize 10 and a minimumIdle equal to it, so every pool fills to ten idle connections whether its tenant is busy or not. With eight more tenant databases, ten tenants in all:

Text

>>> connections per database, from pg_stat_activity
    [dbcontrol=10, tenant_acme=10, tenant_globex=10, tenant_t01=10, tenant_t02=10, tenant_t03=10, tenant_t04=10, tenant_t05=10, tenant_t06=8, tenant_t07=7, tenant_t08=5] (11 rows)
>>> total client connections
    100

The server stopped at its max_connections of 100, and the last three pools never filled. PostgreSQL logged the refusals, 27 of them in that run:

Text

2026-09-18 07:27:56.408 UTC [697] FATAL:  sorry, too many clients already

The application log had nothing: HikariCP 7.0.2 logs a failed background fill at DEBUG. Every request still worked, because each pool had at least five connections, but nothing else could connect to that server while the application ran. A pool per tenant multiplies connections by the number of tenants. For real numbers the pools need maximumPoolSize of 2 or 3 and a minimumIdle of 0, a connection pooler such as PgBouncer in front of the server, and tenant databases spread across several servers.

PostgreSQL row-level security as a second line of defence

Back to the discriminator variant and its seven-row native query. Row-level security moves the tenant predicate into the database: a policy on the table filters every statement, native SQL and JdbcClient included, against a value the application sets on the connection.

A policy on current_setting('app.tenant_id')

The application needs a database role that does not own the tables; the next subsection shows why. It is created once, outside Flyway:

Bash

docker exec sba-a12-pg psql -U demo -d demo -c "create role catalog_app login password 'catalog_app'"

The migration grants it the data and turns the policies on:

src/main/resources/db/migration/V4__row_level_security.sql

grant select, insert, update, delete on tenants, categories, products to catalog_app;
 
alter table categories enable row level security;
alter table products enable row level security;
 
create policy tenant_isolation on categories
    using (tenant_id = current_setting('app.tenant_id', true));
 
create policy tenant_isolation on products
    using (tenant_id = current_setting('app.tenant_id', true));

app.tenant_id is a custom setting, and the second argument of current_setting, missing_ok, makes it return null instead of raising an error when the setting was never defined. A policy with only using applies the same condition to rows being written: as catalog_app with app.tenant_id set to acme, update products set tenant_id = 'globex' where id = 2 failed with new row violates row-level security policy for table "products".

Setting the tenant per transaction with set_config

set_config(name, value, is_local) with is_local = true lasts until the end of the current transaction. In psql, on one connection:

Text

before any set: NULL
BEGIN
inside, is_local=true: acme
COMMIT
after commit: ''
is_local=false: acme
next statement: acme

The transaction-local value was gone after the commit: an empty string, not null, once the setting had been defined on that session, and an empty string matches no tenant either. The session value stayed for the next statement, which on a pooled connection means the next borrower: the same trap as the schema. So the value is set inside every transaction, right after it begins. JpaTransactionManager has a hook for that:

src/main/java/com/example/demo/tenant/TenantAwareTransactionManager.java

package com.example.demo.tenant;
 
import java.sql.PreparedStatement;
import java.util.Objects;
 
import jakarta.persistence.EntityManagerFactory;
 
import org.hibernate.Session;
 
import org.springframework.orm.jpa.EntityManagerHolder;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.support.TransactionSynchronizationManager;
 
public class TenantAwareTransactionManager extends JpaTransactionManager {
 
    public TenantAwareTransactionManager(EntityManagerFactory emf) {
        super(emf);
    }
 
    @Override
    protected void doBegin(Object transaction, TransactionDefinition definition) {
        super.doBegin(transaction, definition);
        String tenantId = Objects.requireNonNullElse(TenantContext.get(), "");
        EntityManagerHolder holder =
                (EntityManagerHolder) TransactionSynchronizationManager.getResource(obtainEntityManagerFactory());
        holder.getEntityManager().unwrap(Session.class).doWork(connection -> {
            try (PreparedStatement statement =
                         connection.prepareStatement("select set_config('app.tenant_id', ?, true)")) {
                statement.setString(1, tenantId);
                statement.execute();
            }
        });
    }
}

src/main/java/com/example/demo/tenant/RowLevelSecurityConfig.java

@Configuration
@Profile("rls")
class RowLevelSecurityConfig {
 
    @Bean
    JpaTransactionManager transactionManager(EntityManagerFactory emf) {
        return new TenantAwareTransactionManager(emf);
    }
}

super.doBegin opens the transaction and binds the EntityManager to the thread, so the statement runs on the connection the transaction will use. Boot's own transactionManager bean backs off when another TransactionManager exists. The rls profile switches the application to the new role and keeps Flyway on the owner:

src/main/resources/application-rls.properties

spring.datasource.username=catalog_app
spring.datasource.password=catalog_app
spring.flyway.user=demo
spring.flyway.password=demo

The native query that leaked, now as a non-owner role

The same lab calls as acme, connected as catalog_app, with a pool of one connection so that every step reuses the same session:

Text

>>> current_user
    catalog_app
>>> findPricedAtLeastNative(0) as acme (native @Query)
select * from products where price >= ? order by id
binding parameter (1:NUMERIC) <- [0]
    [] (0 rows)
>>> findPricedAtLeastNative(0) inside a transaction as acme
select * from products where price >= ? order by id
binding parameter (1:NUMERIC) <- [0]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90] (4 rows)
>>> findByPriceLessThan(50) as acme (derived)
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and p1_0.price<?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:NUMERIC) <- [50]
    [] (0 rows)
>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? order by p1_0.id
binding parameter (1:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90] (4 rows)
>>> JdbcClient select inside a transaction as acme
    [1:acme:KB-01, 2:acme:KB-02, 3:acme:MS-01, 4:acme:MS-02] (4 rows)
>>> JdbcClient select outside a transaction as acme
    [] (0 rows)
>>> current_setting('app.tenant_id', true) outside a transaction
    ''
>>> JdbcClient insert of a globex row inside a transaction as acme
!!! org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into products (tenant_id, name, sku, price, category_id) values ('globex', 'Planted keyboard', 'KB-97', 1.00, 3)]
!!! org.postgresql.util.PSQLException: ERROR: new row violates row-level security policy for table "products"

Inside a transaction, the native query that returned seven rows returned Acme's four, and so did JdbcClient. Hibernate's @TenantId predicate and the policy now both hold, independently.
Outside a transaction the policy saw an empty app.tenant_id and returned nothing. That caught two methods: the native query and findByPriceLessThan, a derived query with a correct tenant_id predicate that still returned zero rows. Spring Data applies @Transactional(readOnly = true) to the CrudRepository methods it implements, such as findAll, but query methods declared on the interface run without a transaction unless something around them starts one. In an application the @Transactional service method does; a query method called outside one fails closed with an empty result.
An insert for another tenant was rejected by the database, although the SQL named globex explicitly.

Why does row-level security filter nothing for the table owner?

The same run connected as demo, the user the Docker image created and the owner of the tables, three of its steps:

Text

>>> current_user
    demo
>>> findPricedAtLeastNative(0) inside a transaction as acme
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90, 5:globex:KB-01 35.00, 6:globex:MN-01 229.00, 7:globex:MN-02 159.00] (7 rows)
>>> JdbcClient select outside a transaction as acme
    [1:acme:KB-01, 2:acme:KB-02, 3:acme:MS-01, 4:acme:MS-02, 5:globex:KB-01, 6:globex:MN-01, 7:globex:MN-02] (7 rows)
>>> JdbcClient insert of a globex row inside a transaction as acme
    1

The policies were enabled, set_config ran, and they changed nothing. Two separate exemptions apply, and a psql session inside a transaction that it rolls back shows both, including FORCE ROW LEVEL SECURITY:

rls-roles.sql

select rolname, rolsuper, rolbypassrls from pg_roles where rolname in ('demo', 'catalog_app') order by rolname;
begin;
select set_config('app.tenant_id', 'acme', true);
select current_user, count(*) from products;
alter table products force row level security;
select current_user, count(*) from products;
create role catalog_owner;
alter table products owner to catalog_owner;
set local role catalog_owner;
select current_user, count(*) from products;
reset role;
alter table products no force row level security;
set local role catalog_owner;
select current_user, count(*) from products;
rollback;

The result tables, without psql's command tags and row counts:

Text

   rolname   | rolsuper | rolbypassrls 
-------------+----------+--------------
 catalog_app | f        | f
 demo        | t        | t
 
 current_user | count 
--------------+-------
 demo         |     7
 
 current_user | count 
--------------+-------
 demo         |     7
 
 current_user  | count 
---------------+-------
 catalog_owner |     4
 
 current_user  | count 
---------------+-------
 catalog_owner |     7

A superuser, or a role with BYPASSRLS, is never filtered. POSTGRES_USER in the official image is a superuser, so demo saw seven rows before and after FORCE. A lab that tests row-level security as the image's default user sees it do nothing.
A table owner is not filtered unless the table has FORCE ROW LEVEL SECURITY. catalog_owner, an ordinary role that owned products inside the transaction, saw four rows with FORCE and seven after NO FORCE.
A role that neither owns the table nor has BYPASSRLS is filtered with ENABLE alone. That is catalog_app, and that is why the application connects as it while Flyway keeps the owner.

Soft delete with Hibernate @SoftDelete

A soft delete keeps the row and marks it, so an order line can still show the product it sold and an admin can undo a mistake. Hibernate 6.4 added @SoftDelete for it, still marked @Incubating in 7.4.5: the annotated entity's delete becomes an update, and every read gets a predicate, the same way @TenantId works.

@SoftDelete on Product and Category

The softdelete variant keeps @TenantId and adds @SoftDelete to both entities. Its migration gives both tables deleted boolean not null default false, the column the default strategy expects:

src/main/java/com/example/demo/product/Product.java

import org.hibernate.annotations.SoftDelete; 
import org.hibernate.annotations.TenantId;
 
@Entity
@Table(name = "products")
@SoftDelete
public class Product {
 
    // id, tenantId, name, sku and price unchanged
 
    @ManyToOne(fetch = FetchType.LAZY, optional = false) 
    @ManyToOne(optional = false) 
    @JoinColumn(name = "category_id", nullable = false)
    private Category category;

src/main/java/com/example/demo/product/Category.java

@Entity
@Table(name = "categories")
@SoftDelete
public class Category {
 
    // id, tenantId and name unchanged
 
    @OneToMany(mappedBy = "category") 
    @OrderBy("id") 
    private List<Product> products = new ArrayList<>();

The LAZY had to go. With it, the application did not start:

Text

Caused by: org.hibernate.metamodel.UnsupportedMappingException: To-one attribute (com.example.demo.product.Product.category) cannot be mapped as LAZY as its associated entity is defined with @SoftDelete

Hibernate refuses a lazy proxy for a target that may be soft-deleted, because a proxy promises a row it has not checked. Every to-one pointing at a @SoftDelete entity is loaded eagerly, which the SQL below shows, and which is a real cost for an entity that many others reference.

The SQL for delete, find, JPQL and a collection

As acme, deleting product 3 and reading around it:

Text

>>> findById(3) as acme
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,c1_0.tenant_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false where p1_0.deleted=false and p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:BIGINT) <- [3]
binding parameter (3:VARCHAR) <- [acme]
    Optional[3:acme:MS-01 24.50]
>>> delete product 3 (MS-01) as acme
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,c1_0.tenant_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false where p1_0.deleted=false and p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:BIGINT) <- [3]
binding parameter (3:VARCHAR) <- [acme]
update products set deleted=true where id=? and deleted=false
binding parameter (1:BIGINT) <- [3]
    deleted
>>> findById(3) as acme
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,c1_0.tenant_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false where p1_0.deleted=false and p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:BIGINT) <- [3]
binding parameter (3:VARCHAR) <- [acme]
    Optional.empty
>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and p1_0.deleted=false order by p1_0.id
binding parameter (1:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 4:acme:MS-02 49.90] (3 rows)
>>> findInCategory("Mice") as acme (JPQL)
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false where p1_0.tenant_id = ? and c1_0.name=? and p1_0.deleted=false order by p1_0.price
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:VARCHAR) <- [acme]
binding parameter (3:VARCHAR) <- [Mice]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
    [4:acme:MS-02 49.90] (1 rows)
>>> category 2 and its products collection as acme
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
select p1_0.category_id,p1_0.id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.category_id=? and p1_0.deleted=false order by p1_0.id
binding parameter (1:BIGINT) <- [2]
    2:acme:Mice -> [4:acme:MS-02 49.90]
>>> deleteInBulkBySku("MS-02") as acme (bulk JPQL delete)
update products p1_0 set deleted=true where p1_0.sku=? and p1_0.tenant_id = ? and p1_0.deleted=false
binding parameter (1:VARCHAR) <- [MS-02]
binding parameter (2:VARCHAR) <- [acme]
    1

delete became update products set deleted=true where id=? and deleted=false. The extra and deleted=false makes a second delete of the same row update nothing.
findById joins categories with an inner join, because optional = false and the target is soft-deletable, and the soft-deleted product came back as Optional.empty, the same as an id that never existed.
findAll has no join; the eager category arrived as one select per distinct category, each with both predicates. That is the N+1 pattern of Basics 28, forced by the mapping.
The collection Category.products skipped the deleted product with p1_0.deleted=false. Its where has no tenant_id: Hibernate loads a collection by the owner's key, and the owner was already loaded through the filter.
A JPQL delete, a bulk statement that bypasses the persistence context, still became an update ... set deleted=true. Nothing deleted a row physically.

Soft delete and tenancy in one statement

The JPQL query above carries four predicates from two annotations, none of them in the query string select p from Product p where p.category.name = :category:

SQL

select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id
from products p1_0
join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false
where p1_0.tenant_id = ? and c1_0.name=? and p1_0.deleted=false
order by p1_0.price

The joined entity gets both of its own in the on clause; where they land in the where varies, since findById above put deleted=false first and the tenant last. The two mechanisms compose without code, which also means both are invisible when reading the repository. The combination matters for indexes: every query on products now carries tenant_id = ? and deleted=false, so indexes that lead with tenant_id, and partial indexes where not deleted, match what every statement asks for.

What happens to a product whose category is soft-deleted?

Soft-deleting category 1, Keyboards, which still has two live products, then reading one of them:

Text

>>> delete category 1 (Keyboards) as acme
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
update categories set deleted=true where id=? and deleted=false
binding parameter (1:BIGINT) <- [1]
    deleted
>>> findById(1) as acme, a product in the deleted category
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,c1_0.tenant_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false where p1_0.deleted=false and p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:BIGINT) <- [1]
binding parameter (3:VARCHAR) <- [acme]
    Optional.empty
>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and p1_0.deleted=false order by p1_0.id
binding parameter (1:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
!!! org.springframework.orm.ObjectRetrievalFailureException: No row with the given identifier exists for entity [com.example.demo.product.Category with id '1']
!!! org.hibernate.ObjectNotFoundException: No row with the given identifier exists for entity [com.example.demo.product.Category with id '1']
>>> count() as acme
select count(*) from products p1_0 where p1_0.tenant_id = ? and p1_0.deleted=false
binding parameter (1:VARCHAR) <- [acme]
    4

Three answers for one live product. findById hid it, because the inner join found no live category. findAll failed for the whole list, because its separate eager select for category 1 found nothing where a non-optional association promised a row. count() joins nothing and still counted it. Soft-deleting a parent while live children point at it leaves the data in a state the mapping cannot represent, so the service has to refuse it, or soft-delete the children in the same transaction.

DELETED, ACTIVE and TIMESTAMP strategies

SoftDeleteType in Hibernate 7.4.5 has three values. The lab mapped one small entity per strategy, and the fragments below are from those runs:

`strategy`	Column it expects	Insert writes	`delete` sends	Reads add
`DELETED` (default)	`deleted boolean`	`deleted` = `false`	`set deleted=true where id=? and deleted=false`	`deleted=false`
`ACTIVE`	`active boolean`	`active` = `true`	`set active=false where id=? and active=true`	`active=true`
`TIMESTAMP`	`deleted timestamp(6)`	`deleted` = `null`	`set deleted=localtimestamp where id=? and deleted is null`	`deleted is null`

The annotation's columnName and converter attributes change the column name and the stored value. localtimestamp is evaluated in the session's time zone, which pgjdbc sets from the JVM: on a machine at UTC+7 the deleted row read 2026-09-18 14:31:50.689779, taken at 07:31 UTC, and the same run twelve minutes later with -Duser.timezone=UTC wrote 2026-09-18 07:43:44.578029. The TIMESTAMP strategy records when, in a timestamp without time zone whose meaning depends on the JVM that deleted the row.

What bypasses @SoftDelete: native SQL and JdbcClient

The same rule as for tenants: Hibernate adds nothing to SQL it does not generate. After deleting products 3 and 4:

Text

>>> findAllNative("acme") as acme (native @Query)
select * from products where tenant_id = ? order by id
binding parameter (1:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90] (4 rows)
>>> findPricedAtLeastNative(0) as acme (native @Query, every tenant)
select * from products where price >= ? order by id
binding parameter (1:NUMERIC) <- [0]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.deleted=false and c1_0.id=? and c1_0.tenant_id = ?
binding parameter (1:BIGINT) <- [3]
binding parameter (2:VARCHAR) <- [acme]
!!! org.springframework.orm.ObjectRetrievalFailureException: No row with the given identifier exists for entity [com.example.demo.product.Category with id '3']
!!! org.hibernate.ObjectNotFoundException: No row with the given identifier exists for entity [com.example.demo.product.Category with id '3']
>>> JdbcClient: acme rows with their deleted flag
    [1:KB-01 deleted=false, 2:KB-02 deleted=false, 3:MS-01 deleted=true, 4:MS-02 deleted=true] (4 rows)

The tenant-scoped native query returned the two deleted products as ordinary entities. The unscoped one is the discriminator leak meeting the forced eager load: it returned Globex's rows, Hibernate then loaded their category 3 through Acme's tenant filter, found nothing, and the whole query failed. JdbcClient sees the column as it is.

@SQLDelete and @SQLRestriction compared with @SoftDelete

Before 6.4, soft delete was written by hand: a custom delete statement and a fragment appended to every read. In Hibernate 7.4.5 that pair is @SQLDelete and @SQLRestriction. The restriction annotation arrived in 6.3, and @Where, which older examples use for the same job, is gone: hibernate-core 7.4.5 has no org.hibernate.annotations.Where class, so such an example does not compile. The sqldelete variant maps the same tables:

src/main/java/com/example/demo/product/Product.java

@Entity
@Table(name = "products")
@SQLDelete(sql = "update products set deleted = true where id = ?")
@SQLRestriction("deleted = false")
public class Product {
 
    // id, tenantId, name, sku and price as before
 
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "category_id", nullable = false)
    private Category category;
 
    private boolean deleted;

Category gets @SQLDelete(sql = "update categories set deleted = true where id = ?") and the same restriction. The same calls as in the @SoftDelete run:

Text

>>> findById(3) as acme
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ? and (p1_0.deleted = false)
binding parameter (1:BIGINT) <- [3]
binding parameter (2:VARCHAR) <- [acme]
    Optional[3:acme:MS-01 24.50]
>>> delete product 3 (MS-01) as acme
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ? and (p1_0.deleted = false)
binding parameter (1:BIGINT) <- [3]
binding parameter (2:VARCHAR) <- [acme]
update products set deleted = true where id = ?
binding parameter (1:BIGINT) <- [3]
    deleted
>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and (p1_0.deleted = false) order by p1_0.id
binding parameter (1:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 4:acme:MS-02 49.90] (3 rows)
>>> findInCategory("Mice") as acme (JPQL)
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and (c1_0.deleted = false) and (c1_0.deleted = false) where p1_0.tenant_id = ? and (p1_0.deleted = false) and c1_0.name=? order by p1_0.price
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:VARCHAR) <- [acme]
binding parameter (3:VARCHAR) <- [Mice]
    [4:acme:MS-02 49.90] (1 rows)
>>> category 2 and its products collection as acme
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.id=? and c1_0.tenant_id = ? and (c1_0.deleted = false)
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
select p1_0.category_id,p1_0.id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.category_id=? and (p1_0.deleted = false) order by p1_0.id
binding parameter (1:BIGINT) <- [2]
    2:acme:Mice -> [4:acme:MS-02 49.90]
>>> JPQL: select p from Product p where p.deleted = true
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and (p1_0.deleted = false) and p1_0.deleted=true
binding parameter (1:VARCHAR) <- [acme]
    [] (0 rows)
>>> deleteInBulkBySku("MS-02") as acme (bulk JPQL delete)
delete from products p1_0 where p1_0.sku=? and p1_0.tenant_id = ? and (p1_0.deleted = false)
binding parameter (1:VARCHAR) <- [MS-02]
binding parameter (2:VARCHAR) <- [acme]
    1
>>> JdbcClient: acme rows with their deleted flag
    [1:KB-01 deleted=false, 2:KB-02 deleted=false, 3:MS-01 deleted=true] (3 rows)

And the category test, with the association still LAZY:

Text

>>> findById(1) as acme, then its category name
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.id=? and p1_0.tenant_id = ? and (p1_0.deleted = false)
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
select c1_0.id,c1_0.name,c1_0.tenant_id from categories c1_0 where c1_0.id=? and c1_0.tenant_id = ? and (c1_0.deleted = false)
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [acme]
!!! jakarta.persistence.EntityNotFoundException: No row with the given identifier exists for entity [com.example.demo.product.Category with id '1']
!!! org.hibernate.ObjectNotFoundException: No row with the given identifier exists for entity [com.example.demo.product.Category with id '1']
>>> findAll() as acme
select p1_0.id,p1_0.category_id,p1_0.deleted,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 where p1_0.tenant_id = ? and (p1_0.deleted = false) order by p1_0.id
binding parameter (1:VARCHAR) <- [acme]
    [1:acme:KB-01 89.00, 2:acme:KB-02 59.00, 3:acme:MS-01 24.50, 4:acme:MS-02 49.90] (4 rows)

@SoftDelete and @SQLDelete with @SQLRestriction side by side: the SQL each sends for delete, find and a bulk JPQL delete, what bypasses both, and the partial unique index

The delete statement is yours, verbatim: update products set deleted = true where id = ?, without the and deleted=false guard that @SoftDelete adds. Its placeholders have to match what Hibernate binds, here only the id.
The restriction is a SQL fragment, pasted in parentheses. In the JPQL join Hibernate 7.4.5 pasted it twice, and (c1_0.deleted = false) and (c1_0.deleted = false): harmless, and a reminder that Hibernate treats it as text.
A bulk JPQL delete is a real delete. @SQLDelete replaces only the statement for one entity. deleteInBulkBySku("MS-02") sent delete from products, and the row was gone from the table; under @SoftDelete the same method sent an update.
The flag can be mapped, which @SoftDelete does not allow, but the restriction still applies to every query, so where p.deleted = true returned nothing.
LAZY still works. The price is the same broken reference as before, discovered later: findAll succeeded and the proxy failed with EntityNotFoundException when the code touched the category.

Native SQL and JdbcClient bypass both approaches in the same way.

Soft delete and unique constraints: the partial unique index

products has unique (tenant_id, sku). A soft-deleted row keeps its SKU, so the SKU cannot be used again. In the softdelete variant, deleting KB-02 and creating a new KB-02:

Text

>>> delete product 2 (KB-02) as acme
update products set deleted=true where id=? and deleted=false
    deleted
>>> save(new Product("Compact keyboard v2", "KB-02", 64.00, Keyboards)) as acme
insert into products (category_id,name,price,sku,tenant_id,deleted) values (?,?,?,?,?,false)
HHH000247: ErrorCode: 0, SQLState: 23505
ERROR: duplicate key value violates unique constraint "products_tenant_sku_uk"
  Detail: Key (tenant_id, sku)=(acme, KB-02) already exists.
!!! org.springframework.dao.DataIntegrityViolationException: could not execute statement [ERROR: duplicate key value violates unique constraint "products_tenant_sku_uk" ...
!!! org.hibernate.exception.ConstraintViolationException: could not execute statement [ERROR: duplicate key value violates unique constraint "products_tenant_sku_uk" ...
!!! org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "products_tenant_sku_uk" ...
>>> JdbcClient: acme rows with sku KB-02
    [2:KB-02 59.00 deleted=true] (1 rows)

The product the user deleted still blocks the one they are creating. A unique index that covers only live rows states the actual rule:

src/main/resources/db/migration/V3__unique_sku_among_live_rows.sql

alter table products drop constraint products_tenant_sku_uk;
 
create unique index products_tenant_sku_live_uk on products (tenant_id, sku) where not deleted;

The same steps after V3:

Text

>>> save(new Product("Compact keyboard v2", "KB-02", 64.00, Keyboards)) as acme
insert into products (category_id,name,price,sku,tenant_id,deleted) values (?,?,?,?,?,false)
binding parameter (1:BIGINT) <- [1]
binding parameter (2:VARCHAR) <- [Compact keyboard v2]
binding parameter (3:NUMERIC) <- [64.00]
binding parameter (4:VARCHAR) <- [KB-02]
binding parameter (5:VARCHAR) <- [acme]
    8:acme:KB-02 64.00
>>> JdbcClient: acme rows with sku KB-02
    [2:KB-02 59.00 deleted=true, 8:KB-02 64.00 deleted=false] (2 rows)

Two rows share the SKU, one deleted, one live, and a second live KB-02 would still be rejected. The insert shows how @SoftDelete writes the flag: deleted is in the column list with the literal false, not a bound parameter.

With the TIMESTAMP strategy the column is null for live rows, and a plain unique (tenant_id, sku, deleted) does not help, because PostgreSQL treats nulls as distinct. Three temporary tables in psql, one per constraint:

timestamp-unique.sql

create temp table skus_plain (tenant_id text, sku text, deleted timestamp(6), unique (tenant_id, sku, deleted));
insert into skus_plain values ('acme', 'KB-02', null);
insert into skus_plain values ('acme', 'KB-02', null);
select count(*) as live_kb02_rows from skus_plain where deleted is null;
create temp table skus_nnd (tenant_id text, sku text, deleted timestamp(6), unique nulls not distinct (tenant_id, sku, deleted));
insert into skus_nnd values ('acme', 'KB-02', localtimestamp - interval '1 day');
insert into skus_nnd values ('acme', 'KB-02', localtimestamp);
insert into skus_nnd values ('acme', 'KB-02', null);
insert into skus_nnd values ('acme', 'KB-02', null);
create temp table skus_partial (tenant_id text, sku text, deleted timestamp(6));
create unique index skus_partial_live_uk on skus_partial (tenant_id, sku) where deleted is null;
insert into skus_partial values ('acme', 'KB-02', localtimestamp - interval '1 day');
insert into skus_partial values ('acme', 'KB-02', localtimestamp);
insert into skus_partial values ('acme', 'KB-02', null);
insert into skus_partial values ('acme', 'KB-02', null);

Text

CREATE TABLE
INSERT 0 1
INSERT 0 1
 live_kb02_rows 
----------------
              2
(1 row)
 
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "skus_nnd_tenant_id_sku_deleted_key"
DETAIL:  Key (tenant_id, sku, deleted)=(acme, KB-02, null) already exists.
CREATE TABLE
CREATE INDEX
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "skus_partial_live_uk"
DETAIL:  Key (tenant_id, sku)=(acme, KB-02) already exists.

The plain constraint accepted two live KB-02 rows. unique nulls not distinct, available since PostgreSQL 15, took two deleted versions with different timestamps and rejected the second live one. The partial index where deleted is null did the same without touching the timestamp: two deleted versions in, the second live row out.

Listing and restoring soft-deleted rows

An admin screen needs the rows @SoftDelete hides. The flag is not an attribute of the entity, so JPQL cannot name it:

Text

>>> JPQL: select p from Product p where p.deleted = true
!!! java.lang.IllegalArgumentException: org.hibernate.query.sqm.UnknownPathException: Could not resolve attribute 'deleted' of 'com.example.demo.product.Product' [select p from Product p where p.deleted = true]
!!! org.hibernate.query.sqm.UnknownPathException: Could not resolve attribute 'deleted' of 'com.example.demo.product.Product'
!!! org.hibernate.query.sqm.PathElementException: Could not resolve attribute 'deleted' of 'com.example.demo.product.Product'

Hibernate 7.4.5 has no switch to turn the restriction off for one query. A native query works and bypasses @TenantId along with it, so it must name the tenant itself. The lab used a second entity instead: a read-only view of the same table, with @TenantId and without @SoftDelete, where deleted is an ordinary attribute:

src/main/java/com/example/demo/product/ProductRecord.java

// read-only view of every row, deleted or not, still scoped to the current tenant
@Entity
@Immutable
@Table(name = "products")
public class ProductRecord {
 
    @Id
    private Long id;
 
    @TenantId
    @Column(name = "tenant_id")
    private String tenantId;
 
    private String sku;
 
    private BigDecimal price;
 
    private boolean deleted;
 
    // getters
}

src/main/java/com/example/demo/product/ProductRecordRepository.java

public interface ProductRecordRepository extends JpaRepository<ProductRecord, Long> {
 
    List<ProductRecord> findByDeletedTrueOrderById();
 
    @Modifying
    @Query(value = "update products set deleted = false where id = :id and tenant_id = :tenantId", nativeQuery = true)
    int restore(Long id, String tenantId);
}

After Acme deleted product 3 and Globex deleted product 7:

Text

>>> findByDeletedTrueOrderById() on ProductRecord as acme
select pr1_0.id,pr1_0.deleted,pr1_0.price,pr1_0.sku,pr1_0.tenant_id from products pr1_0 where pr1_0.tenant_id = ? and pr1_0.deleted=true order by pr1_0.id
binding parameter (1:VARCHAR) <- [acme]
    [3:acme:MS-01 (deleted)] (1 rows)
>>> restore(3, "acme") as acme (native update)
update products set deleted = false where id = ? and tenant_id = ?
binding parameter (1:BIGINT) <- [3]
binding parameter (2:VARCHAR) <- [acme]
    1
>>> findById(3) as acme
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,c1_0.tenant_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.tenant_id from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id and c1_0.tenant_id = ? and c1_0.deleted=false where p1_0.deleted=false and p1_0.id=? and p1_0.tenant_id = ?
binding parameter (1:VARCHAR) <- [acme]
binding parameter (2:BIGINT) <- [3]
binding parameter (3:VARCHAR) <- [acme]
    Optional[3:acme:MS-01 24.50]

The listing kept the tenant predicate and returned only Acme's deleted row, not Globex's product 7. @Immutable stops the view from writing, so the restore is a native update, and it names the tenant because nothing else will; with row-level security enabled, the policy would enforce it anyway. Restoring runs into the partial index from the previous section when a live row took the SKU in the meantime:

Text

>>> restore(2, "acme") while a live KB-02 exists
update products set deleted = false where id = ? and tenant_id = ?
binding parameter (1:BIGINT) <- [2]
binding parameter (2:VARCHAR) <- [acme]
HHH000247: ErrorCode: 0, SQLState: 23505
ERROR: duplicate key value violates unique constraint "products_tenant_sku_live_uk"
  Detail: Key (tenant_id, sku)=(acme, KB-02) already exists.

That is the index doing its job; an admin endpoint would answer it with a 409, the series' status for a conflict, and let a person choose which product keeps the SKU.

Choosing a multi-tenancy strategy and a soft-delete approach

For tenancy, the lab's runs, strategy by strategy:

Strategy	Isolation enforced by	Cost per tenant	Migrations	Noisy neighbour, backup and restore of one tenant	Failure modes seen in the lab
Discriminator, `@TenantId`	A predicate in every statement Hibernate generates	A few rows, no new connections	One schema, one Flyway history	Shared tables and indexes; one tenant's rows come out with `copy (select … where tenant_id = 'acme')`	Native SQL and `JdbcClient` returned all 7 rows; a foreign key accepted another tenant's category; a `null` tenant stopped startup
Schema per tenant	The connection's `search_path`	A schema and its tables; the pool is shared	Flyway once per schema, at startup and on creation	Shared server and pool; `pg_dump -n acme` dumps one tenant with its Flyway history	A pooled connection kept `globex` without a reset; `ddl-auto=validate` failed
Database per tenant	A separate database and pool	A database and 10 connections at HikariCP's defaults	Flyway once per database	Databases can move to separate servers and be restored alone	Ten tenants hit `max_connections` 100, logged by PostgreSQL only
Row-level security on a discriminator	PostgreSQL, for every statement including native SQL	A policy per table	Policies and grants in migrations	As the discriminator	Nothing filtered for the owner or a superuser; empty results outside a transaction

A discriminator is the cheapest per tenant and fits many small tenants; add row-level security as soon as native SQL exists. Schemas trade a per-schema migration loop for isolation that unqualified native SQL cannot escape; a statement that names another schema, globex.products, still can. A database per tenant is for few, large tenants that pay for separate backups, placement or noise isolation, and it needs pools sized for it.

For soft delete:

Approach	`delete` sends	Bulk JPQL `delete`	To-one to a deleted row	Flag in JPQL	Unique SKU among live rows
`@SoftDelete`, `DELETED` or `ACTIVE`	`update … set deleted=true where id=? and deleted=false`	Converted to `update`	Must be eager; `findById` hid the child, `findAll` threw	Not an attribute	Partial index `where not deleted`
`@SoftDelete(strategy = TIMESTAMP)`	`update … set deleted=localtimestamp where id=? and deleted is null`	Converted to `update`	As above	Not an attribute	Partial index `where deleted is null`, or `unique nulls not distinct`
`@SQLDelete` + `@SQLRestriction`	Your SQL, verbatim	A physical `delete`	Lazy allowed; `EntityNotFoundException` on access	Mappable, but still restricted	Partial index `where not deleted`

All three leave native SQL and JdbcClient unfiltered. @SoftDelete is the one that also covers bulk deletes, which makes it the safer default on Hibernate 7.4.5; @SQLDelete with @SQLRestriction fits when the delete statement must do more than flip one column, since its SQL is entirely yours.

FAQ

How do I implement multi-tenancy in Spring Boot with Hibernate 7?

Put the current tenant in a ThreadLocal from a filter, cleared in finally, and give Hibernate a CurrentTenantIdentifierResolver bean that returns it, with a sentinel instead of null. For a shared schema, annotate a column with @TenantId; for a schema or database per tenant, register a MultiTenantConnectionProvider through a HibernatePropertiesCustomizer, or on Hibernate 7.1 and later a TenantSchemaMapper. Boot 4.1.1 needs no other property: Hibernate infers the strategy from the mapping.

Does @TenantId filter native queries and JdbcClient?

No. Hibernate added the tenant predicate to find, JPQL, derived queries, Specifications, count queries and bulk @Modifying updates, but a native @Query and JdbcClient returned all seven rows of both tenants. Scope every native query by hand, or enable PostgreSQL row-level security with the application connected as a role that does not own the tables.

Why does Spring Boot fail with "SessionFactory configured for multi-tenancy, but no tenant identifier specified"?

Spring Data prepares derived queries at startup, which opens a Hibernate session, and the resolver returned null because no request had set a tenant. Return a value that is not a tenant, such as _none_: it matches no rows and fails foreign keys on insert.

Should a schema-per-tenant provider use setSchema or SET search_path?

connection.setSchema. pgjdbc turns it into SET SESSION search_path TO 'tenant' with the name quoted, and HikariCP records the change; a set search_path statement is invisible to the pool. Either way, reset the schema in releaseConnection, or the next borrower gets the last tenant's schema; HikariCP resets it only when spring.datasource.hikari.schema is set and the change went through setSchema.

Why does PostgreSQL row-level security return all rows?

Because the connection's role is a superuser, has BYPASSRLS, or owns the table. The Docker image's POSTGRES_USER is a superuser and saw all seven rows even with FORCE ROW LEVEL SECURITY; an ordinary owner is filtered only with FORCE. Connect the application as a separate role that owns nothing.

How do I find soft-deleted rows when @SoftDelete hides them?

JPQL cannot name the flag, and Hibernate 7.4.5 has no switch to lift the restriction for one query. Use a native query that also names the tenant, or map a second, @Immutable entity to the same table with @TenantId and a plain deleted attribute, which keeps the tenant predicate for the admin listing.

Conclusion

Multi-tenancy is one question asked of every statement, and the three strategies answer it in different places. With @TenantId Hibernate 7.4.5 added tenant_id = ? to finds, JPQL, derived queries, Specifications, paging and even bulk updates, while native SQL and JdbcClient returned both tenants' rows and a foreign key accepted another tenant's category until the tenant became part of the key. A schema per tenant moved the boundary into the connection, where the connection-reuse trap lives: without a reset, a pooled connection handed globex to code that knew nothing about tenants, and HikariCP only helps when the change goes through setSchema and a default schema is configured. Flyway migrated each schema at startup and on creation, and Hibernate 7.1's TenantSchemaMapper does the switch and the reset from a class with one method that matters. A database per tenant isolates the most and cost ten idle connections per tenant, until ten tenants filled PostgreSQL's hundred. Row-level security caught the leaking native query, but only for a role that neither owns the table nor is a superuser, and only inside a transaction that ran set_config. The request side has its own traps: a ThreadLocal that is not cleared serves the last tenant's data, and an @Async thread has no tenant at all.

Soft delete rides on the same machinery. @SoftDelete turned deletes, including bulk JPQL deletes, into updates and added deleted=false next to the tenant predicate, at the price of eager to-one associations and a soft-deleted parent that hid or broke its children. @SQLDelete with @SQLRestriction keeps associations lazy but lets a bulk delete remove rows for real. Either way a unique SKU needs a partial index, and deleted rows are reached through native SQL or a second, read-only entity.

This closes Chapter 2 on advanced data access. Chapter 3 turns to security, and its first article, article 13, covers OAuth2 and OpenID Connect: OAuth2 Login with Google and GitHub, and a Resource Server that validates JWTs, the kind of token a production tenant should come from instead of a header.

[Advanced Spring Boot] Multi-Tenancy and Soft Delete with Spring Boot and Hibernate

Multi-tenancy strategies: discriminator column, schema per tenant, database per tenant

The lab: a multi-tenant catalogue and how the outputs were produced

Resolving the tenant from a request header

A TenantContext holder and a servlet filter

What happens when the ThreadLocal is not cleared?

Is the tenant available on an @Async thread?

Discriminator column multi-tenancy with @TenantId

Mapping @TenantId and a CurrentTenantIdentifierResolver

The SQL for find, insert, update and delete

JPQL, derived queries, Specifications, paging and bulk updates

What bypasses @TenantId: native queries and JdbcClient

A foreign key does not know about tenants

Schema per tenant with a MultiTenantConnectionProvider

A MultiTenantConnectionProvider that switches the schema

The connection-reuse trap: a pooled connection keeps the last tenant's schema

Flyway migrations for every tenant schema

Adding a tenant at runtime

Hibernate 7.1 TenantSchemaMapper: the schema switch without a provider

Database per tenant: one connection pool per tenant

PostgreSQL row-level security as a second line of defence

A policy on current_setting('app.tenant_id')

Setting the tenant per transaction with set_config

The native query that leaked, now as a non-owner role

Why does row-level security filter nothing for the table owner?

Soft delete with Hibernate @SoftDelete

@SoftDelete on Product and Category

The SQL for delete, find, JPQL and a collection

Soft delete and tenancy in one statement

What happens to a product whose category is soft-deleted?

DELETED, ACTIVE and TIMESTAMP strategies

What bypasses @SoftDelete: native SQL and JdbcClient

@SQLDelete and @SQLRestriction compared with @SoftDelete

Soft delete and unique constraints: the partial unique index

Listing and restoring soft-deleted rows

Choosing a multi-tenancy strategy and a soft-delete approach

FAQ

How do I implement multi-tenancy in Spring Boot with Hibernate 7?

Does @TenantId filter native queries and JdbcClient?

Why does Spring Boot fail with "SessionFactory configured for multi-tenancy, but no tenant identifier specified"?

Should a schema-per-tenant provider use setSchema or SET search_path?

Why does PostgreSQL row-level security return all rows?

How do I find soft-deleted rows when @SoftDelete hides them?

Conclusion

Related Posts

[Advanced Spring Boot] Writing Your Own Auto-configuration and Starter

[Advanced Spring Boot] NoSQL with Spring Data: MongoDB and Redis

[Advanced Spring Boot] JPA Performance: N+1 Detection, Batch Fetching, Projections and Batch Inserts

[Advanced Spring Boot] OAuth2 and OpenID Connect in Spring Boot: OAuth2 Login and a JWT Resource Server