QueryQL: Easily Add Filtering, Sorting, and Pagination to Your Node.js REST API

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 route looks familiar to you (it’s using Knex to query the database):

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:

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 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:

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:

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:

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 or hapi.

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 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), 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:

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:

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, 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:

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 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?

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 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. Add it to your Node.js API today with npm:

$ npm install @truepic/queryql