Skip to main content

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:

Hide query results

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):

Hide query results

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 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")
  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 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 (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.