Prisma query with mode 'insensitive' does not work

Mmxfxqr · 2024-09-14 20:33:40.524441

``` 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?

Hi, ! 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:

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";
```
Make sure that your Prisma schema reflects the correct collation. You can add a raw SQL migration to set the collation for the column.
After updating the schema and migration, regenerate the Prisma client to ensure it reflects the changes.
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!