@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.
node.js - Prisma - Update one resource under more condition - Stack Overflow
how to upsert many fields in prisma ORM - javascript
upsert does not working with multi unique key.
How is .upsertMany() implemented in Prisma ORM?
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,
},
})
Just in case someone stumbles upon this: if you cannot find the option in the where clause to select by user_id_hash, then you need to first have declared the unique composite constraint in the table definition.
@@unique([fieldOne, fieldTwo])
After doing this you will be able to select in code, on the where condition: fieldOne_fieldTwo.
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.
Depending on the database (and schema) you use, Prisma supports an optional boolean within a createMany call: skipDuplicates, see https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#createmany
Do not insert records with unique fields or ID fields that already exist. Only supported by databases that support ON CONFLICT DO NOTHING.
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.
You can use upsert to update multiple objects like the following: In the following example, I am updating a user's profile and upserting multiple addresses by using the map function.
await this.prisma.user.update({
where: {
id: user.id
},
data:{
fullName: saveUserProfileDto?.fullName,
email: saveUserProfileDto?.email,
userAddresses: {
upsert: saveUserProfileDto?.addresses?.map(address => ({
where: {
uuid: address.uuid || ""
},
create: {
uuid: uuidv4(),
country: address.country,
cityTown: address.cityTown,
streetAddress: address.streetAddress,
apartmentSuit: address.apartmentSuit,
},
update: {
country: address.country,
cityTown: address.cityTown,
streetAddress: address.streetAddress,
apartmentSuit: address.apartmentSuit,
}
}))
}
}
});
So you don't need to delete anything before upserting.
I have a question about nested upserts and if they're even possible.
I have a Prisma create that's working as expected;
An orders (table) can create an array of artwork (table), and the artwork can create an array of shirts (table).
The issue is when I want to update the shirts table through the order, similar to the create, with an upsert.
This block works as expected.
const CREATE = await prisma.orders.create({
data: {
artwork: {
create: artwork.map(art => {
return {
notes: art.notes,
shirts: {
create: art.shirts.map(shirt => ({
name: shirt.name,
url: shirt.url,
})),
},
};
})
}
},
});This block works to upsert the artwork, but fails when trying to upsert the shirts.
const UPDATE = await prisma.orders.create({
data: {
artwork: {
upsert: artwork.map(art => {
const updatedArtworkWithShirts = {
notes: art.notes,
shirts: {
upsert: art.shirts.map(shirt => {
const updatedShirt = {
name: shirt.name,
url: shirt.url,
};
return {
where: { id: shirt.id },
update: updatedShirt,
create: updatedShirt,
};
}),
},
};
return {
where: { id: art.id },
update: updatedArtworkWithShirts,
create: updatedArtworkWithShirts,
};
}),
},
}
});The error I'm receiving is:
Unknown arg `upsert` in data.artwork.upsert.0.create.shirts.upsert for type shirtsCreateNestedManyWithoutArtworkInput. Did you mean `select`?
Any thoughts? Am I approaching this incorrectly?