Relations

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.

Implicit vs explicit many-to-many relations

Many-to-many relations can be either implicit or explicit in the Prisma schema.

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       // relation scalar field (used in the `@relation` attribute above)
  category    Category @relation(fields: [categoryId], references: [id])
  categoryId  Int      // relation scalar field (used in the `@relation` attribute above)
  @@id([postId, categoryId])
}

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

Relation tables

A relation table (also sometimes called JOIN, link or pivot table) connects two or more other tables and therefore creates a relation between them. Creating relation tables is a common data modeling practice in SQL to represent relationships between different entities. In essence it means that "one m-n relation is modeled as two 1-n relations in the database".

When using Prisma, you can create relation tables by defining models similar to how you would model them as tables. The main difference is that the fields of the relation table are both annotated relation fields with a corresponding relation scalar field.

Relation tables are also often used to add "meta-information" to a relation. For example, to store when the relation was created.

Here is an example for a relation table called CategoriesOnPosts:

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: [catgoryId], references: [id])
  catgoryId   Int
  createdAt   DateTime @default(now())
  @@id([postId, categoryId])
}

In this example, the createdAt field stores additional information about the relation between Post and Category (i.e. it stores the point in time when "the post was added to the category").

Note that the same rules as for 1-n-relations apply (because Post↔ CategoriesOnPosts and Category ↔ CategoriesOnPosts are both in fact 1-n-relations), which means one side of the relation needs to be annotated with the @relation attribute.

When you don't need to attach additional information to the relation, you can model m-n-relations as implicit many-to-many relations. If you're not using Prisma Migrate but obtain your data model from introspection, you can still make use of implicit many-to-many relations by following Prisma's conventions for relation tables.

Annotated relation fields and relation scalar fields

1-1 and 1-n relations require one side of the relation to be annotated with the @relation attribute, they're therefore commonly referred to as annotated relation fields.

The side of the relation which is annotated with the @relation attribute represents the side that stores the foreign key in the underlying database. The "actual" field that represents the foreign key is required on that side of the relation as well, it's called relation scalar field and must be used in the @relation attribute on the annotated relation field. A scalar field becomes a relation scalar field when it's used in the fields of an @relation attribute.

Because a relation scalar field always belongs to a relation field, the two are often named the same but the relation scalar is suffixed with Id. For example, assume an annotated relation field is called author, the corresponding relation scalar would be called authorId according to this convention.

Note that relation scalar fields are read-only in the generated Prisma Client API. If you want to update a relation in your code, you can do so using nested writes.

In 1-1 relations, you can decide yourself which side of the relation you want to annotate with the @relation attribute (and therefore holds the foreign key).

This example annotates the relation field on the Profile model:

model User {
  id      Int      @id @default(autoincrement())
  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)
}

In the code above, the userId relation scalar is a direct representation of the foreign key in the underlying database.

This example annotates the relation field on the User model:

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

In the code above, profileId is a direct representation of the foreign key in the underlying database.

In 1-n-relations, you must annotate the non-list field with the @relation attribute:

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

Note that for 1-1 and 1-n relations, the annotated relation field and its relation scalar field must either both be optional or both be required. So, the following would be illegal because user is optional but userId is required:

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

With multi-field IDs, there might also be multiple relation scalars on the same model which are both used in the @relationattribute:

model User {
  firstName String
  lastName  String
  profile   Profile?
  @@id([firstName, lastName])
}
model Profile {
  id               Int      @id @default(autoincrement())
  user             User     @relation(fields: [authorFirstName, authorLastName], references: [firstName, lastName])
  authorFirstName  String   // relation scalar field (used in the `@relation` attribute above)
  authorLastName   String   // relation scalar field (used in the `@relation` attribute above)
}

The side without a relation scalar must be optional

In a one-to-one relation, the side of the relation without a relation scalar (the field representing the foreign key in the database) must be optional:

model User {
  id        Int       @id @default(autoincrement())
  profile   Profile? // No relation scalar - must be optional
}

This restriction was introduced in 2.12.0.

Example

In the example below, there's one 1-1-relation between User and Profile:

model User {
  id        Int       @id @default(autoincrement())
  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)
}

In the code above, the userId relation scalar is a direct representation of the foreign key in the underlying database.

This 1-1-relation expresses the following:

