Error on Upsert in Prisma+TypeScript with n-to-m relationship

16 views Asked by At

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.

0

There are 0 answers