Creating a REST API Part 5: Manual Pagination, Sorting, and Filtering

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.

At this point in the series, the REST API supports basic CRUD capabilities on the employees' endpoint. However, clients often need some control over how multiple records are fetched from the database. In this post, you'll make the API more flexible by adding pagination, sorting, and filtering capabilities.

Overview

Currently, when an HTTP GET request is issued on the employees' route, all of the table's rows are returned. This may not be a big deal with only 107 rows in the HR.EMPLOYEES table, but imagine what would happen if the table contained thousands or millions of rows. Clients such as mobile and web apps generally consume and display only a fraction of the rows available in the database and then fetch more rows when needed — perhaps when a user scrolls down or clicks the "next" button on some pagination control in the UI.

To allow for this, REST APIs need to support a means of paginating the results returned. Once pagination is supported, sorting capabilities become important as data usually needs to be sorted prior to pagination being applied. Additionally, a means of filtering data is very important for performance. Why send data from the database, through the mid-tier, and all the way to the client if it's not needed?

I will use URL query string parameters to allow clients to specify how results should be paginated, sorted, and filtered. As is always the case in programming, the implementation could vary depending on your requirements, performance goals, etc. In this post, I'll walk you through a manual approach to adding these features to an API. This approach provides very granular control but it can be laborious and repetitive, so I'll show you how a module can be used to simplify these operations in a future post.

Pagination

The query string parameters I will use for pagination are skip and limit. The skip parameter will be used to skip past the number of rows specified while limit will limit the number of rows returned. I'll use a default of 30 for limit if a value isn't provided by the client.

Start by updating the controller logic to extract the values from the query string and pass them along to the database API. Open the controllers/employees.js file and add the following lines of code in the get function, after the line that parses out the req.params.id parameter.

    // *** line that parses req.params.id is here ***
    context.skip = parseInt(req.query.skip, 10);
    context.limit = parseInt(req.query.limit, 10);

Now the database logic needs to be updated to take these values into account and update the SQL query accordingly. In SQL, the offset clause is used to skip rows and the fetch clause is used to limit the number of rows returned from a query. As usual, the values will not be appended directly to the query — they will be added as bind variables instead for performance and security reasons. Open db_apis/employees.js and add the following code after the if block in the find function that appends the where clause to the query.

  // *** if block that appends where clause ends here ***

  if (context.skip) {
    binds.row_offset = context.skip;

    query += '\noffset :row_offset rows';
  }

  const limit = (context.limit > 0) ? context.limit : 30;

  binds.row_limit = limit;

  query += '\nfetch next :row_limit rows only';

That's all you need to do for pagination! Start the API and then run a few cURL commands in another terminal to test it. Here are a few examples you can use:

# use default limit (30)
curl "http://localhost:3000/api/employees"

# set limit to 5
curl "http://localhost:3000/api/employees?limit=5"

# 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"

With pagination now working, you may already see the importance of being able to sort the data before pagination is applied. You will add sorting in the next section.

Sorting

