Prisma•2w 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•2w 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•2w 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•2w ago

this is my organization.schema

message.txt

JeranceOP•2w ago

@🔨 Moderator

harry•2w ago

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

Nurul•2w 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 clients

Gaming

Programming

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

Did you find this page helpful?