I have a n-to-m relationship between GroceryItems and Nutrients with following Prisma schema in my TypeScript+TsEd.io+Koa+Prisma backend stack:
model GroceryItem {
/// @TsED.Groups("!creation")
id String @id @default(cuid())
name String @unique
nutrients GroceryNutrient[]
}
model GroceryNutrient {
itemId String
nutrientId String
item GroceryItem @relation(fields: [itemId], references: [id], onDelete: Cascade)
nutrient Nutrient @relation(fields: [nutrientId], references: [id], onDelete: Cascade)
amount Float
unit String
@@id([itemId, nutrientId])
}
model Nutrient {
/// @TsED.Groups("!creation")
id String @id @default(cuid())
name String @unique
itemNutrients GroceryNutrient[]
}
Assuming that the database contains the following data:
groceryItems: [
{ id: "xyz", name: "Banana" }
]
nutrients: [
{ id: "123", name: "Magnesium" },
{ id: "124", name: "Potassium" },
{ id: "125", name: "Natrium" },
]
groceryNutrients: [
{ itemId: "xyz", nutrientId: "123", amount: 1, unit: "g" },
{ itemId: "xyz", nutrientId: "124", amount: 2, unit: "mg" },
]
From the frontend I do receive the id and the grocery item data as REST parameter and body, respectivelly. For example:
const id = "xyz";
const item = {
id: "xyz",
name: "Banana",
nutrients: [
{ id: "123", name: "Magnesium", amount: 100, unit: "g" },
{ id: "125", name: "Natrium", amount: 200, unit: "g" }
]
};
I want to use an upsert in order to update the nutrient 123, delete the nutrient 124 and add the new nutrient 125 to the item Banana. My code so far throws a PrismaClientValidationError on update().
async updateItem(
@Required() @PathParams("id") id: string,
@Required() @BodyParams() item: GroceryItem
) {
return prisma.groceries.update({
where: { id: id },
data: {
name: item.name,
nutrients: {
upsert: item.nutrients.map(nutrient => ({
where: {
itemId_nutrientId: {
itemId: id,
nutrientId: nutrient.id
}
},
update: {
amount: nutrient.amount,
unit: nutrient.unit,
item: { connect: { id: id } },
nutrient: { connect: { id: nutrient.id } }
},
create: {
amount: nutrient.amount,
unit: nutrient.unit,
item: { connect: { id: id } },
nutrient: { connect: { id: nutrient.id } }
}
}))
}
}
});
return result;
}
}
Until now I have focused solemnly on inserting and updating connections between the grocery items and nutrients, not removing them. I have not found a solution online from: StackOverflow, the official Prisma documentation and ChatGPT4.
PS: can I incorporate a delete logic for the nutrient 124 in this code? I would like to keep the update atomic through a single database call to prevent inconsistencies.