Overview

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.

The following entity relationship diagram defines the same one-to-many relation between the User and Post tables in a database:

A 1-n relationship between a user and posts table.

In SQL, you use a foreign key to create a relation between two tables. Foreign keys are stored on one side of the relation. Our example is made up of:

  • A foreign key column in the Post table named authorId.
  • A primary key column in the User table named id. The authorId column in the Post table references the id column in the User table.

In the Prisma schema, the foreign key / primary key relationship is represented by the @relation attribute on the author field:

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

Note: Relations in the Prisma schema represents relationships that exist between tables in the database. If the relationship does not exist in the database, it does not exist in the Prisma schema.

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 example retrieves a User by id and includes any related Post records:

const getAuthor = await prisma.user.findOne({
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:

The following Prisma schema includes every type of relation:

  • 1-1: UserProfile
  • 1-n: UserPost
  • m-n: PostCategory
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[]
}

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 correponds to the sample Prisma schema:

The sample schema as an entity relationship diagram

Terminology

Relation fields

Relation [fields](data-model#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:

ModelFieldTypeRelation field
UseridIntNo
emailStringNo
roleRoleNo
postsPost[]Yes (Prisma-level)
PostidIntNo
titleStringNo
authorIdIntNo (relation scalar field)
authorUserYes (annotated)

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.

Explicit many-to-many relations

Explicit many-to-many relations define an extra model which represents a relation table (also sometimes called JOIN, link or pivot table) in the underlying database:

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

Implicit many-to-many relations

Implicit many-to-many relations define relation fields as lists on both sides of the relation:

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

Note: Implicit many-to-many relations do not require the @relation attribute unless you need to disambiguate relations (this is not specific to implicit n-m relations).

Implicit many-to-many relations require both models to have a single @id. Be aware that:

  • You cannot use a multi-field ID
  • You cannot use a @unique in place of an @id

To use either of these features, you must set up an explicit many-to-many instead.

The implicit m-n-relation still manifests in a relation table in the underlying database. However, this relation table is managed by Prisma.

Using an implicit instead of an explicit m-n relations makes the Prisma Client API for many-to-many relations a bit simpler (since you e.g. have one fewer level of nesting inside of nested writes).

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.

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 PostCategoriesOnPosts and CategoryCategoriesOnPosts 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 @relation attribute

The @relation attribute can only be applied to the relation fields, not to scalar fields. The attributes is required on one side of 1-1 and 1-n relations.

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

Here are some example scenarios when the @relation attribute is needed, e.g. 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 need to control how the relation table is represented in the underlying database (e.g. use a specific name for a relation table)

Note: The leading underscore in the signature means that the argument name can be omitted.

One-to-one relations

One-to-one (1-1) relations refer to relations where at most one record can be connected on both sides of the relation.

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")
);

Making both sides required in one-to-one relations

When using Prisma, you can "augment" a 1-1-relation and make it required on both sides:

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
}

This 1-1-relation now expresses the following:

  • "a user must always have one profile" (because the profile field is required on User)
  • "a profile must always be connected to one user"

Making the profile field on User required does not impact the schema in the underlying database since relational databases are not able to model such a constraint. This constraint is implemented and enforced by Prisma's query engine.

Note: One-to-one self-relations must not be made required on both sides, at least one side always must be declared optional.

It's important to note that in the case of required 1-1-relations, the only way to create a new record for either User or Profile is by using nested writes:

const user = await prisma.user.create({
data: {
name: "Alice"
profile: {
create: {
bio: "Hello World"
}
}
}
})
// or
const user = await prisma.profile.create({
data: {
bio: "I like turtles"
user: {
create: {
name: "Bob"
}
}
}
})

If you're using introspection, you can make 1-1-relations required by manually adjusting your Prisma schema and making both relation fields required, then you can re-generate Prisma Client which will now make sure that the required 1-1-relation is enforced by the query engine.

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.

This is what the relation looks like in the 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:

Both relation fields 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 profile column of the User table:

One-to-many relations

One-to-many (1-n) relations refer to relations where one record on one side of the relation can be connected to zero or more records on the other side.

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:

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

Many-to-many relations

Many-to-many (m-n) relations refer to relations where zero or more records on one side of the relation can be connected to zero or more records on the other side. m-n-relations are typically modelled via relation tables in a relational database. m-n-relations can be either explicit or implicit in the Prisma schema.

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.

Table name

The name of the relation table must be prefixed with an underscore:

  • Valid: _CategoryToPost, _MyRelation
  • Invalid: CategoryToPost, MyRelation

Columns

A relation table for an implicit-many-to-many relation must have exactly two columns:

  • A foreign key column that points to Category called A
  • A foreign key column that points to Post called B

The columns must be called A and B where A points to the model that comes first in the alphabet and B points to the model which comes last in the alphabet.

Indexes

There further must be:

  • A unique index defined on both foreign key columns:
CREATE UNIQUE INDEX "_CategoryToPost_AB_unique" ON "_CategoryToPost"("A" int4_ops,"B" int4_ops);
  • A non-unique index defined on B:
CREATE INDEX "_CategoryToPost_B_index" ON "_CategoryToPost"("B" int4_ops);

Example

This the a sample SQL statement that would create the three tables including indexes (in PostgreSQL dialect):

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

Self-relations

A relation field can also reference its own model, in this case the relation is called a self-relation. Self-relations can be of any cardinality, 1-1, 1-n and m-n.

Note that self-relations always require the @relation attribute.

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

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.writtenPostsPost.author + Post.authorId
  • User.writtenPostsPost.pinnedBy + Post.pinnedById
  • User.pinnedPostPost.author + Post.authorId
  • User.pinnedPostPost.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?
}
Edit this page on GitHub