Case sensitivity (Reference)

Note: Case-insensitive filtering is a stable feature as of version 2.8.0.

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:

Action	Case sensitive	Case insensitive
Sort ascending	`Apple`, `Banana`, `apple pie`, `banana pie`	`Apple`, `apple pie`, `Banana`, `banana pie`
Match `"apple"`	`apple`	`Apple`, `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 output

  +--------------------------+----------------------+
  | @@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 output

 +----+-----------------------------------+
 | id | email                             |
 +----+-----------------------------------+
 | 61 | alice@prisma.io                   |
 | 49 | birgitte@prisma.io                |
 +----+-----------------------------------+

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: "prisma.io",
      mode: "insensitive", // Default value: default  
    },
  },
});

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:

Create an expression index for Prisma Client queries that use equals or not
Use the pg_trgm module to create a trigram-based index for Prisma Client queries that use startsWith, endsWith, contains (maps toLIKE / ILIKE in PostgreSQL)

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}

Was this helpful?

Edit this page on GitHub

Case sensitivity

Database collation and case sensitivity

Options for case-insensitive filtering

PostgreSQL provider

Caveats

Performance

MySQL provider

Caveats

SQLite provider

Was this helpful?

Products

Resources

Community

Company

Newsletter

Find Us