Client: using update + connect throws an error on an idempotent operation

[jkomyno]

It seems there is a common understanding that the following Client query performed on the given schema should be idempotent, given that user and profile are linked via an optional 1-to-1 relation, and a user with id 1 with a profile whose id is 1 already exists. However, the following currently throws an error (prisma@4.2.0). This problem isn't related to a specific provider.

Schema

generator client {
  provider = "prisma-client-js"
}

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

model User {
  id      String @id
  profile Profile?
}

model Profile {
  id       String @id
  user     User @relation(fields: [userId], references: [id])
  userId   String @unique
}

Client Code

import { PrismaClient } from './node_modules/.prisma/client'

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

  // create user with id 1, with an associated profile with id 1
  await prisma.user.create({
    data: {
      id: '1',
      profile: {
        create: { id: '1' }
      },
    }
  })
  
  // this throws, but it should actually be idempotent
  await prisma.user.update({
    where: { id: '1' },
    data: {
      profile: {
        connect: { id: '1' },
      },
    },
  })
}

main()

Error

❯ ts-node index.ts
/Users/jkomyno/work/prisma/prisma-copy/packages/client/reprod/node_modules/@prisma/client/runtime/index.js:28831
      throw new PrismaClientKnownRequestError(message, error2.code, this.client._clientVersion, error2.meta);
            ^
PrismaClientKnownRequestError: 
Invalid `prisma.user.update()` invocation in
/Users/jkomyno/work/prisma/prisma-copy/packages/client/reprod/index.ts:18:21

  15 })
  16 
  17 // this throws, but it should actually be idempotent
