Getting the where unique input constraint on deleteMany | Prisma | Page 1

rose bridgeBOT Jun 5, 2024, 10:42 AM

#

To help others find answers, you can mark your question as solved via Right click solution message -> Apps -> ✅ Mark Solution

ionic fiber Jun 5, 2024, 1:24 PM

#

Could you share the corresponding section from your Prisma schema?

ancient coral Jun 5, 2024, 1:24 PM

#

yes..just gimme a min

#

I don't understand why it's asking for Id in a deleteMany

#

Better screenshot

#

#

ionic fiber Jun 5, 2024, 1:38 PM

#

@ancient coral I think the solution is to decorate the GRN_NO field as unique

ancient coral Jun 5, 2024, 1:39 PM

#

It’s not unique tho

ionic fiber Jun 5, 2024, 1:39 PM

#

https://www.prisma.io/docs/orm/reference/prisma-client-reference#filter-on-non-unique-fields-with-userwhereuniqueinput

Prisma Client API | Prisma Documentation

API reference documentation for Prisma Client.

ancient coral Jun 5, 2024, 1:39 PM

#

It shouldn’t matter right, in a deletemany?

ionic fiber Jun 5, 2024, 1:40 PM

#

Is the combination of GRN_NO and SPECIFICATION_ID unique?

ancient coral Jun 5, 2024, 1:40 PM

#

For a normal delete, unique makes sense, but deletemany is different right

#

Nope

ionic fiber Jun 5, 2024, 1:42 PM

#

ancient coral It shouldn’t matter right, in a deletemany?

I understand what you mean, not sure why Prisma is that strict. One workaround is to findMany and pass a resulting array of IDs into the deleteMany

ancient coral Jun 5, 2024, 1:44 PM

#

Its supposed to be whereinputs and not whereuniqueinputs

#

#

According to the docs for deletemany

#

It has to be a bug. This does not make sense at all

oak tide Jun 5, 2024, 1:53 PM

#

What database engine are you using? I want to get this back to the team

ancient coral Jun 5, 2024, 1:55 PM

#

Sql server

oak tide Jun 5, 2024, 1:59 PM

#

Thanks! And just to confirm, what version of the ORM?

ancient coral Jun 5, 2024, 2:00 PM

#

5.13

#

Will you be able to confirm if this is indeed a bug? I’ve been losing it for the whole day lol 😔

oak tide Jun 5, 2024, 2:04 PM

#

yeah I'm chatting with Eng right now to see if this is expected behavior. You're correct that our docs make it seem like this should work

#

and for what it's worth my intuition says it should work too

ancient coral Jun 5, 2024, 2:05 PM

#

Great. Thank you for the support on this.

serene oracle Jun 5, 2024, 2:10 PM

#

This should not be the case, indeed @ancient coral.

It looks like your deleteMany() is expecting the wrong Input type with Unique while it should expect the general one.

Any Client Extensions in your app that could mess with the API?

ancient coral Jun 5, 2024, 2:11 PM

#

Nope its just next and prisma

serene oracle Jun 5, 2024, 2:11 PM

#

@ionic fiber All you wrote above is correct for queries that actually require the WhereUniqueInput types like findUnique() and so on, but not deleteMany() and similar.

ancient coral Jun 5, 2024, 2:12 PM

#

Thanks for confirming. I’ll do the workaround @ionic fiber suggested for now

serene oracle Jun 5, 2024, 2:12 PM

#

@ancient coral I'll do a quick repro. But I expect something to be off with your project somehow.

ancient coral Jun 5, 2024, 2:13 PM

#

Repro?

oak tide Jun 5, 2024, 2:13 PM

#

He's going to try and reproduce the issue 🙂

ancient coral Jun 5, 2024, 2:14 PM

#

Oh ok

serene oracle Jun 5, 2024, 2:15 PM

#

Can you quickly post your schema as text instead of image?

#

Same for the query.

ancient coral Jun 5, 2024, 2:15 PM

#

These are our dependencies in case you have a blacklist

#

Sure just a min

