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:

Action	Case sensitive	Case insensitive
Sort ascending	Apple, Banana, apple pie, banana pie	Apple, apple pie, Banana, banana pie
Match "apple"	apple	Apple, apple

If you use a relational database connector, Prisma Client respects your database collation. Options and recommendations for supporting case-insensitive filtering and sorting with Prisma Client depend on your database provider.

If you use the MongoDB connector, Prisma Client uses RegEx rules to enable case-insensitive filtering. The connector does not use MongoDB collation.

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

Database collation and case sensitivity

info

In the context of Prisma Client, the following section refers to relational database connectors only.

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 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%"

Show CLI results

 +----+-----------------------------------+
 | 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
    },
  },
})

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:

• 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.

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, text fields created by Prisma Client in SQLite databases do not support case-insensitive filtering. In SQLite, only case-insensitive comparisons of ASCII characters are possible.

To enable limited support (ASCII only) for case-insensitive filtering on a per-column basis, you will need to add COLLATE NOCASE when you define a text column.

Adding case-insensitive filtering to a new column.

To add case-insensitive filtering to a new column, you will need to modify the migration file that is created by Prisma Client.

Taking the following Prisma Schema model:

model User {
  id    Int    @id
  email String
}

and using prisma migrate dev --create-only to create the following migration file:

-- CreateTable
CREATE TABLE "User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "email" TEXT NOT NULL
);

You would need to add COLLATE NOCASE to the email column in order to make case-insensitive filtering possible:

-- CreateTable
CREATE TABLE "User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    //highlight-next-line
    "email" TEXT NOT NULL COLLATE NOCASE
);

Adding case-insensitive filtering to an existing column.

Since columns cannot be updated in SQLite, COLLATE NOCASE can only be added to an existing column by creating a blank migration file and migrating data to a new table.

Taking the following Prisma Schema model:

model User {
  id    Int    @id
  email String
}

and using prisma migrate dev --create-only to create an empty migration file, you will need to rename the current User table and create a new User table with COLLATE NOCASE.

-- UpdateTable
ALTER TABLE "User" RENAME TO "User_old";

CREATE TABLE "User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "email" TEXT NOT NULL COLLATE NOCASE
);

INSERT INTO "User" (id, email)
SELECT id, email FROM "User_old";

DROP TABLE "User_old";

Microsoft SQL Server provider

Microsoft SQL Server uses case-insensitive collation by default. Therefore, filtering with Prisma Client and Microsoft SQL Server is case-insensitive by default.

mode: 'insensitive' property is not required and therefore not available in the generated Prisma Client API.