Prisma•2mo ago

implement a multi-schema database architecture with Prisma for a B2B SaaS with multiple organization

I'm trying to implement a database architecture with separate schemas for a B2B SaaS application supporting multiple organizations. My desired setup is: - A public schema for user authentication (tokens, sessions) with next-auth and a global organizations table - An organization_template schema with pre-populated tables (roles, permissions, organization_details) - Dynamic creation of new schemas (organization1, organization2, etc.) by cloning the template when a new organization is created The main issue is that Prisma doesn't natively support multi-schema architecture. The Prisma instance only works with the public schema and doesn't recognize or interact with the dynamically created organization schemas.

7 Replies

Prisma AI Help•2mo ago

You're in no rush, so we'll let a dev step in. Enjoy your coffee, or drop into #ask-ai if you get antsy for a second opinion!

JeranceOP•2mo ago

this is my schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init
 
generator client {
  provider = "prisma-client-js"
  previewFeatures = ["prismaSchemaFolder", "multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas = ["public", "organization_template"]
}
 
model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime? @map("email_verified")
  passwordHash  String?   @map("password_hash")
  image         String?
  accounts      Account[]
  sessions      Session[]
  ownedOrganization Organization?
  isAdmin       Boolean   @default(false) @map("is_admin")
  
  members       Member[]
  
  createdAt     DateTime  @default(now()) @map("created_at")
  updatedAt     DateTime  @updatedAt @map("updated_at")
 
  @@map("users")
  @@schema("public")
}

model Organization {
  id String @id @default(cuid())
  name String
  schemaName String @unique @map("schema_name")
  slug String @unique @default(nanoid(6))
  image String?
  ownerId String @unique @map("owner_id")
  owner User @relation(fields: [ownerId], references: [id])
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  members Member[]

  Licence Licence[]

  @@map("organizations")
  @@schema("public")
}

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init
 
generator client {
  provider = "prisma-client-js"
  previewFeatures = ["prismaSchemaFolder", "multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas = ["public", "organization_template"]
}
 
model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime? @map("email_verified")
  passwordHash  String?   @map("password_hash")
  image         String?
  accounts      Account[]
  sessions      Session[]
  ownedOrganization Organization?
  isAdmin       Boolean   @default(false) @map("is_admin")
  
  members       Member[]
  
  createdAt     DateTime  @default(now()) @map("created_at")
  updatedAt     DateTime  @updatedAt @map("updated_at")
 
  @@map("users")
  @@schema("public")
}

model Organization {
  id String @id @default(cuid())
  name String
  schemaName String @unique @map("schema_name")
  slug String @unique @default(nanoid(6))
  image String?
  ownerId String @unique @map("owner_id")
  owner User @relation(fields: [ownerId], references: [id])
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  members Member[]

  Licence Licence[]

  @@map("organizations")
  @@schema("public")
}

this is my next-auth.schema

// DO NOT EDIT THIS FILE !
// All this tables is defined by following the documentation of Authjs
// See : https://authjs.dev/getting-started/adapters/prisma?framework=next-js

model Account {
  id                 String  @id @default(cuid())
  userId             String  @map("user_id")
  type               String
  provider           String
  providerAccountId  String  @map("provider_account_id")
  refresh_token      String? @db.Text
  access_token       String? @db.Text
  expires_at         Int?
  token_type         String?
  scope              String?
  id_token           String? @db.Text
  session_state      String?
 
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
 
  @@unique([provider, providerAccountId])
  @@map("accounts")
  @@schema("public")
}
 
model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique @map("session_token")
  userId       String   @map("user_id")
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
 
  @@map("sessions")
  @@schema("public")
}
 
model VerificationToken {
  identifier String
  token      String  @unique
  expires    DateTime
  data       Json?
 
  @@unique([identifier, token])
  @@map("verification_tokens")
  @@schema("public")
}

// DO NOT EDIT THIS FILE !
// All this tables is defined by following the documentation of Authjs
// See : https://authjs.dev/getting-started/adapters/prisma?framework=next-js

model Account {
  id                 String  @id @default(cuid())
  userId             String  @map("user_id")
  type               String
  provider           String
  providerAccountId  String  @map("provider_account_id")
  refresh_token      String? @db.Text
  access_token       String? @db.Text
  expires_at         Int?
  token_type         String?
  scope              String?
  id_token           String? @db.Text
  session_state      String?
 
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)
 
  @@unique([provider, providerAccountId])
  @@map("accounts")
  @@schema("public")
}
 
model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique @map("session_token")
  userId       String   @map("user_id")
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
 
  @@map("sessions")
  @@schema("public")
}
 
model VerificationToken {
  identifier String
  token      String  @unique
  expires    DateTime
  data       Json?
 
  @@unique([identifier, token])
  @@map("verification_tokens")
  @@schema("public")
}

JeranceOP•2mo ago

this is my organization.schema

message.txt

JeranceOP•2mo ago

@🔨 Moderator

harry•2mo ago

Please don't tag mods unless someone has broke the rules. Someone will help when they can :)

Nurul•2mo ago

If I understand correctly, you would like to achieve the setup as described in this feature request, right? https://github.com/prisma/prisma/issues/12420

GitHub

Isolating multi-tenant data via database schemas · Issue #12420 · p...

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 t...

JeranceOP•2w ago

not really, i don't want to declare all my new tenants in schema.prisma file at datasource db schemas array because all new tenants are created by authentificated user in my app so it's dynamic schemas @Nurul

Gaming

Programming

implement a multi-schema database architecture with Prisma for a B2B SaaS with multiple organization

Did you find this page helpful?