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:

projects.crud.ts
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:

projects.module.ts
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:

scopes.ts
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 })
scopeOptional

CrudScopeValues | ((context: CrudContextValue) => CrudScopeValues | null)

The column values every row must match. A function that returns null leaves that call unscoped on purpose.
options.contextOptional

Record<string, unknown>

The context a function scope reads, per call. Wins over CrudContext.

Defaults to CrudContext.current()

options.skipScopeOptional

boolean

Run a trusted, server-side call without the entity's scope.

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:

report.job.ts
await CrudContext.run({ tenantId: 7 }, () => projects.findAll());
await projects.findAll({}, {}, { context: { tenantId: 7 } }); // per call, wins over CrudContext
CrudContext.current(); // the active context, or undefined
CrudContext memberWhat 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:

billing.job.ts
await projects.count({}, { skipScope: true }); // every tenant

Which methods are scoped?

All of them:

AreaMethodsAnother tenant's row
Readsfind, findOne, exists, findAll, findByCursor, count, countBy, fullTextSearch, relation countsNot found: null, false, or left out of the page and the total.
Single writesupdate, softDelete, restore, delete, forceDelete404
Upsertupsert, massUpsert404 (the conflicting row is not updated)
BulkmassUpdate, massSoftDelete, massRestore, massDelete, massForceDelete400 with a 404 entry for that id, nothing written
Linksattach, detach, sync404

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.

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 record

The 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

Next steps

Last updated View change history

On this page