Case sensitivity

PostgreSQL provider

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.

Use the mode property on a filter as shown:

const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: 'prisma.io',
      mode: 'insensitive', // Default value: default
    },
  },
})

See also: Filtering (Case-insensitive filtering)

MySQL provider

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.

MongoDB provider

To support case-insensitive filtering, use the mode: 'insensitive' property on a per-field basis:

const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: 'prisma.io',
      mode: 'insensitive', // Default value: default
    },
  },
})

The MongoDB uses a RegEx rule for case-insensitive filtering.

SQLite provider

By default, SQLite itself only supports case-insensitive comparisons of ASCII characters. Therefore, Prisma Client does not offer support case-insensitive filtering with SQLite.

To enable limited support (ASCII only) for case-insensitive filtering on a per-column basis, use COLLATE NOCASE when you define table columns:

CREATE TABLE mytable (
    sample TEXT COLLATE NOCASE   /* collating sequence NOCASE */
);