Writing a generic helper function for handling query parameters in node js(sequelize and mongoose)
Currently, I am working on an API using node js, express, and PostgresSQL, sequelize as the ORM. writing an API that will be consumed by other people needs to be written in a way that caters to everyone's needs.
I picked the tickets to work on for the week, the ticket description is to write an API endpoint that gets data from the database based on filters. The best way I could think of is to use query parameters attached in the endpoints URL e.g (https://api.work.com/resource?createdDate=1992&status=activated).
But then, I needed to write simple and less code to achieve this, I am one of the lazy programmers 😃, so I was looking for a way to write a function or something that can cater for any filters, regardless of the number of filters which will come in as a query parameter. After a few hours of thinking, I came up with a helper function.
Enough of the talks and let's get down to the real deal.
It is expected that you have prior knowledge of writing API with node js, and sequelize or mongoose as this article won't touch those parts.
This method is designed to work with sequelize and mongoose.
Getting/fetching data from the database is the most used query while working with a database.
we mostly fetch data using find method in mongoose and findAll in sequelize.
collection.find()
The above code will fetch all the data in the database collection or table, but sometimes we would not need all the data from a collection, sometimes we might want to search for something or get some set of data that falls under some certain conditions. A very good example is the endpoint mentioned above.
(https://api.work.com/resource?createdDate=1992&status=activated).
This URL makes a get request to the resource endpoint, there are other words attached to the URL we have createdDate and status, both are equal to 1992 and activated respectively.
This endpoint is expected to return all the data that their createdDate is 1992 and their status is activated. This can be achieved by writing some DB queries.
for mongoose with MongoDB, the query should look like this
collection.find({createdDate: req.query.createdDate, status: req.query.status});
for sequelize with Postgres, the query should look like this
model.findAll({ where: { createdDate: req.query.createdDate, status: req.query.status } });
I love my code to always look beautiful, so this can also be writing as
const { createdDate, status } = req.query;
collection.find({ createdDate: createdDate, status: status });
and
model.findAll({
where:
{
createdDate: createdDate,
status: status
}
});
Now that we have seen how to query for data using both mongoose and sequelize codes, let's move on.
The above code works fine and smooth, but it has a limitation. Did you guess the limitation🤔? if no, let's talk about it and I hope you will agree with me.
The limitation is that this endpoint and the code can only cater for the two query parameters, what if the user consuming the API needs to add another query parameter to it, something like this
(https://api.work.com/resource?createdDate=1992&status=activated&expired=false).
quickly take a look at the code snippet above, there isn't provision for expire.
Here comes the approach to fix the limitation as at the time of writing this article, it can also be improved. I will be glad to see the improved version of it.
we want to create provision for all the filters that can be done on our data in the database.
P.S This approach is still dependent on your data in the database.
The flow
- Gets all the possible query parameter from the URL
- check if a query is present in the URL
- if yes write the query code for the ORM you are working with
Assume that we want to get data by filtering using Founded, Type and region, based on the below table
There might be a scenario where the user consuming the API wants to get only the data based on the Founded, Type, and region.
This helper function can accept any filter coming in. let's follow the flow first we create a function called query
const { Op } = require("sequelize")
const query = (query) => {
let Query = {};
//get all possible filters from the URL
const { Type, dateRange, region } = query;
if (Type != null) Query.Type = Type
if (dateRange != null) Query.Founded = { [Op.gte]: dateRange }
if (region != null || region != undefined) Query.Region = region
return Query;
}
Note!!! The above function was written for the sequelize ORM
the query function receives an argument which is an object of the query parameters from the URL
checks if any of the filters are not null before writing the DB query
It is important to know how your schema or table is defined and how the fields are spelled because we would be sending the Query object into the find or findAll methods for mongoose and sequelize respectively
Now let's make use of the query function
//call the function
const dbQuery = query(req.query);
// it receives the arguement req.query from the URL
// lets inject the query function
model.findAll({ where: dbQuery });
// incase of mongoose we have
collection.find(dbQuery);
with this approach, any number of filter should work provided there is provision for it in the helper function.
All the magic is happening in the query function, that is where you define and write the query for all possible filter that can be done on your data.
This is how it works. write the query for all possible filters, then check if a filter is added, then append the filter to the Query object.
code at a glance
const { Op } = require("sequelize")
// function
const query = (query) => {
let Query = {};
//get all possible filters from the URL
const { Type, dateRange, region } = query;
if (Type != null) Query.Type = Type
if (dateRange != null) Query.Founded = { [Op.gte]: dateRange }
if (region != null || region != undefined) Query.Region = region
return Query;
}
//call the function
const dbQuery = query(req.query);
// it receives the arguement req.query from the URL
// lets inject the query function
model.findAll({ where: dbQuery });
// incase of mongoose we have
collection.find(dbQuery);
Thank you for reading. I am thinking of writing an NPM package for this. I should make it more generic
Useful resources
you can reach out to me on twitter in case you see a mistake I am open to correction.
twitter: https://twitter.com/fawas_ola
github: https://github.com/phawazzzy