Unique constraint failed on field when using native DB upsert

[mejackreed]

Bug description

PrismaClientKnownRequestError: 
Invalid `prisma.document.upsert()` invocation:


Unique constraint failed on the fields: (`url`)

How to reproduce

    return this.prisma.document.upsert({
      where: { url },
      create: {
        url,
        status: ProcessingStatus.PENDING,
      },
      update: {}, // Do nothing if document already exists
    });

Sometimes this will throw the following:

PrismaClientKnownRequestError: 
Invalid `prisma.document.upsert()` invocation:


Unique constraint failed on the fields: (`url`)

Expected behavior

Per the documentation, this usage of upsert should be eligible for the db version that should not have this error.

Prisma information

prisma@5.22.0

enum ProcessingStatus {
  PENDING
  UPLOADING
  PROCESSING
  COMPLETED
  FAILED
}

model Document {
  id           String           @id @default(cuid())
  status       ProcessingStatus

  url              String?   @unique

  @@index([status])
  @@index([url])
}

// Add your code using Prisma Client

Environment & setup

• OS:
• Database: PostgreSQL
• Node.js version: 20

Prisma Version

prisma                  : 5.22.0
@prisma/client          : 5.22.0
Computed binaryTarget   : darwin-arm64
Operating System        : darwin
Architecture            : arm64
Node.js                 : v20.15.0
Query Engine (Node-API) : libquery-engine 605197351a3c8bdd595af2d2a9bc3025bca48ea2 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine           : schema-engine-cli 605197351a3c8bdd595af2d2a9bc3025bca48ea2 (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Schema Wasm             : @prisma/prisma-schema-wasm 5.22.0-44.605197351a3c8bdd595af2d2a9bc3025bca48ea2
Default Engines Hash    : 605197351a3c8bdd595af2d2a9bc3025bca48ea2
Studio                  : 0.503.0
Preview Features        : metrics, relationJoins

[nurul3101]

Hello @mejackreed 👋

It looks like you are running into this case: Unique Key constraints on upsert
Can you have a look?

When your application tries to perform two or more concurrent upsert operations, then a race condition might happen where two or more operations do not find the record and therefore try to create that record. In this situation, one of the operations successfully creates the new record but the other operations fail and return a unique key constraint error.

[mejackreed]

Yes exactly, however, my understanding is that:

To use a database upsert, Prisma Client sends the SQL construction INSERT ... ON CONFLICT SET .. WHERE to the database.

https://www.prisma.io/docs/orm/reference/prisma-client-reference#cause

How I understood the documentation is that when using the database upsert, this condition would not happen.

Database upserts have the following advantages:

They are faster than upserts handled by Prisma Client
Unique key constraint errors cannot happen

[nurul3101]

Are all these criteria's met in your use case?

The database upsert would only be done if these conditions are met. You can also enable logging to view the underlying SQL. The SQL would help in determining if INSERT..ON CONFLICT SET ... WHERE is used or not.

[mejackreed]

Yes they are. See from above:

    return this.prisma.document.upsert({
      where: { url },
      create: {
        url,
        status: ProcessingStatus.PENDING,
      },
      update: {}, // Do nothing if document already exists
    });
    
    enum ProcessingStatus {
  PENDING
  UPLOADING
  PROCESSING
  COMPLETED
  FAILED
}

model Document {
  id           String           @id @default(cuid())
  status       ProcessingStatus

  url              String?   @unique

  @@index([status])
  @@index([url])
}

[wmadden]

Thanks for the report, we have enough information to reproduce this and we'll tackle it as soon as we have capacity 👍🏻

[cookrn]

@mejackreed @wmadden is this a duplicate of #21853? In my app, adding an arbitrary update value (for example, the same field(s) that compose the unique key) makes the on conflict do ... behavior take effect.

[masoud-msk]

Looks like Prisma doesn't generate a ON CONFLICT DO NOTHING query in upsert (which is correct, because upsert means update or insert, NOT nothing or insert, which would be nosert!).

The way to do it is to use the skipDuplicates option in createMany/createManyAndReturn.

return this.prisma.document.createMany({
  data: [{
    url,
    status: ProcessingStatus.PENDING,
  }],
  skipDuplicates: true,
});

A downside of this approach is that the row is returned only if inserted. So it's not exactly findOrCreate.

I thinks docs should be updated to add a criteria in Database upsert query criteria:

• update option shouldn't be empty (if you require ON CONFLICT DO NOTHING, use skipDuplicates option in createMany/createManyAndReturn)

BTW, after enabling logging, I see that the single createMany/createManyAndReturn is wrapped inside a transaction which is not necessary. Why is that?