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 .
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;
+--------------------------+----------------------+| @@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%"
+----+-----------------------------------+| 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
ornot
- Use the
pg_trgm
module to 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 . 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.