Add the "a single DB for all tenants" model of multi-tenancy

[Errorname]

Currently, Prisma-multi-tenant uses the "one DB per tenant" model of multi-tenancy. However, we could also add the possibility to use the "a single DB for all tenants" model of multi-tenancy.

Here is how it would work:

1. pmt init
   • Asks which mode to use
   • If SingleDB, add a "Tenant { id String @id }" model, add "tenantId: String @default('dev')" on all models
   • Migrate DB, then creates the first tenant: "dev"
2. const multiTenant = new MultiTenantSingleDB({sharedModels: []})
   • An option for "shared models" which are shared between tenants (ex: Users?)
3. const prisma = multiTenant.get('tenant_A')
   • Adds a middleware to the PrismaClient that adds { where: { tenantId: 'tenant_A' } } on every queries
4. prisma.users.findMany()
   • Same as always!

---

References:

• https://github.com/prisma/prisma/pull/3029
• https://github.com/prisma/prisma-client-js/issues/770
• https://github.com/prisma/prisma/discussions/2846

[BjoernRave]

this is my WIP take on how to solve this:

export const injectTenant = (prisma: PrismaClient, subDomain: string) => {
  prisma.$use(async (params, next) => {
    if (params.model === 'Setting') {
      return next(params)
    }

    if (params.action === 'delete') {
      return next(params)
    }

    if (['create', 'update'].includes(params.action)) {
      params.args.data = { ...params?.args?.data, tenantId: subDomain }

      return next(params)
    }

    if (!params?.args) {
      params = { ...params, args: {} }
    }

    if (!params?.args?.where) {
      params = { ...params, args: { ...params.args, where: {} } }
    }

    if (params.action === 'findOne') {
      params.action = 'findFirst'

      params.args.where = Object.keys(params.args.where).reduce(
        (prev, next) => {
          return { ...prev, [next]: { equals: params.args.where[next] } }
        },
        {}
      )
    }

    if (params?.args?.where?.AND) {
      params.args.where = {
        AND: [{ tenantId: { equals: subDomain } }, ...params.args.where.AND],
      }
    } else {
      if (params?.args?.where && Object.keys(params?.args?.where).length > 0) {
        params.args.where = {
          AND: [
            { tenantId: { equals: subDomain } },
            ...Object.keys(params.args.where).map((key) => ({
              [key]: params.args.where[key],
            })),
          ],
        }
      } else {
        params.args.where = {
          tenantId: { equals: subDomain },
        }
      }
    }

    return next(params)
  })
}

Every model in the db has a tenantId field, which sadly has to be marked optional, since the prisma calls dont know a middleware is injecting the tenantId

[camsloanftc]

I don't have much to add here, except that it would be awesome to have this.

I think a lot of SaaS db's are following a single-db multi-tenant pattern, especially in the early days.

For me it makes no sense to spin up a db for each new customer when I am testing the waters with a blitz app to see if there is a viable market.

This could make this plug-in much more accessible to a wide array of bootstrapping SaaS entrepreneurs using these frameworks like blitz and redwood.

Would love to keep the discussion going here. @BjoernRave how is your solve above working out? It looks like it is missing some cases, but seems like a solid jumping off point. Have you made any adjustments to that since implementing?

I'm no prisma/db expert, but happy to help where I can on this.

[Dhalias]

Hey guys ! Any news about single DB for all tenants being supported by Prisma ? As stated, for SaaS entrepreneur this would be a massive plus for Prisma.

[jmarbutt]

@BjoernRave I was curious if the WIP example you provided here is still the best option or have you made progress in other areas?

[Jarrodsz]

Kick it. Anyone managed to find a solution? Or does prisma stays a kindergarten orm

[willemmulder]

@BjoernRave Do you have an update on your version or a Git place where you store it? Then we can improve upon it together. Thanks :-)