NestJS backend with schema-per-tenant multi-tenancy: one PostgreSQL database, one public schema for app/tenant registry, and one schema per tenant for tenant-specific data.
- One database – A single PostgreSQL instance holds everything.
- Public schema – Stores app-level data: the list of tenants (
tenant_entity) and the main app migrations table. No tenant-specific tables (e.g. cats) live here. - One schema per tenant – Each tenant has its own schema:
tenant_1,tenant_2, … Inside that schema live that tenant’s tables (e.g.cat_entity) and that schema’s migration history.
When you create a tenant (e.g. POST /example/tenants with {"name": "Acme"}):
- A row is inserted into public.tenant_entity (id, name, …).
- A new schema is created: tenant_<id> (e.g.
tenant_1). - Tenant migrations run inside that schema (
SET search_path), so tables likecat_entityand the schema’smigrationstable exist only intenant_1, not inpublic.
When you call tenant-scoped APIs (e.g. create/list cats):
- You send the header
x-tenant-id(e.g.1). - The app uses nestjs-cls to store that id in request context.
- The TenancyModule provides a connection scoped to schema tenant_<id> so all queries for that request run in the correct tenant schema.
So: public = tenant registry and app metadata; tenant_X = data for tenant X only.
| Schema | Purpose | Example tables | Migrations |
|---|---|---|---|
| public | App-level / tenant registry | tenant_entity, migrations |
main/migrations/ |
| tenant_1 | Data for tenant with id 1 | cat_entity, migrations |
tenant/migrations/ |
| tenant_2 | Data for tenant with id 2 | cat_entity, migrations |
tenant/migrations/ |
| … | … | … | … |
- Main (public) – Never put tenant-only entities (e.g. cats) here. Only tenant list and main migrations.
- Tenant schemas – All tenant-scoped tables and that schema’s migrations live here.
SET search_pathis used so migrations and tables are created in the tenant schema, not in public.
From the project root:
docker-compose up -d postgresUses env vars or defaults: DB_USERNAME, DB_PASSWORD, DB_NAME, DB_PORT (see backend/.env.example).
cd backend
cp .env.example .env # adjust DB_HOST=localhost if needed
npm install
npm run migration:run # create public.tenant_entity etc.
npm run start:dev-
Tenant registry (public / main DB)
POST /example/tenants– create tenant (body:{ "name": "..." }).GET /example/tenants– list tenants.
Nox-tenant-idheader.
-
Tenant-scoped data (tenant schema)
POST /example/cats– create cat (body:{ "name": "..." }).GET /example/cats– list cats.
Require header:x-tenant-id: <id>(e.g.1).
Example: create a tenant → use returned id as x-tenant-id for /example/cats. See backend/api.http for ready-made requests.
Migrations are split by target:
- Main DB (public schema) – Tables like
tenant_entity. Migrations inbackend/src/postgres-databases/main/migrations/. Never put tenant-only tables here. - Tenant schemas – Tables like
cat_entityper tenant. Migrations inbackend/src/postgres-databases/tenant/migrations/. Naming:*-Name.ts(e.g.1739580000001-CreateCatEntity.ts).
- New tenant – Creating a tenant via the API creates the schema and runs tenant migrations inside that schema (with
search_pathset). - Existing tenants – After adding a new tenant migration file, run it for all existing tenant schemas with the command below.
| Goal | Command |
|---|---|
| Run main DB migrations | npm run migration:run |
| Run tenant migrations for one schema | npm run migration:run:tenant -- tenant_1 |
| Run tenant migrations for all tenants | npm run migration:run:tenant:all |
| Generate main migration | npm run migration:generate:main |
| Generate tenant migration | npm run migration:generate:tenant |
If cat_entity or a tenant migrations table was created in public (e.g. before search_path was set), remove them manually:
DROP TABLE IF EXISTS public.cat_entity CASCADE;
-- Only if this is the tenant migrations table, not main's:
-- DROP TABLE IF EXISTS public.migrations CASCADE;Main app keeps one migrations table in public for main migrations; each tenant schema has its own migrations table.
- One PostgreSQL DB: public schema = tenant list + main migrations; one schema per tenant = that tenant’s data + migrations.
- Tenants are created via the API; each gets a dedicated schema and tenant migrations run inside it.
- Tenant-scoped requests use header
x-tenant-id; the app uses CLS and a per-request connection to the correct schema. - Migrations: main for public only; tenant migrations only in tenant schemas, run on tenant creation and via
migration:run:tenant/migration:run:tenant:all.
