Prisma-client-js: Running into an issue with the connectOrCreate operation. The following query should create an entity for a Winery, and assign a Tag to it.

[janpio]

_Originally posted by @RoaldSchuring in https://github.com/prisma/prisma-client-js/issues/336#issuecomment-653785179_

Running into an issue with the connectOrCreate operation. The following query should create an entity for a Winery, and assign a Tag to it.

mutation createwinery {
  createOneWinery(
    data: {
      name: "Winery X",
      author: { connect: { email: "[email protected]" } },
      tag: {
        create: {
          tagName: {
            connectOrCreate: {
              where: { name: "Tag 1" }
              create: {
                name: "Tag 1",
                author: { connect: { email: "[email protected]" } }
              }
            }
          }
          author: { connect: { email: "[email protected]" } }
        }
      }
    }
  ) {
    name
    author {
      name
    }
    tag {
      tagName {
        name
      }
    }
  }
}

I get the error message in the image below
.

When I replace the connectOrCreate operation with a regular create the query runs as expected, without error.

[janpio]

Thanks for reporting this!

Can you share your schema @RoaldSchuring or the relevant part of it? That would make it a lot easier to reproduce this.

Can you possible also run with DEBUG=* and attach its output as well please?

[RoaldSchuring]

Thanks for the quick response! Absolutely. Here's are the Tag, TagWinery and Winery tables from the schema.

model Tag {
  id          String     @default(uuid()) @id
  name        String     @unique
  createdAt   DateTime   @default(now())
  author      User       @relation(fields: [authorId], references: [id])
  authorId    String
  tagWinery   TagWinery[]
}

model TagWinery {
  id          String      @default(uuid()) @id
  tagName     Tag         @relation(fields: [tagId], references: [id])
  tagId       String
  winery      Winery      @relation(fields: [wineryId], references: [id])
  wineryId    String
  createdAt   DateTime    @default(now())
  author      User        @relation(fields: [authorId], references: [id])
  authorId    String
}

model Winery {
  id                  String              @default(uuid()) @id
  name                String              @unique
  createdAt           DateTime            @default(now())
  author              User                @relation(fields: [authorId], references: [id])
  authorId            String
  tag                 TagWinery[]

}

And here is the error message after running with DEBUG

[pantharshit00]

I can reproduce this. Here is a reproduction without nexus: https://github.com/harshit-test-org/prisma-client-issue-764

[tomhoule]

Update for release planning: this needs more immersion in the query engine internals, I haven't had time to tackle it for 2.4.0.

[dpetrick]

Just looking at the query: This can't succeed. If you don't have an author to connect to, the query will always fail.
Specifically, these lines can't work if the author doesn't exist: author: { connect: { email: "[email protected]" } }, because as opposed to connectOrCreate, connect assumes that the record to connect exists.

[special-character]

Not sure if it helps but I also ran into this when trying out connectOrCreate. I was curious what SQL ran but nothing stood out to me in the below:

2020-08-12 10:53:34.630 PDT [70026] LOG:  statement: BEGIN
2020-08-12 10:53:34.631 PDT [70026] LOG:  execute s163: SELECT "public"."Item"."id" FROM "public"."Item" WHERE "public"."Item"."id" = $1 OFFSET $2
2020-08-12 10:53:34.631 PDT [70026] DETAIL:  parameters: $1 = 'ckdro9ym80000dSetvcs5iir7', $2 = '0'
2020-08-12 10:53:34.631 PDT [70026] LOG:  execute s13: SELECT "public"."Log"."id" FROM "public"."Log" WHERE "public"."Log"."id" = $1 OFFSET $2
2020-08-12 10:53:34.631 PDT [70026] DETAIL:  parameters: $1 = '2020.8.12-cEBicXCDIHgG9Sw8uvACwN0ss', $2 = '0'
2020-08-12 10:53:34.632 PDT [70026] LOG:  execute s15: SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1 OFFSET $2
2020-08-12 10:53:34.632 PDT [70026] DETAIL:  parameters: $1 = 'cEBicXCDIHgG9Sw8uvACwN0ss', $2 = '0'
2020-08-12 10:53:34.632 PDT [70026] LOG:  execute s15: SELECT "public"."User"."id" FROM "public"."User" WHERE "public"."User"."id" = $1 OFFSET $2
2020-08-12 10:53:34.632 PDT [70026] DETAIL:  parameters: $1 = 'cEBicXCDIHgG9Sw8uvACwN0ss', $2 = '0'
2020-08-12 10:53:34.632 PDT [70026] LOG:  statement: ROLLBACK

Running each of those manually succeeded without issue (although the first one returns nothing because the item was new)

My upsert effectively looked like this:

 const upsertArgs = {
    id: itemId,
    creator: { connect: { id: userId } },
    owner: { connect: { id: userId } },
    ...(logId
      ? {
          log: {
            connectOrCreate: {
              where: { id: logId },
              create: {
                id: logId,
                owner: { connect: { id: userId } },
                date: date,
              },
            },
          },
        }
      : {}),
  }
  return await db.item.upsert({
    where: { id: itemId },
    create: upsertArgs,
    update: upsertArgs
  })

I confirmed that userId and logId were both in the db already and were valid.

[pantharshit00]

Another possible duplicate case with a reproduction: https://github.com/prisma/prisma/issues/3612#issuecomment-692712726

[janpio]

Closing in favor of: https://github.com/prisma/prisma/issues/3612