Prisma nested update with condition

[HartS]

Is there a way to make an atomic update of a unique record with a nested update?

For my situation, I want to increment a field on an existing record that meets a condition, and create a nested record that describes that modification.

I could do something like the following just to update the record with the nested update also:

prisma.account.update({
  where: {
    id: 1,
  },
  data: {
    balance: {
      decrement: 1,
    },
    balanceUpdates: {
      create: {
        adjustment: -1
      }
    }
  }
})

However, I'd like to avoid an update when it would cause a negative balance, something like:

prisma.account.update({
  where: {
    id: 1,
    balance: {
      gte: 1
    },
  },
  data: {
    balance: {
      decrement: 1,
    },
    balanceUpdates: {
      create: {
        adjustment: -1
      }
    }
  }
})

Unfortunately, only unique fields are allowed in the where clause for a a unique update.

Typically if you need a unique update with a condition, you can use updateMany with the id/unique fields set; however, updateMany doesn't support nested creates/updates.

Is there a workaround for this or anything planned in the future?

[HartS]

As a follow-on to this, I'm wondering if I can use the transaction generated by prisma for the first query example, and modify it to include the filter I need. However, I'm under the impression that prisma.$executeQuery doesn't allow transactions (BEGIN...COMMIT) which is how this works.

Does that mean I'd have to use a different sqlite library in order to execute this transaction safely with some parameters, or is there a way to do this with Prisma?

[ryands17]

@HartS 👋

We are aware of this and we have a request for the same, so it would be great if you could 👍 on this request so that we can look at the priority for this: #4988

As for raw queries, you can use begin and commit in raw queries afaik.

[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 have already been marked as answered but remain open.

If this discussion still requires further input or clarification, feel free to reopen it or start a new one with updated details. Your contributions are invaluable to the community, and we’re here to help!

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 ongoing support of the Prisma community!