Dynamic Multi Column and Relational Filtering With Prisma ORM

If you already use Prisma, then you already know all about the awesomeness, except maybe the migration or schema part when you are working in a big team.

But anyway, my blog today will be about how we can use Prisma Query to achieve dynamic filtering!

To give you a little bit more context, what I am talking about is building one function that can apply to all your sql table, with fully customizeable :

Aplly Multi-Filtering a table by any column and it's relation too!
Search by any column and it's relation too !
Ordering by any column

How is this possible?

Maybe some of you guys have already kinda guess it right, it's because, prisma use JS / TS most precious thing (Object) in order to execute any query, especially the findMany query.




const blogs = await prisma.blogs.findMany({
    where:{
       title: "A blog"
    },
    orderBy:{
        createdAt:'desc'
    }
})

See how the WHEREclause and ORDER BY is basically formed by an object?

That's what we'll experiment with as we go.
In this part 1, we'll go with the WHERE clause for data filtering first, other things will hopefully covered in next parts of this series ! :D

Enough Talk, Lets Get Started

Setting Up

First things first, let's set up our mini server with just some several dependencies

npm init -y

npm install express prisma @prisma/client

Don't forget to initialize the prisma folders too !

npx prisma init

Alright, now we basically have something like this in our project

node_modules
prisma
- schema.prisma

If you don't want to go the hassle of setting-it-up , you can clone the repository here

Database Config and Prisma Schema

If you're kinda new, prisma schema also contains the database url, which actually can be overrided later on the code when you initialize prisma client instance.

All you have to do is just fill in your Database Connection URI in the .env file (generated from npx prisma init) and choose your provider, i'll be using mysql for this tutorial. But I think there'll be no difference in postgres too !

For this tutorial, we'll keep it simple, we'll just do it with two entities

Blogs
User

and i'll just use enum for the blog category.

The schema will look like this :



// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

enum BlogCategory {
  BACKEND
  FRONTEND
  MOBILE
}

model Blogs {
  id        String       @id @default(uuid())
  title     String
  content   String
  category  BlogCategory
  writer    User         @relation(fields: [writerId], references: [id])
  writerId  String
  createdAt DateTime     @default(now())
  updatedAt DateTime     @updatedAt
}

model User {
  id       String  @id @default(uuid())
  username String  @unique
  fullName String
  blogs    Blogs[]
}

And we're done with schema, now we just have to migrate it with this command :

npx prisma migrate dev --name migrationName

Implementation

Folder Structure

Basically, i will just split the code into some parts

