SELECT FOR UPDATE

[brandonmpetty]

Problem

To avoid mid-air-collisions in REST there are two primary techniques: Last-Modified / If-Unmodified-Since, and ETags.
Prisma makes it extremely difficult to support these techniques.

There is no concept of SELECT FOR UPDATE, which is required to ensure that nothing issues an update underneath me while I am verifying the @updatedAt value for If-Unmodified-Since, or the hash of the select results if I am using ETags.

Suggested solution

I would recommend a solution that uses a callback.
This is just one idea. It would create a transaction and use a FOR UPDATE on the SELECT if it detects an 'update' or 'delete' property.
My callback is placed in 'update.before', as to not pollute the root property list.
If my callback throws, the transaction is broken and we are done.
If it goes through, we issue the update... or delete.

It could look like this:

// My own custom 'middleware' allowing me to do what ever I'd like before the update.
const myCallback = result => {
    
    if (!result) {
        throw new HTTPException(204, 'No Content');
    }

    if (result.updatedAt > myLastModifiedDate) {
        throw new HTTPException(412, 'Record has been modified');
    }
};

prisma.user.findUnique({
    where: {
        id: id
    },
    select: {
        updatedAt: true
    },
    update: {
        before: myCallback,
        data: myUpdatedData
    } 
});

Alternatives

There may be a number of ways one could accomplish this.

Additional context

[swipeby-dev]

I just wanted to note that the above was just an example of what this type of solution could solve.
For solving the specific problem above, mid-air-collision with updatedAt, it would be recommended to blindly apply the update WHERE updatedAt has not changed, and if it fails then select to see if it existed (if you need to know that). This is made more difficult with Prisma due to the lack of filtering by non-unique values even if the where clause is guaranteed to always be unique.

I also wanted to note that .Net's Entity framework does not appear to offer any type of select for update capability.

[ziimakc]

I would suggest to pass around a transaction object in "interactive transactions" as argument, like here: https://vincit.github.io/objection.js/guide/transactions.html#using-a-transaction

export class SmthModel{
  static async checkUniqueName(name, trx?: Transaction) {
    const isNameUsed = await this.client.smth.findFirst({ where: { name }, trx});

    if (isNameUsed) {
      throw new Error();
    }
  }
}

function handler() {
    return prisma.$transaction(async (trx) => {
      const smth = await this.client.smth.findUnique({
        where: { id: 1 },
        for: 'forUpdate' // or forShare an so on
        trx
      });

      await SmthModel.checkUniqueName(smth.name, trx);

     // update smth.name
    });
}

[GuiSim]

FOR UPDATE is a core tool for achieving atomic guarantees for distributed applications.

Here are some issues that have been closed but aren't totally resolved without better FOR UPDATE mechanism:
#1918
#1986

And this issue would also be avoidable with FOR UPDATE by enabling developers to manually serialize operations
#8668

[bodinsamuel]

Hey, I will respectfully +1 since it's not been udpated in a long time.
Transactions and Nested Write are great and solve many issues, but locking for update (and all the others keywords https://www.postgresql.org/docs/current/explicit-locking.html) have very specific meaning that are not solved by the proposed solutions right now.

It would be great to have more updates on this since it's a core feature in many databases and makes the switch to Prisma quite harder.

[Thinkscape]

I'm having bad time trying to work around this mess with .executeRaw and that not working very well because of how Prisma does things.

Transactionality is the prime reason to use RDBMS in the first place, and Prisma gets in the way of that...

Related to #1918

[janpio]

Potentially related:
#17136
#5983

[JasperSui]

I really hope this can be done with Prisma instead of using weird workarounds or executing a bunch of raw SQL queries, so here is my +1 for this feature 🙏