Remove WhereUnique constraint from single object lookups

[florianmartens]

Problem

Single object lookups are constrained to unique fields. This affects the methods create(), update() and findOne(). It is not possible to include non-unique fields in the lookup expression. Example:prisma.Post.delete({where: {id: 1, nonUniqueField: false}}) // this is not allowed. This is a constraint that Prisma adds. This constraint does not exist in the underlying SQL. The ability to only include unique fields is a significant limitation for many professional use cases and bloats up the required code for many applications while only providing marginal benefits.

Example: Most professional apps use a soft-delete feature. When retrieving an instance it is very common to check, if the instance was soft deleted. Desired syntax prisma.Post.findUnique({where: {id: postId, isDeleted: false}}).

Current workarounds

• Use *Many operations, which don't return the instance and are far more dangerous to use. Imagine forgetting to put in the id for an update call prisma.Post.updateMany({where: category: "basic" }) (we now have to restore the database).
• Adding multi-field indexes, which creates a lot of overhead and changes the DB just to get around this limitation
• Writing raw SQL. With raw sql you loose Prisma's type safety and add additional complexity just to add the most basic functionality of an ORM (creating a single object WHERE statement).

This problem was discussed here and here. But no good argument was brought up as to why this limitation exists other than marginally better typings.

Suggested solution

Option 1: Single object lookups should accept unique and non-unique input. Single object lookups should throw an exception if more than one object is found which sufficiently guarantees correct behavior.

Option2: Add new methods findOne, createOne ... that accept non-unique input and return one instance.

[theGOTOguy]

This is an important feature for code health, and seems like it would be entirely reverse-compatible with the existing API.

In terms of implementation and reverse-compatibility, whether you are running .findUnique(), .update(), or .delete(), it is sufficient that at least one of the parameters in the where clause is unique. Additional filters will never add more records to the result.

In terms of code health, a common bug is one where any user knowing the ID of an object owned by another user is able to modify that other user's object. For instance:

logged_in_check();

updated = prisma.post.update({
    where: {id: req.body.postId},
    data: {...}
});

would potentially cause an issue where any user knowing the post's ID would be able to modify it.

What I'd like to do is something like this:

user_id = logged_in_check();

updated = prisma.post.update({
    where: {id: req.body.postId, ownerId: user_id},
    data: {...}
});

Right now, I have to do this:

user_id = logged_in_check();

the_post = prisma.post.find({
    where: {id: req.body.postId},
    data: {...}
});

if (the_post.ownerId === user_id) {
  updated = prisma.post.update({
      where: {id: req.body.postId},
      data: {...}
  });
}

Clearly, the "make sure this user can edit this post" pattern that I have to use within Prisma now is less optimal because of more back-and-forth with the database and also is less concise and readable. Allowing at least one unique field and any number of other fields in the where clause would make my code more readable overall.

[villesau]

I'm facing the same. WhereUniqueInput should enforce the unique fields, but allow any other field as well. This is crucial for the access management and scope limiting purposes. This would be a major improvement to Prisma.

So I don't think that the constraint should be removed, but it should allow optional fields too.

[villesau]

This is an important feature for code health, and seems like it would be entirely reverse-compatible with the existing API.

@theGOTOguy I actually think that it would be fully backwards compatible improvement: The unique fields could still be enforced, but the rest could still be allowed. The api would only be extended, but would not affect existing queries.

[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 🙏.