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 @id @default(autoincrement())name String?successorId Int? @uniquesuccessor 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. ThesuccessorId
scalar relation field also requires a@unique
attribute to guarantee a one-to-one relation.
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 previous example, repeated below, successor
is backed by successorId
:
model User {id Int @id @default(autoincrement())name String?successorId Int? @uniquesuccessor User? @relation("BlogOwnerHistory", fields: [successorId], references: [id])predecessor User? @relation("BlogOwnerHistory")}
Alternatively, you could rewrite this so that predecessor
is backed by predecessorId
:
model User {id Int @id @default(autoincrement())name String?successor User? @relation("BlogOwnerHistory")predecessorId Int? @uniquepredecessor User? @relation("BlogOwnerHistory", fields: [predecessorId], references: [id])}
No matter which side is backed by a foreign key, 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,},},},});
One-to-one self relations in the database
Relational databases
In relational databases, a one-to-one self-relation is represented by the following 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");
MongoDB
For MongoDB, Prisma currently uses a normalized data model design, which means that documents reference each other by ID in a similar way to relational databases.
The following MongoDB documents represent a one-to-one self-relation between two users:
{ "_id": { "$oid": "60d97df70080618f000e3ca9" }, "name": "Elsa the Elder" }
{"_id": { "$oid": "60d97df70080618f000e3caa" },"name": "Elsa","successorId": { "$oid": "60d97df70080618f000e3ca9" }}
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.
One-to-many self-relations in the database
Relational databases
In relational databases, a one-to-many self-relation is represented by the following 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);
Notice the lack of UNIQUE
constraint on teacherId
- multiple students can have the same teacher.
MongoDB
For MongoDB, Prisma currently uses a normalized data model design, which means that documents reference each other by ID in a similar way to relational databases.
The following MongoDB documents represent a one-to-many self-relation between three users - one teacher and two students with the same teacherId
:
{"_id": { "$oid": "60d9b9e600fe3d470079d6f9" },"name": "Ms. Roberts"}
{"_id": { "$oid": "60d9b9e600fe3d470079d6fa" },"name": "Student 8","teacherId": { "$oid": "60d9b9e600fe3d470079d6f9" }}
{"_id": { "$oid": "60d9b9e600fe3d470079d6fb" },"name": "Student 9","teacherId": { "$oid": "60d9b9e600fe3d470079d6f9" }}
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")following User[] @relation("UserFollows")}
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 for relational databases, this many-to-many-relation is implicit. This means Prisma maintains a relation table for it in the underlying database.
If you need the relation to hold other fields, you can create an explicit many-to-many self relation as well. The explicit version of the self relation shown previously is as follows:
model User {id Int @id @default(autoincrement())name String?followedBy Follows[] @relation("following")following Follows[] @relation("follower")}model Follows {follower User @relation("follower", fields: [followerId], references: [id])followerId Intfollowing User @relation("following", fields: [followingId], references: [id])followingId Int@@id([followerId, followingId])}
Many-to-many self-relations in the database
Relational databases
In relational databases, a many-to-many self-relation (implicit) is represented by the following SQL:
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);
MongoDB
For MongoDB, Prisma currently uses a normalized data model design, which means that documents reference each other by ID in a similar way to relational databases.
The following MongoDB documents represent a many-to-many self-relation between five users - two users that follow "Bob"
, and two users that follow him:
{"_id": { "$oid": "60d9866f00a3e930009a6cdd" },"name": "Bob","followedByIDs": [{ "$oid": "60d9866f00a3e930009a6cde" },{ "$oid": "60d9867000a3e930009a6cdf" }],"followingIDs": [{ "$oid": "60d9867000a3e930009a6ce0" },{ "$oid": "60d9867000a3e930009a6ce1" }]}
{"_id": { "$oid": "60d9866f00a3e930009a6cde" },"name": "Follower1","followingIDs": [{ "$oid": "60d9866f00a3e930009a6cdd" }]}
{"_id": { "$oid": "60d9867000a3e930009a6cdf" },"name": "Follower2","followingIDs": [{ "$oid": "60d9866f00a3e930009a6cdd" }]}
{"_id": { "$oid": "60d9867000a3e930009a6ce0" },"name": "CoolPerson1","followedByIDs": [{ "$oid": "60d9866f00a3e930009a6cdd" }]}
{"_id": { "$oid": "60d9867000a3e930009a6ce1" },"name": "CoolPerson2","followedByIDs": [{ "$oid": "60d9866f00a3e930009a6cdd" }]}
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?teacherId Int?teacher User? @relation("TeacherStudents", fields: [teacherId], references: [id])students User[] @relation("TeacherStudents")followedBy User[] @relation("UserFollows")following User[] @relation("UserFollows")}