Feature: Allow non-unique fields to be used with update

[capaj]

Problem

when doing an update on a model sometime we want to filter on non-unique field to make sure that we only update rown which user has access to, for example like this:

 prismaClient.userPicture.update({
            where: {
              id: picture.id,
              user_id: req.user.id // currently this line is not possible when user_id is not unique
            },
            data: {
              verified_at: new Date()
            },
          })

It would be nice to be able to optionally provide non unique fields to the where condition.
Currently it only allows fiels which are unique.
I would like if we could use fields which are not unique as well.

Suggested solution

Allow non-unique fields to be used with update.

Alternatives

Currently we can work around this by using updateMany, but this is not ideal. In most API calls you want to return the updated item right away, so being able to do it all with a single prisma query would be nice.

Additional context

[floelhoeffel]

hey @capaj - thanks for the issue! Would a composite index over id and user_id help by chance?

[capaj]

Yes it would, but sometimes the update is ok to be slower and we want to keep writes as fast as possible without updating an index.

[benjreinhart]

Hey @floelhoeffel, just want to second these issues around updates supporting non-unique attributes to filter by. IMO, the framework shouldn't encourage adding indexes when there doesn't need to be one. Arbitrary conditionals in a where clause is supported by every database that I'm aware of regardless of unique indexes and is pretty common when writing update or delete queries.

Let me provide a concrete example below to (hopefully) further motivate this request.

We have a session object that can be revoked by setting its revokedAt timestamp to datetime (it is null by default). To look up valid sessions, we query WHERE id=<id> AND revokedAt IS NULL, including when we update other attributes of the session. However, as far as I can tell, I cannot write an update query with a revokedAt IS NULL clause in it unless there is a unique index on the revokedAt column. The id is the primary key and is already unique, so an extra index on revokedAt or a composite index is unnecessary and (imo) poor design.

The solution instead seems to be to write two queries instead of one. One network request to find the object and one to then update it. There are two problems with this:

1. It's less performant. We are now executing two queries instead of one.
2. There's a race condition. The object could be revoked in-between the first and second query.

Transactions may be able to help with 2), but then we'd be adding even more code/complexity to what should otherwise just have been one simple update query.

[janpio]

Thanks, we are looking into this right now.

Here is another issue with a lot more use cases and comments: #7290

[tatianajiselle]

+1 would love support on this, something like a findOne() but an updateOne() where the return type is the updated row

[Weakky]

Hey folks,

A solution was implemented by prisma/prisma-engines#3281. It will be available in the next release under a new preview flag called extendedWhereUnique.

If you have any feedback you'd like to give about this feature, please post it here #15837 (comment)

Thanks for your patience, closing now 🙏.

[aruns05]

Hey @floelhoeffel, just want to second these issues around updates supporting non-unique attributes to filter by. IMO, the framework shouldn't encourage adding indexes when there doesn't need to be one. Arbitrary conditionals in a where clause is supported by every database that I'm aware of regardless of unique indexes and is pretty common when writing update or delete queries.

Let me provide a concrete example below to (hopefully) further motivate this request.

We have a session object that can be revoked by setting its revokedAt timestamp to datetime (it is null by default). To look up valid sessions, we query WHERE id=<id> AND revokedAt IS NULL, including when we update other attributes of the session. However, as far as I can tell, I cannot write an update query with a revokedAt IS NULL clause in it unless there is a unique index on the revokedAt column. The id is the primary key and is already unique, so an extra index on revokedAt or a composite index is unnecessary and (imo) poor design.

The solution instead seems to be to write two queries instead of one. One network request to find the object and one to then update it. There are two problems with this:

1. It's less performant. We are now executing two queries instead of one.
2. There's a race condition. The object could be revoked in-between the first and second query.

Transactions may be able to help with 2), but then we'd be adding even more code/complexity to what should otherwise just have been one simple update query.

Was there any solution to this.. @Weakky For updatemany with non unique key is inserting a new row