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;
Show CLI output

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%"
Show CLI output

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.

Case-insensitive querying is a preview feature. Add the highlighted feature flag to enable it:

generator client {
provider = "prisma-client-js"
previewFeatures = ["insensitiveFilters"]
}

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)

Caveats

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

Performance

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.

Caveats

  • 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 */
);
export const _frontmatter = {"title":"Case sensitivity","metaTitle":"Case sensitivity (Reference)","metaDescription":"How Prisma Client handles case sensitivity when filtering and sorting.","preview":false}
Edit this page on Github