Creating a REST API: Module Based Pagination, Sorting, and Filtering

By August 22, 2018 Uncategorized

I previously demonstrated a manual approach to adding pagination, sorting, and filtering capabilities to a REST API. In this post, I’ll show you how to use a module to simplify and standardize these operations.

Getting started

To demonstrate the use of a module, I created one called QueryWrap. Here’s its description on GitHub:

QueryWrap takes a SQL query and wraps it with additional SQL to simplify filtering, sorting, and pagination. QueryWrap was designed to help with building REST APIs that need to support these types of features without reinventing the wheel.

Sounds perfect, right? 🙂 QueryWrap has an execute method that’s similar to node-oracledb’s execute method with some important differences. To start, QueryWrap uses the ‘default’ connection pool to execute a query (unless a pool or connection is passed in via the options object). When working with a pool (default or otherwise), connections are automatically obtained and closed.

Next, QueryWrap’s options parameter adds support for the following properties:

  • skip – starts the rows returned at a given offset (used for pagination)
  • limit – restricts the number of rows returned (used for pagination)
  • sort – builds a dynamic order by clause from an array (JS or JSON)
  • filter – builds a dynamic where clause from an object (JS or JSON)
  • pool – the pool from which connections should be obtained (poolAlias or instance)
  • connection – the connection to use to execute SQL statements (a pool should be used instead when possible)

Finally, the object returned from QueryWrap’s execute method is different from the result that the driver returns. QueryWrap returns an object with these properties:

  • items – the rows of data
  • count – the number of rows returned (count === items.length)
  • limit – the limit used (will match the value was passed in or the default)
  • offset – the offset used (will match the value was passed in or the default)
  • totalResults – the total number of rows returned from the query (after filtering but before limit applied)
  • hasMore – a Boolean that indicates when pagination has prevented additional results from being returned

That’s QueryWrap in a nutshell. If it’s not clicking yet, it should once you see some actual code. Speaking of code, I’m going to pick up where I left off at the end of part 5. If you don’t already have the code, you can get it here.

Install QueryWrap by running the following command in a terminal from the hr_app directory.

With QueryWrap installed, you’re ready to convert the manual code to leverage the module instead. I will take you through the same sequence as before: pagination, sorting, and then filtering.

Pagination

Open db_apis/employees.js and add this line under line 1, which requires in node-oracledb.

Next, replace the find function with the following code.

As you can see, QueryWrap takes in the baseQuery as well as values for skip and limit via the options object. If skip or limit aren’t provided or are invalid then their default values (0 and 50 respectively) will be used. To retrieve all the rows, limit can be disabled by setting it to 0.

Because QueryWrap returns an object with some additional meta-data, the controller logic needs to be updated to handle that. Open controllers/employees.js and make these changes:

  • Remove the calls to parseInt for skip and limit. They are not needed because QueryWrap will do that for you if it’s passed strings for those properties (lines 6 & 7 below).
  • Rename rows to result (lines 12 & 21 below).
  • Replace references to the rows array with result.items (lines 15 & 16 below).

Save your changes and start the API. Use these cURL commands from a different terminal to test it.

If everything is working correctly, then QueryWrap should be doing the pagination for you – there’s no need to manipulate the base query manually. The implementation is a little different than what I showed in the previous post. With QueryWrap, I decided to use an older syntax for pagination so that it could work with earlier versions of Oracle (support for offset and fetch was added in 12c).

Sorting

When it comes to sorting, QueryWrap expects an array of objects (could be JSON) to specify how the sort should work, in relative sort order. Each object must contain a column property which should contain the name of the column to sort by. An optional order property can be specified to control the sort order. Use either ‘asc’, ‘1’, or 1 for ascending order and ‘desc’, ‘-1’, or -1 for descending order. The default sort order is ascending.

As in the manual implementation, you’ll want to provide a default sort order if one isn’t specified. Return to db_apis/employees.js and make the following changes in the find function.

  • Add a constant named sort and initialize it to the value from either context.sort or the default you’d like to use (line 2 below).
  • Map the value of sort to the sort property in the options object passed to execute (line 10 below). Don’t forget the comma on the line above.

Save your changes, restart the API, and then test it with a few cURL commands (note that URLs need to be properly escaped to work correctly).

QueryWrap’s sorting support is already a bit more advanced than what I added to the manual implementation as it supports multiple columns. However, it doesn’t yet support controlling nulls within sorts, nor does it allow the developer to whitelist sorts on specific columns.

Filtering

Filtering is where QueryWrap really starts to shine as its far more generic than what I coded in the manual approach. The filter option accepts an object and converts it to a where clause and the appropriate bind variables.

