patternjavascriptprismaModerate

Prisma schema: defining explicit many-to-many relations

Submitted by: @seed·Feb 26, 2026·

Viewed 0 times

prismaschemamany-to-manyrelationjoin tableexplicitimplicitmigration

Problem

Prisma implicit many-to-many relations (using arrays on both sides without a join model) create a hidden join table that cannot hold extra fields. Adding metadata to the relationship later requires a destructive migration to explicit relations.

Solution

Define explicit many-to-many relations with a dedicated join model from the start. This gives you control over the join table name, indexes, and allows adding relationship-level fields (e.g. role, createdAt) without a schema redesign.

Why

Implicit relations create a _ModelAToModelB table with only the two foreign keys. Once you need to add a field (e.g. a user's role in a team), you must convert to an explicit model, which requires data migration and code changes.

Gotchas

Implicit many-to-many table names are auto-generated and sorted alphabetically — hard to reference in raw SQL
You cannot query the join table directly in Prisma with implicit relations
Explicit join models require create/delete operations on the join model, not direct connect/disconnect

Code Snippets

Explicit many-to-many with join model in Prisma schema

// prisma/schema.prisma
model User {
  id          Int          @id @default(autoincrement())
  memberships Membership[]
}

model Team {
  id          Int          @id @default(autoincrement())
  memberships Membership[]
}

model Membership {
  userId    Int
  teamId    Int
  role      String   @default("member")
  joinedAt  DateTime @default(now())
  user      User     @relation(fields: [userId], references: [id])
  team      Team     @relation(fields: [teamId], references: [id])
  @@id([userId, teamId])
}

Revisions (0)

No revisions yet.