Database upsert with composite unique constraint

[valentinpollart]

Question

Hello,

I'm not sure to interpret properly the documentation regarding database upsert. I do have a situation similar to the one described as example in the situation :

model User {
  id           Int    @id
  profileViews Int
  userName     String @unique
  email        String

  @@unique([id, profileViews])
}

However, for my upsert query, I'd need to use a where clause on the unique constraint id_profileViews as follows :

prisma.user.upsert({
  where: {
    id_profileViews: {
      id: 1
      profileViews: 1
    }
  },
  create: {
    id: 1,
    profileViews: 1,
    userName: 'Alice',
    email: 'alice@prisma.io',
  },
  update: {
    email: 'updated@example.com',
  },
})

Does it counts as one unique field in where clause towards the fourth database upsert query criteria specified in the docs? If so, is there a specific way to use the same field in the create clause to respect the fifth criteria? In my case, it seems it doesn't work (I get P2022 error, which is not supposed to happen with database upsert). I made sure the three other criterion are respected as well as the prerequisites in terms of Prisma version and database engine.

How to reproduce (optional)

No response

Expected behavior (optional)

No response

Information about Prisma Schema, Client Queries and Environment (optional)

No response

[ludralph]

Hi @valentinpollart 👋

Your interpretation of upsert is correct. The constraint constraint id_profileViews counts as one unique key. The P2022 error indicates that a specified column does not exist in the current database. The error message typically looks like this:

The column `{column}` does not exist in the current database.

Is that the same error message that is being thrown?

I tried the upsert code you shared and it works for me.

[valentinpollart]

My bad, I meant the P2002: Unique constraint error.
Here's my code sample that fails into P2002 (note that the upsert is called at high frequency, that's why I suspect a race condition and therefore Prisma not using the database upsert):

model Transaction {
  id               Int     @id @default(autoincrement())
  transactionHash  String  @map("transaction_hash")
  transactionIndex Int     @map("transaction_index")
  from             String
  to               String?
  fees             Decimal @default(0) @db.Decimal(78, 0)
  blockHeight      Int     @map("block_height")
  chainId          String  @map("chain_id")

  transfers Transfer[]

  @@unique([transactionHash, chainId])
  @@index([chainId])
  @@map("transactions")
  @@schema("warehouse")
}

const transaction = await this.prisma.transaction.upsert({
          where: { transactionHash_chainId: { transactionHash: transfer.transactionHash, chainId } },
          update: {},
          create: {
            transactionHash: transfer.transactionHash,
            transactionIndex: transfer.transactionIndex,
            from: transfer.transactionFrom,
            to: transfer.transactionTo,
            fees: transfer.transactionFees.toString(),
            blockHeight: transfer.blockNumber,
            chainId,
          },
        });

[ludralph]

Hi @valentinpollart

Thank you for providing the additional information and code sample. The P2002 error in this context is likely due to concurrent upsert operations trying to create the same record simultaneously. Can you try this?

const transaction = await this.prisma.transaction.upsert({
  where: { 
    transactionHash_chainId: { 
      transactionHash: transfer.transactionHash, 
      chainId 
    }
  },
  update: {
    transactionIndex: transfer.transactionIndex,
    from: transfer.transactionFrom,
    to: transfer.transactionTo,
    fees: transfer.transactionFees.toString(),
    blockHeight: transfer.blockNumber,
  },
  create: {
    transactionHash: transfer.transactionHash,
    transactionIndex: transfer.transactionIndex,
    from: transfer.transactionFrom,
    to: transfer.transactionTo,
    fees: transfer.transactionFees.toString(),
    blockHeight: transfer.blockNumber,
    chainId,
  },
});

By providing values in the update object, you ensure that all fields are updated even if the record already exists. This can help prevent issues with partial updates.

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that haven’t had any recent activity and appear to be outdated.

If this discussion is still important to you or unresolved, we’d love to hear from you! Feel free to reopen it or start a new one with updated details.

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and being part of the community!;