serene oracle Jun 5, 2024, 2:15 PM

#

Did you restart your editor already?

ancient coral Jun 5, 2024, 2:16 PM

#

ancient coral

This is the query

ancient coral Jun 5, 2024, 2:16 PM

#

serene oracle Did you restart your editor already?

??

serene oracle Jun 5, 2024, 2:16 PM

#

ancient coral ??

Ah yeah forget the question, you don't get red squiggly underline but an error on execution. Sorry.

#

Query also as text please that I can copy/paste into my project.

ancient coral Jun 5, 2024, 2:19 PM

#

Just gimme a few mins its on my work pc so got to login now

serene oracle Jun 5, 2024, 2:20 PM

#

Yeah no hurry.

#

First reproduction with a different schema and query:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  await prisma.user.deleteMany()

  const user1Email = `alice${Date.now()}@prisma.io`
  const user2Email = `bob${Date.now()}@prisma.io`

  const user1 = await prisma.user.create({
    data: {
      email: user1Email,
      name: 'Alice'
    }
  })
  const user2 = await prisma.user.create({
    data: {
      email: user2Email,
      name: 'Bob'
    }
  })

  console.log(await prisma.user.findMany())
  await prisma.user.deleteMany({
    where: {
      name: "Bob",
      email: {
        in: [user1Email, user2Email]
      }
    }
  })
  console.log(await prisma.user.findMany())
}

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

and

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

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

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User?    @relation(fields: [authorId], references: [id])
  authorId  Int?
}

leads to this output:

> ts-node ./script.ts

[
  { id: 1, email: '[email protected]', name: 'Alice' },
  { id: 2, email: '[email protected]', name: 'Bob' }
]
[ { id: 1, email: '[email protected]', name: 'Alice' } ]

ancient coral Jun 5, 2024, 2:27 PM

#

📎 schema.prisma

#

await prisma.SPM_GRN_QUANTITY_DETAIL.deleteMany({ where: { GRN_NO: "test", SPECIFICATION_ID: { in: [1, 2, 3] } } })

ancient coral Jun 5, 2024, 2:53 PM

#

