Prisma Explicit Many to Many - how to delete item without deleting user

Question

My use case is simple:

There's users
Users can create workspaces
Users can delete workspaces
User gets a role on each workspace (OWNER, ADMIN, MANAGER, VIEWER)

The problem:

This requires an explicit many-to-many relation (users <-> workspaces).

Creating workspaces is fine, it all works. Deleting a workspace is where the problem occurs. It either:

Deletes nothing
Or deletes everything (user + workspace + their relation table row) using onDelete: Cascade

I obviously don't want my users to get deleted.

Here's my Prisma schema:

model User {
  id                 String              @id @default(cuid())
  createdAt          DateTime            @default(now())
  updatedAt          DateTime            @updatedAt
  email              String?             @unique
  plan               Plan?               @relation(fields: [planId], references: [id], onDelete: Cascade)
  planId             String?
  workspaces         WorkspacesOnUsers[]
}

model Workspace {
  id           String              @id @default(cuid())
  createdAt    DateTime            @default(now())
  updatedAt    DateTime            @updatedAt
  name         String?
  users        WorkspacesOnUsers[]
}

model WorkspacesOnUsers {
  id          String     @id @default(cuid())
  user        User?      @relation(fields: [userId], references: [id], onDelete: Restrict, onUpdate: Restrict)
  userId      String?
  workspace   Workspace? @relation(fields: [workspaceId], references: [id])
  workspaceId String?
  role        Role?      @default(VIEWER)
}

enum Role {
  OWNER
  ADMIN
  MANAGER
  VIEWER
}

And this is how I try to delete a workspace from a user:

await prisma.workspace.delete({
  where: { id: workspaceId },
})

I also tried this but it only removes the row in the relation, but I still can't remove the workspace after that (Foreign key constraint failed on the field: WorkspacesOnUsers_userId_fkey (index)):

  await prisma.workspace.update({
    where: { id: workspaceId },
    data: {
      users: { deleteMany: {} },
    },
  })

Someone help please!

score 1 · Answer 1 · answered Nov 01 '22 at 17:10

I had a similar issue but with users deleting posts, this is how I deleted a post attached to a user via there id (in this case their sessionId)

await prisma.user.update({
    where: { sessionId: sessionId },
    data: {
        Post: {
            delete: {
                id: postId,
            },
        },
    },
});

in your case it will look something like this

await prisma.user.update({
    where: { id: userIdFromFunction },
    data: {
        Workspace: {
            delete: {
                id: workspaceIdFromFunction,
            },
        },
    },
});

Hope this helps!

Prisma Explicit Many to Many - how to delete item without deleting user

1 Answers1