Use @bitovi/querystring-parser With Your Favorite ORM

The @bitovi/querystring-parser library helps to parse query parameters into a moderately complicated database query. It could help the client query the database directly via query parameters.

Have you ever considered using some of the benefits of Graphql using REST API, such as reducing over or under-fetching of data (i.e., the client requesting exactly the data it needs)? The @bitovi/querystring-parser makes that plausible for your team. All database queries and relations can be done directly from the client side using query params.

The @bitovi/querystring-parser parses your query params into a syntax that is understandable by the database following the JSON:API specifications. The package has also been built with ORMs integration in mind, so you can directly convert query parameters into a syntax that is understandable by your ORM of choice.

Note: Querystring parsers are the bridge between your front and backend. Bitovi has experience using and creating high-performance parsers. If you need help with your application's connectivity, reach out to us for Backend Engineering Consulting.

Benefits of @bitovi/querystring-parser

• Rapid development
• Type coercion
• Minimal hassle
• Clients can pick and choose exactly what is needed
• Don't have to bike-shed over querystring formats
• Easy to use with your ORM of choice

Installation

Install the package from npm:

npm install @bitovi/querystring-parser --save

Basic API Usage

Here's a quick start guide with just two steps:

1. Pass the raw querystrings from incoming HTTP requests into the parser:

const qsParser = require("@bitovi/querystring-parser");

// Various web frameworks (such as express or fastify) have their own methods for accessing querystrings from incoming requests.
// For demonstration purposes we'll use this hardcoded example.
const querystring = "page[number]=0&page[size]=10";

const { page } = qsParser.parse(querystring);
console.log(page.number); // --> 0
console.log(page.size); // --> 10

This is a basic example, but now you have the pagination parameters parsed from the query string. And you can use them to craft your database query. Maybe you'll use an ORM to help you with that.

We want it to be easier to make our APIs more expressive, so we follow the JSON:API specification for querystring parameters as much as possible. There are other ways to express pagination but we'll follow these conventions.

Let's take a look at a similar example for sort parameters.

Sorting

Sorting is relatively straightforward, especially when we can just follow the JSON:API specification for the querystring format.

const qsParser = require("@bitovi/querystring-parser");

const { sort } = qsParser.parse("sort=-date,name");
console.log(sort[0]); // --> { field: 'date', direction: 'DESC' }
console.log(sort[1]); // --> { field: 'name', direction: 'ASC' }

The goal here is to parse the raw string into structured data that can be integrated into a database query more quickly.

Notice how we got the sort variable by destructuring what the parser returned? You don't have to do it like this, but it shows how simple it is to pick and choose which query parameters you want to deal with. You can use the sort-related parsing and do your own parsing for pagination if that's what you prefer.

Alright—enough of the basics. Let's take a look at some filtering examples!

Filtering

Without filtering, APIs would be pretty boring, right? Clients might only be able to request a specific record, or a list of EVERY record and have to do the filtering for their usecase on their own. An api with expressive filtering allows clients to leverage more of the awesome power of the database.

What does filtering look like here? Let's take a look at a few examples:

const querystringParser = require("@bitovi/querystring-parser");

const { filter } = querystringParser.parse("filter=equals(name,'michael')");
console.log(filter) // ---> { '=': [ '#name', 'michael' ] }

First, let's take a look at the querystring filter format. This format is based on the jsonapi.net filter specification. This example is pretty straightforward. You can probably formulate the intended SQL clause right in your head: WHERE name = 'michael'.

But what the heck is that output? Well, it too, is a representation of the intended SQL clause, but in object notation. This makes it a lot easier to use with an ORM or query builder. The format was inspired by JsonLogic. We can see the equals operator (=) associated with 2 operands: '#name' and 'michael'.

Let's take a look at another example:

const querystringParser = require("@bitovi/querystring-parser");

const { filter } = querystringParser.parse("filter=any(age,'20','21','22')");
console.log(filter) // ---> { IN: [ '#age', 20, 21, 22 ] }

Notice how this operator supports an arbitrary number of operands? Neat, right?

Again, you can probably intuit the intended SQL clause here: WHERE age IN (20, 21, 22)

Let's look at one more example that really shows off the power of these filters using higher-order operators:

const querystringParser = require("@bitovi/querystring-parser");

const { filter } = querystringParser.parse("filter=and(contains(name,'mi'),greaterThan(age,'25'))");
console.log(filter) // ---> see below

// console.log(filter) JSON equivilent
{
  "AND": [
    {
      "LIKE": [
        "#name",
        "%mi%"
      ]
    },
    {
      ">": [
        "#age",
        25
      ]
    }
  ]
}

This parser supports 2 separate filter styles.

The parsed output uses SQL operator literals to make it as database-agnostic as possible.

Integrating With ORM

