How to handle case sensitivity in a Prisma query?

prisma

#1

I have a User.email field that contains the value Bob@bob.com

I would like the user query to match bob@bob.com but querying on this value returns no results and I can’t figure out where I should do this or if it is possible with Prisma. Is this a TypeScript or Prisma issue?

Also, could someone point me to resources where I could learn about how to use the query command? eg: ctx.db.query.user({ where: { email } } )

# datamodel.graphql

type User {
  id: ID! @unique
  email: String! @unique
  password: String!
  name: String!
  posts: [Post!]!
  products: [Product!]!
}
# schema.graphql

type User {
  id: ID!
  email: String!
  name: String!
  posts: [Post!]!
}
// resolvers>Mutation>auth.ts
...
async login(parent, { email, password }, ctx: Context, info) {
    email = email.toLowerCase();

    const user = await ctx.db.query.user({ where: { email } })
...

#2

I suggest making sure in your mutation resolvers that all emails you enter to Prisma lower cased.
Then you can also filter for the lower cased email in your query.

Do you have more specific questions?

By the way, I improved the formatting of your post, so it’s easier to read :slight_smile:


#3

Hello, Nilan!
What if I have user.firstName field? When signing up user can type it in w/e case. But when he searches I want to match any users regardless of case. Any tips how to achieve it?
I found only this stale issue: https://github.com/prismagraphql/graphcool-framework/issues/223


#4

Hey,

I think you will have a lot of troubles if you use the filter as a “search”:

  • a letter won’t return the letter with accent : e -/> éèê …
  • multiple keywords search
  • if you miss a letter : Bran won’t return Brian

I think the only solution is to use an indexer ( elasticsearch, algolia, …), am I right @nilan ?


#5

Yeah, I figured that would be the end goal. But as I work on a prototype, it would be nice to have a temporary solution. I think the ability to perform simple case insensitive searches would fit with Prisma API very well.


#6

I don’t think that that’s the only solution, but that sure seems like the best one long term :slight_smile:


#7

I have the exact same problem with a name field.

I don’t need, at least for now, a complicated indexer with accent handling or missing letter handling …

I just need that if a user types ‘john’ I get list all the ‘John’ in the system …

Possibility to have a case insentivity in where clause would be really helpful in my case.

For the moment, the only workaround I can think of is to change my datamodel to have 2 fields name and name_lower, and to maintain both, only to be able to filter on name_lower:frowning:

I hope there is a cleaner solution coming.