Multi-tenancy with Drizzle in NestJS
Build a NestJS multi tenant API on Drizzle with one scope key. Every read and write is limited to the caller's tenant; a missing tenant is a 403.
To build a NestJS multi tenant API on Drizzle with nestjs-drizzle-crud, set scope on an entity, for example scope: (ctx) => ({ tenant_id: ctx.tenantId }). Every query the service runs is then limited to the rows that match it: reads, counts, cursors, writes, bulk writes, upserts, soft deletes, restores and force deletes. A create writes the scope onto the new row. Another tenant's row is a 404, and a request without a tenant is a 403.
You cannot forget it in one method
The scope is not a filter you add per call. Every read builds its WHERE from the same row scope, and every UPDATE and DELETE from the same write predicate, so no method runs without it.
How do I scope an entity to a tenant?
Add scope to the entity config
The scope reads the call's context and returns the column: value pairs every row must match:
import type { CrudFeatureConfig } from "@avytrix/nestjs-drizzle-crud";
export const projectsCrud = {
scope: (ctx) => ({ tenant_id: ctx.tenantId }),
} satisfies CrudFeatureConfig;Put the tenant in the request context
CrudContext.middleware resolves the context from the request and runs the rest of the request inside it:
import { type MiddlewareConsumer, Module, type NestModule } from "@nestjs/common";
import { CrudContext, DrizzleCrudModule } from "@avytrix/nestjs-drizzle-crud";
import { projects } from "../db/schema";
import { ProjectsController } from "./projects.controller";
import { projectsCrud } from "./projects.crud";
import { ProjectsService } from "./projects.service";
@Module({
imports: [
DrizzleCrudModule.forFeature([
{ service: ProjectsService, table: projects, config: projectsCrud },
]),
],
controllers: [ProjectsController],
})
export class ProjectsModule implements NestModule {
configure(consumer: MiddlewareConsumer) {
consumer
.apply(CrudContext.middleware((req) => ({ tenantId: req.user?.tenantId })))
.forRoutes(ProjectsController);
}
}Call the service as usual
projects.findAll() now returns only the caller's projects, and projects.update(id, dto) on another tenant's id is a 404. The example app does exactly this in apps/api/src/projects, reading the tenant from an x-tenant-id header so the e2e suite can switch tenants.
What does a scope look like?
column: value pairs, each an exact match (null means IS NULL). Either a fixed object, or a function of the call's context:
scope: { region_id: 1 } // fixed
scope: (ctx) => ({ tenant_id: ctx.tenantId }) // per request
scope: (ctx) => ({ tenant_id: ctx.tenantId, owner_id: ctx.userId })
scope: (ctx) => (ctx.isAdmin ? null : { tenant_id: ctx.tenantId })scopeOptionalCrudScopeValues | ((context: CrudContextValue) => CrudScopeValues | null)
options.contextOptionalRecord<string, unknown>
Defaults to CrudContext.current()
options.skipScopeOptionalboolean
Defaults to false
Returning null leaves that call unscoped on purpose, for an admin or a background job. A key that is not a column of the table throws, and so does a function that returns something other than an object or null. The function runs synchronously, once per call (since 3.8.0; it used to run once per statement and relation), so resolve anything async (the user, their tenant) in middleware or a guard and put the result in the context.
Where does the context come from?
From options.context on the call, or else from CrudContext, which keeps a per-request value in Node's AsyncLocalStorage so it follows the request through every await without being passed by hand. Outside a request, wrap the work yourself:
await CrudContext.run({ tenantId: 7 }, () => projects.findAll());
await projects.findAll({}, {}, { context: { tenantId: 7 } }); // per call, wins over CrudContext
CrudContext.current(); // the active context, or undefinedCrudContext member | What it does |
|---|---|
run(context, fn) | Runs fn with context as the current context and returns its result. |
current() | The context of the running code, or undefined outside one. |
middleware(resolve) | An Express style middleware: calls resolve(req) and runs the rest of the request inside the result. A throw in resolve goes to next(error). |
What happens without a tenant?
The call fails closed. When the scope function returns undefined for a column (no tenant on the request), the service throws MissingScopeException, a 403, instead of running unscoped. A trusted server-side call can opt out with skipScope: true:
await projects.count({}, { skipScope: true }); // every tenantWhich methods are scoped?
All of them:
| Area | Methods | Another tenant's row |
|---|---|---|
| Reads | find, findOne, exists, findAll, findByCursor, count, countBy, fullTextSearch, relation counts | Not found: null, false, or left out of the page and the total. |
| Single writes | update, softDelete, restore, delete, forceDelete | 404 |
| Upsert | upsert, massUpsert | 404 (the conflicting row is not updated) |
| Bulk | massUpdate, massSoftDelete, massRestore, massDelete, massForceDelete | 400 with a 404 entry for that id, nothing written |
| Links | attach, detach, sync | 404 |
The scope is ANDed into the existence check and into the UPDATE or DELETE statement itself, so a row cannot change tenant between the two. Since every statement carries tenant_id = $1, put the tenant column first in your composite indexes; Database indexes for fast Drizzle queries shows them. On upsert it sits in the ON CONFLICT DO UPDATE ... WHERE, on PostgreSQL and SQLite alike. MySQL's ON DUPLICATE KEY UPDATE has no WHERE, so a scoped upsert on MySQL is a 400 (upsert on a scoped entity needs PostgreSQL or SQLite).
What does a create or update do with the scope column?
create, massCreate and upsert write the scope values onto the row, over whatever the body said, so a client cannot create a row in another tenant. update never sets the scope columns: they are dropped from the SET, so a body cannot move a row to another tenant. An update whose only column was the scope column is a 400 (nothing to update).
With nested create, child rows get the scope values too when their table has the same columns, their declared belongs-to keys are checked like the parent's (see below), and attach only links targets in the caller's tenant when the related table has the scope column.
Are related tables scoped?
Yes, when the related table has the scope columns. Loaded relations, joins, relation filters (some, none, every and nested columns) and counts only see related rows of the caller's tenant. A belongs-to row of another tenant loads as null, and a filter on it matches nothing. A related table without the scope column is treated as shared and left unscoped.
Can a write point a foreign key at another tenant's row?
No, since 3.7.2, when the related table has the scope columns. On a scoped entity, create, update, upsert and the mass writes check every belongs-to key they set (localKey of a relation in relations) against the caller's tenant, the way attach checks its targets. A key that points at another tenant's row gets exactly the answer a key pointing at a missing row gets, a 400 ForeignKeyViolationException, so the response does not reveal that the row exists:
// notes are scoped by tenant_id and belong to a doc; doc 4 is tenant 2's
await notes.create({ docId: 4, body: 'x' }, { context: { tenantId: 1 } });
// 400 Invalid reference for doc_notes: 'doc_id' does not refer to an existing recordThe check costs one SELECT per relation for the whole call, whatever the number of rows; a mass write lists the failing items in a BulkOperationException. A related table without the scope columns is shared and not checked, and neither is a call with skipScope: true. A key to a row that does not exist at all is refused the same way, even without a database foreign key.
Which key matches is the database's call, as it is for a foreign key. Since 3.8.2 the check joins the keys it was sent against the related table, so the column's type and collation compare them: a citext or SQLite NOCASE key in the caller's own tenant matches in any case, a PostgreSQL text key or a MySQL key under a _bin collation only matches exactly, and a uuid matches in any case. A key the column's type cannot hold, such as "nope" for a uuid, is a 400 on every write, a mass write or one inside your own transaction included. attach and sync check their targets the same way and link each one by its key as stored, so "ann@x.io" and "Ann@X.io" on a citext column give one link.
Since 3.8.1 the same check runs on the rows a nested create writes. A child key is checked when the nested relation declares it in its own relations, with the same 400:
relations: {
posts: {
type: 'hasMany', table: posts, foreignKey: 'author_id',
// declared here, so a post's category_id is checked against the tenant
relations: { category: { table: categories, localKey: 'category_id' } },
},
},Only declared relations are checked. A child column that no nested relation names is left to the database's foreign key, which knows nothing about tenants, so declare every belongs-to a nested child can set.
A composite foreign key such as (tenant_id, author_id) still makes the database itself refuse a cross-tenant reference, for writes that do not go through the service.
Common questions
No. When the scope function returns undefined for a column, the call throws MissingScopeException, a 403. Only a function that returns null, or a call with skipScope: true, runs unscoped.
A 404 on a write and null on a read, the same as an id that does not exist. The response never reveals that the row exists in another tenant.
No. The scope columns are dropped from the SET of every update, and a create always writes the caller's scope values over the body.
No. MySQL's ON DUPLICATE KEY UPDATE cannot carry the scope condition, so a scoped upsert on MySQL is a 400 rather than a write that could touch another tenant's row.
Yes, when the related table has the scope columns. Loaded relations, joins, relation filters and counts only see the caller's tenant, and a belongs-to row of another tenant loads as null. A related table without the scope columns is treated as shared and is not filtered. Give it its own scoped service if clients query it directly.
Next steps
NestJS CRUD controller generator
Generate scoped REST routes for an entity in one call.
Drizzle optimistic locking with versions
Refuse a write that names a stale version with a 409.
Error handling in NestJS with Drizzle
MissingScopeException and every other exception, with its status.
Database indexes for fast Drizzle queries
Tenant-first composite indexes for scoped reads and writes.
Need a team?
Avytrix Technologies maintains this library and builds multi-tenant NestJS and Drizzle backends. See what they can take on.
NestJS Drizzle filter and column allowlists
A NestJS Drizzle filter allowlist limits the columns a client may filter, sort, select, search and write. Anything outside a set list is a 400.
Drizzle hooks and validation in NestJS
Add Drizzle hooks and validation to a NestJS service by overriding validateCreate, beforeCreate, afterUpdate and the DTO mappers on SqlBaseCrudService.