• "a user can have zero or one profiles" (because the profile field is optional on User)
• "a profile must always be connected to one user"

In SQL, this is typically modeled as follows:

CREATE TABLE "User" (
    id SERIAL PRIMARY KEY
);
CREATE TABLE "Profile" (
    id SERIAL PRIMARY KEY,
    "userId" INTEGER NOT NULL UNIQUE,
    FOREIGN KEY ("userId") REFERENCES "User"(id)
);

Notice the UNIQUE constraint on the foreign key userId. If this UNIQUE constraint was missing, the relation would be considered a 1-n relation.

You can also define this relation using multi-field IDs:

model User {
  firstName String
  lastName  String
  profile   Profile?
  @@id([firstName, lastName])
}
model Profile {
  id               Int      @id @default(autoincrement())
  user             User     @relation(fields: [userFirstName, userLastName], references: [firstName, lastName])
  userFirstName  String     // relation scalar field (used in the `@relation` attribute above)
  userLastName   String     // relation scalar field (used in the `@relation` attribute above)
}

In this case, there are two relation scalar fields on Profile. This is what the models map to in SQL:

CREATE TABLE "User" (
    firstName TEXT,
    lastName TEXT,
    PRIMARY KEY ("firstName","lastName")
);
CREATE TABLE "Profile" (
    id SERIAL PRIMARY KEY,
    "userFirstName" TEXT NOT NULL UNIQUE,
    "userLastName" TEXT NOT NULL UNIQUE,
    FOREIGN KEY ("userFirstName", "userLastName") REFERENCES "User"("firstName", "lastName")
);

Determining the side of the foreign key

Consider again the above 1-1-relation between User and Profile. The relation field on the Profile model is annotated with the @relation attribute:

model User {
  id        Int      @id @default(autoincrement())
  name      String
  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)
}

You can also annotate the other side of the relation with the @relation attribute. The following example annotates the relation field on the User model:

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

In the code above, profileId is a direct representation of the foreign key in the underlying database.

However, you can still determine yourself on which side of the relation the foreign key should be stored. To store the foreign key on User you need to add the @relation attribute to its relation field and add a corresponding relation scalar field:

One relation field required but using @relation to determine the foreign key

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

Both relation fields optional but using @relation to determine the foreign key*

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

In both cases, the foreign key is now defined on the profileId column of the User table.

One-to-one vs one-to-many relations

In relational databases, the main difference between a 1-1 and a 1-n-relation is that in a 1-1-relation the foreign key must have a UNIQUE constraint defined on it.

Example

In the example below, there's one 1-n-relation between User and Post:

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
}

Note The posts field does not "manifest" in the underlying database schema. On the other side of the relation, the annotated relation field author and its relation scalar authorId represent the side of the relation that stores the foreign key in the underlying database.

This 1-n-relation expresses the following:

• "a user can have zero or more posts"
• "a post must always have an author"

In SQL, this is typically modeled as follows:

CREATE TABLE "User" (
    id SERIAL PRIMARY KEY
);
CREATE TABLE "Post" (
    id SERIAL PRIMARY KEY,
    "authorId" integer NOT NULL,
    FOREIGN KEY ("authorId") REFERENCES "User"(id)
);

Since there's no UNIQUE constraint on the author column (foreign key), you can create multiple Post records that point to the same User record therefore creating a one-to-many relationship between the two tables.

You can also define this relation using multi-field IDs:

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

In this case, there are two relation scalar fields on Post. This is what the models map to in SQL:

CREATE TABLE "User" (
    firstName TEXT,
    lastName TEXT,
    PRIMARY KEY ("firstName","lastName")
);
CREATE TABLE "Post" (
    id SERIAL PRIMARY KEY,
    "authorFirstName" TEXT NOT NULL UNIQUE,
    "authorLastName" TEXT NOT NULL UNIQUE,
    FOREIGN KEY ("authorFirstName", "authorLastName") REFERENCES "User"("firstName", "lastName")
);

Required vs optional relation fields in one-to-many relations

A 1-n-relation always has two relation fields:

• a list relation field which is not annotated with @relation
• the annotated relation field (including its relation scalar)

The relation fields in a 1-n relation can take the following forms:

• the annotated relation field can be either optional or required
  • if the annotated relation field is required, the relation scalar must be required
  • if the annotated relation field is optional, the relation scalar must be optional
