- Spring boot
- Postgresql
- MySQL
- Jooq
PostgreSQL: Application Implementation
- Same database, same schema, same table, filter by
tenant_id
column
- Auto extend SQL, add
where tenant_id in
or and tenant_id in
conditions
- Check tenant_id related conditions when we
select
,update
, delete
tenant tables
- See multi-tenancy-library and springboot-postgres-jooq
Install Postgresql with Docker
docker run --name postgres -e POSTGRES_PASSWORD=123456 -e TZ=PRC -p 5432:5432 postgres:latest
Create Table and Insert Data into Table
multi:
tenancy:
# enable multi tenancy
enabled: true
# tenant table tenant related column name
tenant-identifier: tenant_id
# tenant tables
tables:
- public.t_user
- public.t_order
# check tenant condition exist in SQL or not
sql-check-filters-exist: true
# auto add tenant condition to SQL
sql-auto-add-filters: true
- If there are SQL releated schedulers in application, we can not get tenantID
through
request.getHeader("X-TenantID")
in WebMvcConfig
- We need to add
MultiTenancyStorage.setTenantID(tenantID)
before scheduler logic
and add MultiTenancyStorage.setTenantID(null)
after scheduler logic
- Please check
springboot-postgres-jooq/src/main/java/com/example/springbootpostgresjooq/job/ScheduledJob.java
for detailed information
PostgreSQL: Row Level Security Implementation
- Same database, same schema, same table, filter by
tenant_id
column
- Using PostgreSQL Row Level Security
- See rls
Create Table and Insert Data into Table
Enable RLS and Add Policies
MySQL: Application Implementation
- Same database, same schema, same table, filter by
tenant_id
column
- Auto extend SQL, add
where tenant_id in
or and tenant_id in
conditions
- Check tenant_id related conditions when we
select
,update
, delete
tenant tables
- See multi-tenancy-library and springboot-mysql-jooq
Install MySQL with Docker
docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql
Create Table and Insert Data into Table
multi:
tenancy:
# enable multi tenancy
enabled: true
# tenant table tenant related column name
tenant-identifier: tenant_id
# tenant tables
tables:
- multi_tenancy.t_user
- multi_tenancy.t_order
# check tenant condition exist in SQL or not
sql-check-filters-exist: true
# auto add tenant condition to SQL
sql-auto-add-filters: true
- If there are SQL releated schedulers in application, we can not get tenantID
through
request.getHeader("X-TenantID")
in WebMvcConfig
- We need to add
MultiTenancyStorage.setTenantID(tenantID)
before scheduler logic
and add MultiTenancyStorage.setTenantID(null)
after scheduler logic
- Please check
springboot-mysql-jooq/src/main/java/com/example/springbootmysqljooq/job/ScheduledJob.java
for detailed information