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
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 | 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 bymode
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
ornot
- Use the
pg_trgm
module to create a trigram-based index for Prisma Client queries that usestartsWith
,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 themode
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 supports case-insensitive comparisons of ASCII characters. 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.