Troubleshooting relations

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

Implicit many-to-many self-relations return incorrect data if order of relation fields change

Problem

In the following implicit many-to-many self-relation, the lexicographic order of relation fields in a_eats (1) and b_eatenBy (2):

model Animal {
id Int @id @default(autoincrement())
name String
a_eats Animal[] @relation(name: "FoodChain")
b_eatenBy Animal[] @relation(name: "FoodChain")
}

The resulting relation table in SQL looks as follows, where A represents prey (a_eats) and B represents predators (b_eatenBy):

AB
8 (Plankton)7 (Salmon)
7 (Salmon)9 (Bear)

The following query returns a salmon's prey and predators:

const getAnimals = await prisma.animal.findMany({
where: {
name: 'Salmon',
},
include: {
b_eats: true,
a_eatenBy: true,
},
})
Hide query results
{
"id": 7,
"name": "Salmon",
"b_eats": [
{
"id": 8,
"name": "Plankton"
}
],
"a_eatenBy": [
{
"id": 9,
"name": "Bear"
}
]
}

Now change the order of the relation fields:

model Animal {
id Int @id @default(autoincrement())
name String
b_eats Animal[] @relation(name: "FoodChain")
a_eatenBy Animal[] @relation(name: "FoodChain")
}

Migrate your changes and re-generate Prisma Client. When you run the same query with the updated field names, Prisma Client returns incorrect data (salmon now eats bears and gets eaten by plankton):

const getAnimals = await prisma.animal.findMany({
where: {
name: 'Salmon',
},
include: {
b_eats: true,
a_eatenBy: true,
},
})
Hide query results
{
"id": 1,
"name": "Salmon",
"b_eats": [
{
"id": 3,
"name": "Bear"
}
],
"a_eatenBy": [
{
"id": 2,
"name": "Plankton"
}
]
}

Although the lexicographic order of the relation fields in the Prisma schema changed, columns A and B in the database did not change (they were not renamed and data was not moved). Therefore, A now represents predators (a_eatenBy) and B represents prey (b_eats):

AB
8 (Plankton)7 (Salmon)
7 (Salmon)9 (Bear)

Solution

If you rename relation fields in an implicit many-to-many self-relations, make sure that you maintain the alphabetic order of the fields - for example, by prefixing with a_ and _b.

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 explicitly. Implicitly means letting Prisma ORM 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 ORM 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")
Category Category? @relation("Post", fields: [categoryId], references: [id])
categoryId Int?
}
model Category {
id Int @id @default(autoincrement())
name String
posts Post[] @relation("Post")
Post Post? @relation("Category", fields: [postId], references: [id])
postId Int?
}

This however tells Prisma ORM to expect two separate one-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 many-to-many 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")
}

Using m-n relations in databases with enforced primary keys

Problem

Some cloud providers enforce the existence of primary keys in all tables. However, any relation tables (JOIN tables) created by Prisma ORM (expressed via @relation) for many-to-many relations using implicit syntax do not have primary keys.

Solution

You need to use explicit relation syntax, manually create the join model, and verify that this join model has a primary key.