Troubleshooting relations

Modelling your schema can sometimes offer up some unexpected results. This section aims to cover the most prominent of those.

How to use a relation table with a many-to-many relationship

There are a couple of ways to define a m-n relationship, implicitly or explicity. Implicitly means letting Prisma handle the relation table (JOIN table) under the hood, all you have to do is define an array/list for the non scalar types on each model, see implicit many-to-many relations.

Where you might run into trouble is when creating an explicit m-n relationship, that is, to create and handle the relation table yourself. It can be overlooked that Prisma requires both sides of the relation to be present.

Take the following example, here a relation table is created to act as the JOIN between the Post and Category tables. This will not work however as the relation table (PostCategories) must form a 1-to-many relationship with the other two models respectively.

The back relation fields are missing from the Post to PostCategories and Category to PostCategories models.

// This example schema shows how NOT to define an explicit m-n relation
model Post {
id Int @id @default(autoincrement())
title String
categories Category[] // This should refer to PostCategories
}
model PostCategories {
post Post @relation(fields: [postId], references: [id])
postId Int
category Category @relation(fields: [categoryId], references: [id])
categoryId Int
@@id([postId, categoryId])
}
model Category {
id Int @id @default(autoincrement())
name String
posts Post[] // This should refer to PostCategories
}

To fix this the Post model needs to have a many relation field defined with the relation table PostCategories. The same applies to the Category model.

This is because the relation model forms a 1-to-many relationship with the other two models its joining.

model Post {
id Int @id @default(autoincrement())
title String
categories Category[]
postCategories PostCategories[]
}
model PostCategories {
post Post @relation(fields: [postId], references: [id])
postId Int
category Category @relation(fields: [categoryId], references: [id])
categoryId Int
@@id([postId, categoryId])
}
model Category {
id Int @id @default(autoincrement())
name String
posts Post[]
postCategories PostCategories[]
}

Using the @relation attribute with a many-to-many relationship

It might seem logical to add a @relation("Post") annotation to a relation field on your model when composing an implicit many-to-many relationship.

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

This however tells Prisma to expect two separate many-to-many relationships. See disambiguating relations for more information on using the @relation attribute.

The following example is the correct way to define an implicit many-to-many relationship.

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

The @relation annotation can also be used to name the underlying relation table created on a implicit m-n relationship.

model Post {
id Int @id @default(autoincrement())
title String
categories Category[] @relation("CategoryPostRelation")
}
model Category {
id Int @id @default(autoincrement())
name String
posts Post[] @relation("CategoryPostRelation")
}
Edit this page on GitHub