prisma In nested writes, Prisma should guarantee an order between delete and create

Bug description

In a nested write that contains both creates and deletes, the result can depend on the order those operations happen, but Prisma's documentation doesn't guarantee any particular order.

How to reproduce

With the below schema, the query

await prisma.recipe.update({
    where: { id: recipeId },
    data: {
  "ingredients": {
    "createMany": {
      "data": [{"name": "eggs"}]
    },
    "deleteMany": {"id": {"notIn": [3,]}},
    "update": [{
        "where": {"id": 3},
        "data": {"name": "sugar"}
      },]
  }}});

deletes the just-added ingredient, but

await prisma.recipe.update({
    where: { id: recipeId },
    data: {
  "ingredients": {
    "deleteMany": {"id": {"notIn": [3,]}},
    "createMany": {
      "data": [{"name": "eggs"}]
    },
    "update": [{
        "where": {"id": 3},
        "data": {"name": "sugar"}
      },]
  }}});

runs the delete before the create, and leaves the new ingredient around.

(Sorry if this doesn't work exactly; I deleted bits from my actual code but haven't tested the result.)

Expected behavior

I initially expected Prisma to always run the delete first, but it's possible that some nested writes need to run the delete last. It looks like the actual behavior is to match the enumeration order of the JS object parameter, which is subtle but not obviously the wrong thing to do. If that was intentional, please document it so that it won't change in future versions.

Prisma information

With unnecessary fields stripped out:

model Recipe {
  id          Int                @id @default(autoincrement())
  ingredients RecipeIngredient[]
}

model RecipeIngredient {
  id   Int @id @default(autoincrement())
  name String

  recipe   Recipe @relation(fields: [recipeId], references: [id])
  recipeId Int
}

Environment & setup

OS: Ubuntu 22.04.1 inside WSL
Database: PostgreSQL
Node.js version: v18.12.0

Prisma Version

prisma                  : 4.6.1
@prisma/client          : 4.6.1
Current platform        : debian-openssl-3.0.x
Query Engine (Node-API) : libquery-engine 694eea289a8462c80264df36757e4fdc129b1b32 (at node_modules/.pnpm/@[email protected]/node_modules/@prisma/engines/libquery_engine-debian-openssl-3.0.x.so.node)
Migration Engine        : migration-engine-cli 694eea289a8462c80264df36757e4fdc129b1b32 (at node_modules/.pnpm/@[email protected]/node_modules/@prisma/engines/migration-engine-debian-openssl-3.0.x)
Introspection Engine    : introspection-core 694eea289a8462c80264df36757e4fdc129b1b32 (at node_modules/.pnpm/@[email protected]/node_modules/@prisma/engines/introspection-engine-debian-openssl-3.0.x)
Format Binary           : prisma-fmt 694eea289a8462c80264df36757e4fdc129b1b32 (at node_modules/.pnpm/@[email protected]/node_modules/@prisma/engines/prisma-fmt-debian-openssl-3.0.x)
Format Wasm             : @prisma/prisma-fmt-wasm 4.6.1-3.694eea289a8462c80264df36757e4fdc129b1b32
Default Engines Hash    : 694eea289a8462c80264df36757e4fdc129b1b32
Studio                  : 0.476.0
Preview Features        : postgresqlExtensions

Dec 02 '22 22:12 jyasskin

Do you have any feedback on when it will be fixed? I start having the same issue. I'm using the following version:

"@prisma/client": "^4.16.0",
"prisma": "^4.16.0"

Jun 22 '23 06:06 joao-moonward

I'm actually relying on the current behaviour so I would prefer if this won't change and instead added to the docs. As an example my schema looks something like this:

model User {
  email String                  @unique
  id    String                  @id @default(uuid())
  tags  UserTag[]
}

model UserTag {
  name  String
  user    User   @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  userId String

  @@unique([name, userId])
  @@index([userId])
}

And when I update the user I want to ensure that only relations for the provided tag names exist so I rely on first deleting all existing relations before creating the updated/new ones:

await prismaClient.user.update({
  data: {
    tags: {
      // NOTE:
      // The order of props matters. We first want to delete all existing relations
      // before creating new ones
      deleteMany: {},
      createMany: {
        data: input.tags.map((name) => ({ name })),
      },
    },
  },
  where: {
    id: input.id,
  },
})

Aug 22 '23 14:08 davidruisinger

It seems strange/unexpected to me to use order of object keys to dictate the order of operations. It also seems limiting - since object keys must be unique, you cannot "delete then create then delete" in one call.

Arrays would not have either of these problems. Array order is guaranteed in javascript and used routinely, and there are no keys to conflict.

