Prisma Client: When relationMode="prisma" Deleting an item in an Implicit Many-to-Many does not delete the corresponding entry in the implicit pivot table

[Hardel-DW]

Bug description

The bug is quite simple to understand.
To facilitate the reading of this bug report I put an example diagram.

model Item {
  id          Int        @id @default(autoincrement())
  categories  Category[]
  (Other field...)
}

model Category {
  id        Int       @id @default(autoincrement())
  items     Item[]
  (Other field...)
}

To explain how this bug appear, I will proceed step by step.

1. To start we need two tables with implicit many-to-many relationship. (Exemple: Items and Categories)
2. Now Items and Categories each have records in the database.
3. Now we link them together, with Prisma Studio for example or with PrismaClient.
4. Now in case I delete an element in either the Items or Categories table that has a relationship with the other table.
   We see that in the associative table between Categories and Items that a record row is present but empty.

So basically the data is not deleted in cascade.
We end up with tons of empty lines that point to a non-existent element.

How to reproduce

1. To start we need two tables with implicit many-to-many relationship. (Exemple: Items and Categories)
2. Now Items and Categories each have records in the database.
3. Now we link them together, with Prisma Studio for example or with PrismaClient.
4. Now in case I delete an element in either the Items or Categories table that has a relationship with the other table.
   We see that in the associative table between Categories and Items that a record row is present but empty.

Expected behavior

The data must be deleted if one of the two fields of the associative table is missing.

Prisma information

model Item {
  id          Int        @id @default(autoincrement())
  categories  Category[]
  createdAt   DateTime   @default(now())
  updatedAt   DateTime?  @updatedAt
}

model Category {
  id        Int       @id @default(autoincrement())
  items     Item[]
  createdAt DateTime  @default(now())
  updatedAt DateTime? @updatedAt
}

    async delete(id: number) {
        return await prisma.item.delete({
            where: {
                id
            }
        });
    }
    
    await delete(1);

Environment & setup

• OS: Windows 11
• Database: PlanetScale
• Node.js version: 16.14.2
• Yarn

Prisma Version

• Prisma and Prisma Client 4.6.1

[janpio]

I assume you are using the referentialIntegrity preview feature with relationMode="prisma"?

[Jolg42]

I was curious and found the full schema at https://github.com/Hardel-Labs/labs.hardel.io/blob/beta/prisma/schema.prisma
This is the datasource and generator blocks

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialIntegrity"]
}

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

[Jolg42]

I could reproduce the issue

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialIntegrity"]
}

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

model Item {
  id         Int        @id @default(autoincrement())
  categories Category[]
  createdAt  DateTime   @default(now())
  updatedAt  DateTime?  @updatedAt
}

model Category {
  id        Int       @id @default(autoincrement())
  items     Item[]
  createdAt DateTime  @default(now())
  updatedAt DateTime? @updatedAt
}

import { PrismaClient } from "@prisma/client";

async function main() {
  const prisma = new PrismaClient();

  // Start from a clean state
  await prisma.item.deleteMany({});
  await prisma.category.deleteMany({});
  await prisma.$executeRaw`TRUNCATE TABLE \`_CategoryToItem\`;`;

  // Create one category
  const category = await prisma.category.create({
    data: {},
  });
  console.log("create category:", { category });

  // Create one item linked to the category
  const item = await prisma.item.create({
    data: {
      categories: {
        connect: {
          id: category.id,
        },
      },
    },
    include: {
      categories: true,
    },
  });
  console.log("create item:", { item });

  // Check the pivot table entries
  const pivotTable = await prisma.$queryRaw`SELECT * FROM \`_CategoryToItem\`;`;
  console.log("pivot table:", { pivotTable });

  await prisma.item.delete({
    where: {
      id: item.id,
    },
  });
  console.log(`deleted: item id ${item.id}`);

  // Item query now returns null
  const getItem = await prisma.item.findUnique({
    where: {
      id: item.id,
    },
    include: {
      categories: true,
    },
  });
  console.log("item now:", { getItem });

  // Category has no items
  const getCategory = await prisma.category.findUnique({
    where: {
      id: category.id,
    },
    include: {
      items: true,
    },
  });
  console.log("category now:", { getCategory });

  // Everything looks good but....
  // Let's check the pivot table

  // :warning: The entry was not deleted :warning:
  const pivotTableNow =
    await prisma.$queryRaw`SELECT * FROM \`_CategoryToItem\`;`;
  console.log("pivot table now:", { pivotTableNow });

  prisma.$disconnect();
}