The Querystring-parser can be integrated with any ORM of choice, it is modeled in a way that users of the library can query their database using query params. The users of the library may need to write a few functions to get it to work to taste.

The Querystring-parser library has been made to work with two top ORMS for the Node.js library: Objection.js and Sequelize. You can read more about them here.

Integration With Objection.js

The @bitovi/objection-querystring-parser was built with Objection in mind, it makes it easy to automatically query your tables using query params. This library helps parse query parameters into their equivalent Objection functions.

It sounds exciting, right? Let's jump into how it's done.

Basic API Usage

The library uses the main @bitovi/querystring-parser as a dependency and does not need to be installed differently. This library can be installed by running the command below.

npm i @bitovi/objection-querystring-parser

This library takes query params based on the JSON:API and returns an array of functions and parameters that Objection.js understands.

You only need to chain the functions to your model and you can filter your query, do joins on your model, select fields you want…

The data result is an array of objects with "fx ", "isNested ", and "parameters " as keys.

{
  data: {
    fx: "where",
    isNested: false,
    parameters: ['number', '>=', 100]
  }
}

• fx is a string for the function that is to be chained to the model, it could be the "where ", "select ", "orderBy ", and so on.

• The isNested is a boolean that determines if the parameters of function fx should be wrapped in a function. The isNested is only true when the 'AND', 'OR', and the 'NOT' filters are used. A typical nested objection query looks like this.

  Model.query().where(function() {
    this.whereNull('name');
    this.orWhereNot('id',7)
  }).

• The parameters could differ based on the isNested field.

  • The parameters are an array that is to be directly spread in your function fx when the isNested field is false. It follows the format Model.query().fx1(...parameters).fx2(…parameters).e.g When fx is ‘whereIn', isNested is false, and parameters is [’name', ‘=', 'Roy’].

    Model.query().whereIn('name', '=' ,'Roy')

  • When isNested is true, the parameters are an array of objects with the 'fx', 'isNested' and 'parameters'.

Let's take a look at some examples:

const querystringParser = require("@bitovi/objection-querystring-parser");

const result = querystringParser.parse("filter=any(age,'10','20')&fields[people]=id,name,age&page[number]=0&page[size]=10");
console.log(result);

{
  orm: "objection",
  data: [
    [
      {
        fx: "select",
        isNested: false,
        parameters: ["id","name"],
      },
      {
          fx: "whereIn",
          isNested: false,
          parameters: ["age", [10, 20]],
      },
      {
        fx: "offset",
        isNested: false,
        parameters: [0],
      },
      {
        fx: "limit",
        isNested: false,
        parameters: [10],
      },
    ],
  ],
  errors: [],
};

Chaining the above data using the format Model.query().fx1(...parameters1).fx2(…parameters2), you will end up having

Model.query().select('id', 'name').whereIn('age', [10,20]).offset(0).limit(10)

The example above shows a typical response where there is no Nested query i.e isNested is false. For more context on nested queries, please check out the README here.

A typical example of how to integrate these in your backend application can be found here.

Integration With Sequelize

The @bitovi/sequelize-querystring-parser was built with Sequelize in mind, it makes it easy to automatically query your tables using query params.

Basic API Usage

The library uses the main @bitovi/querystring-parser as a dependency and does not need to be installed differently. This library can be installed by running the command below.

npm i @bitovi/sequelize-querystring-parser

This library takes query params based on the JSON:API and returns an object that can be directly passed into the Sequelize Model. It is really easy to use as you will see below, you only need to pass your query params into the library.

The result from the library comes in the format

{
  orm: "sequelize",
  data: {
    offset: 0,
    limit: 10
 },
  errors: []
};

The data field is an object of keys that can be directly passed into the Sequelize findAll. The above result will take become

Model.findAll({offset: 0, limit: 10})

Let's take a look at some more examples:

const queryStringParser = require("@bitovi/sequelize-querystring-parser");
const result = queryStringParser.parse("filter=and(any('#age','10','20'),
                                        equals('#name','mike')),
                                        fields[table]='id','age','name'")

console.log(result)

{
  orm: "sequelize",
  data: {
    where: {
      [Symbol(and)] : {
        [Symbol(any)]: {
          age: [10, 20]
        },
        [Symbol(eq)]: {
          name: 'mike'
        }
      }
    },
    attributes: ["id", "age", "name"]
 },
  errors: []
};

You can find an example of how to integrate it into your project here.

The blog post does not contain every possible use case and query params that these libraries parse, please check out the README for more details.

Conclusion

Install the @bitovi/querystring-parser library to help parse query parameters into a moderately complicated database query. The @bitovi/querystring-parser can also be used with your ORM of choice but was built to specifically work well with two top ORMS for the Node.js library: Objection.js and Sequelize.

Need more help?

Bitovi has expert backend consultants ready to dive in and assist you with your project! Schedule a free consultation to get started.