Case sensitivity

Case sensitivity affects filtering and sorting of data, and is determined by your database collation. Sorting and filtering data yields different results depending on your settings:

ActionCase sensitiveCase insensitive
Sort ascendingApple, Banana, apple pie, banana pieApple, apple pie, Banana, banana pie
Match "apple"appleApple, apple

Prisma Client respects your database collation. Options and recommendations for supporting case-insensitive filtering and sorting with Prisma Client depend on your database provider.

Note: Follow the progress of case-insensitive sorting on GitHub.

Database collation and case sensitivity

Collation specifies how data is sorted and compared in a database, which includes casing. Collation is something you choose when you set up a database.

The following example demonstrates how to view the collation of a MySQL database:

SELECT @@character_set_database, @@collation_database;
Hide CLI results
| @@character_set_database | @@collation_database |
| utf8mb4 | utf8mb4_0900_ai_ci |

The example collation, utf8mb4_0900_ai_ci, is:

  • Accent-insensitive (ai)
  • Case-insensitive (ci).

This means that prisMa will match prisma, PRISMA, priSMA, and so on:

SELECT id, email FROM User WHERE email LIKE "%prisMa%"
Hide CLI results
| id | email |
| 61 | |
| 49 | |

The same query with Prisma Client:

const users = await prisma.user.findMany({
where: {
email: {
contains: 'prisMa',
select: {
id: true,
name: true,

Options for case-insensitive filtering

The recommended way to support case-insensitive filtering with Prisma Client depends on your underlying provider.

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: "",
mode: "insensitive", // Default value: default

See also: Filtering (Case-insensitive filtering)


  • You cannot use case-insensitive filtering with C collation
  • citext columns are always case-insensitive and are not affected by mode


If you rely heavily on case-insensitive filtering, consider creating indexes in the PostgreSQL database to improve performance:

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.


  • You must use a case-insensitive (_ci) collation in order to support case-insensitive filtering. Prisma Client does no support the mode filter property for the MySQL provider.

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 */
Edit this page on GitHub