relationMode = prisma: Error validating: A self-relation must have onDelete and onUpdate referential actions set to NoAction

[baptisteArno]

Bug description

With mysql and relationMode set to prisma, I'm getting this lint error:

Error validating: A self-relation must have `onDelete` and `onUpdate` referential actions set to `NoAction` in one of the @relation attributes. (Implicit default `onDelete`: `SetNull`, and `onUpdate`: `Cascade`) Read more at https://pris.ly/d/cyclic-referential-actions

Even though that should only be the case if it uses MongoDB or SQL Server providers as explained here?

Prisma information

# provider = "mysql"
# relationMode = "prisma"

model DashboardFolder {
  id             String            @id @default(cuid())
  createdAt      DateTime          @default(now())
  updatedAt      DateTime          @default(now()) @updatedAt
  name           String
  parentFolderId String?
  workspaceId    String
  parentFolder   DashboardFolder?  @relation("ParentChild", fields: [parentFolderId], references: [id])
  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  workspace      Workspace         @relation(fields: [workspaceId], references: [id], onDelete: Cascade)
  childrenFolder DashboardFolder[] @relation("ParentChild")
  typebots       Typebot[]
}

[janpio]

I can indeed reproduce your observation:

PS C:\Users\Jan\Documents\throwaway\1374> npx prisma validate       
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma

Error: Prisma schema validation - (query-engine-node-api library)
Error code: P1012
error: Error validating: A self-relation must have `onDelete` and `onUpdate` referential actions set to `NoAction` in one of the @relation attributes. (Implicit default `onDelete`: `SetNull`, and `onUpdate`: `Cascade`) Read more at https://pris.ly/d/cyclic-referential-actions
  -->  schema.prisma:18
   | 
17 |   workspaceId    String
18 |   parentFolder   DashboardFolder?  @relation("ParentChild", fields: [parentFolderId], references: [id])
19 |   childrenFolder DashboardFolder[] @relation("ParentChild")
   | 

Validation Error Count: 1
[Context: getDmmf]

Prisma CLI Version : 4.9.0
PS C:\Users\Jan\Documents\throwaway\1374> cat .\prisma\schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

model DashboardFolder {
  id             String            @id @default(cuid())
  parentFolderId String?
  workspaceId    String
  parentFolder   DashboardFolder?  @relation("ParentChild", fields: [parentFolderId], references: [id])
  childrenFolder DashboardFolder[] @relation("ParentChild")

  @@index([parentFolderId])
}
PS C:\Users\Jan\Documents\throwaway\1374> 

Investigating internally now if this is maybe a mistake that this is enabled for relationMode=prisma all the time.
Also seems to be happening for provider="postgresql".

Thanks for the issue!

[janpio]

@jkomyno did some sleuthing, and I am here with the simple job to report his findings:

This is currently working as designed, although we are lacking the documentation for it.

#9931 was an issue triggered by a user having a self relation, and it then crashing Prisma Client. This was fixed by expanding our SQL Server and MongoDB cyclic ref actions detections to these cases as well in this PR: prisma/prisma-engines#2415

Here is the explanation why this is needed:

Our query engine doesn't do JIT with the queries, so all the query planning is done first and only after that executed. This means a cascade that cycles back to itself will just generate a query until stack overflow, a bit like what Microsoft SQL Server would do... Therefore the first action will be that we just prevent cascading cycles completely if Prisma handles referential integrity...
Source

And the "workaround":

The thing you can do right now is cut the cycle with NoAction for updates and deletes, then handling the deletion in your application code.

Would this work for you @baptisteArno?

[baptisteArno]

Yes, totally fine! Thank you for the investigation 👌

[janpio]

We'll still keep this issue open as that is indeed unexpected, and we at least should make sure to document this.

[Songkeys]

The thing you can do right now is cut the cycle with NoAction for updates and deletes, then handling the deletion in your application code.

But NoAction is not available in Postgres. I got this error:

Error validating: Invalid referential action: `NoAction`. Allowed values: (`Cascade`, `Restrict`, `SetNull`). `NoAction` is not implemented for Postgres when using `relationMode = "prisma"`, you could try using `Restrict` instead. Learn more at https://pris.ly/d/relation-mode

I think what I need is onUpdate="Nothing" / onDelete="Nothing" as referred in this issue

[jkomyno]

Hi @Songkeys, wouldn't onUpdate/onDeleteRestrict work for your usecase? Can you please tell us a bit more of what behaviour you're looking for / expecting?

[Songkeys]

