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

I previously demonstrateda 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.

Please Note:This post is part of a series on creating a REST API with Node.js on Oracle Database . See that post for details on the project and links to other parts. Get the code here .

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 ofpart 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.

npm install query-wrap --save

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.

// *** line that requires oracledb is here ***
const queryWrap = require('query-wrap');

Next, replace the find function with the following code.

async function find(context) {
  const result = await queryWrap.execute(
    baseQuery,
    [],
    {
      skip: context.skip,
      limit: context.limit
    }
  );
 
  return result;
}

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).

async function get(req, res, next) {
  try {
    const context = {};
 
    context.id = parseInt(req.params.id, 10);
    context.skip = req.query.skip;
    context.limit = req.query.limit;
    context.sort = req.query.sort;
    context.department_id = parseInt(req.query.department_id, 10);
    context.manager_id = parseInt(req.query.manager_id, 10);
 
    const result = await employees.find(context);
 
    if (req.params.id) {
      if (result.items.length === 1) {
        res.status(200).json(result.items[0]);
      } else {
        res.status(404).end();
      }
    } else {
      res.status(200).json(result);
    }
  } catch (err) {
    next(err);
  }
}

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

# use default limit (50)
curl "http://localhost:3000/api/employees"
 
# set limit to 5
curl "http://localhost:3000/api/employees?limit=5"
 
# set limit to 0 (get all rows)
curl "http://localhost:3000/api/employees?limit=0"
 
# use default limit and set skip to 5
curl "http://localhost:3000/api/employees?skip=5"
 
# set both skip and limit to 5
curl "http://localhost:3000/api/employees?skip=5&limit=5"

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.

async function find(context) {
  const sort = context.sort || [{column: 'id'}];
 
  const result = await queryWrap.execute(
    baseQuery,
    [],
    {
      skip: context.skip,
      limit: context.limit,
      sort: sort
    }
  );
 
  return result;
}

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).

# use default sort (id ascending)
curl "http://localhost:3000/api/employees"
 
# sort by id descending
# JSON: [{"column":"id","order":"desc"}]
curl "http://localhost:3000/api/employees?sort=%5B%7B%22column%22:%22id%22,%22order%22:%22desc%22%7D%5D"
 
# sort by department_id ascending and hire_date descending
# JSON: [{"column":"department_id"},{"column":"hire_date","order":"desc"}]
curl "http://localhost:3000/api/employees?sort=%5B%7B%22column%22:%22department_id%22%7D,%7B%22column%22:%22hire_date%22,%22order%22:%22desc%22%7D%5D"

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.

async function find(context) {
  const sort = context.sort || [{column: 'id'}];
  let filter = context.filter;
 
  if (context.id) {
    filter = {id: context.id};
  }
 
  const result = await queryWrap.execute(
    baseQuery,
    [],
    {
      skip: context.skip,
      limit: context.limit,
      sort: sort,
      filter: filter
    }
  );
 
  return result;
}

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.

    // *** line that assigns context.sort is here ***
    context.filter = req.query.filter;

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

# get the employee with id 101
curl "http://localhost:3000/api/employees/101"
 
# get employees in department 60
# JSON: {"department_id":60}
curl "http://localhost:3000/api/employees?filter=%7B%22department_id%22:60%7D"
 
# get employees in departments 50, 60, and 70
# JSON: {"department_id":{"$in":[50,60,70]}}
curl "http://localhost:3000/api/employees?filter=%7B%22department_id%22%3A%7B%22%24in%22%3A%5B50%2C60%2C70%5D%7D%7D"
 
# get employees with salary between 3000 and 4000
# JSON: {"salary":{"$between":[3000,4000]}}
curl "http://localhost:3000/api/employees?filter=%7B%22salary%22%3A%7B%22%24between%22%3A%5B3000%2C4000%5D%7D%7D"
 
# get employees with manager id = 100 and salary >= 10000
# JSON: {"manager_id":{"$eq":100},"salary":{"$gte":10000}}
curl "http://localhost:3000/api/employees?filter=%7B%22manager_id%22:%7B%22%24eq%22:100%7D,%22salary%22:%7B%22%24gte%22:10000%7D%7D"

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.