Search code examples
prismaprisma2

Prisma Queries with conditions

I'm currently building a new project with Prisma and I'm thinking about how to build my SQL queries when I have different dependencies.

Suppose I want to add an include or a where condition to the query under a certain condition. Should I now use a new method for this? Like a second API Get Path /api/users/with-include respectively /api/users/condition1

Or should I extend the existing method with an if query as suggested here?

Prisma 2 query to return records only that are associated with ALL of the provided tag IDs

And use a query parameter /api/users?with-include=true respectively /api/users?condition1=true

What is the best practice here?

Solution

  • Since you can have many conditions, does that mean you will make many endpoints for different conditions? At first, you might be tempted to but that becomes inflexible.

    For a given endpoint, you can query data based on filters like where, include, limit, order, etc. in query parameters for GET (or body if using POST).

    REST

    /api/users?filter[where][gender]=male&filter[take]=10

    Or stringified JSON format:

    /api/users?filter={"where":{"gender":"male"},"take":10}

    With the above example, you can make flexible queries and you can directly use this filter query object into your Prisma query, assuming you have crafted it in that way (the above example is).

    But I'd advise against using it directly since anyone can manipulate the query parameter values. Say, someone changed the value of take from 10 to 200. You wouldn't want that. So in that case, you need to place limits by performing a check:

    const filter = getQueryObjectFromURL();

if( filter.where.take > 100 ) {
  filter.where.take = 100;
}

    Finally, you can use filter in Prisma like:

    await prisma.users.findMany(filter);

    But if conditions are less and you don't want this level of filter, then you can make a custom query based on the condition.

    api/users?gender=male

    const { gender } = getQueryObjectFromURL();

const filter = {
   where: {}
};


if(gender){
  filter.where.gender = gender; // You might want to check for values before doing this because gender=skdjshdf can be passed
}

    You can pass any number of query parameters, but you only check for the presence of those which are required for your case. If it's there and is valid, add it to your filter or else ignore it.

    If there are many conditions (condition2, condition3, ...), you know what it is going to be used for, so it can then be accordingly used for, say, preparing AND or OR or customize anyhow you want based on your needs.