Prisma Self Relations

Basic Utils

Introduction

When working with databases, relationships are sometimes required, whereby a record in the database points to another of the same type. We refer to this as a self-relation. Think of it as a way for an entity to be linked with another instance of itself. Take Twitter as an example. A person is able to follow another person. Each user in this scenario can be linked to many other users. This shows that the user model must make self-references. Managing such self-relations is made easy using Prisma. Prisma can asists you to define and manage these relationships in your database architecture without being limited by complicated queries. Self relations can be of any cardinality. These can fall under either; prisma one-to-one self relation (1:1), prisma one-to-many self relation(1:m),prisma many-to-many self relation(m;m). In this blog, we'll go through each of these prisma self relations.

Prisma one-to-one self-relations

In a prisma 1:1 self-relation, a record in a table is connected to exactly one other record in the same table . Let's go over a concise example with Prisma.

Example Scenario

Assume we have a user model in which there can only be one manager for each user and only one manager for each user. This establishes a prisma self-relation of 1:1.

Prisma Schema

model User {
  id        Int    @id @default(autoincrement())
  name      String
  manager   User?  @relation( "ManagerUserRelation" , fields: [managerId], references: [id])
  managerId Int?   // Foreign key User's manager
  managedUser User? @relation("ManagerUserRelation")
}

Explanation

@relation("ManagerUserRelation"): This is prisma self-relations name. Its how Prisma determines that the field labeled "manager" corresponds to a different user within the same table.

fields: [managerId]: This suggests that the manager reference is stored in the managerId column.

references: [id]: This tells Prisma that managerId is a reference to the User model's id field.

Practical example of a prisma one-to-one self relation

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function createUser() {
  // Create two users
  const alice = await prisma.user.create({
    data: {
      name: 'Alice',
    },
  });

  const bob = await prisma.user.create({
    data: {
      name: 'Bob',
    },
  });

  // Set Alice as Bob's manager
  await prisma.user.update({
    where: { id: bob.id },
    data: { managerId: alice.id },
  });

  console.log('Alice and Bob created with a 1:1 self-relation');
}

createUser();

Prisma one-to-many self-relations

A record in a table can be linked to numerous other entries in the same table by a prisma one to many self-relation. This implies that one record can have multiple related records, but each related record links back to only one main record.

Example Scenario

Consider a category model in which there can be several subcategories inside each category, but each subcategory is only a part of one category. Thus, one to prisma many self-relation (1:m)

Prisma Schema

model Category {
  id            Int          @id @default(autoincrement())
  name          String
  subcategories Category[]   @relation("CategorySubcategories")
}

Explanation

@relation("CategorySubcategories"): This specifies the name of the self relation. Prisma uses this to understand that the subcategories field refers to other Category records.

Category[]: Indicates that a category can have multiple subcategories.

Practical Example of prisma one to many self relation

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function createCategories() {
  // Create a main category
  const electronics = await prisma.category.create({
    data: {
      name: 'Electronics',
    },
  });

  // Create subcategories under the main category
  await prisma.category.createMany({
    data: [
      { name: 'Laptops', parentId: electronics.id },
      { name: 'Smartphones', parentId: electronics.id },
      { name: 'Headphones', parentId: electronics.id },
    ],
  });

  console.log('Electronics category with subcategories created');
}

createCategories();

Prisma many-to-many self-relations

In a (m:m) prisma many-to-many self-relation, entries in one table may be associated to numerous other entries in the same table, and vice versa. This implies that a record may have more than one connected record, and that record may have more than one related record per record.

Example scenario of many-to-many self-relation

Consider a Person model where each person can have multiple friends, and each friend can also be friends with multiple people. This is what m:m self relation means

Prisma schema of many-to-many self-relation

model Person {
  id         Int       @id @default(autoincrement())
  name       String
  friends    Person[]  @relation("Friendships", references: [id])
  friendsOf  Person[]  @relation("Friendships")
}

Explanation

@relation("ManagerRelation"): This indicates the self relation name. This is how Prisma determines that the field labeled "manager" corresponds to a different user within the same table.

fields: [managerId]: This tells that the manager reference is stored in the managerId column.

references: [id]:This instructs Prisma that managerId is a reference to the User model's id field.

Practical example of prisma many-to-many self-relation

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function createPeopleAndFriendships() {
  // Create people
  const alice = await prisma.person.create({
    data: { name: 'Alice' },
  });

  const bob = await prisma.person.create({
    data: { name: 'Bob' },
  });

  const charlie = await prisma.person.create({
    data: { name: 'Charlie' },
  });

  // Set friendships
  await prisma.person.update({
    where: { id: alice.id },
    data: {
      friends: {
        connect: [
          { id: bob.id },
          { id: charlie.id },
        ],
      },
    },
  });

  await prisma.person.update({
    where: { id: bob.id },
    data: {
      friends: {
        connect: [
          { id: alice.id },
          { id: charlie.id },
        ],
      },
    },
  });

  await prisma.person.update({
    where: { id: charlie.id },
    data: {
      friends: {
        connect: [
          { id: alice.id },
          { id: bob.id },
        ],
      },
    },
  });

  console.log('People created and friendships set up');
}

createPeopleAndFriendships();

Summary

We have looked at how to manage prisma self-relations, a well-liked database toolkit, in this blog. Three categories of self-relations were discussed:

1:1 prisma one to one self-selations:In the same table, every record is connected to exactly one other record. We gave the example of a user model in which there is one manager for every user and one manager for every user.

1:m prisma one to many self-Relations A single record in a table is linked to several other records, but all of those records are pointing to the same primary record. We used a category model to demonstrate this, in which there can be several subcategories inside each category.

M:M prisma many to many self relationsEach record can be linked to many other records, and each of those records can also be linked to many records. . We used a Person model to illustrate this, in which a person can have many friends, and a friend can have many friends.