Application Development

Build REST APIs for Node.js, Part 3

Add routing, controller, and database logic.

By Dan McGhan

September/October 2018

After our brief detour to create a generic database module, it’s time to continue building out the high-level REST API components for Node.js discussed in the first article of this series. In this article, you will add routing, controller, and database logic to handle an HTTP GET request on an “employees” API endpoint.

Note: the instructions and steps in this article assume that you have completed the steps in Part 2 of this article series.

Adding Routing Logic

Express, which creates the web server for this project, ships with a Router class that makes it easy to route HTTP requests to the appropriate controller logic. Route paths define the URL endpoints of the API and can contain route parameters that capture values in the URL (query strings are not part of route paths).

There are many ways to define the routes for your application. For example, when the app starts, you could read all the files in the controllers directory and autogenerate routing logic based on some predefined rules, such as the filenames and properties they expose. Alternatively, you could add a file to the config directory and read that at start time. Consider such automation when your API matures and its patterns are well known.

In this application, you will take a slightly lower-level approach by defining routes programmatically via a new router module. Create a new file named router.js in the services directory. Add the following code to the file, and save your changes:

const express = require('express');
const router = new express.Router();
const employees = require('../controllers/employees.js');


module.exports = router;

This router module starts by bringing in Express and then creates a new instance of Express’ Router class. The router’s route method is used to define a route based on the route path passed in. The path includes a parameter named id, which is made optional by the question mark that follows it. The route that’s returned from route includes methods that correspond to HTTP methods and enable handlers to be defined. In this case, the get method is used to map an incoming GET request to the get function defined in the employees controller (which will be created in the next part of this article).

At this point, you have a router but it’s not currently used in the application. To use it, open the services/web-server.js file and remove the line at the top that requires the database module (it was used only for testing in the previous article). Add the following line of code in its place:

// *** line that requires ../config/web-server.js is here ***
const router = require('./router.js');

Next, replace the entire app.get handler that responds to GET requests using the database module (all seven lines) with the following code:

// *** line that adds morgan to app here ***

// Mount the router at /api so all routes start with /api
app.use('/api', router);

Now the router is required in the web service module and mounted at /api. This means that the full URL for the employees endpoint will be http://server:port/api/employees/:id.

Adding Controller Logic

The controller logic will take over from the point that the URL endpoint and the HTTP method are known. Because the web server is built with Express, the controller logic will be defined with custom middleware or functions that have access to the request and response objects as well as the next function.

The middleware function will use incoming data from the request object to generate a response that is sent using the response object. The next function is typically used to invoke the next middleware function in the pipeline. However, in this API, the controller logic will be the last step in the pipeline and will end the HTTP response. The next function will be invoked only if an error occurs, in which case control will be passed to Express’ default error handler.

I usually create one module in the controllers directory for each endpoint in the API. Here are some examples:

URL Endpoint Controller File
/api/employees/:id controllers/employees.js
/api/departments/:id controllers/departments.js
/api/departments/:dept_id/employees/:emp_id controllers/departments_employees.js
A middleware function that handles a particular HTTP method will be defined and exposed within each module. I usually name each function based on the HTTP method it handles, which makes it easy to wire things up in the router module.

Go to the controllers directory, and create a new file named employees.js. Copy and paste the following code into the file, and save your changes:

 const employees = require('../db_apis/employees.js');

async function get(req, res, next) {
  try {
    const context = {}; = parseInt(, 10);

    const rows = await employees.find(context);

    if ( {
      if (rows.length === 1) {
      } else {
    } else {
  } catch (err) {

module.exports.get = get;

Here’s a breakdown of the controller module so far:

Line(s) Description
1 The employees database API (created in the next part of this article) is required.
3–23 An async function named get is declared. A try-catch block is used in the body of the function to catch exceptions thrown on the main thread and pass them to the next function.
5–7 A constant named context is declared. This is a generic object that will contain properties that are relevant to the database API’s find method. An id property is added to context, based on the value that comes in via
9 The database API’s find method is used to fetch the appropriate employee records in the database.
11–19 Conditional logic is used to determine the correct HTTP status code and body for the response. If one employee was requested but not found, a “404 Not Found” error code will be sent as a response. Otherwise, a “200 OK” code, along with a JSON-based response body, will be sent.
25 The get function is exported from the module so it can be used in the router module.

The req.params object is just one of several properties used to get data from the incoming request object. Other common properties include req.query, for the query string values in the URL; req.body, for the request body; and req.cookies. HTTP headers can be fetched with the req.get method.

If you don’t like the magic numbers used for the status codes, consider using a module such as http-status instead. That module provides constants, such as OK and NOT_FOUND, that can add clarity to the code.

Adding Database Logic

As I mentioned in Part 1 of this article series, I’ll be using the Node.js database driver/API for Oracle Database, node-oracledb, instead of a higher-level object- relational mapping (ORM). To start the employees database module, go to the db_apis directory and create a new file named employees.js. Add the following code to the file:

const database = require('../services/database.js');

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`;

async function find(context) {
  let query = baseQuery;
  const binds = {};

  if ( {
    binds.employee_id =;

    query += `\nwhere employee_id = :employee_id`;

  const result = await database.simpleExecute(query, binds);

  return result.rows;

module.exports.find = find;

The employees database module brings in the generic database module and then initializes a constant named baseQuery to a SQL query on the employees table. Double-quoted column aliases are used in the query to control the case of the keys returned.

Next, a function named find is declared and used to execute the query and return the rows fetched. If the context parameter passed in has a “truthy” value for id, then a WHERE clause is appended to the query so that only a single employee is returned.

Note that the value of was not appended to the query directly. Instead, a placeholder named :employee_id was used—this is known as a bind variable. Using bind variables with Oracle Database is very important, for security and performance reasons. The value of the bind variable is assigned to the binds object, which is passed with the query to database.simpleExecute. Finally, the rows returned from the database are returned to the caller.

Once the database module is in place, you’ll be ready to test everything. Start the app, and then navigate Firefox to http://localhost:3000/api/employees. You should see a list of employees (I’ve collapsed a couple), as shown in Figure 1.

Node.js, part 3 figure 1Figure 1: Getting the list of employees

Node.js, part 3 figure 2Figure 2: Getting a single employee

You can fetch a single employee by adding an ID to the end of the URL, such as http://localhost:3000/api/employees/100, as shown in Figure 2.

At this point, your API can handle GET requests on the employees endpoint. In the next article, you will round out the create, replace, update, delete (CRUD) functionality by adding logic that handles POST, PUT, and DELETE requests.

Next Steps

READ Part 1 of this article series.

READ Part 2 of this article series.

LEARN more about JavaScript and Oracle.

TRY Oracle Cloud.

GET more about this article’s code from GitHub.

Illustration by Wes Rowell