QueryQL: Easily Add Filtering, Sorting, and Pagination to Your Node.js REST API
Tags: javascript, node, api
Published: 2019-12-13 17:04:58

---
title: QueryQL: Easily Add Filtering, Sorting, and Pagination to Your Node.js REST API
published: true
tags: javascript, node, api
canonical_url: https://medium.com/truepicinc/queryql-easily-add-filtering-sorting-and-pagination-to-your-node-js-rest-api-9222135c93ae
---

When you first start building an API, you probably hard-code in a number of things like sorting and pagination. Maybe something like this [Express](https://expressjs.com/) route looks familiar to you (it’s using [Knex](https://knexjs.org/) to query the database):

```javascript
app.get('/images', async (req, res, next) => {
  const images = await knex('images')
    .orderBy('created_at', 'desc')
    .limit(20)

  res.send({ images })
})
```

Once you get a little further along, you start adding UI to control sorting and pagination — and maybe something like a “Status” filter for convenience. Those previously-hard-coded-values now have to be accepted via the query string, and your code begins to grow:

```javascript
app.get('/images', async (req, res, next) => {
  const orderByColumn = req.query.order_by_column || 'created_at'
  const orderByDirection = req.query.order_by_direction || 'desc'
  const page = req.query.page || 1
  const limit = req.query.limit || 20
  const where = {}

  if (req.query.status) {
    where.status = req.query.status
  }

  const images = await knex('images')
    .where(where)
    .orderBy(orderByColumn, orderByDirection)
    .limit(limit)
    .offset((page - 1) * limit)

  res.send({ images })
})
```

This isn’t unwieldy yet, but it’s also missing any sort of validation of the query string values. What if a non-existent `order_by_column` is passed in? Or a `limit` of `foobar`? The query blows up.

Perhaps even worse, what if a `limit` of `1000000` (one million) locks up your database? Or a `status` is specified that shouldn’t be allowed? None of these are necessarily difficult to solve on their own, but it does become a significant chore to manage across an entire API. Routes become bloated with boilerplate, consistency in naming and defaults breaks down, and it becomes a slog to add new endpoints.

Now, if you’re an enterprising developer, maybe you extract some of this into middleware, libraries, or service objects that are easier to share and maintain. That’s a great start. But do you really want to spend time on something so far removed from the actual purpose of your API?

## Introducing QueryQL

[QueryQL](https://github.com/TRUEPIC/queryql) solves all of this.

Instead of the fragile, bloated route we ended up with above, QueryQL allows you to define filtering, sorting, and pagination in a separate _querier_ class:

```javascript
const QueryQL = require('@truepic/queryql')

class ImageQuerier extends QueryQL {
  defineSchema(schema) {
    schema.filter('status', '=')
    schema.sort('name')
    schema.sort('created_at')
    schema.page()
  }

  defineValidation(schema) {
    return {
      'filter:status[=]': schema.string().valid('open', 'closed'),
      'page:size': schema.number().max(100),
    }
  }

  get defaultSort() {
    return {
      created_at: 'desc',
    }
  }
}
```

Our route can then use this querier class to significantly clean itself up:

```javascript
app.get('/images', async (req, res, next) => {
  const querier = new ImageQuerier(req.query, knex('images'))

  const images = await querier.run()

  res.send({ images })
})
```

## How It Works

Let’s talk about what’s happening here, starting with the use of the querier class in our route:

```javascript
const querier = new ImageQuerier(req.query, knex('images'))
```

The first parameter, `req.query`, is the parsed query string. We pull it from Express’ `req` here, but it can come from anywhere. QueryQL isn’t dependent on any specific Node.js web framework. It works just as well with Express as it does with [Koa](https://koajs.com/) or [hapi](https://hapi.dev/).

The specific format of the query string is important, however. (The “QL” in QueryQL stands for “query language”, after all.) Here’s a simple example of how it might look for our route:

```
/images?filter[status]=open&sort=name&page[size]=10
```

And here it is parsed:

```
{
  filter: {
    status: 'open',
  },
  sort: 'name',
  page: {
    size: 10,
  },
}
```

There’s a lot to unpack about the specific format of the query string, but I’ll leave that to the [documentation](https://github.com/TRUEPIC/queryql/blob/development/DOCS.md) to enumerate for now.

The second parameter, `knex('images')`, is the start of a Knex query that QueryQL will apply the requested filtering, sorting, and pagination to. Knex is supported out-of-the-box (and ORMs built on top of it like [Objection.js](https://vincit.github.io/objection.js)), but adapters can be written for any query builder / ORM without too much effort. (It doesn’t even have to be SQL-based.)

Let’s turn now to the querier class itself.

The only required function of a querier class is `defineSchema`, which is where we whitelist what’s allowed:

```javascript
defineSchema(schema) {
  schema.filter('status', '=')
  schema.sort('name')
  schema.sort('created_at')
  schema.page()
}
```

Here, we’re whitelisting

- a `status` filter with the `=` operator (lots of other operators are also supported),
- sorting on `name` and `created_at`,
- and enabling pagination.

This alone solves most of our problems. What if a non-existent `sort` field is passed in? Or a `page:size` of `foobar`? QueryQL catches these — and any other malformed or nefarious requests — and returns an elegant, precise error message long before it has a chance to blow up your database query.

While this provides a good starting line of defense, you likely still want to enforce validation specific to your API. That’s where `defineValidation` comes in:


```javascript
defineValidation(schema) {
  return {
    'filter:status[=]': schema.string().valid('open', 'closed'),
    'page:size': schema.number().max(100),
  }
}
```

Here, we’re simply

- restricting the `status` filter to a value of `open` or `closed`,
- and limiting the max page size to `100`.

`schema`, in this case, is [Joi](https://hapi.dev/family/joi/), which is the validator that comes out-of-the-box with QueryQL. But, as with adapters, validators can be written using any underlying validation library without too much effort, just in case you prefer something different.

The final function in our querier sets the default sort when it’s not specified in the query string:

```javascript
get defaultSort() {
  return {
    created_at: 'desc',
  }
}
```

And that’s it! (Well, there’s still lots more — like how to set other defaults, customize queries for more advanced uses, and more — but the [documentation](https://github.com/TRUEPIC/queryql/blob/development/DOCS.md) is there to explain it all when the time comes.)

## A Big Improvement Over Hand-Rolled Filtering, Sorting, and Pagination

Remember our route from earlier?

```javascript
app.get('/images', async (req, res, next) => {
  const orderByColumn = req.query.order_by_column || 'created_at'
  const orderByDirection = req.query.order_by_direction || 'desc'
  const page = req.query.page || 1
  const limit = req.query.limit || 20
  const where = {}

  if (req.query.status) {
    where.status = req.query.status
  }

  const images = await knex('images')
    .where(where)
    .orderBy(orderByColumn, orderByDirection)
    .limit(limit)
    .offset((page - 1) * limit)

  res.send({ images })
})
```

We had code just like this in our APIs at [Truepic](https://truepic.com/) for a long time. It worked, sure, but it was far from bulletproof, and certainly not enjoyable to maintain. When we should have been focused on the much more important (and fun) work of building photo and video verification, we were managing boilerplate throughout our routes.

That’s why we built QueryQL, and we’re excited to share it with the community as an open source project on [GitHub](https://github.com/TRUEPIC/queryql). Add it to your Node.js API today with [npm](https://www.npmjs.com/package/@truepic/queryql):

```bash
$ npm install @truepic/queryql
```