server.js (Our Express based webserver to do request and response)
services/blogService.js ( we'll do logics here)
services/buildFilterQuery.js (we'll apply the filtering before passing it to the query here)
prisma-instance.js (prisma instance will be stored here)

It'll look basically like this :

Initalizing Server and Prisma Instance

On this part, we'll create one route, and then inside it, we'll check whatever filters that is provided.

server.js




const express = require("express");
const { getAll } = require("./blogs/blogService");


const app = express();



const checkFilteringQuery = (req) => {
    const enabledFilters = {};

    if (req.query.searchKey) {
        enabledFilters.searchKey = req.query.searchKey;
    }

    if (req.query.searchValue) {
        enabledFilters.searchValue = req.query.searchValue;
    }

    if (req.query.filters) {
        // This needs to be parsed into object first, because we are retrieving this from a request query, which is basically just a string.
        enabledFilters.filters = JSON.parse(req.query.filters);
    }


    if (req.query.orderKey) {
        enabledFilters.orderKey = req.query.orderKey;
    }

    if (req.query.orderRule) {
        enabledFilters.orderRule = req.query.orderRule;
    }


    if (req.query.page) {
        enabledFilters.page = req.query.page;
    }

    if (req.query.rows) {
        enabledFilters.rows = req.query.rows;
    }

    return enabledFilters
}



app.get("/blogs", async (req, res) => {
    const enabledFilters = checkFilteringQuery(req);

    const result = await getAll(enabledFilters);


    return res.status(200).json({
        data: result
    })
})

app.listen(3000, () => {
    console.log("[INFO] READY!!!")
})

If you are a bit confused, i'll explain it shortly, basically in this server.js we already did :

Create a simple express app
Create one GET routes to retrieve blogs data
And we'll only use combination of filters that client / user choose to pass via request. We are doing this by simply checking it inside the checkFilteringQuery function and then pass it to the blogService's getAll function (will be explained later).

And next up, it's just a simple task, we'll create a prisma instance in a separate file (just to get close into real-world scenario here :p)

prisma-instance.js



const { PrismaClient } = require("@prisma/client")

const prisma = new PrismaClient();


module.exports = {
    prisma
}

Building The Query and Service

Okay, so now, this is where the magic happens , we'll try to manipulate prisma's findMany query, to be more flexible, matching our end-user requirements of dynamic filtering.

services/buildQuery.js



const buildFilterQuery = (enabledFilters) => {
    const usedFilter = {
        where: {
            AND: []
        }
    };


    if (enabledFilters.filters) {

        /*
                Filters would look like this : 

                {
                    "filter1":["a","b"],
                    "filter2":["a"]
                }
            */


        // First, we'll loop through the key inside enabledFilters . filters

        // IT'S IMPORTANT THAT key of these filter object, reflect to the column in our database !
        for (const key in enabledFilters.filters) {

            // We'll store the filter values (array of value that we want to be inside where clause, to make the code more readable)
            const filterValues = enabledFilters.filters[key];



            /* In this part we'll include relational filter as well, such as user.fullName or user.username
                It's because, prisma support this, the where clause will looks like : 

                where:{
                    relation:{
                        column : "value"
                    }
                }

            */
            if (key.includes(".")) {
                let [relation, column] = key.split(".");

                /*If values array length is just 1 , we'll just filter for exact match 

                 Pretty much like : 

                    prisma.blog.findMany({
                        where:{
                            relation:{
                                column: "value"
                            }
                        }
                    })

                */

                if (filterValues.length === 1) {
                    usedFilter.where.AND.push({
                        [`${relation}`]: {
                            [`${column}`]: filterValues,
                        },
                    });
                }

                /*But if values array length is more than 1 , we'll filter with `in` operator

                 Pretty much like : 

                    prisma.blog.findMany({
                        where:{
                            relation:{
                                column: {
                                    in : ["value1", "value2"]
                                }
                            }
                        }
                    })

                */
                else {
                    usedFilter.where.AND.push({
                        [`${relation}`]: {
                            [`${column}`]: { in: filterValues,
                            },
                        },
                    });
                }
            }
            /* This next part, is for filtering column that is available in our table 

                where:{
                    column:"value"
                }

            */
            else {
                if (filterValues.length === 1) {
                    usedFilter.where.AND.push({
                        [`${key}`]: filterValues[0]
                    })
                } else {
                    usedFilter.where.AND.push({
                        [`${key}`]: { in: filterValues,
                        },
                    });
                }
            }
        }
    }



    if (enabledFilters.searchKey && enabledFilters.searchValue) {
        /*
            Same logic as filter applied here, if the searchKey include ".", then it means we are searching based on relation
        */
        if (enabledFilters.searchKey.includes(".")) {
            let [relation, column] = enabledFilters.searchKey.split(".");
            usedFilter.where.AND.push({
                [`${relation}`]: {
                    [`${column}`]: {
                        contains: `${enabledFilters.searchValue}`,
                    },
                },
            });
        } else {
            usedFilter.where.AND.push({
                [`${enabledFilters.searchKey}`]: {
                    contains: `${enabledFilters.searchValue}`,
                },
            });
        }
    }



    return usedFilter;
}

Right now, the code may looks a bit crazy, but for now let's just try to understand the code based on comments that I already put there, and later on, during the testing part, I'll break it down about how the filter is applied in various cases.

Now we're done building the query, we'll call prisma's find many and from our blogService ,

services/blogService.js




const { prisma } = require("../prisma-instance");
const { buildFilterQuery } = require("./buildQuery");

const getAll = async(filters) => {

    const usedFilter = buildFilterQuery(filters);

    const blogs = await prisma.blogs.findMany({
        ...usedFilter,
        include: {
            writer: true
        }
    })

    return blogs
}


module.exports = {
    getAll
}

Tests and Explanation

Okay, i've promised you that the crazy looking code from some minutes ago, is going to be explained here as we test the functionality right

Data shown below is inserted by seeding, you can find it on repository I have listed for you back on setup section !

Test 1 : Filter by Blog's Category

Filtering by One Category

Okay, so what is happening here is:

We provide a request query of :

{"category":["BACKEND"]

which our buildFilterQuery function translates into :



where:{
    AND:[
       {category:"BACKEND"}
    ]
}

combine this with the full prisma query in blogService.js, it will actually looks like this :



prisma.blogs.findMany({
   where:{
    AND:[
       {category:"BACKEND"}
    ]
   },
   include:{
     writer:true
   }
})

What if we want to filter blog with category of BACKEND and FRONTEND?

Easy! we'll just pass this into the request query :

{"category":["BACKEND","FRONTEND"]

and it'll translate to this :



where:{
    AND:[
       {category: {in:["BACKEND","FRONTEND"]}
    ]
}

combine this with the full prisma query in blogService.js, it will actually looks like this :



prisma.blogs.findMany({
   where:{
    AND:[
        {category: {in:["BACKEND","FRONTEND"]}
    ]
   },
   include:{
     writer:true
   }
})

Notice that if we give multiple argument to it, it will need to use the 'in' operator.

Test 2 : Filtering by Category + Searching

The Request Will Look Like this :

And then, our buildFilterQuery function, will simply translate this into :



where:{
    AND:[
       {category: "BACKEND"},
       {title:{
           contains : "Part 2"
         }
       }
    ]
}

And the full query will be :



prisma.blogs.findMany({
   where:{
     AND:[
       {category: "BACKEND"},
       {title:{
           contains : "Part 2"
         }
       }
    ]
   },
   include:{
     writer:true
   }
})

Test 3 (Bonus) Filter by Category and Search By Writer's Name

Oh yes, we can do that !
If you look at filter query, for filtering and searching, we have if condition that will check if the filter key or the searchKey contains "." inside of it,

to use it we'll just simply do :

and our buildFilterQuery translate this into :



where:{
    AND:[
       {category: "BACKEND"},
       {
         writer:{
           fullName:{
              contains : "Writer 2"
           }
         }
       }
    ]
}

And the full query will be :



prisma.blogs.findMany({
  where:{
    AND:[
       {category: "BACKEND"},
       {
         writer:{
           fullName:{
              contains : "Writer 2"
           }
         }
       }
     ]
   },
   include:{
     writer:true
   }
})

So at last, we got an Object Relation Mapper that works really-well with Object Manipulaton Right !

With this, you can even have many more endpoints and will just have to apply this one filtering function for all different entities in your application.

Stay tuned for the next parts where we will go through more with things like ordering, paginating (with cursor and limit-offset).

Cheers !

Github Repository: here

Connect With Me :
LinkedIn

Blog