Self-relations

/ Concepts / Components / Prisma schema / 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:

Relational databases

MongoDB


model User {
  id          Int     @id @default(autoincrement())
  name        String?
  successorId Int?    @unique
  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. The successorId 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 a bestfriend2.

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

Relational databases

MongoDB


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

Alternatively, you could rewrite this so that predecessor is backed by predecessorId:

Relational databases only


model User {
  id            Int     @id @default(autoincrement())
  name          String?
  successor     User?   @relation("BlogOwnerHistory")
  predecessorId Int?    @unique
  predecessor   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:

Relational databases only


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:

Relational databases

MongoDB


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:

Relational databases

MongoDB


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:

Relational databases


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  Int
  following   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:

Relational databases

MongoDB


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

Was this helpful?

Edit this page on GitHub

Self-relations

One-to-one self-relations

One-to-one self relations in the database

Relational databases

MongoDB

One-to-many self relations

One-to-many self-relations in the database

Relational databases

MongoDB

Many-to-many self relations

Many-to-many self-relations in the database

Relational databases

MongoDB

Defining multiple self-relations on the same model

Was this helpful?

Product

Developers

Use Cases

Company

Newsletter