[
  { deleteMany: {} },
  { createMany: { data: ... } },
  { deleteMany: { id: ... } },
]

Clearly it would be a breaking change, but could we consider using arrays for when order of operations matters, and objects for when it does not?

Oct 28 '23 23:10 mattrabe

Hey all, it's been a while, but I can reproduce this both on [email protected] and the latest [email protected], with the following schema and snippets:

schema.prisma

generator client {
  provider        = "prisma-client-js"
  output          = "../node_modules/.prisma/client"
  previewFeatures = []
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Recipe {
  id          Int                @id @default(autoincrement())
  ingredients RecipeIngredient[]
}

model RecipeIngredient {
  id   Int @id @default(autoincrement())
  name String

  recipe   Recipe @relation(fields: [recipeId], references: [id])
  recipeId Int
}

TypeScript snippet 1: nested update with createMany -> deleteMany -> update

import { PrismaClient } from '@prisma/client'

async function main() {
  const prisma = new PrismaClient()

  await prisma.recipeIngredient.deleteMany()
  await prisma.recipe.deleteMany()

  const created1 = await prisma.recipe.create({
    data: {
      id: 1,
      ingredients: {
        create: [{ id: 1, name: 'flour' }],
      },
    },
    select: { id: true, ingredients: true },
  })

  const created2 = await prisma.recipe.create({
    data: {
      id: 2,
      ingredients: {
        create: [{ id: 2, name: 'polenta' }],
      },
    },
    select: { id: true, ingredients: true },
  })

  console.log('[created]')
  console.dir(created1, { depth: null })
  console.dir(created2, { depth: null })
  console.log('')

  // (query 1)
  const updatedThatDeleted = await prisma.recipe.update({
    select: { id: true, ingredients: true },
    where: { id: 2 },
    data: {
      ingredients: {
        createMany: {
          data: [{ id: 3, name: 'eggs' }]
        },
        deleteMany: { id : { notIn: [3]}},
        update: [{
          where: { id: 3 },
          data: { id: 5, 'name': 'sugar' }
        }],
      },
    },
  })

  console.log('\n[updated]')
  console.dir(updatedThatDeleted, { depth: null })

  const recipes = await prisma.recipe.findMany({
    select: { ingredients: true, id: true },
    orderBy: { id: 'asc' },
  })
  console.log('\n[recipes]')
  console.dir(recipes, { depth: null })
}

main().catch((e) => {
  console.log(e.message)
  process.exit(1)
})

Output:

[created]
{ id: 1, ingredients: [ { id: 1, name: 'flour', recipeId: 1 } ] }
{ id: 2, ingredients: [ { id: 2, name: 'polenta', recipeId: 2 } ] }

[updated]
{ id: 2, ingredients: [] }

[recipes]
[
  { ingredients: [ { id: 1, name: 'flour', recipeId: 1 } ], id: 1 },
  { ingredients: [], id: 2 }
]

In particular, (query 1) results in the following SQL transactional statements:

BEGIN

SELECT "public"."Recipe"."id" FROM "public"."Recipe" WHERE ("public"."Recipe"."id" = 2 AND 1=1)

-- ingredients.createMany
INSERT INTO "public"."RecipeIngredient" ("id","name","recipeId") VALUES (3,eggs,2)

-- ingredients.deleteMany
SELECT "public"."RecipeIngredient"."id", "public"."RecipeIngredient"."recipeId" FROM "public"."RecipeIngredient" WHERE ("public"."RecipeIngredient"."id" NOT IN (3) AND "public"."RecipeIngredient"."recipeId" IN (2)) OFFSET 0
DELETE FROM "public"."RecipeIngredient" WHERE ("public"."RecipeIngredient"."id" IN (2) AND 1=1)

-- ingredients.update
SELECT "public"."RecipeIngredient"."id", "public"."RecipeIngredient"."recipeId" FROM "public"."RecipeIngredient" WHERE (("public"."RecipeIngredient"."id" = 3 AND 1=1) AND "public"."RecipeIngredient"."recipeId" IN (2)) OFFSET 0
UPDATE "public"."RecipeIngredient" SET "id" = 5, "name" = sugar WHERE ("public"."RecipeIngredient"."id" IN (3) AND ("public"."RecipeIngredient"."id" = 3 AND 1=1))

SELECT "public"."Recipe"."id" FROM "public"."Recipe" WHERE "public"."Recipe"."id" = 2 LIMIT 1 OFFSET 0
SELECT "public"."RecipeIngredient"."id", "public"."RecipeIngredient"."name", "public"."RecipeIngredient"."recipeId" FROM "public"."RecipeIngredient" WHERE "public"."RecipeIngredient"."recipeId" IN (2) OFFSET 0

COMMIT

TypeScript snippet 2: nested update with deleteMany -> createMany -> update

import { PrismaClient } from '@prisma/client'

async function main() {
  const prisma = new PrismaClient()

  await prisma.recipeIngredient.deleteMany()
  await prisma.recipe.deleteMany()

  const created1 = await prisma.recipe.create({
    data: {
      id: 1,
      ingredients: {
        create: [{ id: 1, name: 'flour' }],
      },
    },
    select: { id: true, ingredients: true },
  })

  const created2 = await prisma.recipe.create({
    data: {
      id: 2,
      ingredients: {
        create: [{ id: 2, name: 'polenta' }],
      },
    },
    select: { id: true, ingredients: true },
  })

  console.log('[created]')
  console.dir(created1, { depth: null })
  console.dir(created2, { depth: null })
  console.log('')

  // (query 2)
  const updatedThatDidntDelete = await prisma.recipe.update({
    select: { id: true, ingredients: true },
    where: { id: 2 },
    data: {
      ingredients: {
        deleteMany: { id : { notIn: [3]}},
        createMany: {
          data: [{ id: 3, name: 'eggs' }]
        },
        update: [{
          where: { id: 3 },
          data: { id: 5, 'name': 'sugar' }
        }],
      },
    },
  })

  console.log('\n[updated]')
  console.dir(updatedThatDidntDelete, { depth: null })

  const recipes = await prisma.recipe.findMany({
    select: { ingredients: true, id: true },
    orderBy: { id: 'asc' },
  })
  console.log('\n[recipes]')
  console.dir(recipes, { depth: null })
}

main().catch((e) => {
  console.log(e.message)
  process.exit(1)
})

Output:

[created]
{ id: 1, ingredients: [ { id: 1, name: 'flour', recipeId: 1 } ] }
{ id: 2, ingredients: [ { id: 2, name: 'polenta', recipeId: 2 } ] }


[updated]
{ id: 2, ingredients: [ { id: 5, name: 'sugar', recipeId: 2 } ] }

[recipes]
[
  { ingredients: [ { id: 1, name: 'flour', recipeId: 1 } ], id: 1 },
  { ingredients: [ { id: 5, name: 'sugar', recipeId: 2 } ], id: 2 }
]

In particular, (query 2) results in the following SQL transactional statements:

BEGIN

SELECT "public"."Recipe"."id" FROM "public"."Recipe" WHERE ("public"."Recipe"."id" = 2 AND 1=1)

-- ingredients.deleteMany
SELECT "public"."RecipeIngredient"."id", "public"."RecipeIngredient"."recipeId" FROM "public"."RecipeIngredient" WHERE ("public"."RecipeIngredient"."id" NOT IN (3) AND "public"."RecipeIngredient"."recipeId" IN (2)) OFFSET 0
DELETE FROM "public"."RecipeIngredient" WHERE ("public"."RecipeIngredient"."id" IN (2) AND 1=1)

-- ingredients.createMany
INSERT INTO "public"."RecipeIngredient" ("recipeId","name","id") VALUES (2,eggs,3)

-- ingredients.update
SELECT "public"."RecipeIngredient"."id", "public"."RecipeIngredient"."recipeId" FROM "public"."RecipeIngredient" WHERE (("public"."RecipeIngredient"."id" = 3 AND 1=1) AND "public"."RecipeIngredient"."recipeId" IN (2)) OFFSET 0
UPDATE "public"."RecipeIngredient" SET "id" = 5, "name" = sugar WHERE ("public"."RecipeIngredient"."id" IN (3) AND ("public"."RecipeIngredient"."id" = 3 AND 1=1))

SELECT "public"."Recipe"."id" FROM "public"."Recipe" WHERE "public"."Recipe"."id" = 2 LIMIT 1 OFFSET 0
SELECT "public"."RecipeIngredient"."id", "public"."RecipeIngredient"."name", "public"."RecipeIngredient"."recipeId" FROM "public"."RecipeIngredient" WHERE "public"."RecipeIngredient"."recipeId" IN (2) OFFSET 0

COMMIT

So yeah, at the present state, it looks like the order object keys definitely dictates the order of operations. We will raise the attention to this internally, so that figure out the next steps to take and to shed light on what the intended behavior should be. Thanks!

Mar 21 '24 13:03 jkomyno

prisma prisma copied to clipboard

In nested writes, Prisma should guarantee an order between delete and create

Bug description

How to reproduce

Expected behavior

Prisma information

Environment & setup

Prisma Version

prisma
prisma copied to clipboard