Schema-Based Multi-Tenancy with NestJS and Prisma

We recently migrated a big NestJS project to adopt Prisma as the ORM. It turned out to be a great decision, and I described our wonderful experience in a recent blog post.

The project is an API backend for a multi-tenant system, where each tenant has its own schema in our Postgres database.

The problem

In an ideal scenario, we would just need to specify the database schema for every performed query. Unfortunately, Prisma does not support that at the moment. However, it allows passing the database connection string when the PrismaClient is instantiated, skipping the one declared in the .env file. Thanks to that and the power of NestJS we came up with a quite solid solution.

The solution

We decided to dedicate a separate instance of PrismaClient to each tenant schema. Now, obviously, we cannot create an instance for every request, we better find a way to cache instances and reuse them when possible.

It's important to note that in our case we need to handle a relatively small amount of tenants, and we are also working with a database that can handle a large number of connections. This solution would not have been a good fit otherwise, since every instance of PrismaClient requires its own connection to the database.

The idea is to have a manager class that will hold all created instances, provide them, and will eventually create new ones when needed. It would also have to dispose every created instance on server shutdown to free up connections.

Let's now take a look at how we implemented this solution.

The implementation

The manager class is pretty straightforward. It is going to be a simple NestJS provider, holding a "cache" object for instantiated clients as a class property.

In our case tenant IDs are used as database schema names, but you might also provide your own mapping logic.

We need three methods here:

1. getTenantId: this will extract the tenant id from the request object. In our case, it will be extracted from the JWT in the Authorization header, but it may also be deduced from the hostname.
2. getClient: this will be called for every request to retrieve a tenant-dedicated PrismaClient instance, and it does most of the work.
3. onModuleDestroy: this will be called by the framework on application shutdown, and it will be in charge of clients disposal.

import { Injectable, OnModuleDestroy } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';
import { Request } from 'express';

@Injectable()
export class PrismaClientManager implements OnModuleDestroy {
  // the client instances cache object
  private clients: { [key: string]: PrismaClient } = {};

  getTenantId(request: Request): string {
    // TODO: retrieve and return the tenant ID from the request object,
    // eventually throw an exception if the ID is not valid
  }

  getClient(request: Request): PrismaClient {
    const tenantId = this.getTenantId(request);
    let client = this.clients[tenantId];

    // create and cache a new client when needed
    if (!client) {
      const databaseUrl = process.env.DATABASE_URL!.replace('public', tenantId);

      client = new PrismaClient({
        datasources: {
          db: {
            url: databaseUrl,
          },
        },
      });

      // setup prisma middlewares if any

      this.clients[tenantId] = client;
    }

    return client;
  }

  async onModuleDestroy() {
    // wait for every cached instance to be disposed
    await Promise.all(
      Object.values(this.clients).map((client) => client.$disconnect()),
    );
  }
}

You may also want to implement type safety and validation for tenant IDs.

Note that the code above assumes the existence of a DATABASE_URL environment variable holding a database connection string with the schema query param set to public, like the one in the .env file generated by Prisma:

DATABASE_URL="postgresql://user:password@localhost:5432/mydb?schema=public"

Also, make sure to add this provider to a global module, like a DatabaseModule or the AppModule.

Now, the last thing is to find a way to call getClient for every request and pass around the instance based on where we need it.

First approach

The first, really verbose approach is to access the request object from every handler method in every controller and pass it around to every service method we need to call to handle the request.

// controller example method
  @Get()
  findAll(@Req() request: Request) {
    return this.service.findAll(request);
  }

export class MyService {
  constructor(private prismaClientManager: PrismaClientManager) {}

  findAll(request: Request) {
    const prisma = this.prismaClientManager.getClient(request);
    // method logic ...
  }
}

This is extremely verbose, we can definitely do better.

Second approach

Thanks to the NestJS dependency injection system, every registered provider is instantiated the first time it needs to be injected, and then the instance is reused any subsequent time. However, we can take advantage of a built-in mechanism to make a service request-scoped, asking the framework to create a new instance for each incoming request.

To do that we need to add a scope parameter to the Injectable decorator. This way we will also have access to the incoming request object.

import { Injectable, Scope, Inject } from '@nestjs/common';
import { REQUEST } from '@nestjs/core';
import { Request } from 'express';
import { PrismaClientManager } from '../database/prisma-client-manager';

@Injectable({ scope: Scope.REQUEST })
export class MyService {
  private prisma: PrismaClient;

  constructor(
    @Inject(REQUEST) request: Request,
    prismaClientManager: PrismaClientManager,
  ) {
    this.prisma = prismaClientManager.getClient(request);
  }

  findAll() {
    // just use this.prisma to access the database
  }
}

This is less verbose and easier to manage than the first approach, but we still need to add the code above to all of the services from which we need to access the Prisma client.

Let's try to take advantage of dependency injection again to avoid that.

Third approach and final solution

Turns out all we need is a custom factory provider, which will be the only explicitly request-scoped component and will be called by the framework to provide us a PrismaClient whenever we need it to be injected as a service dependency.

// src/database/database.module.ts

import { FactoryProvider, Global, Module, Scope } from '@nestjs/common';
import { REQUEST } from '@nestjs/core';
import { PrismaClient } from '@prisma/client';
import { Request } from 'express';
import { PrismaClientManager } from './prisma-client-manager';

const prismaClientProvider: FactoryProvider<PrismaClient> = {
  provide: PrismaClient,
  scope: Scope.REQUEST,
  inject: [REQUEST, PrismaClientManager],
  useFactory: (request: Request, manager: PrismaClientManager) => manager.getClient(request),
};

@Global()
@Module({
  providers: [PrismaClientManager, prismaClientProvider],
  exports: [PrismaClient],
})
export class DatabaseModule {}

This way we can totally forget about the request-scoped logic and simply use the PrismaClient in our services as if it was a regular provider.

import { Injectable } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class MyService {
  constructor(private prisma: PrismaClient) {}

  findAll() {
    // just use this.prisma to access the database
  }
}

The providers with a dependency on the PrismaClient will still be request-scoped, but we don't need to make it explicit anymore, we can just use them as regular providers.

A complete example of the final implementation can be found in this repo.

This is how we achieved multi-tenancy with Prisma in a NestJS project. Please don't hesitate to ask any questions or leave a comment.

Note: the strategy described above is the same adopted by this great library. If you don't want to write your own implementation or you'd like to take advantage of other features such as a multi-tenant-aware migrations CLI, I highly suggest taking a look at it.

Cheers!