Creating a REST API with Node.js and an Oracle Database. Part 5

Part 5. Creating a REST API: pagination, manual sorting, and filtering



In the previous article, you completed building the core functionality of the CRUD API.



And now, when an HTTP GET request is issued on the employees route, all rows of the table are returned. This may not matter much with only 107 rows in the HR.EMPLOYEES table, but imagine what would happen if the table contains thousands or millions of rows. Clients such as mobile and web applications typically display only a fraction of the rows available in the database, and then select more rows when necessary — perhaps when the user scrolls down or clicks the “Next” button on some break control to pages in the user interface.



To do this, REST APIs must support pagination tools. Once pagination is supported, sorting capabilities become necessary, since data should usually be sorted before pagination is applied. In addition, the data filtering tool is very important for performance. Why send data from the database, through the intermediate layer and completely to the client, if this is not necessary?



I will use the URL query string parameters so that clients can specify how the results should be paginated, sorted and filtered. As always in programming, the implementation may vary depending on your requirements, performance goals, etc. In this post I will tell you about a manual approach to adding these functions to the API.



Pagination



Query string parameters that I will use for pagination: skip and limit. The skip parameter will be used to skip the specified number of lines, while limit will limit the number of lines returned. I will use the default value of 30 for the limit if the client does not provide the value.



Start by updating the controller logic to extract the values ​​from the query string and pass them to the database API. Open the controllers / employee.js file and add the following lines of code to the get function after the line that parses 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 you need to update the database logic 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 by the query. As usual, values ​​will not be added directly to the query - instead, they will be added as bind variables for performance and security reasons. Open db_apis / employee.js and add the following code after the if block in the find function, which adds the where clause to the request.

 // *** 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';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

This is all you need to do to paginate! Launch the API and then run a few URL commands in another terminal to test it. Here are some 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"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Sorting



At a minimum, customers should be able to specify a column to sort and order (ascending or descending). The easiest way to do this is to define a query parameter (I will use sort), which allows you to pass a string like 'last_name: asc' or 'salary: desc'. The only way to guarantee the order of the result set returned from the SQL query is to include the order by clause. For this reason, it would be nice to have a default order definition defined to ensure consistency when the client does not specify it.



Go back to controllers / employee.js and add the following line of code to the get function after the line that parses the req.query.limit parameter.

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Then open db_apis / employee.js and add the following line below the lines that declare and initialize baseQuery.

 // *** lines that initalize baseQuery end here *** const sortableColumns = ['id', 'last_name', 'email', 'hire_date', 'salary'];
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

sortableColumns is a whitelist of columns that customers can use to sort. Then, inside the find function, add the following if block, which adds the order by clause. This must be done after adding the where clause, but before the offset and fetch clauses.

 // *** if block that appends where clause ends here *** if (context.sort === undefined) { query += '\norder by last_name asc'; } else { let [column, order] = context.sort.split(':'); if (!sortableColumns.includes(column)) { throw new Error('Invalid "sort" column'); } if (order === undefined) { order = 'asc'; } if (order !== 'asc' && order !== 'desc') { throw new Error('Invalid "sort" order'); } query += `\norder by "${column}" ${order}`; }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The first part of the if block checks if the client passed the sort value. If not, then the default order by clause is added to the SQL query, which sorts by last_name in ascending order. If a sort value is specified, it is first broken down into column and order values, and each value is checked before adding order by to the query.



Now you can run several URL commands to validate it. Here are some examples to try:

 # 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 entered in the whitelist. It uses the standard Express error handler, so the error is returned as an HTML web page.



Filtration



The ability to filter data is an important feature that all REST APIs must provide. As with sorting, the implementation may be simple or complex depending on what you want to support. The easiest approach is to add support for full match filters (for example, last_name = Doe). More complex implementations can add support for basic operators (for example, <,>, instr, etc.) and complex logical operators (for example, and / or) that can group several filters together.



In this post I will try to simplify the situation and add filter support for only two columns: department_id and manager_id. For each column, I will enable the corresponding parameter in the query string. The database logic that adds the where clause when GET requests are sent to the endpoint with a single employee needs to be updated to accommodate these new filters.



Open controllers / employee.js and add the following lines below the line that parses 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);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Then edit db_apis / employee.js to add the sentence 1 = 1 to the base query, as shown 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 be true, so the optimizer will simply ignore it. However, this method will simplify the addition of additional predicates in the future.



In the find function, replace the if block, which adds the where clause when passing context.id, 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 to the binds object and then adds the corresponding predicate to the where clause. Save the changes and restart the API. Then use these URL commands to verify this:

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

That's it - the API now supports pagination, sorting and filtering! A manual approach provides a lot of control, but requires a lot of code. The search function now has 58 lines and supports only limited sorting and filtering capabilities. You may consider using a module, such as the Knex.js query builder , to simplify these operations.