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 |
Prisma Client respects your database collation. Options and recommendations for supporting case-insensitive filtering and sorting with Prisma Client depend on your database provider.
Note: Follow the progress of case-insensitive sorting on GitHub.
Database collation and case sensitivity
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.
SQLite provider
By default, SQLite itself only supports case-insensitive comparisons of ASCII characters. Therefore, Prisma Client does not offer support 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 */);