• the other side of the relation must be a list and is always required

So, the following variant of the example above would be allowed:

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?
}

But this one would be not allowed:

model User {
  id        Int      @id @default(autoincrement())
  posts     Post[]?  // illegal
}
model Post {
  id        Int    @id @default(autoincrement())
  author    User?  @relation(fields: [authorId], references: [id])
  authorId  Int?
}

Example

In the example below, there's one implicit m-n-relation between Post and Category:

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

Implicit many-to-many relations are maintained by Prisma with a relation table that's not reflected in the Prisma schema. An implicit m-n-relation does not require @relation attribute.

The implicit m-n-relation maps to the following tables (following Prisma's conventions for relation tables):

CREATE TABLE "Category" (
    id SERIAL PRIMARY KEY
);
CREATE TABLE "Post" (
    id SERIAL PRIMARY KEY
);
-- Relation table + indexes -------------------------------------------------------
CREATE TABLE "_CategoryToPost" (
    "A" integer NOT NULL REFERENCES "Category"(id),
    "B" integer NOT NULL REFERENCES "Post"(id)
);
CREATE UNIQUE INDEX "_CategoryToPost_AB_unique" ON "_CategoryToPost"("A" int4_ops,"B" int4_ops);
CREATE INDEX "_CategoryToPost_B_index" ON "_CategoryToPost"("B" int4_ops);

An explicit variant of a similar m-n-relation would define an extra model to represent a relation table. In this case, you can also attach additional information to the relation (such as the point in time when it was created):

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       // relation scalar field (used in the `@relation` attribute above)
  category    Category @relation(fields: [categoryId], references: [id])
  categoryId  Int      // relation scalar field (used in the `@relation` attribute above)
  createdAt   DateTime @default(now())
  @@id([postId, categoryId])
}

This would be represented as follows in SQL:

CREATE TABLE "Category" (
    id SERIAL PRIMARY KEY
);
CREATE TABLE "Post" (
    id SERIAL PRIMARY KEY
);
-- Relation table + indexes -------------------------------------------------------
CREATE TABLE "CategoryToPost" (
    "categoryId" integer NOT NULL,
    "postId" integer NOT NULL,
    "createdAt" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY ("categoryId")  REFERENCES "Category"(id),
    FOREIGN KEY ("postId") REFERENCES "Post"(id)
);
CREATE UNIQUE INDEX "CategoryToPost_category_post_unique" ON "CategoryToPost"("category" int4_ops,"post" int4_ops);

Conventions for relation tables in implicit m-n-relations

If you're not using Prisma Migrate but obtain your data model from introspection, you can still make use of implicit many-to-many relations by following Prisma's conventions for relation tables. For the following example, assume you want to create a relation table to get an implicit many-to-many relation for two models called Post and Category.

CREATE UNIQUE INDEX "_CategoryToPost_AB_unique" ON "_CategoryToPost"("A" int4_ops,"B" int4_ops);

CREATE INDEX "_CategoryToPost_B_index" ON "_CategoryToPost"("B" int4_ops);

CREATE TABLE "_CategoryToPost" (
    "A" integer NOT NULL REFERENCES "Category"(id) ,
    "B" integer NOT NULL REFERENCES "Post"(id)
);
CREATE UNIQUE INDEX "_CategoryToPost_AB_unique" ON "_CategoryToPost"("A" int4_ops,"B" int4_ops);
CREATE INDEX "_CategoryToPost_B_index" ON "_CategoryToPost"("B" int4_ops);
CREATE TABLE "Category" (
    id integer SERIAL PRIMARY KEY
);
CREATE TABLE "Post" (
    id integer SERIAL PRIMARY KEY
);

Configuring the name of the relation table in implicit many-to-many relations

When using Prisma Migrate, you can configure the name of the relation table that's managed by Prisma using the @relation attribute. The only requirement is that it starts with an underscore. For example, if you want the relation table to be called _MyRelationTable instead of the default name _CategoryToPost, you can specify it as follows:

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

One-to-one self-relations

The following example models a one-to-one self-relation:

model User {
  id             Int     @default(autoincrement()) @id
  name           String?
  successorId Int?
  successor   User?   @relation("BlogOwnerHistory", fields: [successorId], references: [id])
  predecessor User?   @relation("BlogOwnerHistory")
}

