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:
schema.prisma
1model User {2 id Int @id @default(autoincrement())3 posts Post[]4}56model Post {7 id Int @id @default(autoincrement())8 author User @relation(fields: [authorId], references: [id])9 authorId Int // relation scalar field (used in the `@relation` attribute above)10}
At a Prisma level, the User
/ Post
relation is made up of:
- Two relation fields:
author
andposts
. 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 connectsPost
andUser
.
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:
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 namedauthorId
. - A primary key column in the
User
table namedid
. TheauthorId
column in thePost
table references theid
column in theUser
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:
- Creates a
User
with an auto-generatedid
(for example,20
) - Creates two new
Post
records and sets theauthorId
of both records to20
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:
- Retrieves the
User
record with anid
of20
- Retrieves all
Post
records with anauthorId
of20
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:
- Sets the
authorId
to20
on thePost
record where theid
is4
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
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 corresponds to the sample Prisma schema:
Terminology
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 @uniquerole Role @default(USER)posts Post[]}model Post {id Int @id @default(autoincrement())title Stringauthor User @relation(fields: [authorId], references: [id])authorId Int // relation scalar field (used in the `@relation` attribute above)}
These models have the following fields:
Model | Field | Type | Relation field |
---|---|---|---|
User | id | Int | No |
email | String | No | |
role | Role | No | |
posts | Post[] | Yes (Prisma-level) | |
Post | id | Int | No |
title | String | No | |
authorId | Int | No (relation scalar field) | |
author | User | Yes (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 Stringcategories CategoriesOnPosts[]}model Category {id Int @id @default(autoincrement())name Stringposts 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 Stringcategories CategoriesOnPosts[]}model Category {id Int @id @default(autoincrement())name Stringposts CategoriesOnPosts[]}model CategoriesOnPosts {post Post @relation(fields: [postId], references: [id])postId Intcategory Category @relation(fields: [categoryId], references: [id])categoryId IntcreatedAt 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 @uniquerole Role @default(USER)posts Post[]}model Post {id Int @id @default(autoincrement())title Stringauthor 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]) // ILLEGALuserId 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 @relation
attribute:
model User {firstName StringlastName Stringprofile 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.
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 onUser
) - "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 StringlastName Stringprofile 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 Stringprofile 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-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 fieldauthor
and its relation scalarauthorId
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 StringlastName Stringpost 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?}
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 Stringcategories CategoriesOnPosts[]}model Category {id Int @id @default(autoincrement())name Stringposts 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"("categoryId" int4_ops,"postId" 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
calledA
- A foreign key column that points to
Post
calledB
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()) @idname 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 thefield
andreferences
arguments. - One relation field must be backed by a foreign key. The
successor
field is backed by thesuccessorId
foreign key, which references a value in theid
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 abestfriend2
.
Either side of the relation can be backed by a foreign key. In the following example, successor
is backed by successorId
:
schema.prisma
1model User {2 id Int @default(autoincrement()) @id3 name String?4 successorId Int?5 successor User? @relation("BlogOwnerHistory", fields: [successorId], references: [id])6 predecessor User? @relation("BlogOwnerHistory")7}
In the following example, predecessor
is backed by predecessorId
:
schema.prisma
1model User {2 id Int @default(autoincrement()) @id3 name String?4 successor User? @relation("BlogOwnerHistory")5 predecessorId Int?6 predecessor User? @relation("BlogOwnerHistory", fields: [predecessorId], references: [id])7}
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 IntpinnedBy 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 IntpinnedBy User? @relation(name: "PinnedPost", fields: [pinnedById], references: [id])pinnedById Int?}