Prisma query with mode 'insensitive' does not work

[mmxfxqr]

import { prisma } from '@/prisma/prisma-client';
import { NextRequest, NextResponse } from 'next/server';

export async function GET(req: NextRequest) {
  const query: string = req.nextUrl.searchParams.get('query') || '';

  try {
    console.log(`Search query: '${query}'`);

    // Запрос с Prisma
    const products = await prisma.product.findMany({
      where: {
        name: {
          contains: query,
          mode: 'insensitive',
        },
      },
    });

    return NextResponse.json(products);
  } catch (error) {
    console.error('Error executing query:', error);
    return NextResponse.json({ error: 'An error occurred' }, { status: 500 });
  }
}

In the database is a pizza with the name ‘Cheez’. If I try

http://localhost:3000/api/product/search?query=cheez

nothing is returned, just .

Updating prisma@client did not help.

It seems like the problem is related to case sensitivity. I created the PostgreSQL database on Vercel and performed SQL queries, but case-insensitive matching does not work. How can this be fixed?

[pawlean (Pauline P. Narvas)]

Hi, @mmxfxqr! Welcome to the Vercel Community.

Thanks for your patience!

By default, PostgreSQL is case-sensitive, which is why your query for ‘cheez’ is not returning the expected result for ‘Cheez’. The issue you’re encountering with case-insensitive matching in PostgreSQL using Prisma can be resolved by ensuring that the collation of the database or the specific column is set to a case-insensitive collation.

Some debugging steps:

1. You can alter the collation of the specific column to be case-insensitive. For example, you can set the collation to und-x-icu which is case-insensitive.

   ALTER TABLE products
   ALTER COLUMN name
   TYPE text
   COLLATE "und-x-icu";
   

2. Make sure that your Prisma schema reflects the correct collation. You can add a raw SQL migration to set the collation for the column.

3. After updating the schema and migration, regenerate the Prisma client to ensure it reflects the changes.

4. Make sure your query in the code remains the same, as Prisma will now handle the case-insensitive search correctly with the updated collation.

const products = await prisma.product.findMany({
  where: {
    name: {
      contains: query,
      mode: 'insensitive',
    },
  },
});

Let us know how you get on!