Hi @jkomyno , I explained here. Restrict throws error in my case cuz it asks me to handle related rows (I don't quite remember the error; i think it's error 2014 "The change you are trying to make would violate the required relation 'ChildToParent' between the Child and Parent models."). I need a Nothing relationMode to handle the onUpdate and onDelete checking myself.

[jkomyno]

Thanks for the link to the comment @Songkeys.

[Songkeys]

Also, after taking another look, I don't understand the solution above even for db like mysql:

The thing you can do right now is cut the cycle with NoAction for updates and deletes, then handling the deletion in your application code.

In this PR:

When relationMode = "prisma", NoAction does the same as Restrict, rather than not doing anything at all.

How does the NoAction (i.e., Restrict) help avoid the built-in relation checking then?

[nkeil]

Not sure if this has something to do with this issue, but I'm getting an issue when trying to delete a self-relation. When I try deleting all nested children in a 1-M self-relation, I get the error below. However, using a raw query works.

Schema:

model Topic {
  id            Int          @id @default(autoincrement())
  parentTopicId Int?
  parentTopic   Topic?       @relation("Subtopic", fields: [parentTopicId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  subTopics     Topic[]      @relation("Subtopic")
}

Prisma delete query (trying to delete all subtopics):

await prisma.topic.deleteMany({
    where: { NOT: { parentTopicId: null } },
});

Error message:

The change you are trying to make would violate the required relation 'Subtopic' between the `Topic` and `Topic` models.

Solution (raw query):

await prisma.$queryRaw`
    DELETE FROM topics T 
    WHERE T. parentTopicId IS NOT NULL
`;

Is there any current solution to this without using a raw query?

[Songkeys]

@nkeil I completely understand the confusion! You can fix the version to 4.4.0, just like I did. Unfortunately, the maintainers don't have any plans to fix this, as they mentioned in their reply.

[XolifyDev]

I tried the NoAction for onDelete and for onUpdate but I'm still getting this error. (Provider MongoDB)

Error parsing attribute "@relation": The type of the field `replyMessageId` in the model `CommunityMessage` is not matching the type of the referenced field `id` in model `CommunityMessage`.

My Model

model CommunityMessage {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  body      String?
  image     String?
  createdAt DateTime @default(now())

  seenIds String[] @db.ObjectId
  seen    User[]   @relation("ChannelSeen", fields: [seenIds], references: [id])

  channelId String  @db.ObjectId
  channel   Channel @relation(fields: [channelId], references: [id], onDelete: Cascade)

  replyMessageId Int?    @unique
  replyMessage   CommunityMessage?   @relation("reply", fields: [replyMessageId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  replies CommunityMessage[]   @relation("reply")

  senderId String @db.ObjectId
  sender   User   @relation(fields: [senderId], references: [id], onDelete: Cascade)
  

}

I would love if someone could help me out.

[XolifyDev]

I tried the NoAction for onDelete and for onUpdate but I'm still getting this error. (Provider MongoDB)

Error parsing attribute "@relation": The type of the field `replyMessageId` in the model `CommunityMessage` is not matching the type of the referenced field `id` in model `CommunityMessage`.

My Model

model CommunityMessage {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  body      String?
  image     String?
  createdAt DateTime @default(now())

  seenIds String[] @db.ObjectId
  seen    User[]   @relation("ChannelSeen", fields: [seenIds], references: [id])

  channelId String  @db.ObjectId
  channel   Channel @relation(fields: [channelId], references: [id], onDelete: Cascade)

  replyMessageId Int?    @unique
  replyMessage   CommunityMessage?   @relation("reply", fields: [replyMessageId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  replies CommunityMessage[]   @relation("reply")

  senderId String @db.ObjectId
  sender   User   @relation(fields: [senderId], references: [id], onDelete: Cascade)
  

}

I would love if someone could help me out.

Now when I run npx prisma format I get this error

Error parsing attribute "@relation": The type of the field `replyMessageId` in the model `CommunityMessage` is not matching the type of the referenced field `id` in model `CommunityMessage`.

[allestaire]

Prisma seems weird, I suspect that the line of code is correct on my part but it throws error

parent Category? @relation("ParentChild", fields: [parent_id], references: [id], onDelete: SetNull, onUpdate: Cascade)

[denvudd]

I tried the NoAction for onDelete and for onUpdate but I'm still getting this error. (Provider MongoDB)

Error parsing attribute "@relation": The type of the field `replyMessageId` in the model `CommunityMessage` is not matching the type of the referenced field `id` in model `CommunityMessage`.

My Model

model CommunityMessage {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  body      String?
  image     String?
  createdAt DateTime @default(now())

  seenIds String[] @db.ObjectId
  seen    User[]   @relation("ChannelSeen", fields: [seenIds], references: [id])

  channelId String  @db.ObjectId
  channel   Channel @relation(fields: [channelId], references: [id], onDelete: Cascade)

  replyMessageId Int?    @unique
  replyMessage   CommunityMessage?   @relation("reply", fields: [replyMessageId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  replies CommunityMessage[]   @relation("reply")

  senderId String @db.ObjectId
  sender   User   @relation(fields: [senderId], references: [id], onDelete: Cascade)
  

}

I would love if someone could help me out.

Now when I run npx prisma format I get this error

Error parsing attribute "@relation": The type of the field `replyMessageId` in the model `CommunityMessage` is not matching the type of the referenced field `id` in model `CommunityMessage`.

Hello, I have almost the same problem. Have you managed to solve it somehow?

model Post {
  id          String    @id @default(cuid())
  title       String
  content     Json?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  subreddit   Subreddit @relation(fields: [subredditId], references: [id])
  subredditId String

  author   User   @relation(fields: [authorId], references: [id])
  authorId String

  comments Comment[]
  votes     Vote[]
}

model Comment {
  id        String   @id @default(cuid())
  text      String
  createdAt DateTime @default(now())
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId    String

  replyToId String?
  replyTo   Comment?  @relation("ReplyTo", fields: [replyToId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  replies   Comment[] @relation("ReplyTo")

  votes     CommentVote[]
  commentId String?
}

[ivstiv]

This problem makes it pretty much impossible to work with self-relating models. I don't understand how come NoAction works like Restrict:

model GroupActivity {
  id            Int               @id @default(autoincrement())
  rootComment   Int?
  RootComment   GroupActivity?    @relation("NestedReplies", fields: [rootComment], references: [id], onDelete: NoAction, onUpdate: NoAction)
  NestedReplies GroupActivity[]   @relation("NestedReplies")

  groupId       String
  // this can't be Cascade because we then hit: The change you are trying to make would violate the required relation 'NestedReplies' between the `GroupActivity` and `GroupActivity` models.
  Group         OrganisationGroup @relation(fields: [groupId], references: [id], onDelete: NoAction) 

  @@index(groupId)
  @@index(rootComment)
}

If I was just deleting GroupActivities it would've been fine because I can just drop to raw queries, but if I come from a long chain of cascades through the group relation, I would need to write a bunch of extra code to clean up this table before we delete any of the parent resources. Not only that but all of this should also be done as a transaction just in case part of it fails, so we don't end up in a flaky state of half-deleted chained resources.

Anyone with ideas on how I should go about it?

The solution I went ahead with looks something like this. Looks terrible but seems like the only escape hatch for now.

const relatedGroups = getRelatedGroupsDependingOnWhatGetsDeleted(idToDelete);

await db.$transaction([
  ...deleteGroupActivitiesIn(relatedGroups),
  db.whateverTopLevelResource.delete({
    where: { id: idToDelete},
  }),
]);

const deleteGroupActivitiesIn = (groupIds: string[]) => {
  // we need to return array otherwise we can't call .join with an empty array
  // it throws an error... but we can't just return undefined either because
  // it is supposed to be used inside $transaction which expects a PrismaPromise
  return groupIds.length === 0 ? [] : [
    db.$queryRaw`
      DELETE FROM GroupActivity WHERE groupId IN (${Prisma.join(groupIds)})
    `,
  ];
};

[leo-paz]

@ivstiv I agree, I have similar schema with nested folders. Is this still being looked at?

[yagyesh-bobde]

@jkomyno did some sleuthing, and I am here with the simple job to report his findings:

This is currently working as designed, although we are lacking the documentation for it.

#9931 was an issue triggered by a user having a self relation, and it then crashing Prisma Client. This was fixed by expanding our SQL Server and MongoDB cyclic ref actions detections to these cases as well in this PR: prisma/prisma-engines#2415

Here is the explanation why this is needed:

Our query engine doesn't do JIT with the queries, so all the query planning is done first and only after that executed. This means a cascade that cycles back to itself will just generate a query until stack overflow, a bit like what Microsoft SQL Server would do... Therefore the first action will be that we just prevent cascading cycles completely if Prisma handles referential integrity...
Source

And the "workaround":

The thing you can do right now is cut the cycle with NoAction for updates and deletes, then handling the deletion in your application code.

Would this work for you @baptisteArno?

Worked with me using mongoDB as well

[sugamkarki]

Is this issue still being worked on?