Case insensitive search with Prisma using indexes

Case insensitive search with Prisma using indexes

Prisma has an issue

Right here. Click that link and give it a thumbs up.

What's the issue?

Case insensitive search. I spent the hour I set aside each day for programming happily coding up a front-end for my web app. I'm loving React and Next and Prisma, and then... I start to write up a search component. This is fine, we covered that in Wes Bos's Advanced React course and it was pretty straight-forward.

That one hour turned into two. Then three. Three hours later I'm finally able to search for the ISBN of 'A Game of Thrones' by searching for 'game'.

The reason it took me so long to get a working prototype of a search function is that GraphQL and therefore Prisma does not natively support queries that are case insensitive. All of the other query languages I can think of support this type of search, so I waltzed into the GraphQL party thinking I could search without making sure I'm capitalizing everything correctly.

It turns out, you actually can!

The work-around

Everyone loves a good work-around. The work-around here comes in the flavor of a search index.

Indexes are a well-known method of reducing overhead when querying and making queries more performant, and in this case it can even add a much-needed feature!

All you need to do is store the data you want to search in an index, lowercased.

In my case, I have a Book type with author, title, isbn, description, etc. The user should be able to search for part of an author or book's name and pull up the book they want. Like I mentioned above, a user may search for the book 'A Game of Thrones Iby typing in 'game' or 'GAME OF THRONES' or 'George R. R. Martin'. Naively implementing a search with a resolver looking for titlecontains or authorcontains would only match for 'George R. R. Martin'.

The closest solution I could find through Google searching is to store all of my data in lowercase, but I want the data on my website to look nice.

The solution I came up with is to create a BookIndex type. This has the book's ISBN, title, and author, with title and author in lowercase. When a user searches for a keyword, I can apply .toLowerCase() and search for the ISBN there, then route the user to the book's page via the ISBN. Then, on the book page, a query uses the ISBN to get all of the correctly formatted data.

In practice, this looks like the following:

Search component:

const SEARCH_KEYWORDS_QUERY = gql`
  query SEARCH_KEYWORDS_QUERY($kw: String!) {
    bookSearch(kw: $kw) {
      isbn10
      name
    }
  }
`

datamodel.prisma

type Book {
  id: ID! @unique
  isbn10: String! @unique
  isbn13: String
  name: String!
  author: String
  image: String
  description: String
  publishDate: DateTime
  pageCount: Int
  related: [String]
}
type BookIndex {
  id: ID! @unique
  isbn10: String! @unique
  name: String!
  author: String
}

Query.js (resolver)

const Query = {
  bookSearch(parent, args, ctx, info) {
    const { kw } = args;
    return ctx.db.query.bookIndexes(
      {
        where: {
          OR: [
            { name_contains: kw.toLowerCase() },
            { author_contains: kw.toLowerCase() }
          ]
        }
      },
      info
    );
  }
}

schema.graphql

type Query {
    bookSearch(kw: String!): [BookIndex]
}

Hope this helps!

Photo by Anthony Martino on Unsplash

Bridger Putnam

Bridger Putnam

Full Stack Developer

January 16th, 2019