serene oracle First reproduction with a different schema and query: ```ts import { PrismaClien...

I think the difference between this and my schema is that both filters in mine are foreign keys from other tables

serene oracle Jun 5, 2024, 2:55 PM

#

Ok, without the foreign keys everything works indeed.

> ts-node ./script.ts

[
  {
    ID: 7,
    GRN_NO: 'test',
    SPECIFICATION_ID: 1,
    ACCEPTED_QUANTITY: 1,
    MODIFIED_ON: null,
    MODIFIED_BY: null,
    REMARKS: null,
    TOTAL_AMOUNT: 1
  },
  {
    ID: 8,
    GRN_NO: 'not_test',
    SPECIFICATION_ID: 1,
    ACCEPTED_QUANTITY: 1,
    MODIFIED_ON: null,
    MODIFIED_BY: null,
    REMARKS: null,
    TOTAL_AMOUNT: 1
  }
]
[
  {
    ID: 8,
    GRN_NO: 'not_test',
    SPECIFICATION_ID: 1,
    ACCEPTED_QUANTITY: 1,
    MODIFIED_ON: null,
    MODIFIED_BY: null,
    REMARKS: null,
    TOTAL_AMOUNT: 1
  }
]

ancient coral Jun 5, 2024, 2:55 PM

#

got the root cause?

serene oracle Jun 5, 2024, 2:56 PM

#

No, just confirmed it also works fine with a simplified model based on yours.

#

But : Your SPM_GRN_MASTER is broken in the schema you sent.

ancient coral Jun 5, 2024, 2:57 PM

#

wdym broken

#

its a legacy data model we are trying to migrate it to a new app. so have to clean up a lot of things

serene oracle Jun 5, 2024, 3:01 PM

#

It is missing the closing } so is not a valid Prisma Schema.

#

ancient coral Jun 5, 2024, 3:02 PM

#

oh ok...I just generate it using the db pull

#

maybe accidentally deleted it

serene oracle Jun 5, 2024, 3:54 PM

#

Can you make sure you send me the full ones?

ancient coral Jun 5, 2024, 4:06 PM

#

📎 schema_1.prisma

#

that closing } was the only thing

#

everything else is updated

ionic fiber Jun 5, 2024, 4:42 PM

#

serene oracle <@297456381210918913> All you wrote above is correct for queries that actually r...

Thanks for confirming @serene oracle

serene oracle Jun 5, 2024, 4:43 PM

#

Ok, here it is again with the relations foreign keys not removed

> ts-node ./script.ts

[
  {
    ID: 4,
    GRN_NO: 'test',
    SPECIFICATION_ID: 3,
    ACCEPTED_QUANTITY: 1,
    MODIFIED_ON: null,
    MODIFIED_BY: null,
    REMARKS: null,
    TOTAL_AMOUNT: 1,
    SPM_ITEM_SPECIFICATIONS_MASTER: {
      SPECIFICATION_ID: 3,
      ITEM_CODE: 'test',
      ...
      MIN_QUANTITY_ESCALATION_FLAG: null,
      SPECIFICATION_TYPE: null
    },
    SPM_GRN_MASTER: {
      GRN_NO: 'test',
      BU_ID: null,
      ...
      AMC_VENDOR_NAME: null,
      Requestor_ID: null,
      ID: 3
    }
  },
  {
    ID: 5,
    GRN_NO: 'not_test',
    SPECIFICATION_ID: 3,
    ACCEPTED_QUANTITY: 1,
    MODIFIED_ON: null,
    MODIFIED_BY: null,
    REMARKS: null,
    TOTAL_AMOUNT: 1,
    SPM_ITEM_SPECIFICATIONS_MASTER: {
      SPECIFICATION_ID: 3,
      ITEM_CODE: 'test',
      ...
      MIN_QUANTITY_ESCALATION_FLAG: null,
      SPECIFICATION_TYPE: null
    },
    SPM_GRN_MASTER: {
      GRN_NO: 'not_test',
      BU_ID: null,
      ...
      Requestor_ID: null,
      ID: 4
    }
  }
]
// deleteMany() here
// prisma.sPM_GRN_QUANTITY_DETAIL.deleteMany({ where: { GRN_NO: "test", SPECIFICATION_ID: { in: [1, 2, 3] } } }),
[
  {
    ID: 5,
    GRN_NO: 'not_test',
    SPECIFICATION_ID: 3,
    ACCEPTED_QUANTITY: 1,
    MODIFIED_ON: null,
    MODIFIED_BY: null,
    REMARKS: null,
    TOTAL_AMOUNT: 1,
    SPM_ITEM_SPECIFICATIONS_MASTER: {
      SPECIFICATION_ID: 3,
      ITEM_CODE: 'test',
      ...
      MIN_QUANTITY_ESCALATION_FLAG: null,
      SPECIFICATION_TYPE: null
    },
    SPM_GRN_MASTER: {
      GRN_NO: 'not_test',
      BU_ID: null,
      ...
      AMC_VENDOR_NAME: null,
      Requestor_ID: null,
      ID: 4
    }
  }
]

#

As you can see @ancient coral it correctly deletes an entry with the Prisma Client query that is not working for you.

ancient coral Jun 5, 2024, 4:45 PM

#

hmm, so what could be the issue in mine

serene oracle Jun 5, 2024, 4:48 PM

#

Honestly no idea.
The Type it complains about is not required by deleteMany().
But your error message clearly says otherwise.

#

Can you shared the code with us, privately?

ancient coral Jun 5, 2024, 4:50 PM

#

I will need to speak to my manager and all, as they are very strict about sharing anything. I'm on Indian time so it's late for me now. I will try once tomorrow and see if the error still persists. If yes, will try to send the code.

#

should I maybe upgrade to 5.15 and check

#

also, the only difference in the code is that I am running it inside a transaction using the $transaction api

#

would it make a difference

serene oracle Jun 5, 2024, 5:00 PM

#

No, I am actually also doing that.

#

  await prisma.$transaction([
    prisma.sPM_GRN_QUANTITY_DETAIL.deleteMany({ where: { GRN_NO: "test", SPECIFICATION_ID: { in: [1, 2, 3] } } }),
  ])

#

Upgrade is definitely a good idea, but I also tried the older version you mentioned.

ancient coral Jun 5, 2024, 5:02 PM

#

hmm ok...let me get back to you tomorrow after upgrading and checking

serene oracle Jun 5, 2024, 5:02 PM

#

Ok cool, good luck.

ancient coral Jun 5, 2024, 5:02 PM

#

thank you for the help

serene oracle Jun 5, 2024, 5:02 PM

#

Maybe also check out in a new place and do a fresh npm install and so on.

ancient coral Jun 5, 2024, 5:03 PM

#

ah ok, will do that. thanks 🙂

ancient coral Jun 6, 2024, 4:42 AM

#

the deleteMany works outside the transaction fine

#

I took it outside and ran fine. Now the next upsert has the same issue which is still inside transaction

#

there is something really weird going on

#

now if I put that deleteMany only in a different transaction, it works

#

which is the scenario you tested

#

can you try with a deleteMany and an upsert inside the transaction

#

@serene oracle

ancient coral Jun 6, 2024, 5:30 AM

#

I can screen share the code on teams, but I'm not supposed to send the file.

ancient coral Jun 6, 2024, 5:49 AM

#

Brother in Christ

#

The error was coming from the next item in the transaction

#

and not deleteMany

#

but your error was mix and matching

#

showing deleteMany as the invocation failure

#

I removed the upserts which were causing the issue

#

and everything started working

#

y'all really need to fix the errors

#

the ID error was coming from the next in line transaction upsert but was showing as a failure of deleteMany

#

I ran deleteMany separately like you said in a new environment and it ran fine

#

Please fix your errors 🙏🏼 I just wasted a whole day on this

serene oracle Jun 6, 2024, 7:29 AM

#

Can you share some code that shows the same problem?
I am happy to try to reproduce this, and if we can, trigger this getting fixed.
But for now I still do not understand the problem.

ionic fiber Jun 6, 2024, 9:48 PM

#

If there really is an error in one of the calls subsequent to deleteMany in the array passed in to $transaction, maybe there is an issue with the way bestError is determined in packages\client\src\runtime\utils\waitForBatch.ts.

serene oracle Jun 7, 2024, 1:08 PM

#

Yes, that is also my theory - the transaction errors are a bit tricky.
Optimally @ancient coral could share the other Client queries in the transaction that was failing, and then we can reproduce this.

ancient coral Jun 7, 2024, 1:10 PM

#

Hi, sorry for the lack of response. Work was quite hectic. I will send a detailed example tomorrow from my personal pc. These people dont allow to share anything even for this kind of bug report

#

Basically, the transaction had multiple things in the transaction array. The transaction after deletemany, which was an upsert was failing due to my fault. But instead of showing the upsert as the failed query, it incorrectly showed deletemany as the failed one, somehow mashing together the error messages

#

Nothing is wrong with the client as such. It is a problem with the display of the error

#

I will try to reproduce and send it tomorrow

serene oracle Jun 7, 2024, 1:16 PM

#

Understood, thank you. Having a reproduction from you will save us a lot of time, so much appreciated. Let'S get that error fixed!

ancient coral Jun 7, 2024, 1:17 PM

#

Yes, will do that as soon as possible. Thank you 🙂

ancient coral Jun 9, 2024, 12:31 PM

#

I cant seem to figure out to host a sql server locally to replicate this

#

do you guys know any easy way

ancient coral Jun 9, 2024, 1:18 PM

#

#

📎 index.js 📎 schema.prisma

#

now with this you can easily replicate this

#

The mashed together error

#

PrismaClientValidationError:
Invalid `prisma.sPM_GRN_QUANTITY_DETAIL.deleteMany()` invocation in
C:\Users\rohit\Documents\personal-projects\prisma-error-repro\index.js:26:40

  23 console.log(await prisma.sPM_GRN_QUANTITY_DETAIL.findMany())
  24
  25 await prisma.$transaction([
→ 26     prisma.sPM_GRN_QUANTITY_DETAIL.deleteMany({
           where: {
             ACCEPTED_QUANTITY: {
               gt: 5,
               lt: 30
             }
           },
         + update: SPM_GRN_QUANTITY_DETAILUpdateInput | SPM_GRN_QUANTITY_DETAILUncheckedUpdateInput
         })

Argument `update` is missing.
    at Tn (C:\Users\rohit\Documents\personal-projects\prisma-error-repro\node_modules\@prisma\client\runtime\library.js:115:6855)
    at In.handleRequestError (C:\Users\rohit\Documents\personal-projects\prisma-error-repro\node_modules\@prisma\client\runtime\library.js:122:6533)
    at In.handleAndLogRequestError (C:\Users\rohit\Documents\personal-projects\prisma-error-repro\node_modules\@prisma\client\runtime\library.js:122:6211)
    at In.request (C:\Users\rohit\Documents\personal-projects\prisma-error-repro\node_modules\@prisma\client\runtime\library.js:122:5919)
    at async l (C:\Users\rohit\Documents\personal-projects\prisma-error-repro\node_modules\@prisma\client\runtime\library.js:127:11167) {
  clientVersion: '5.15.0'
}

#

I'm sorry if this is shit code or anything...this is literally my first js project 😦

#

@serene oracle

#

also one more thing unrelated, if you see my model, all model names are in capital but in intellisense dropdowns, it comes as first letter lowercased

#

#

📎 index.js

ancient coral Jun 9, 2024, 1:28 PM

#

ancient coral

this is the exact error i initially reported

#

but it doesnt matter since whatever error comes after the deletemany gets mashed together as a deletemany error

#

I hope its clear 😭

serene oracle Jun 9, 2024, 4:32 PM

#

I cant seem to figure out to host a sql server locally to replicate this
https://github.com/prisma/prisma/blob/ea868e9aa54e71fd1832c846cca74ec8d0280020/docker/docker-compose.yml#L144-L156 works prety well

#

Thanks for the code, that should probably be enough for me to experience the problem tomorrow. Will take a look.

ancient coral Jun 9, 2024, 4:45 PM

#

serene oracle > I cant seem to figure out to host a sql server locally to replicate this https...

damn it...i went through so much pain to get it installed from ms lol

ancient coral Jun 9, 2024, 4:46 PM

#

serene oracle Thanks for the code, that should probably be enough for me to experience the pro...

Thanks. Much appreciated.

ancient coral Jun 13, 2024, 5:18 AM

#

ancient coral also one more thing unrelated, if you see my model, all model names are in capit...

Hi @serene oracle . Do you know why this is the case when using typescript? my model names require to be first letter lowercased even though it is all uppercase in the schema

#

it works uppercased too. it's just that typescript wont shut up

serene oracle Jun 13, 2024, 10:09 AM

#

That's a Prisma default that can not be changed. It is because many people have model User but expect prisma.user. Something to live with, sorry. Maybe in the future we might introduce configuration to change that behavior. (Maybe you could actually open a feature request and suggest that)

ancient coral Jun 13, 2024, 5:21 PM

#

Ah ok. Pretty weird solution tho. I just replaced the prisma singleton from typescript to js to get ts to shutup.

#

Will raise a feature request

serene oracle Jun 13, 2024, 9:40 PM

#

I would honestly not have expected this to work in JS. A bit weird shrug

ancient coral Jun 14, 2024, 1:22 PM

#

lul this somehow seems to work

dire thistle Jun 14, 2024, 5:07 PM

#

ancient coral lul this somehow seems to work

sorry for offtop, but what font are you using? it looks neat

ancient coral Jun 14, 2024, 5:19 PM

#

thats a screenshot from GitHub mobile app

#

idk the font

#Getting the where unique input constraint on deleteMany