Hey @hirasaki1985 👋 ,

I reproduced this on my end as well (using MySQL) and it does seem unexpected. I'm going to convert this discussion into an issue where our engineering teams will pick it up for investigation.

If you are still down here without an answer, I used a combination from @Antoine's answer and another SO answer:

model Likes {
  id         String     @id @unique @default(uuid())
  user_id    String
  tag        String
  auth_user  AuthUser   @relation(references: [id], fields: [user_id], onDelete: Cascade)

  @@unique([user_id, tag], name: "user_id_tag")  // <-- this is the unique constraint
  @@index([user_id])
  @@map("likes")
}

Then I was able to upsert via the following:

prisma.likes.upsert({
    where: {
        user_id_tag: { // <-- And this bit is important
            user_id: user.userId,
            tag: tag.tag
        }
    },
    update: {},
    create: tag
})

In this case, you would need to use updateMany as you are using a non-unique field along with the unique one.

Sorry for this, I have changed and found another way to do this. If anyone was curious on how i changed it (for future use if anyone gets stuck and gets the same results like this)

    const prisma = new PrismaClient()
    const { username, email, hashPassword } = await request.json();
    let verified = crypto.randomBytes(32).toString('hex');

    const userExist = await prisma.users.findFirst({ where: { OR: [{ username }, { email }] } })
    if (userExist === null) {
        await prisma.users.create({ data: { username, email, hashPassword, verified } })
        return new NextResponse('Success!', { status: 201 })
    } else {
        return new NextResponse("Sorry, username and or email already exists!", { status: 500 })
    }

If I understand your question correctly, the request body may optionally contain an id or be undefined. If the id is present in the request, you want to update the corresponding record. If it isn't, you want to create a new record.

upsert functionality requires a unique identifier that can be used by the database to check whether there is a matching record. The database will then decide what to do. However with an undefined value, prisma cannot work.

You could use a key that will not exist in the database in case the value of id is undefined to make upsert insert the record in this case.

await prisma.profile.upsert({
  where: { id: id || '' },
  // ...
});

Or you could differentiate the cases and treat them appropriately:

if (id) {
  await prisma.profile.update({
    where: { id },
    data: {
      username,
      about,
      // ...
    },
  });
} else {
  await prisma.profile.create({
    data: {
      username,
      about,
      // ...
    },
  });
}

You might want to think about the case that there is an id given in the body, but it does exist in the database. Do you want the update to fail and reply with an error? Or do you want the database to silently insert? Then use upsert instead of update above.

When a combination of fields is unique, Prisma will generate a new type for the where condition which is basically just the name of all the relevant fields appended together with _.

Take a look at the categoriesWhereUniqueInput to see what the name is. Most likely it is called user_id_hash or hash_user_id.

This is what the query will look like, roughly speaking:

const category = await prisma.categories.upsert({
    where: {
        user_id_hash: {  // user_id_hash is the type generated by Prisma. Might be called something else though. 
            user_id: 1,
            hash: "foo"
        }
    },
    update: {
        name: "bar",
    },
    create: {
        hash: "foo",
        name: "bar",
        user_id: 1,
    },
})