upsert() fails on unique key when being used as "findOrCreate" with empty update field

[DRoet]

Bug description

Prisma does not seems to hand off upsert correctly when the update field is left empty. I instead get a Unique constraint failed on the fields (Username, password) database error.

How to reproduce

When using a compound index primary key like this:

@@id([username, password])

And doing an upsert like this:

// empty update to use upsert as findOrCreate
await this._prisma.user.upsert({
	update: {},
	create: {
		username,
		password,
	},
	where: {
		username_password: {
			username,
			password,
		},
	},
});

Expected behavior

Upsert is correctly acting as findOrCreate method for looking up the user.

Workaround:

We can workaround this issue by providing an arbitrary field inside the update, but ideally it should perform a DO NOTHING when the update field is empty. Took me a while to figure out this issue, which ultimately led me to #18883 (comment)

Prisma information

model user {
    username string
    password string
    // ....more fields
    
    @@id([username, password])
}

Environment & setup

• OS: Windows
• Database: PostgreSQL
• Node.js version: v18+

Prisma Version

v5.3.1

[jcyling]

Can reproduce this issue with the following code as well:

const queryData = await prisma.queryData.upsert({
    where: {
      domain_text_sources_units_regions_years: {
        domain: domain,
        regions,
        text,
        units,
        years,
        sources,
      },
    },
    create: {
      domain: domain,
      text: text,
      sources: sources,
      regions: regions,
      years: years,
      units: units,
    },
    update: {},
});

Logging the prisma query with and without an arbitrary field inside update, here are the generated SQL queries:

Without arbitrary field inside update

INSERT INTO "public"."query_data" ("id","domain","text","sources","units","regions","years","created_at") 
VALUES ($1,$2,$3,$4,$5,$6,$7,$8) 
RETURNING "public"."query_data"."id"

With arbitrary field inside update

INSERT INTO "public"."query_data" ("id","domain","text","sources","units","regions","years","created_at") 
VALUES ($1,$2,$3,$4,$5,$6,$7,$8) 
ON CONFLICT ("domain","text","sources","units","regions","years") 
DO UPDATE SET "text" = $9 
WHERE (("public"."query_data"."domain" = $10 AND "public"."query_data"."text" = $11 
AND "public"."query_data"."sources" = $12 
AND "public"."query_data"."units" = $13 
AND "public"."query_data"."regions" = $14 AND "public"."query_data"."years" = $15) 
AND 1=1) 
RETURNING "public"."query_data"."id", "public"."query_data"."domain", "public"."query_data"."text", "public"."query_data"."sources", "public"."query_data"."units", "public"."query_data"."regions", "public"."query_data"."years", "public"."query_data"."created_at"

Seems that in the first case one would expect a DO NOTHING or ON CONFLICT SET.

[GeeWee]

I can also reproduce this. A workaround is setting an arbitrary value to update, however in our postgres, this query is 10x slower than the query ON CONFLICT DO NOTHING would be.