Incorrect Upsert Query used

[dammy001]

Bug description

The Upsert query logged in Prisma is different from what Upsert actually does. It doesn't specify ON DUPLICATE KEY in query level.

Query without duplicate key

prisma:query BEGIN
prisma:query SELECT `auth`.`users`.`id` FROM `auth`.`users` WHERE (`auth`.`users`.`email` = ? AND 1=1)
prisma:query INSERT INTO `auth`.`users` (`id`,`first_name`,`last_name`,`email`,`email_verified`,`phone_no`,`role`,`verified`,`password`,`created_at`,`updated_at`) VALUES (?,?,?,?,?,?,?,?,?,?,?)
prisma:query SELECT `auth`.`users`.`id`, `auth`.`users`.`first_name`, `auth`.`users`.`last_name`, `auth`.`users`.`email`, `auth`.`users`.`email_verified`, `auth`.`users`.`phone_no`, `auth`.`users`.`role`, `auth`.`users`.`verified`, `auth`.`users`.`password`, `auth`.`users`.`created_at`, `auth`.`users`.`updated_at`, `auth`.`users`.`deleted_at` FROM `auth`.`users` WHERE `auth`.`users`.`id` = ? LIMIT ? OFFSET ?
prisma:query COMMIT

Query with duplicate key:

prisma:query BEGIN
prisma:query SELECT `auth`.`users`.`id` FROM `auth`.`users` WHERE (`auth`.`users`.`email` = ? AND 1=1)
prisma:query SELECT `auth`.`users`.`id` FROM `auth`.`users` WHERE (`auth`.`users`.`email` = ? AND 1=1)
prisma:query UPDATE `auth`.`users` SET `first_name` = ?, `last_name` = ?, `email` = ?, `phone_no` = ?, `email_verified` = ?, `verified` = ?, `password` = ?, `updated_at` = ? WHERE (`auth`.`users`.`id` IN (?) AND (`auth`.`users`.`email` = ? AND 1=1))
prisma:query SELECT `auth`.`users`.`id`, `auth`.`users`.`first_name`, `auth`.`users`.`last_name`, `auth`.`users`.`email`, `auth`.`users`.`email_verified`, `auth`.`users`.`phone_no`, `auth`.`users`.`role`, `auth`.`users`.`verified`, `auth`.`users`.`password`, `auth`.`users`.`created_at`, `auth`.`users`.`updated_at`, `auth`.`users`.`deleted_at` FROM `auth`.`users` WHERE `auth`.`users`.`id` = ? LIMIT ? OFFSET ?
prisma:query COMMIT

How to reproduce

1. Add the schema below in your prisma.schema
2. Create a seed.ts file and add the following seed content below
3. Run migration and seed
4. Check for query log in your console.

Expected behavior

Expected result should be just one query:

INSERT INTO `auth`.`users` (`id`, `first_name`,`last_name`,`email`,`email_verified`,`phone_no`,`role`,`verified`,`password`,`created_at`,`updated_at`) values (?,?,?,?,?,?,?,?,?,?,?) on duplicate key update  `first_name` = values(`first_name`), `last_name` = values(`last_name`), `email_verified` = values(`email_verified`), `phone_no` = values(`phone_no`), `role` = values(`role`), `verified` = values(`verified`), `password` = values(`password`);

Prisma information

// Add your schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch", "fullTextIndex", "views"]
}

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

model User {
  id            String    @id @default(cuid()) @db.Char(36)
  /// @zod.max(100, { message: "first name must be shorter than 100 characters" })
  firstName     String    @map("first_name") @db.VarChar(100)
  /// @zod.max(100, { message: "last name must be shorter than 100 characters" })
  lastName      String    @map("last_name") @db.VarChar(100)
  /// @zod.max(150, { message: "email must be shorter than 100 characters" })
  email         String    @unique @db.VarChar(150)
  emailVerified DateTime? @map("email_verified")
  phoneNo       String    @map("phone_no") @db.VarChar(50)
  role          ROLE      @default(USER)
  verified      Boolean  @default(false) @db.UnsignedTinyInt
  password      String

  createdAt DateTime  @default(now()) @map("created_at")
  updatedAt DateTime  @updatedAt @map("updated_at")
  deletedAt DateTime? @map("deleted_at")

  @@index([emailVerified])
  @@index([deletedAt])
  @@index([createdAt])
  @@index([verified])
  @@fulltext([firstName, lastName, email])
  @@map(name: "users")
}

enum ROLE {
  USER  @map("user")
  ADMIN @map("admin")
}

// Add your code using Prisma Client
// Create a seed file
import type { Prisma } from '@prisma/client';
import { PrismaClient, ROLE } from '@prisma/client';

const prismaOptions: Prisma.PrismaClientOptions = {};

if (process.env.NODE_ENV !== 'production')
  prismaOptions.log = ['query', 'error', 'warn'];

export const prisma = new PrismaClient(prismaOptions);

async function createUser(data: {
  firstName: string;
  lastName: string;
  email: string;
  phoneNo: string;
  role?: ROLE;
  password?: string;
}) {
  const userData = {
    ...data,
    emailVerified: new Date(),
    verified: true,
    password: 'password',
  };

  const user = await prisma.user.upsert({
    where: { email: userData.email },
    create: userData,
    update: userData,
  });

  console.log(
    `👤 Upserted successfully with email: "${data.email}" & password: "password".`,
  );

  return user;
}

async function main() {
  await createUser({
    firstName: 'John',
    lastName: 'Doe',
    email: 'johndoe@gmail.com',
    phoneNo: '+9911000000',
  });
}

main()
  .then(() => {})
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Environment & setup

• OS: macOS
• Database: MySQL
• Node version: 18.16.1

Prisma Version

5.0.0

[millsp]

Hey @dammy001, I have checked internally and confirmed that this is indeed something we can and should improve. Thanks for the report.

[dammy001]

Hi @millsp @janpio any update on this issue. The team needs community help for this?

[millsp]

Hey @dammy001 👋, unfortunately this issue has not been prioritized so I cannot give you any timeline. Community help is very welcome :)

[janpio]

(I updated the title to reflect that the issue is not about wrong logging, but more fundamentally about the query being used)

[laplab]

Thank you for reporting the issue.

We agree that native upserts provide a better experience for reasons described in the docs. There is a separate issue requesting just that for MySQL: #16876. We do not have a timeline, but it is on our radar. Currently, we advise that MySQL users consider workarounds described in the docs.

While we do plan to use native upserts, I am afraid that the exact approach you suggested is incorrect in the general case. create parameter may contain a different unique key compared to the one in where. This means that INSERT ... ON DUPLICATE KEY might succeed with inserting the row, even though a row matching condition from where is in the table.

For now, I am closing this issue as a duplicate of #16876.