upsert on compound primary key should do ON CONFLICT DO ... in postgresql - IS BACK

[AllanOricil]

Bug description

It is the same bug as this #18883

It reapered on v5

How to reproduce

I'm also facing this problem with prisma v5.6

model Instance {
  id        String   @id @default(auto()) @map("_id")
  subdomain String  @unique
  account_id String  @db.ObjectId
  account   Account @relation(fields: [account_id], references: [id])
  created_at DateTime @default(now())
  updated_at DateTime @updatedAt

  @@index([account_id, subdomain], name: "account_id_subdomain_unique")
}

2023-12-12T02:32:37.056Z	f3e3f645-0f34-5b41-889c-abb4c74cab10	ERROR	Invoke Error 	{
    "errorType": "PrismaClientValidationError",
    "errorMessage": "\nInvalid `prisma.instance.upsert()` invocation:\n\n{\n  where: {\n    account_id_subdomain_unique: {\n    ~~~~~~~~~~~~~~~~~~~~~~~~~~~\n      account_id: \"6570eb74a0a5f0d856f2db82\",\n      subdomain: \"test\"\n    },\n?   id?: String,\n?   subdomain?: String,\n?   AND?: InstanceWhereInput | InstanceWhereInput[],\n?   OR?: InstanceWhereInput[],\n?   NOT?: InstanceWhereInput | InstanceWhereInput[],\n?   aws_id?: StringNullableFilter | String | Null,\n?   aws_instance_state?: EnumAWS_INSTANCE_STATEFilter | AWS_INSTANCE_STATE,\n?   state?: EnumINSTANCE_STATEFilter | INSTANCE_STATE,\n?   tier_id?: StringFilter | String,\n?   account_id?: StringFilter | String,\n?   created_at?: DateTimeFilter | DateTime,\n?   updated_at?: DateTimeFilter | DateTime,\n?   tier?: InstanceSubscriptionTierRelationFilter | InstanceSubscriptionTierWhereInput,\n?   account?: AccountRelationFilter | AccountWhereInput\n  },\n  update: {\n    account_id: \"6570eb74a0a5f0d856f2db82\",\n    aws_id: undefined,\n    aws_instance_state: \"PENDING\",\n    subdomain: \"test\",\n    state: \"UNAVAILABLE\",\n    tier_id: \"6570ed90d09e2638e19eeeff\"\n  },\n  create: {\n    account_id: \"6570eb74a0a5f0d856f2db82\",\n    aws_id: undefined,\n    aws_instance_state: \"PENDING\",\n    subdomain: \"test\",\n    state: \"UNAVAILABLE\",\n    tier_id: \"6570ed90d09e2638e19eeeff\"\n  }\n}\n\nUnknown argument `account_id_subdomain_unique`. Available options are marked with ?.",
    "name": "PrismaClientValidationError",
    "clientVersion": "5.6.0",
    "stack": [
        "PrismaClientValidationError: ",
        "Invalid `prisma.instance.upsert()` invocation:",
        "",
        "{",
        "  where: {",
        "    account_id_subdomain_unique: {",
        "    ~~~~~~~~~~~~~~~~~~~~~~~~~~~",
        "      account_id: \"6570eb74a0a5f0d856f2db82\",",
        "      subdomain: \"test\"",
        "    },",
        "?   id?: String,",
        "?   subdomain?: String,",
        "?   AND?: InstanceWhereInput | InstanceWhereInput[],",
        "?   OR?: InstanceWhereInput[],",
        "?   NOT?: InstanceWhereInput | InstanceWhereInput[],",
        "?   aws_id?: StringNullableFilter | String | Null,",
        "?   aws_instance_state?: EnumAWS_INSTANCE_STATEFilter | AWS_INSTANCE_STATE,",
        "?   state?: EnumINSTANCE_STATEFilter | INSTANCE_STATE,",
        "?   tier_id?: StringFilter | String,",
        "?   account_id?: StringFilter | String,",
        "?   created_at?: DateTimeFilter | DateTime,",
        "?   updated_at?: DateTimeFilter | DateTime,",
        "?   tier?: InstanceSubscriptionTierRelationFilter | InstanceSubscriptionTierWhereInput,",
        "?   account?: AccountRelationFilter | AccountWhereInput",
        "  },",
        "  update: {",
        "    account_id: \"6570eb74a0a5f0d856f2db82\",",
        "    aws_id: undefined,",
        "    aws_instance_state: \"PENDING\",",
        "    subdomain: \"test\",",
        "    state: \"UNAVAILABLE\",",
        "    tier_id: \"6570ed90d09e2638e19eeeff\"",
        "  },",
        "  create: {",
        "    account_id: \"6570eb74a0a5f0d856f2db82\",",
        "    aws_id: undefined,",
        "    aws_instance_state: \"PENDING\",",
        "    subdomain: \"test\",",
        "    state: \"UNAVAILABLE\",",
        "    tier_id: \"6570ed90d09e2638e19eeeff\"",
        "  }",
        "}",
        "",
        "Unknown argument `account_id_subdomain_unique`. Available options are marked with ?.",
        "    at Zn (/var/task/node_modules/@prisma/client/runtime/library.js:117:5888)",
        "    at ni.handleRequestError (/var/task/node_modules/@prisma/client/runtime/library.js:124:6516)",
        "    at ni.handleAndLogRequestError (/var/task/node_modules/@prisma/client/runtime/library.js:124:6206)",
        "    at ni.request (/var/task/node_modules/@prisma/client/runtime/library.js:124:5926)",
        "    at async l (/var/task/node_modules/@prisma/client/runtime/library.js:129:10023)",
        "    at async InstanceRepository.upsertByAccountIdAndSubdomain (/var/task/index.js:181:5)",
        "    at async InstancesController.createOne (/var/task/index.js:486:23)",
        "    at async Runtime.main [as handler] (/var/task/index.js:712:23)"
    ]
}

Expected behavior

the same as #18883

Prisma information

no longer have it. It has been more than a month since I reported it here #18883

Environment & setup

• OS: macOS Sonoma
• Database: PostgreSQL and MongoDB

OBS: this bug actually happened when I was using mongodb. When I changed to postgres I no longer needed that compound key, so the issue was gone because I removed it. So I can't know for sure if the bug exists in postgres. Please, run a test using the sample model I shared

• Node.js version: 18

Prisma Version

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

[SevInf]

I don't think it's the same issue - #18883 talks about unique constraint violation error, where in your case, you getting a validation error, telling you that account_id_subdomain_unique compound field does not exist. It does not exist because index on (accountId, subdomain) pair is not unique in your model definition. You can fix it by replacing @@index attribute in your model definition with @@unique.