Relations

A relation is a connection between two models in the Prisma schema. For example, there is a one-to-many relation between User and Post because one user can have many blog posts.

The following Prisma schema defines a one-to-many relation between the User and Post models. The fields involved in defining the relation are highlighted:

model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id       Int  @id @default(autoincrement())
  author   User @relation(fields: [authorId], references: [id])
  authorId Int // relation scalar field  (used in the `@relation` attribute above)
}

At a Prisma level, the User / Post relation is made up of:

• Two relation fields: author and posts. Relation fields define connections between models at the Prisma level and do not exist in the database. These fields are used to generate the Prisma Client.
• The scalar authorId field, which is referenced by the @relation attribute. This field does exist in the database - it is the foreign key that connects Post and User.

At a Prisma level, a connection between two models is always represented by a relation field on each side of the relation.

Relations in the database

author     User        @relation(fields: [authorId], references: [id])

{ "_id": { "$oid": "60d5922d00581b8f0062e3a8" }, "name": "Ella" }

[
  {
    "_id": { "$oid": "60d5922e00581b8f0062e3a9" },
    "title": "How to make sushi",
    "authorId": { "$oid": "60d5922d00581b8f0062e3a8" }
  },
  {
    "_id": { "$oid": "60d5922e00581b8f0062e3aa" },
    "title": "How to re-install Windows",
    "authorId": { "$oid": "60d5922d00581b8f0062e3a8" }
  }
]

model User {
  id    String @id @default(auto()) @map("_id") @db.ObjectId
  posts Post[]
}

model Post {
  id       String @id @default(auto()) @map("_id") @db.ObjectId
  author   User   @relation(fields: [authorId], references: [id])
  authorId String @db.ObjectId // relation scalar field  (used in the `@relation` attribute above)
}

Relations in the Prisma Client

The Prisma Client is generated from the Prisma schema. The following examples demonstrate how relations manifest when you use the Prisma Client to get, create, and update records.

The following query creates a User record and two connected Post records:

const userAndPosts = await prisma.user.create({
  data: {
    posts: {
      create: [
        { title: 'Prisma Day 2020' }, // Populates authorId with user's id
        { title: 'How to write a Prisma schema' }, // Populates authorId with user's id
      ],
    },
  },
})

In the underlying database, this query:

1. Creates a User with an auto-generated id (for example, 20)
2. Creates two new Post records and sets the authorId of both records to 20

The following query retrieves a User by id and includes any related Post records:

const getAuthor = await prisma.user.findUnique({
  where: {
    id: "20",
  },
  include: {
    posts: true, // All posts where authorId == 20
  },
});

In the underlying database, this query:

1. Retrieves the User record with an id of 20
2. Retrieves all Post records with an authorId of 20

The following query associates an existing Post record with an existing User record:

const updateAuthor = await prisma.user.update({
  where: {
    id: 20,
  },
  data: {
    posts: {
      connect: {
        id: 4,
      },
    },
  },
})

In the underlying database, this query:

1. Sets the authorId to 20 on the Post record where the id is 4

Types of relations

There are three different types (or cardinalities) of relations in Prisma:

• One-to-one (also called 1-1-relation)
• One-to-many (also called 1-n-relation)
• Many-to-many (also called m-n-relation)

The following Prisma schema includes every type of relation:

• 1-1: User ↔ Profile
• 1-n: User ↔ Post
• m-n: Post ↔ Category

Notice that the syntax is slightly different between relational databases and MongoDB - particularly for many-to-many relations.

Note: This schema is the same as the example data model but has all scalar fields removed (except for the required relation scalars) so you can focus on the relation fields.

The following entity relationship diagram represents the database that corresponds to the sample Prisma schema:

Terminology

model User {
  id      Int      @id @default(autoincrement())
  posts   Post[]
  profile Profile?
}

model Profile {
  id     Int  @id @default(autoincrement())
  user   User @relation(fields: [userId], references: [id])
  userId Int // relation scalar field (used in the `@relation` attribute above)
}