→ 18 await prisma.user.update(
  The change you are trying to make would violate the required relation 'ProfileToUser' between the `Profile` and `User` models.
    at RequestHandler.handleRequestError (/Users/jkomyno/work/prisma/prisma-copy/packages/client/reprod/node_modules/@prisma/client/runtime/index.js:28831:13)
    at RequestHandler.request (/Users/jkomyno/work/prisma/prisma-copy/packages/client/reprod/node_modules/@prisma/client/runtime/index.js:28813:12)
    at async PrismaClient._request (/Users/jkomyno/work/prisma/prisma-copy/packages/client/reprod/node_modules/@prisma/client/runtime/index.js:29738:16)
    at async main (/Users/jkomyno/work/prisma/prisma-copy/packages/client/reprod/index.ts:18:3) {
  code: 'P2014',
  clientVersion: '4.2.0',
  meta: {
    relation_name: 'ProfileToUser',
    model_a_name: 'Profile',
    model_b_name: 'User'
  }
}

Also see the internal discussion.

[pimeys]

Debug output:

  prisma:tryLoadEnv  Environment variables loaded from /bugs/prisma-14759/.env +0ms
  prisma:tryLoadEnv  Environment variables loaded from /bugs/prisma-14759/.env +2ms
  prisma:client  dirname /bugs/prisma-14759/node_modules/.prisma/client +0ms
  prisma:client  relativePath ../../../prisma +0ms
  prisma:client  cwd /bugs/prisma-14759/prisma +0ms
  prisma:client  clientVersion 4.3.1 +0ms
  prisma:client  clientEngineType library +0ms
  prisma:client:libraryEngine  internalSetup +0ms
  prisma:client:libraryEngine:loader  Searching for Query Engine Library in /bugs/prisma-14759/node_modules/.prisma/client +0ms
  prisma:client:libraryEngine:loader  loadEngine using /bugs/prisma-14759/node_modules/.prisma/client/libquery_engine-debian-openssl-3.0.x.so.node +0ms
  prisma:client  Prisma Client call: +41ms
  prisma:client  prisma.profile.deleteMany(undefined) +0ms
  prisma:client  Generated request: +0ms
  prisma:client  mutation {
  deleteManyProfile {
    count
  }
}
 +0ms
  prisma:client:libraryEngine  sending request, this.libraryStarted: false +42ms
  prisma:client:libraryEngine  library starting +0ms
prisma:info Starting a postgresql pool with 33 connections.
  prisma:client:libraryEngine  library started +16ms
prisma:query BEGIN
prisma:query SELECT "public"."Profile"."id" FROM "public"."Profile" WHERE 1=1 OFFSET $1 /* traceparent=00-00-00-00 */
prisma:query SELECT "public"."Profile"."id" FROM "public"."Profile" WHERE 1=1 /* traceparent=00-00-00-00 */
prisma:query DELETE FROM "public"."Profile" WHERE "public"."Profile"."id" IN ($1) /* traceparent=00-00-00-00 */
prisma:query COMMIT
  prisma:client  Prisma Client call: +29ms
  prisma:client  prisma.user.deleteMany(undefined) +0ms
  prisma:client  Generated request: +0ms
  prisma:client  mutation {
  deleteManyUser {
    count
  }
}
 +0ms
  prisma:client:libraryEngine  sending request, this.libraryStarted: true +12ms
prisma:query BEGIN
prisma:query SELECT "public"."User"."id" FROM "public"."User" WHERE 1=1 OFFSET $1 /* traceparent=00-00-00-00 */
prisma:query SELECT "public"."User"."id" FROM "public"."User" WHERE 1=1 /* traceparent=00-00-00-00 */
prisma:query DELETE FROM "public"."User" WHERE "public"."User"."id" IN ($1) /* traceparent=00-00-00-00 */
prisma:query COMMIT
  prisma:client  Prisma Client call: +5ms
  prisma:client  prisma.user.create({
  data: {
    id: '1',
    profile: {
      create: {
        id: '1'
      }
    }
  }
}) +1ms
  prisma:client  Generated request: +0ms
  prisma:client  mutation {
  createOneUser(data: {
    id: "1"
    profile: {
      create: {
        id: "1"
      }
    }
  }) {
    id
  }
}
 +0ms
  prisma:client:libraryEngine  sending request, this.libraryStarted: true +6ms
prisma:query BEGIN
prisma:query INSERT INTO "public"."User" ("id") VALUES ($1) RETURNING "public"."User"."id" /* traceparent=00-00-00-00 */
prisma:query INSERT INTO "public"."Profile" ("id","userId") VALUES ($1,$2) RETURNING "public"."Profile"."id" /* traceparent=00-00-00-00 */
prisma:query SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1 LIMIT $2 OFFSET $3 /* traceparent=00-00-00-00 */
prisma:query COMMIT
  prisma:client  Prisma Client call: +4ms
  prisma:client  prisma.user.update({
  where: {
    id: '1'
  },
  data: {
    profile: {
      connect: {
        id: '1'
      }
    }
  }
}) +0ms
  prisma:client  Generated request: +0ms
  prisma:client  mutation {
  updateOneUser(
    where: {
      id: "1"
    }
    data: {
      profile: {
        connect: {
          id: "1"
        }
      }
    }
  ) {
    id
  }
}
 +0ms
  prisma:client:libraryEngine  sending request, this.libraryStarted: true +4ms
prisma:query BEGIN
prisma:query SELECT "public"."Profile"."id", "public"."Profile"."userId" FROM "public"."Profile" WHERE "public"."Profile"."id" = $1 OFFSET $2 /* traceparent=00-00-00-00 */
prisma:query SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1 /* traceparent=00-00-00-00 */
prisma:query SELECT "public"."Profile"."id", "public"."Profile"."userId" FROM "public"."Profile" WHERE (1=1 AND "public"."Profile"."userId" IN ($1)) OFFSET $2 /* traceparent=00-00-00-00 */
prisma:query ROLLBACK
  prisma:client:request_handler  PrismaClientKnownRequestError: The change you are trying to make would violate the required relation 'ProfileToUser' between the `Profile` and `User` models.
    at prismaGraphQLToJSError (/bugs/prisma-14759/node_modules/@prisma/client/runtime/index.js:20426:12)
    at LibraryEngine.buildQueryError (/bugs/prisma-14759/node_modules/@prisma/client/runtime/index.js:26363:12)
    at LibraryEngine.request (/bugs/prisma-14759/node_modules/@prisma/client/runtime/index.js:26297:22)
    at async RequestHandler.request (/bugs/prisma-14759/node_modules/@prisma/client/runtime/index.js:29873:24)
    at async PrismaClient._request (/bugs/prisma-14759/node_modules/@prisma/client/runtime/index.js:30864:16)
    at async main (/bugs/prisma-14759/script.ts:22:3) {
  code: 'P2014',
  clientVersion: '4.3.1',
  meta: {
    relation_name: 'ProfileToUser',
    model_a_name: 'Profile',
    model_b_name: 'User'
  }
} +0ms
PrismaClientKnownRequestError: 
Invalid `prisma.user.update()` invocation in
/bugs/prisma-14759/script.ts:22:21

  19 })
  20 
  21 // this throws, but it should actually be idempotent
