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:

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;

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

 +----+-----------------------------------+
 | id | email                             |
 +----+-----------------------------------+
 | 61 | [email protected]                   |
 | 49 | [email protected]                |
 +----+-----------------------------------+

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,
    "email" TEXT NOT NULL COLLATE NOCASE -- [!code highlight]
);

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.