@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.

When a combination of fields is unique, Prisma will generate a new type for the where condition which is basically just the name of all the relevant fields appended together with _.

Take a look at the categoriesWhereUniqueInput to see what the name is. Most likely it is called user_id_hash or hash_user_id.

This is what the query will look like, roughly speaking:

const category = await prisma.categories.upsert({
    where: {
        user_id_hash: {  // user_id_hash is the type generated by Prisma. Might be called something else though. 
            user_id: 1,
            hash: "foo"
        }
    },
    update: {
        name: "bar",
    },
    create: {
        hash: "foo",
        name: "bar",
        user_id: 1,
    },
})

This is an old post, but I bumped into the same question in 2023, so I'll leave a solution I found for whoever googled here.

Short Answer

Using AND needs to be enabled like this:

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

More Info

You can find discussion about this feature on Prisma's GitHub in this post.

Originally answered at this comment.

You can't do it right now in Prisma. There is createMany, updateMany and deleteMany, but no upsertMany. (Docs)

The most efficient way if you need to handle lots of data would probably be something like that:

prisma.$transaction([
  prisma.posts.deleteMany({ where: { userId: 1 } }),
  prisma.posts.createMany({
    { id: 1, title: 'first',  userId: 1 },
    { id: 2, title: 'second', userId: 1 },
    { id: 3, title: 'third',  userId: 1 },
  }),
]);

So you delete existing records and then recreate them again inside of a transaction.

Hey @hirasaki1985 👋 ,

I reproduced this on my end as well (using MySQL) and it does seem unexpected. I'm going to convert this discussion into an issue where our engineering teams will pick it up for investigation.

Prisma doesn't natively support upsertMany.

There is a Feature Request to provide the upsertMany method.

As of now the best approach would be to loop over the data and invoke upsert in the loop along with using $transaction.

Example:

const collection = await prisma.$transaction(
    userData.map(cur =>
      prisma.cur.upsert({
        where: { id: cur.id },
        update: {},
        create: { id: cur.id },
      })
    )
  )

Here's a reference to $transaction API which should be helpful.

If I understand your question correctly, the request body may optionally contain an id or be undefined. If the id is present in the request, you want to update the corresponding record. If it isn't, you want to create a new record.

upsert functionality requires a unique identifier that can be used by the database to check whether there is a matching record. The database will then decide what to do. However with an undefined value, prisma cannot work.

You could use a key that will not exist in the database in case the value of id is undefined to make upsert insert the record in this case.

await prisma.profile.upsert({
  where: { id: id || '' },
  // ...
});

Or you could differentiate the cases and treat them appropriately:

if (id) {
  await prisma.profile.update({
    where: { id },
    data: {
      username,
      about,
      // ...
    },
  });
} else {
  await prisma.profile.create({
    data: {
      username,
      about,
      // ...
    },
  });
}

You might want to think about the case that there is an id given in the body, but it does exist in the database. Do you want the update to fail and reply with an error? Or do you want the database to silently insert? Then use upsert instead of update above.

@moroshko 👋

Currently yes this is the only way you could do it via Prisma's provided API. It would be great if you could add a 👍 to this request so that we can look at the priority for this.
#4134