→ 22 await prisma.user.update(
The change you are trying to make would violate the required relation 'ProfileToUser' between the `Profile` and `User` models.
    at RequestHandler.handleRequestError (/bugs/prisma-14759/node_modules/@prisma/client/runtime/index.js:29909:13)
    at RequestHandler.request (/bugs/prisma-14759/node_modules/@prisma/client/runtime/index.js:29892:12)
    at async PrismaClient._request (/bugs/prisma-14759/node_modules/@prisma/client/runtime/index.js:30864:16)
    at async main (/bugs/prisma-14759/script.ts:22:3) {
  code: 'P2014',
  clientVersion: '4.3.1',
  meta: {
    relation_name: 'ProfileToUser',
    model_a_name: 'Profile',
    model_b_name: 'User'
  }
}
  prisma:client:libraryEngine  library stopping +16ms
  prisma:client:libraryEngine  library stopped +1ms
  prisma:client:libraryEngine:exitHooks  exit event received: exit +0ms

[pimeys]

Schema SQL:

CREATE TABLE "User" (
  "id" TEXT NOT NULL,
  CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "Profile" (
  "id" TEXT NOT NULL,
  "userId" TEXT NOT NULL,
  CONSTRAINT "Profile_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "Profile_userId_key" ON "Profile"("userId");

ALTER TABLE
  "Profile"
ADD CONSTRAINT "Profile_userId_fkey"
  FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE

[pimeys]

The culprit SQL causing the issue (using foreign keys, but we trigger no updates):

SELECT "public"."Profile"."id", "public"."Profile"."userId"
FROM "public"."Profile"
WHERE "public"."Profile"."id" = 1;

SELECT "public"."User"."id"
FROM "public"."User"
WHERE "public"."User"."id" = 1;

SELECT "public"."Profile"."id", "public"."Profile"."userId"
FROM "public"."Profile"
WHERE ("public"."Profile"."userId" IN (1));

This looks like the core is doing something wrong.

[sorsby]

Was there any update on this issue? I think I've just run into this but in a slightly different scenario.

When I do an upsert and the record already exists, it attempts to update the record and its relations. The update block is exactly the same as the create block therefore I would expect this to be idempotent and not throw an error.

const price = {
  ...
  Transaction: {
    connect: {
      block_id_transaction_index_transaction_hash: {
        block_id: event.blockHeight,
        transaction_index: event.transactionIndex,
        transaction_hash: event.transactionHash,
      },
    },
  },
};

return ctx.prisma.price.upsert({
  where: {
    transaction_hash: event.transactionHash,
  },
  update: price,
  create: price,
});

This throws a similar error as described in this issue:

Invalid `prisma.price.upsert()` invocation: The change you are trying to make would violate the required relation 'PriceToTransaction' between the `Price` and `Transaction` models. Error: Invalid `prisma.price.upsert()` invocation: The change you are trying to make would violate the required relation 'PriceToTransaction' between the `Price` and `Transaction` models.