Working with Self-Relations
1. Defining Self-Referencing Model
model Category {
id Int @id @default(autoincrement())
name String
parentId Int?
parent Category? @relation("CategoryHierarchy", fields:[parentId], references:[id])
children Category[] @relation("CategoryHierarchy")
}| Required | Detail |
|---|---|
| Relation name | Mandatory for self-relations |
2. Creating One-to-One Self-Relation
model User {
id Int @id @default(autoincrement())
mentorId Int? @unique
mentor User? @relation("Mentorship", fields:[mentorId], references:[id])
mentee User? @relation("Mentorship")
}| Key | Detail |
|---|---|
| Uniqueness | @unique on FK forces 1:1 |
3. Creating One-to-Many Self-Relation
| Example | Notes |
|---|---|
| Tree / hierarchy | Category → children |
| Comments tree | Comment → replies |
4. Creating Many-to-Many Self-Relation
model User {
id Int @id @default(autoincrement())
followers User[] @relation("Followers")
following User[] @relation("Followers")
}| Aspect | Detail |
|---|---|
| Join table | Auto-created _Followers |
| Direction | Two virtual fields point to same relation name |
5. Setting Relation Names
| Why | Detail |
|---|---|
| Required | Schema invalid otherwise for self-relations |
| Convention | PascalCase descriptive label |
6. Querying Self-Related Records
const root = await prisma.category.findUnique({
where: { id: 1 },
include: { children: { include: { children: true } } }
});| Pattern | Detail |
|---|---|
| Fixed depth | Manually nest include |
| Arbitrary depth | Use recursive raw CTE |
7. Creating Hierarchical Structures
| Model | Use |
|---|---|
| Adjacency list | parentId — easy writes, expensive reads |
| Materialized path | Add path String column |
| Nested set | Fastest reads, complex writes |
8. Implementing Parent-Child Patterns
await prisma.category.create({
data: {
name: "Sub",
parent: { connect: { id: parentId } }
}
});| Op | Effect |
|---|---|
| connect parent | Set FK to existing node |
| nested create children | Build subtree in one call |
9. Handling Recursive Queries
const tree = await prisma.$queryRaw`
WITH RECURSIVE t AS (
SELECT id, name, "parentId" FROM "Category" WHERE id = ${rootId}
UNION ALL
SELECT c.id, c.name, c."parentId" FROM "Category" c JOIN t ON c."parentId" = t.id
) SELECT * FROM t`;| Tip | Detail |
|---|---|
| PostgreSQL | WITH RECURSIVE supported |
| MySQL 8+ | Same syntax |
10. Managing Circular References
| Risk | Mitigation |
|---|---|
| Cycles in hierarchy | Validate at app level before writes |
| Cascade loops | Avoid mutual Cascade — use NoAction or Restrict |
| JSON serialization | Break cycles before JSON.stringify |