model Post {
  id         Int        @id @default(autoincrement())
  author     User       @relation(fields: [authorId], references: [id])
  authorId   Int // relation scalar field  (used in the `@relation` attribute above)
  categories Category[]
}

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

{ "_id": { "$oid": "60d5922d00581b8f0062e3a8" }, "name": "Ella" }

[
  {
    "_id": { "$oid": "60d5922e00581b8f0062e3a9" },
    "title": "How to make sushi",
    "authorId": { "$oid": "60d5922d00581b8f0062e3a8" }
  },
  {
    "_id": { "$oid": "60d5922e00581b8f0062e3aa" },
    "title": "How to re-install Windows",
    "authorId": { "$oid": "60d5922d00581b8f0062e3a8" }
  }
]

Relation fields

Relation fields are fields on a Prisma model that do not have a scalar type. Instead, their type is another model.

Every relation must have exactly two relation fields, one on each model. In case of 1-1 and 1-n relations, an additional relation scalar field is required which gets linked by one of the two relation fields in the @relation attribute. This relation scalar is the direct representation of the foreign key in the underlying database.

Consider these two models:

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  role  Role   @default(USER)
  posts Post[]
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User   @relation(fields: [authorId], references: [id])
  authorId Int // relation scalar field (used in the `@relation` attribute above)
}

These models have the following fields:

Both posts and author are relation fields because their types are not scalar types but other models.

Also note that the annotated relation field author needs to link the relation scalar field authorId on the Post model inside the @relation attribute. The relation scalar represents the foreign key in the underlying database.

The other relation field called posts is defined purely on a Prisma-level, it doesn't manifest in the database.

author     User    @relation(fields: [authorId], references: [id])
authorId   Int

The @relation attribute

The @relation attribute can only be applied to the relation fields, not to scalar fields.

The @relation attribute is required when:

• you define a 1-1 or 1-n relation, it is required on one side of the relation (with the corresponding relation scalar field)
• you need to disambiguate a relation (that's e.g. the case when you have two relations between the same models)
• you define a self-relation
• you define an m-n for MongoDB
• you need to control how the relation table is represented in the underlying database (e.g. use a specific name for a relation table)

Note: Implicit m-n relations in relational databases do not require the @relation attribute.

Disambiguating relations

When you define two relations between two the same models, you need to add the name argument in the @relation attribute to disambiguate them. As an example for why that's needed, consider the following models:

model User {
  id           Int     @id @default(autoincrement())
  name         String?
  writtenPosts Post[]
  pinnedPost   Post?
}

model Post {
  id         Int     @id @default(autoincrement())
  title      String?
  author     User    @relation(fields: [authorId], references: [id])
  authorId   Int
  pinnedBy   User?   @relation(fields: [pinnedById], references: [id])
  pinnedById Int?
}

In that case, the relations are ambiguous, there are four different ways to interpret them:

• User.writtenPosts ↔ Post.author + Post.authorId
• User.writtenPosts ↔ Post.pinnedBy + Post.pinnedById
• User.pinnedPost ↔ Post.author + Post.authorId
• User.pinnedPost ↔ Post.pinnedBy + Post.pinnedById

To disambiguate these relations, you need to annotate the relation fields with the @relation attribute and provide the name argument. You can set any name (except for the empty string ""), but it must be the same on both sides of the relation:

model User {
  id           Int     @id @default(autoincrement())
  name         String?
  writtenPosts Post[]  @relation("WrittenPosts")
  pinnedPost   Post?   @relation("PinnedPost")
}

model Post {
  id         Int     @id @default(autoincrement())
  title      String?
  author     User    @relation("WrittenPosts", fields: [authorId], references: [id])
  authorId   Int
  pinnedBy   User?   @relation(name: "PinnedPost", fields: [pinnedById], references: [id])
  pinnedById Int?
}