<TopBlock>

Case sensitivity affects **filtering** and **sorting** of data, and is determined by your [database collation](#database-collation-and-case-sensitivity). 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](/orm/prisma-client) respects your database collation. Options and recommendations for supporting **case-insensitive** filtering and sorting with Prisma Client depend on your [database provider](#options-for-case-insensitive-filtering).

If you use the MongoDB connector, [Prisma Client](/orm/prisma-client/queries) uses RegEx rules to enable case-insensitive filtering. The connector _does not_ use [MongoDB collation](https://www.mongodb.com/docs/manual/reference/collation/).

> **Note**: Follow the progress of [case-insensitive sorting on GitHub](https://github.com/prisma/prisma-client-js/issues/841).

</TopBlock>

## 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:

<CodeWithResult expanded={true}>

<cmd>

```sql no-lines
SELECT @@character_set_database, @@collation_database;
```

</cmd>

<cmdResult>

```no-lines no-copy
  +--------------------------+----------------------+
  | @@character_set_database | @@collation_database |
  +--------------------------+----------------------+
  | utf8mb4                  | utf8mb4_0900_ai_ci   |
  +--------------------------+----------------------+
```

</cmdResult>

</CodeWithResult>

The example collation, [`utf8mb4_0900_ai_ci`](https://dev.mysql.com/doc/refman/8.0/en/charset-collation-names.html), is:

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

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

<CodeWithResult expanded={true}>

<cmd>

```sql no-lines
SELECT id, email FROM User WHERE email LIKE "%prisMa%"
```

</cmd>

<cmdResult>

```no-lines no-copy
 +----+-----------------------------------+
 | id | email                             |
 +----+-----------------------------------+
 | 61 | alice@prisma.io                   |
 | 49 | birgitte@prisma.io                |
 +----+-----------------------------------+
```

</cmdResult>

</CodeWithResult>

The same query with Prisma Client:

```ts
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](https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC) 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:

```ts highlight=5;normal
const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: 'prisma.io',
      mode: 'insensitive', // Default value: default
    },
  },
})
```

See also: [Filtering (Case-insensitive filtering)](/orm/prisma-client/queries/filtering-and-sorting#case-insensitive-filtering)

#### Caveats

- You cannot use case-insensitive filtering with C collation
- [`citext`](https://www.postgresql.org/docs/12/citext.html) 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](https://www.postgresql.org/docs/current/indexes.html) to improve performance:

- [Create an expression index](https://www.postgresql.org/docs/current/indexes-expressional.html) for Prisma Client queries that use `equals` or `not`
- Use the `pg_trgm` module to [create a trigram-based index](https://www.postgresql.org/docs/12/pgtrgm.html#id-1.11.7.40.7) for Prisma Client queries that use `startsWith`, `endsWith`, `contains` (maps to`LIKE` / `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:

```ts highlight=5;normal
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](https://www.sqlite.org/faq.html#q18) 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:

```prisma
model User {
  id    Int    @id
  email String
}
```

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

```sql
-- 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:

```sql
-- 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:

```prisma
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`.

```sql
-- 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.

---

# Query optimization using Prisma Optimize