There are two syntaxes currently supported:

  1. {column: value}
  2. {column: {operator: value}}

column should be the name of the column to filter by (note that the keys are case sensitive). The first syntax will create a column = :value predicate while the second syntax allows for operators other than =. The following operators are currently supported:

  • $eq – whether a column value is equal to a given scalar
  • $ne – whether a column value is different from a given scalar
  • $lt – whether a column value is less than a given scalar
  • $lte – whether a column value is less than or equal to a given scalar
  • $gt – whether a column value is greater than a given scalar
  • $gte – whether a column value is greater than or equal to a given scalar
  • $between – whether a colum value is between (inclusive) to scalar values
  • $nbetween – whether a colum value is not between (inclusive) to scalar values
  • $in – weather a column value is a member of a given set of scalar values
  • $nin – weather a column value is not a member of a given set of scalar values
  • $like – whether a column value matches a given SQL LIKE pattern
  • $instr – whether a column value has a given substring
  • $ninstr – whether a column value does not have a given substring
  • $null – whether a column is null (value should be null, e.g. {column: {$null: null}})
  • $nnull – whether a column is not null (value should be null, e.g. {column: {$nnull: null}})

filter objects can contain multiple filters for different columns, but work would need to be done to support multiple filters on the same column (perhaps via complex $and or $or operators or by accepting an array of filters).

To add support for filtering to the API, return to the db_apis/employees.js file and make the following changes:

  • Declare a variable named filter and intialize it to the value from context.filter (line 3 below).
  • Add an if block that checks to see if a context.id value was passed in. If so, set the value of filter to {id: context.id} (lines 5-7 below).
  • Map the value of filter to the filter property in the options object passed to execute (line 16 below). Don’t forget the comma on the line above.

Of course, the filter value needs to be passed in from the controller logic. Return to controllers/employees.js and replace the lines that parse department_id and manager_id with this line.

Save your work and restart the API. Use these cURL commands to test the changes (remember that URLs need to be properly escaped to work correctly):

Using QueryWrap, the find function in db_apis/employees.js went from 58 lines down to 21. At the same time, however, the API became a lot more flexible as the sorting and filtering capabilities were greatly expanded.

QueryWrap going forward

I wrote QueryWrap as a demonstration module, and I hope you now see how powerful a module like this can be. Unfortunately, I don’t have the bandwidth to maintain QueryWrap as there are many other topics I’d like to write about! 🙂 Feel free to fork it, rename and republish it, customize it, etc. If you submit issues or pull requests, I’ll try to look at them when I have time, but there are no guarantees.

4 Comments

  • Robert says:

    Hi,
    Thanks for the article. Below are several suggestions based on an existing large application that provides a REST read-only interface.

    I’d like to mention that it is probably a better idea overall to use a query builder/runner such as knex.js for constructing SQL. This way, you can dynamically add .limit clauses with minimum effort and without any wrapping involved – it gives you a consistent API to transform and enrich queries.

    Also, using keyset-based paging instead of offset-based is much more efficient in the long run. Join the NO-OFFSET movement today: https://use-the-index-luke.com/no-offset . This is a vastly different approach, and it requires a total linear order among all records in the DB – which is a good idea anyway, unless you intend to serve records in a seemingly-random fashion.

    Finally, there are already languages and tools that do provide AND, OR, and general AST capabilities for querying. One such language is RQL (Resource Query Language), and it ships a JavaScript library capable of parsing query strings using it. Even though it is not tremendously popular, since it is very similar to the query syntax you present, I would say definitely check it out. It would be great to see more users of this language that has gone under the radar for years now.

    Cheers!

    • danmcghan says:

      Hi Robert,

      Thanks for your comments! There are many approaches to solving problems in programming, each with its own set of tradeoffs.

      I’ll try to cover Knex.js, an ORM, keyset pagination, and maybe even RQL in the future.

      Regards,
      Dan

  • Alex says:

    Resulting syntax looks very similar to NoSQL like Firebase and MongoDB .
    My point is that they already provided RESTful APIs .
    But still yet another level of indirection never inflicts any harm, probably .

    • danmcghan says:

      Hi Alex,

      Thank you for your comments!

      Oracle also provides out-of-the-box tools for creating REST APIs on Oracle Database, see Oracle REST Data Services (ORDS).

      However, this is a series on creating REST APIs with Node.js on Oracle Database. I’m trying to strike a balance granular control and magical black boxes. No one solution is right for everyone.

      Regards,
      Dan

Leave a Reply