nestjs-sequelize-multi-tenant
overview
A B2B SaaS architecture where each organisation gets a dynamically provisioned PostgreSQL schema at runtime. The design requirement was complete data isolation between tenants no shared tables, no risk of cross-tenant data leakage without the operational overhead of managing a separate database instance per organisation.
The solution uses schema-per-tenant within a single PostgreSQL instance. A TenantMiddleware intercepts every incoming request, extracts the organisation identity from either the x-tenant-id header or the subdomain, then fetches or provisions a Sequelize connection scoped to that tenant's schema. Connections are cached in a pool with an LRU eviction strategy to avoid reconnecting on every request.
On organisation signup, the provisioning flow creates a new PostgreSQL schema and runs Sequelize migrations against it automatically no manual DBA steps. The API gateway validates tenant identity before any resolver or controller fires, so tenant isolation is enforced at the infrastructure layer rather than scattered across business logic.
architecture
Incoming Request
│
▼
TenantMiddleware
├── extract org from x-tenant-id header
└── OR extract org from subdomain
│
▼
TenantConnectionPool (LRU cache)
├── org exists? ──► return cached Sequelize instance
└── org new? ──► provision schema + run migrations
└──► cache + return instance
│
▼
Sequelize Instance (per-tenant)
│
▼
PostgreSQL
├── schema: org_a (Tenant A data)
├── schema: org_b (Tenant B data)
└── schema: org_n (Tenant N data)The TenantMiddleware runs before the NestJS request pipeline. It uses the resolved tenant identity to look up or create a Sequelize instance in the connection pool. The instance is scoped to the tenant's schema via Sequelize's `schema` option every query is prefixed with the schema name at the ORM level, making cross-tenant queries structurally impossible through normal application code.
technical.decisions
Database-per-tenant provides stronger isolation but creates significant operational overhead: connection limits, backup strategies, and migration orchestration multiply with each new org. Schema-per-tenant within a single PostgreSQL instance provides sufficient isolation for most B2B use cases while keeping infrastructure costs and complexity linear.
Subdomain-based routing requires DNS configuration per environment (localhost development is awkward). Header-based routing via x-tenant-id works uniformly in development, staging, and production. In production, the API gateway can inject the header from the authenticated JWT claims, removing client-side trust requirements.
Rather than pre-creating schemas, provisioning is triggered on first access for a new org identity. Sequelize migrations run against the new schema as part of the provisioning flow. This keeps the signup flow synchronous from the API perspective and doesn't require a separate provisioning service.
Naive implementations create a new Sequelize connection per request, which is prohibitively expensive. An LRU cache keyed by tenant ID reuses connections for active tenants and evicts least-recently-used entries when the pool approaches its memory limit. This keeps latency low for active tenants without unbounded memory growth.
outcomes
- Zero cross-tenant data access by construction schema-level isolation
- New orgs onboarded automatically without manual DB setup
- Domain-based routing enables white-label deployment
- Connection pooling keeps latency low across active tenants
- Migration strategy works uniformly across all tenant schemas