main();

output of ts-node main.ts

create category: {
  category: {
    id: 14,
    createdAt: 2022-11-22T09:43:43.089Z,
    updatedAt: 2022-11-22T09:43:43.089Z
  }
}
create item: {
  item: {
    id: 32,
    createdAt: 2022-11-22T09:43:43.101Z,
    updatedAt: 2022-11-22T09:43:43.101Z,
    categories: [ [Object] ]
  }
}
pivot table: { pivotTable: [ { A: 14, B: 32 } ] }
deleted: item id 32
item now: { getItem: null }
category now: {
  getCategory: {
    id: 14,
    createdAt: 2022-11-22T09:43:43.089Z,
    updatedAt: 2022-11-22T09:43:43.089Z,
    items: []
  }
}
pivot table now: { pivotTableNow: [ { A: 14, B: 32 } ] }

This code shows that the implicit pivot table still contains the relationship between the Item and the Category, which shows in Prisma Studio as "empty" elements.

Removing referentialIntegrity = "prisma" (which means switching to the default behavior using foreign keys) shows that the pivot table entry is correctly deleted.

[Hardel-DW]

Thank you, I was not too present to answer your question.
Thank you, I will do that.

As it was indicated by PlanetScale of indicated :
previewFeatures = ["referentialIntegrity"]

I added it without really understanding what it could be.

[edmbn]

I can confirm this is happening for me too. Unfortunately, I cannot get rid of relationMode = "prisma" since I'm working with Planetscale.

[edmbn]

This error can be more problematic than it seems. If you retrieve relation items like this:

select: {
  id: true,
}

the pivot table will return all the items that have id, even the ones that no longer exist on the original table (not the pivot). To solve this problem, as a patch, you need to include another field that won't exist in the pivot table, for example:

select: {
  id: true,
  createdAt: true,
}

[jonasmerlin]

Yes, this is happening for me as well and causes a lot of headaches.

Thanks for the (albeit imperfect, of course) workaround @edmbn

[edmbn]

Is this issue in any roadmap version? Tests where made and there even exist a draft pull request with fixes. Looks like it was on the 4.9.0 milestone but that never happened.

[zhanghsoft]

这个问题 现在还存在

Edit (translation): "That's still a problem."

[ahkhanjani]

I just realized this is a thing the hard way. Please address this as soon as possible + provide a way to fix the existing data that have been affected by this (if even possible)

[stuartrobinson3007]

As a workaround if you're trying to delete relationships between many-to-many tables, you can do this with $executeRaw if you find the name of the generated join table (for example. by looking in Planetscale).

If you've got two tables, "item" and "category" then it'll be _ItemToCategory

The generated table has two columns, A and B. Each column is the relation id.

You can manually delete the records in the join table using $executeRaw

For example, to completely clear the join table of all records you can perform:

await prisma.$executeRawDELETE FROM _ItemToCategory

Or just clear all join records for a single Category

await prisma.$executeRawDELETE FROM _ItemToCategory WHERE B = 'example-category-id'

[morascode]

This is happening for my team too. It can be a source of many bugs if we are not careful with how we write our queries.

We will implement a workaround for now by disconnecting all m-n relations from a record before deleting it.

This issue has been open since over a year now, can it please be addressed soon?

[ahkhanjani]

Could a member from the Prisma team please take a look at this. This is a serious flaw.