Isolating multi-tenant data via database schemas

[floelhoeffel]

Problem

Multi-tenant applications want to leverage per tenant schemas to implement data isolation.

Suggested solution

• a shared model referencing a schema that holds tenant information
• one or more tenant models where the schema name is dynamic (e.g. per tenant)
• at runtime inject tenant information into a query

Alternatives

Initiate a new Prisma client per tenant as described here: https://darioielardi.dev/schema-based-multi-tenancy-with-nestjs-and-prisma

Additional context

This issue was created as a sub issue to #1122

[Dhalias]

Hi @floelhoeffel ! Is there anything we can do to help the Prisma team with this issue?

[prudnikov]

Lack of this functionality prevents me from switching to Prisma. Please do it.

We need to specify schema in runtime based on who is making the request. Here is how knex.js does this https://knexjs.org/guide/query-builder.html#withschema

[floelhoeffel]

Hey @Dhalias and @prudnikov - thanks for the input! We are working to get multi-schema support (without the runtime component though, so won't help with multi-tenancy) into full preview with 4.7.0 for PostgreSQL.

Unfortunately, the multi-tenancy use cases adds a lot of complexity for Prisma schema. We would need to introduce a new class of "dynamic" models where the data model is shared among multiple schemas (aka tenants).

Depending on the number of tenants and models you have, would a workaround via @@map be useful?

model TenantAComment {
  id      Int    @id
	...
  @@schema("tenantA")
  @@map("comment")
}
model TenantBComment {
  id      Int    @id
	...
  @@schema("tenantB")
  @@map("comment")
}

and then something like this:

type TennantAComment = {
  text: string
}

type TennantBComment = {
  text: string
}

declare class PrismaClient {
  
  tennantAComment: {
    findMany: () => Promise<TennantAComment[]>
  }

  tennantBComment: {
    findMany: () => Promise<TennantBComment[]>
  }
}

type TennantId = 'tennantA' | 'tennantB'
type ModelName = 'Comment'

function moduleName<Id extends TennantId, Model extends ModelName>(id: Id, model: Model): `${Id}${Model}` {
  return `${id}${model}`
}

const prisma = new PrismaClient()


const comments = await prisma[moduleName('tennantA', 'Comment')].findMany()
//    ^?

declare const unknownTennant: TennantId

const comments2 = await prisma[moduleName(unknownTennant, 'Comment')].findMany()
//    ^?

Haven't tried running this though 😅

This could give you migrate support but comes with other disadvantages like a bloated client and you need to know tenants at compile time etc..

[Ustice]

We accomplished this by creating one client per tenant, and then using a function to switch between clients for each of the tenants. We use mysql, so the multiSchema feature isn't currently supported. It would be really nice if I could include this info in the Prisma schema, and not have to manage 50 clients (and all of their associated database connections).

Given our use-case, it would be extremely cumbersome to use @@map as a workaround. I would rather have the ability to list the available tenants in the datasource section of the schema. Maybe with the ability to set a default. Then when making database calls, we could either switch between them via an argument, or as a method that takes a tenantId, and returns a model client.

const comments = await prisma.comment.findMany({
  tenant: tenandId,
})
const alsoComments = await prisma.tenant(tenantId).comment.findMany()

[bsplosion]

As a note, this would also help in our scenario unrelated to multi-tenancy. We have to stage periodic batch data changes from another system, and when we do there's a clean cutover to a completely fresh schema. That new schema has exactly the same structure as the prior one, just with new content.

If we could pass the schema as a tenant or use some other dynamic schema provision setup, we'd be able to drive this via configuration instead of having to hack around it with a bunch of views to be updated (and by extension also having to clear caches manually).

[tech-chieftain]

Hey @Ustice I'm currently facing something very similar to this and was thinking of the same approach. Would love it if you can elaborate a bit more on how you provide one client per tenant. How is a prisma client generated and how does it get connected to a databse schema on the go? Also, do you use any form of microservice/caching of these clients as a separate service to support this or is that an overkill?

Thank you in advance 🙏

[nephix]

@tech-chieftain one approach on how to have a client per tenant is linked in the OP

https://darioielardi.dev/schema-based-multi-tenancy-with-nestjs-and-prisma

[tech-chieftain]

Thank you for sharing @nephix, I read this page when I first saw this issue. The proposed solutions could be migrated to frameworks other than Nestjs with some workarounds but I was curious to see if @Ustice had another solution in mind that would be beneficial.

[VanCoding]

I think it would be sufficient if prisma supported sharing the connection pool between multiple clients connecting to the same host and database. The main problem of not having multi-tenancy support is that for each tenant client a separate connection pool is created. If you have a lot of tenants, that becomes a problem very quickly.

Or what's the memory footprint of a prisma client instance? Would it become a problem for 100 instances as well?

[marcofoof]

An idea to solve this problem:

prisma.schema

model Account {
  id        String   @id @default(uuid())
  name      String   @unique
  email     String   @unique
  password  String
  tenantId  String  @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@multiTenancy(tenants: [tenentId])
  @@schema("public")
}

model Products {
  id    String @id @default(uuid())
  name  String @unique
  price Float

  @@tenantSchema("_schema") // results in a schema <TENANT_ID>_schema
}

this will do it all, the tag "@@multiTenancy..." select the field which identify the correct schema.

on migrate schema, run each @@tenentSchema by @@multiTenancy and update it.

on client-side something like "prisma.tenant(id)..."

[Ananym]

Just to add to some of the considerations here, CockroachDb allows seamlessly querying across multiple databases as well as multiple schemas. We have a tiered kind of multi-tenancy, with each tenant served by one database, containing an arbitrary number of separated sites represented by one schema within that database.

It sounds unlikely that prisma will support our usecase in the near future, but it'd be nice to see a solution that enables all the features of the underlying tech. As in the OP, a prisma client per database is an option if we just get arbitrary schema support, but this involves using massively more database connections than a raw cockroach solution would.

[Ustice]

Hey @Ustice I'm currently facing something very similar to this and was thinking of the same approach. Would love it if you can elaborate a bit more on how you provide one client per tenant. How is a prisma client generated and how does it get connected to a databse schema on the go? Also, do you use any form of microservice/caching of these clients as a separate service to support this or is that an overkill?

Thank you in advance 🙏

@tech-chieftain Sorry for the late reply. So the way that we handle it is that our Prisma schema is linked via environment variable to enable migrations. We essentially create a Prisma client per database. We don't do that all at once, but instead have a getClient function that basically has a Map that points a tenantId to a Prisma client, and if the client doesn't already exist, we create it, overriding the connection string in PrismaClient instantiation to point to . The prisma object that I referenced in my code above is not the PrismaClient, but another object that I use as a Proxy that controls some other things (I built it before prisma.$extends was available). The prisma.tenant function basically just calls that getClient function that I mentioned earlier. All of the types are the same, so that is really all you have to do.

[robertjpayne]

A somewhat sad part of the way Prisma currently generates queries is each query in Postgres has a forced schema added to table specifiers. e.g. SELECT ... FROM "public"."<table>" which means you can't even use client extensions to set the search path before each query.

It could be as easy as removing explicit schema's in Postgres and letting the community deal with it via client extensions for custom use cases 😭

[Takuya-Ampi]

G no h GhhHg

[michaelbridge]

As mentioned by robertjpayne, this use case is easily solvable with search_path if the Postgres schema is not forcibly included. Are any of the maintainers able to suggest how we might fork/patch this behavior away?

Rather than SELECT * FROM "public"."todos" as is current, generate SELECT * FROM "todos". Then the prevailing search_path will direct the query to the proper schema.

[michaelbridge]

I've been poking around the code looking for the logic that defaults to qualifying table identifiers with schema names. @janpio any suggestions? I think a very simple patch can likely solve this issue, along with the use of client extensions.

[egorguscha]

@michaelbridge Hi! Is there any news about solving this issue?

[michaelbridge]

I think maybe this is buried in a binary; I've heard nothing from the maintainers and moved on to a more transparent lib.

[matheus-silva-fator3]

Any updates?

On my project, we are trying to generate more than 23 tables across different schemas, but it seems that prisma doesnt support that use case. Docs says that we should use multiSchema and map each table with @@map and @@schema, but our schema file already has 426 lines and duplicating this for each one of the users seems like a really, really bad nightmare.

I think that our problem could be solved if we could do something like that:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["config", "user1", "user2"]
}

model Bills {
  id String @id @default(cuid())

  @@schema(["user1", "user2"])
}

Or, even better:

model Bills {
  id String @id @default(cuid())

  @@schema({
       except: ["config"]
   })
}