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:

ActionCase sensitiveCase insensitive
Sort ascendingApple, Banana, apple pie, banana pieApple, apple pie, Banana, banana pie
Match "apple"appleApple, 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 .

Note: Follow the progress of .

Database collation and case sensitivity

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;
Hide CLI results
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_0900_ai_ci |
+--------------------------+----------------------+

The example collation, , 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 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 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
  • columns are always case-insensitive and are not affected by mode

Performance

If you rely heavily on case-insensitive filtering, consider to improve performance:

  • for Prisma Client queries that use equals or not
  • Use the pg_trgm module to 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, SQLite itself only . Therefore, Prisma Client does not offer support for 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 */
);

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.