This relation expresses the following:

• "a user can have one or zero predecessors" (for example, Sarah is Mary's predecessor as blog owner)
• "a user can have one or zero successors" (for example, Mary is Sarah's successor as blog owner)

Note: One-to-one self-relations cannot be made required on both sides. One or both sides must be optional, otherwise it becomes impossible to create the first User record.

To create a one-to-one self-relation:

• Both sides of the relation must define a @relation attribute that share the same name - in this case, BlogOwnerHistory.
• One relation field must be a fully annotated. In this example, the successor field defines both the field and references arguments.
• One relation field must be backed by a foreign key. The successor field is backed by the successorId foreign key, which references a value in the id field.

Note: One-to-one self relations require two sides even if both sides are equal in the relationship. For example, to model a 'best friends' relation, you would need to create two relation fields: bestfriend1 and a bestfriend2.

Either side of the relation can be backed by a foreign key. In the following example, successor is backed by successorId:

model User {
  id             Int     @default(autoincrement()) @id
  name           String?
 successorId Int?
 successor   User?   @relation("BlogOwnerHistory", fields: [successorId], references: [id])
  predecessor User?   @relation("BlogOwnerHistory")
}

In the following example, predecessor is backed by predecessorId:

model User {
  id             Int     @default(autoincrement()) @id
  name           String?
  successor   User?   @relation("BlogOwnerHistory")
 predecessorId Int?
 predecessor User?   @relation("BlogOwnerHistory", fields: [predecessorId], references: [id])
}

No matter which side is backed by a foreign key, the Prisma Client surfaces both the predecessor and successor fields:

  const x = await prisma.user.create({
    data: {
      name: "Bob McBob",
      successor: {
        connect: {
          id: 2,
        },
      },
      predecessor: {
        connect: {
          id: 4,
        },
      },
    },
  });

This relation is represented as follows in SQL:

CREATE TABLE "User" (
    id SERIAL PRIMARY KEY,
    "name" TEXT,
    "successorId" INTEGER
);
ALTER TABLE "User" ADD CONSTRAINT fk_successor_user FOREIGN KEY ("successorId") REFERENCES "User" (id);
ALTER TABLE "User" ADD CONSTRAINT successor_unique UNIQUE ("successorId");

One-to-many self relations

A one-to-many self-relation looks as follows:

model User {
  id       Int      @id @default(autoincrement())
  name     String?
  teacherId Int?
  teacher  User?     @relation("TeacherStudents", fields: [teacherId], references: [id])
  students User[]   @relation("TeacherStudents")
}

This relation expresses the following:

• "a user has zero or one teachers "
• "a user can have zero or more students"

Note that you can also require each user to have a teacher by making the teacher field required.

This relation is represented as follows in SQL:

CREATE TABLE "User" (
    id SERIAL PRIMARY KEY,
"name" TEXT,
    "teacherId" INTEGER
);
ALTER TABLE "User" ADD CONSTRAINT fk_teacherid_user FOREIGN KEY ("teacherId") REFERENCES "User" (id);

Many-to-many self relations

A many-to-many self-relation looks as follows:

model User {
  id          Int      @id @default(autoincrement())
  name        String?
  followedBy  User[]   @relation("UserFollows", references: [id])
  following   User[]   @relation("UserFollows", references: [id])
}

This relation expresses the following:

• "a user can be followed by zero or more users"
• "a user can follow zero or more users"

Note that this n-n-relation is implicit. This means Prisma maintains a relation table for it in the underlying database:

CREATE TABLE "User" (
    id integer DEFAULT nextval('"User_id_seq"'::regclass) PRIMARY KEY,
    name text
);
CREATE TABLE "_UserFollows" (
    "A" integer NOT NULL REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
    "B" integer NOT NULL REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE
);

Defining multiple self-relations on the same model

You can also define multiple self-relations on the same model at once. Taking all relations from the previous sections as example, you could define a User model as follows:

model User {
  id          Int      @id @default(autoincrement())
  name        String?
  husband     User?    @relation("MarriagePartners")
  wife        User     @relation("MarriagePartners")
  teacher     User?    @relation("TeacherStudents")
  students    User[]   @relation("TeacherStudents")
  followedBy  User[]   @relation("UserFollows")
  following   User[]   @relation("UserFollows")
}