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 columnandit's relationtoo! - Search by
any columnandit's relationtoo ! - 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
checkFilteringQueryfunction 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
Top comments (0)