Prisma 2 Documentation on where clause with multiple conditions in update and delete statements

[Dreamystify]

I cant seem to find any information on running the following code:

    return await prisma.users.update({
        where: {
            user_id: id,
            active: true,
        },
        ...data,
    });

It wont allow the 2 where clauses, and the examples are simple where most projects aren't. Is it possible to put some more complex examples in the examples repo for myself and others to shift from sql to prisma seamlessly

[ryands17]

@Dreamystify 👋

We have docs on the supported operations here. Currently you're trying to update on multiple conditions where one of them is not unique i.e. the active part. update just taken in unique values so in this case, you would need to replace update with updateMany.

[sehmaschine]

@ryands17 Ok, thanks for the clarification. Pretty weird decision in my opinion (but that's just my 2 cents and you probably know much better what you're doing).

[ryands17]

We do have a feature request so it would be great if you could add a 👍 and your comments so that we can look at the priority 🙂

[ridhwaans]

would @@unique do the job for multiple where conditions?
example from docs

model TimePeriod {
  year          Int
  quarter       Int
  total         Decimal

  @@unique(fields: [year, quarter], name: "timePeriodId")
}

const timePeriod = await prisma.timePeriod.findUnique({
  where: {
    timePeriodId: {
      quarter: 4,
      year: 2020,
    },
  },
})

[ryands17]

Yes this will work.

[jaydip-github]

Yes this will work.

sir, can we update multiple records with multiple conditions (each record has its own condition parameter), is it possible?

[brandonmpetty]

I would like to second this "issue".
For example, I am creating a REST service that uses an 'If-Unmodified-Since' header to avoid mid-air-collisions.
A great way of solving this for Delete, Patch, and Put operations would be to have a WHERE clause with my target 'id' AND then specify where 'updatedAt' is 'lte' a specified date-time. That way it would only take the action if it were updated out from under me.

Technically, if you have a required unique field, like the primary key in my case... adding as optional any other field to filter on should be possible and you will be guaranteed that the result is unique.

Without this feature, how else am I suppose to accomplish this mid-air-collision avoidance? By creating a transaction with an otherwise extraneous select in it?

[bdelamotte]

It would be a tremendous benefit to put multiple conditions inside the where clause of an update and delete statement.
Use case:
I want to update a listing, but I want to ensure it is owned by the user passed in.
I'd like to do something like this:

             where: {
                 id: id,
                 userId: user.id
             },

I get the user.id from the session so if someone is trying to update something they don't own, then the update would fail.

[vaughngit]

Any way of returning data with deleteMany? Cant select whats been deleted.

[nenadfilipovic]

Maybe something like this

const [deletedDevice] = await prisma.$transaction([
    prisma.device.findFirst({ where }),
    prisma.device.deleteMany({ where: { AND: composeQuery } }),
  ]);
  return deletedDevice;

[brandonmpetty]

@nenadfilipovic I have seen those patterns proposed before. Are you claiming that the findFirst/findUnique will put a lock on the row so that other threads trying to perform an Update, for example, will be blocked until the full transaction is done? If true, am I correct to say that this is not working for SQLite, since PrismaJs does not claim to have transaction support for it?

[nenadfilipovic]

I am really not sure.

[2chanhaeng]

I solve this with @@unique!

1. Add @@unique in the defintion of the model to update.

// prisma/schema.prisma
model <The model to update>{
  <id field>  <id type> @id
  <not nuique field> <not nuique type>
  ...
  @@unique([<id field>, <not nuique field>]) // The most important part!
}

2. Migrate DB.
3. Then you can update where: <id field>_<not nuique field>!

import { PrismaClient } from "@prisma/client";
const db = new PrismaClient();
db.todo.update({
  where: {
    "<id field>_<not nuique field>": {
      "<id field>": "<id value>",
      "<not nuique field>": "<not nuique value>"
    },
  data: { "<update field>": "<update value>" },
  });

Also, you can name it!

@@unique(..., name:"<unique name>")

Then you can update using it like this!

db.todo.update({
  where: {
    "<unique name>": {
        "<id field>": "<id value>",
        "<not nuique field>": "<not nuique value>"
      },
...

The default name is very complicate, so I recomend name the unique field!

[blevine]

It seems to me that having to do an "extra" SELECT to retrieve the data you may have just updated is not that big of a deal. If you were not using an ORM like Prisma, this is what you'd need to do. In the updateUnique case, is Prisma just doing that SELECT for you? It looks like the confusion here boils down to the fact that one type of UPDATE returns the new data and one just returns counts. Although I agree that the fact that you can't do an updateUnique with a predicate containing multiple conditions that are guaranteed to refer to a unique row is confusing.

[vaughngit]

really?

[blevine]

yes, really

[vaughngit]

Its that kind of mindset that kills innovation. If this is the mindset of the developers, it would be good to know.

[panzi]

If you were not using an ORM like Prisma, this is what you'd need to do.

Not true. If I'd use the DB directly I'd do:

UPDATE things SET foo = 'bar' WHERE id = ? AND user_id = ? RETURNING *;

Other ORMs support this. The nice thing about this is that this is actually atomic! UPDATE - SELECT isn't.

[blevine]

@panzi I was not aware of RETURNING *. So I learned something new today. Thank you :)

[bpetty-interlace]

Any idea of when something like this could be supported?

[bpetty-interlace]

A related issue: #7290

The reason this probably wont happen any time soon is because the prisma framework generates classes specifically for when we are doing a unique selection, to only expose those properties. This would require deleting all of that work and basically using the same class used by the "Many" calls to expose all of the properties, with the caveat that when doing a select, update or delete... it would iterate through to make sure you chose to filter by at least one property that can determine uniqueness. While significantly more usable, you turn what was once a compile time catch into something that will throw at run time. Honestly, I don't know how Prisma is handling composite indexes with this... it could be a complete mess. All that to say, you're probably better off to write a plugin that exposes this functionality than to expect anyone on the Prisma project to actually redesign everything. This discussion is almost 2 years old already.

[janpio]

It is definitely not super high on our priority list, compared to all the other things we are working on. But generally we of course see the use case here, and also hear the need - there are a bunch of issues like that one. And we will take a look at all these sooner or later and then potentially also try to implement something. But it is hard to say when that will be unfortunately.

[doiya46]

Use extendedWhereUnique feature in schema.prisma file

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["extendedWhereUnique"]
}

[Abdel-Monaam-Aouini]

Nice :)