Multi-tenancy with Drizzle in NestJS

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.

How do I scope an entity to a tenant?Copy Anchor Link

import type { CrudFeatureConfig } from "@avytrix/nestjs-drizzle-crud";

export const projectsCrud = {
  scope: (ctx) => ({ tenant_id: ctx.tenantId }),
} satisfies CrudFeatureConfig;

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);
  }
}

What does a scope look like?Copy Anchor Link

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 })

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, 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?Copy Anchor Link

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 undefined

What happens without a tenant?Copy Anchor Link

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 tenant

Which methods are scoped?Copy Anchor Link

All of them:

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. On upsert it sits in the ON CONFLICT DO UPDATE ... WHERE. 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).

What does a create or update do with the scope column?Copy Anchor Link

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, and attach only links targets in the caller's tenant when the related table has the scope column.

Are related tables scoped?Copy Anchor Link

On reads, no. The scope applies to the service's own table; a relation loaded with relations or counted with counts follows its foreign key. That is safe when related rows always share the parent's tenant, which a foreign key usually guarantees. Give a related table its own service with its own scope when it is queried on its own.

Common questionsCopy Anchor Link

Next stepsCopy Anchor Link