[SOLVED] Implicit or Explicit Many to Many relationship in prisma

Implicit or Explicit Many to Many relationship in prisma

When should you use a implicit many to many relationship in prisma and when explicit many to many relationship ?

Do they have any trade-off or anything that should be noted

Solution

Short answer: Prefer the implicit relationship unless you need to store additional meta-information about the relation itself.

For example, a plain n-m relation between Post and Category would look like this in the implicit version:

model Post {
  id         Int        @id @default(autoincrement())
  title      String
  categories Category[]
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]
}

Now, if you need to store meta-data about this relation, e.g. information when an Post has been added to a Category, you should create an explicit version:

model Post {
  id         Int                 @id @default(autoincrement())
  title      String
  categories CategoriesOnPosts[]
}

model Category {
  id    Int                 @id @default(autoincrement())
  name  String
  posts CategoriesOnPosts[]
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int 

  assignedAt DateTime @default(now())

  @@id([postId, categoryId])
}

The main tradeoff really is convenience. It's much simpler to work with an implicit relationship because the relation table is maintained for you under the hood. Also, the relation queries in the Prisma Client API are easier to work with because you save "one hop" in the connect API (with an explicit relation table, you always have to "go through" the relation table in your Prisma Client queries).

Also, you can migrate an implicit relation to an explicit relation later down the line. So you can always start with an implicit one and turn it into an explicit relation later when needed.