Creating a REST API Part 3: Handling GET Requests

After taking a brief detour to create a generic database module, it's time to continue on with the development of the high-level components discussed in the parent post. In this post, you will add routing, controller, and database logic to handle an HTTP GET request on an "employees" API endpoint.

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.

Adding Routing Logic

Express ships with a 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 you could define the routes for your application. For example, when the app starts, you could read all the files in the controllers directory and auto-generate 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');

router.route('/employees/:id?')
  .get(employees.get);

module.exports = router;

The 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 get an instance of a single route based on the route path passed in. In this case, 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 has methods which correspond to HTTP methods and allow 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).

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 (that was only used for testing in the previous post). Add the following line of code in its place.

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

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

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

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

Now the router is required into 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 endpoint and 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 responseobjects, as well as the next function.

The middleware function will use the data coming in from the request object to generate a response which is sent with the response object. Because the controller function will attempt to end the HTTP request, the next function will only be invoked if an error occurs.

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

Within each module, a middleware function that handles a particular HTTP method will be defined and exposed. 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 open the employees.js file that was created in the first part of this series. 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 = {};

    context.id = Number(req.params.id);

    const rows = await employees.find(context);

    if (req.params.id) {
      if (rows.length === 1) {
        res.status(200).json(rows[0]);
      } else {
        res.status(404).end();
      }
    } else {
      res.status(200).json(rows);
    }
  } catch (err) {
    next(err);
  }
}

module.exports.get = get;

Here's a line-by-line breakdown of the controller module so far:

• Line 1: The employees database API (created in the next part) is required in.
• Lines 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.
  • Lines 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  req.params.id.
  • Lines 9: The database API's find method is used to fetch the appropriate employee records in the database.
  • Lines 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 is sent as a response. Otherwise, a "200 - OK" code, along with a JSON-based response body, is sent.
• Line 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 using the req.get method.

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

Adding Database Logic

As I mentioned in the parent post, I'll be using the Node.js database driver/API for Oracle Database, node-oracledb, instead of a higher level ORM (I'll likely cover ORMs in the future). To start the employees database module, open the db_apis/employees.js file and add the following code.

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 (context.id) {
    binds.employee_id = context.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 initializes a constant named baseQuery to a SQL query on the employees table. Double-quoted column aliases are used 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" id value, then a where clause is appended to the query so that only a single employee is returned.

Note that the value of context.id was not appended to the query directly. Instead, a placeholder named :employee_id was used instead - 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 to database.simpleExecute, along with the query.

Once the database module is in place, you should be able to start the application and test. Once started, navigate Firefox to http://localhost:3000/api/employees. You should see a list of employees as follows (I've collapsed a couple):

You can fetch a single employee by adding an id to the end of the URL, for example, http://localhost:3000/api/employees/100.

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