Filtering with Prisma and GraphQL
3 min read

Filtering with Prisma and GraphQL

Filtering with Prisma and GraphQL

Since it's New Year's day, I decided to start it off productively. In this post you'll learn my approach to filtering through tables using Prisma and Apollo Server. It shows how simple prisma is, and how little code is required to represent some pretty lengthy sql statements under the hood.

Prelude

I'm rebuilding an application that has a listing page with a lot of complicated filter requirements.

For instance, there's a users table. Users have cases. Cases have types. The UI requires filtering by the case type on the user list. So, we've got

users
cases
types	

Three different tables, all we want is users who have at least one case with a certain type to show up in our listing page.

This is an annoying sql query if you're manually writing it. You have to grab users and join the cases, and join where at least one of the cases has the matching case type. I'm new to Postgres, and used MySQL for years before that, but even still, I can't write that query without a few quick googles. Writing SQL is a pain and doing this sort of stuff is pretty typical in many apps, this is why Prisma is awesome!

Getting Started

This tutorial assumes you've already ran through the Prisma Quickstart. If this is your first time, that's okay. Let's take a look at how easy it is to implement the query I described above.

Prisma schema

Here's an example schema that you would need for the code below to work:

model User {
  id         Int       @id @default(autoincrement())
  name       String
  cases      Case[]
}

model Case {
  id   Int        @id @default(autoincrement())
  type CaseType?  @relation(fields: [caseTypeId], references: [id])
  caseTypeId  Int?
}
  
model CaseType {
  id    Int    @id @default(autoincrement())
  title String
  Case  Case[]
}

Now for the code:

 db.user.findMany({
     where: {
      cases: {
        some: {
          type: { id: caseTypeId },
        },
      },
     },
 })

That's really all the code we need! Kind of awesome to think about the scenario I wrote above, and how minimal the code is. We want users where some of their cases have the matching type. If we needed to show users that had every or none of their cases with the matching type, we can swap out the word. Prisma supports these 3 different keywords, it's so readable.

Let's dive in a little deeper.

GraphQL Setup

How would we break this up a bit, and support filtering as a GraphQL resolver? Here's how I broke it up:

let andWhere: Prisma.UserWhereInput[] = []

// more code will be placed here

db.user.findMany({
    where: {
        AND: andWhere,
    },
})

I started out by using the AND keyword in prisma. This does AND WHERE if we pass multiple filters to our query.

To keep the code type safe, I need to pass the type to this array. This will ensure anything we push into it has the correct TS definition.

Now we can add a few filters. Let's say nameContains and caseTypeId for now:

if (nameContains) {
    andWhere.push(
      { name: { contains: nameContains, mode: 'insensitive' } }
    )
}
if (caseTypeId) {
    andWhere.push({
      cases: {
        some: {
          type: { id: caseTypeId },
        },
      },
    })
  }

This will conditionally add where clauses if the variables are set. In the nameContains portion, I set the mode to insensitive. This will ignore the casing of letters when searching through Postgres.

When passing in a caseTypeId and nameContains, it will find users where both of those clauses match (since we are using AND). In otherwords, the filtering keeps getting more specific.

Now we can see the full resolver code for reference:

export const users = async (_, { input }) => {
  let { nameContains, caseTypeId } = input;
  let andWhere: Prisma.UserWhereInput[] = [];

  if (nameContains) {
    andWhere.push(
      { name: { contains: nameContains, mode: "insensitive" } }
    );
  }

  if (caseTypeId) {
    andWhere.push({
      cases: {
        some: {
          type: { id: caseTypeId },
        },
      },
    });
  }

  return db.patient.findMany({
    where: {
      AND: andWhere,
    }
  });
};

This resolver would have a type definition like this:

 input UsersInput {
    nameContains: String
    caseTypeId: Int
  }

users(input: UsersInput!): UsersResponse!

Conclusion

The amount of code we just wrote was so minimal... In the past, I used other ORMs, and there was so much more setup involved. Prisma's approach to a quick schema change, and auto code generation is awesome.

In this post, we added relationships to our prisma schema, and with a simple object syntax, filtered by those relationships. If we make an array, we can add multiple filters, and conditionally apply them. In my app, I'm using GraphQL and pass in those optional filters to my resolvers.

Even if you aren't using GraphQL, you can take advantage of Prisma and the approach I outlined in this post.

If you're interested in using Prisma, but found this post a bit confusing, I would suggest reading up on Prisma and trying it in your own apps. For a better understanding of GraphQL, the code written in this post was using Apollo Server.

Enjoying these posts? Subscribe for more