How can I query a string in a non case sensitive way?

Question

My question is as simple as that : I have a query and I am looking for a match on a string such as :

const test_name = 'ExAmPlE'
const database_resources = await prisma.market.findMany({
    where: {
        name: test_name
    }
})

I can use string.toLowerCase() but only on specific use cases

How can I get all of the rows where name can be anything such as Example, ExAMple or example but not any other key such as Exàmplé ?

Answer 1

Depends on your DB. Quoting from the docs:

PostgreSQL uses deterministic collation by default, which means that filtering is case-sensitive. To support case-insensitive filtering, use the mode: 'insensitive' property on a per-field basis:

const test_name = 'ExAmPlE'
const database_resources = await prisma.market.findMany({
    where: {
        name: {
            equals: test_name,
            mode: 'insensitive'
        }
    }
})

MySQL uses case-insensitive collation by default. Therefore, filtering with Prisma Client and MySQL is case-insensitive by default. mode: 'insensitive' property is not required and therefore not available in the generated Prisma Client API.

See docs for more info about other providers