At a minimum, clients should be able to specify the column to sort by and the order (ascending or descending). The simplest way to do this is to define a query parameter (I'll use sort ) that allows a string like 'last_name:asc' or 'salary:desc' to be passed in. Of course, you could take this further, perhaps allowing clients to sort by multiple columns, control how nulls are treated, etc. I'll keep things simple and only allow clients to specify a single column and direction as above.

In SQL, the order by clause is used to sort data. Unfortunately, it is not possible to bind in the column name in the order by clause of a SQL query as it's considered an identifier rather than a value. This means you'll need to be very careful when appending the column name and direction to the query to prevent SQL injection. You could sanitize the values passed in or compare them against a whitelist of values. I'll use the whitelist approach as it provides more control than generic sanitization.

One last thing before we get to the code...the only way to guarantee the order of a result set returned from a SQL query is to include an order by clause. For this reason, it's a good idea to have a default order by clause defined to ensure consistency when the client doesn't specify one.

Return to the controllers/employees.js file and add the following line of code in the get function, after the line that parses out the req.query.limit parameter.

    // *** line that parses req.query.limit is here ***
    context.sort = req.query.sort;

Next, open db_apis/employees.js and add the following line below the lines that declare and initalize baseQuery.

// *** lines that initalize baseQuery end here ***

const sortableColumns = ['id', 'last_name', 'email', 'hire_date', 'salary'];

sortableColumns is the whitelist of columns that clients will be able use for sorting. Next, inside the find function, add the following if block which appends the order by clause. This needs to be done after the where clause is added, but before the offset and fetch clauses.

The first part of the if block checks to see if the client passed in a sort value. If not, a default order by clause that sorts by last_name in ascending order is appended to the SQL query. If a sort value is specified, then it's first broken up into the column and order values and each value is validated before the order by clause is appended to the query.

Now you can restart the API and run some cURL commands to test it. Here are some examples to try out:

# use default sort (last_name asc)
curl "http://localhost:3000/api/employees"

# sort by id and use default direction (asc)
curl "http://localhost:3000/api/employees?sort=id"

# sort by hire_date desc
curl "http://localhost:3000/api/employees?sort=hire_date:desc"

# use sort with limit and skip together
curl "http://localhost:3000/api/employees?limit=5&skip=5&sort=salary:desc"

# should throw an error because first_name is not whitelisted
curl "http://localhost:3000/api/employees?sort=first_name:desc"

# should throw an error because 'other' is not a valid order
curl "http://localhost:3000/api/employees?sort=last_name:other"

The last two examples should throw exceptions because they contain values that were not whitelisted. Currently, Express' default error handler is being used, which is why the error is returned as an HTML web page. I'll show you how to implement custom error handling in a future post.

Filtering

The ability to filter data is an important feature that all REST APIs should provide. As was the case with sorting, the implementation can be simple or complex depending on what you want to support. The easiest approach is to add support for equals filters (e.g. last_name=Doe). More complex implementations may add support for basic operators (e.g., instr, etc.) and complex boolean operators (e.g. and & or) that can group multiple filters together.

In this post, I'll keep things simple and only add support for equals filters on two columns: department_id and manager_id. For each column, I'll allow for a corresponding parameter in the query string. The database logic that appends a where clause, when GET requests are issued on the single-employee endpoint, will need to be updated to allow for these new filters.

Open controllers/employees.js and add the following lines below the line that parses out the value of req.query.sort in the get function.

    // *** line that parses req.query.sort is here ***
    context.department_id = parseInt(req.query.department_id, 10);
    context.manager_id = parseInt(req.query.manager_id, 10);

Next, edit db_apis/employees.js by adding a 1 = 1 where clause to the baseQuery as seen below.

const baseQuery =
 `select employee_id "id",
    first_name "first_name",
    last_name "last_name",
    email "email",
    phone_number "phone_number",
    hire_date "hire_date",
    job_id "job_id",
    salary "salary",
    commission_pct "commission_pct",
    manager_id "manager_id",
    department_id "department_id"
  from employees
  where 1 = 1`;

Of course, 1 = 1 will always resolve to true so the optimizer will just ignore it. However, this technique will simplify adding additional predicates later on.

In the find function, replace the if block that appends the where clause when a context.id is passed in with the following lines.

  // *** line that declares 'binds' is here ***

  if (context.id) {
    binds.employee_id = context.id;

    query += '\nand employee_id = :employee_id';
  }

  if (context.department_id) {
    binds.department_id = context.department_id;

    query += '\nand department_id = :department_id';
  }

  if (context.manager_id) {
    binds.manager_id = context.manager_id;

    query += '\nand manager_id = :manager_id';
  }

As you can see, each if block simply adds the value passed into the binds object and then appends a corresponding predicate to the where clause.

Save your changes and then restart the API. Then use these cURL commands to test it:

# filter where department_id = 90 (returns 3 employees)
curl "http://localhost:3000/api/employees?department_id=90"

# filter where manager_id = 100 (returns 14 employees)
curl "http://localhost:3000/api/employees?manager_id=100"

# filter where department_id = 90 and manager_id = 100 (returns 2 employees)
curl "http://localhost:3000/api/employees?department_id=90&manager_id=100"

And there you have it — the API now supports pagination, sorting, and filtering! The manual approach provides a lot of control but requires a lot of code. The find function is now 58 lines long and it only supports limited sorting and filtering capabilities. Of course, there are ways to make this easier, though you may have to sacrifice some control.

This post is the last "core" post in the series on building a REST API with Node.js and Oracle Database. However, I will extend this series with a number of follow-up posts that cover a variety of REST API and Oracle Database features. The first such follow-up post will show you how to use a module to simplify and standardize pagination, sorting, and filtering. Stay tuned!