DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Spring Boot - How To Use Native SQL Queries | Restful Web Services
  • Building REST API Backend Easily With Ballerina Language
  • Composite Requests in Salesforce Are a Great Idea
  • Creating a Secure REST API in Node.js

Trending

  • Testing SingleStore's MCP Server
  • The Human Side of Logs: What Unstructured Data Is Trying to Tell You
  • Automating Data Pipelines: Generating PySpark and SQL Jobs With LLMs in Cloudera
  • The Cypress Edge: Next-Level Testing Strategies for React Developers
  1. DZone
  2. Data Engineering
  3. Databases
  4. Creating a REST API Part 5: Manual Pagination, Sorting, and Filtering

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

In this post, you'll make the API more flexible by adding pagination, sorting, and filtering capabilities.

By 
Dan McGhan user avatar
Dan McGhan
·
Updated Aug. 20, 18 · Tutorial
Likes (7)
Comment
Save
Tweet
Share
76.5K Views

Join the DZone community and get the full member experience.

Join For Free

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!

API REST Web Protocols Database Sorting sql

Published at DZone with permission of Dan McGhan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Spring Boot - How To Use Native SQL Queries | Restful Web Services
  • Building REST API Backend Easily With Ballerina Language
  • Composite Requests in Salesforce Are a Great Idea
  • Creating a Secure REST API in Node.js

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!