How to make unique key constraint made with multiple fields case insensitive in prisma?

Question

Can anyone tell me how to make this unique constraint case insensitive? I was looking something like

@@unique([name, size, color], options:{caseInsensitive: true})

I am using prisma clientVersion: 5.4.1

model SomeModel {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  name      String 
  size      String 
  color     String

  @@unique([name, size, color])
}

Or is there any way to check case insensitive during prisma update query like below?

await prisma.update.SomeModel({
  where: {
    name_size_color: {
      name: data.name ? {equals: data.name, mode: "insensitive"} : data.name,
      size: data.size ? {equals: data.size, mode: "insensitive"} : data.size,
      color: data.color ? {equals: data.color, mode: "insensitive"} : data.color
    } 
  },
  data: someData
});

When I tried the prisma update the following error occurred for each fields: Argument color: Invalid value provided. Expected String, provided Object. Full error below:

Invalid `prisma.productDetail.update()` invocation:

{
  where: {
    uuid: "3790181d-c4f8-4832-94bb-22b969eafcf6",
    name_size_color: {
      name: "Some Name",
      size: "",
      color: {
        contains: {
          value: "purple",
          mode: "insensitive"
        }
      },
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    }
  },
  update: {
    quantity: {
      increment: 3
    },
  },
}

Argument `color`: Invalid value provided. Expected String, provided Object.

Note: In my case, value of name, size or color can be null or empty string or a string.

Answer 1

Prisma does not support defining a case-insensitive unique constraint directly within the schema as of version 5.4.1, although, you can handle case insensitivity in your application by using the contains filter along with a custom SQL function to perform the operation.

const result = await prisma.someModel.update({
  where: {
    name_size_color: {
      name: { contains: { value: data.name, mode: 'insensitive' } },
      size: { contains: { value: data.size, mode: 'insensitive' } },
      color: { contains: { value: data.color, mode: 'insensitive' } },
    },
  },
  data: someData,
});

}

this might not be as efficient as a true case-insensitive unique constraint in the database, but it provides a workaround until Prisma introduces built-in support for case-insensitive constraints.