Prisma: A Comprehensive Guide to Managing Relationships with set, deleteMany, and disconnect

Prisma: set, deleteMany, and disconnect

Let's dive into how Prisma handles set, deleteMany, and disconnect operations in cases of one-to-one, one-to-many, and many-to-many relations, using simple and clear explanations along with examples in a NestJS project using Prisma.

1. One-to-One Relations

In a one-to-one relationship, each record in a table is linked to one (and only one) record in another table. Let's assume we have two models: User and Profile, where each User has one Profile.

Prisma Schema

model User {
  id      Int     @id @default(autoincrement())
  name    String
  profile Profile?
}

model Profile {
  id      Int    @id @default(autoincrement())
  bio     String
  userId  Int    @unique
  user    User   @relation(fields: [userId], references: [id])
}

set: Assign or update the relation

You can use set to assign or update the relation between User and Profile.

Example:

// Update user profile by setting a new profile
await this.prisma.user.update({
  where: { id: 1 },
  data: {
    profile: {
      set: { id: 2 }, // Assign existing profile with id 2 to the user
    }
  },
});

disconnect: Break the relationship without deleting the related record

If you want to break the connection between User and Profile without deleting the Profile, you can use disconnect.

Example:

// Disconnect profile from the user
await this.prisma.user.update({
  where: { id: 1 },
  data: {
    profile: {
      disconnect: true, // This removes the connection between User and Profile
    }
  },
});

deleteMany: Not applicable in one-to-one relations

In a one-to-one relation, deleteMany does not apply because there's only one record in the related table.

---

2. One-to-Many Relations

In a one-to-many relationship, one record in a table is related to multiple records in another table. Let's assume we have User and Post, where each User can have many Posts.

Prisma Schema

model User {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]
}

model Post {
  id     Int    @id @default(autoincrement())
  title  String
  userId Int
  user   User   @relation(fields: [userId], references: [id])
}

set: Replace all related records

The set operation replaces all the related records. If you use set, it will remove any previous relations and replace them with the new ones.

Example:

// Replace all user posts with a new set of posts
await this.prisma.user.update({
  where: { id: 1 },
  data: {
    posts: {
      set: [{ id: 1 }, { id: 2 }] // Replace existing posts with post IDs 1 and 2
    }
  }
});

disconnect: Break the relation without deleting the related records

If you want to remove the connection between the User and some Posts without deleting the Post records, you can use disconnect.

Example:

// Disconnect a specific post from the user
await this.prisma.user.update({
  where: { id: 1 },
  data: {
    posts: {
      disconnect: [{ id: 1 }] // Disconnect post with id 1 from the user
    }
  }
});

deleteMany: Delete multiple related records

You can delete multiple records from the related table using deleteMany. This is useful when you want to delete posts that match certain criteria.

Example:

// Delete all posts of the user
await this.prisma.post.deleteMany({
  where: { userId: 1 } // Delete all posts that belong to user with id 1
});

---

3. Many-to-Many Relations

In a many-to-many relationship, multiple records in one table are related to multiple records in another table. Let's assume we have Post and Tag, where a Post can have many Tags, and a Tag can be related to many Posts.

Prisma Schema

model Post {
  id    Int    @id @default(autoincrement())
  title String
  tags  Tag[]  @relation("PostTags")
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[] @relation("PostTags")
}

set: Replace all related records

The set operation replaces all the records in a many-to-many relationship. It will remove any previous relations and set new ones.

Example:

// Set tags for a post, replacing any existing tags
await this.prisma.post.update({
  where: { id: 1 },
  data: {
    tags: {
      set: [{ id: 1 }, { id: 2 }] // Replace existing tags with tag IDs 1 and 2
    }
  }
});

disconnect: Break the relation without deleting the records

You can use disconnect to break the connection between the Post and Tag without deleting either record.

Example:

// Disconnect a specific tag from a post
await this.prisma.post.update({
  where: { id: 1 },
  data: {
    tags: {
      disconnect: [{ id: 1 }] // Disconnect tag with id 1 from the post
    }
  }
});

deleteMany: Delete multiple related records

If you want to delete tags that are no longer needed, you can use deleteMany.

Example:

// Delete specific tags
await this.prisma.tag.deleteMany({
  where: { id: { in: [1, 2] } } // Delete tags with ids 1 and 2
});

---

takeaways:

• set: Replaces the existing related records with a new set of records.

• disconnect: Breaks the relationship between records without deleting the records themselves.

• deleteMany: Deletes multiple records that match the provided conditions.