After taking a brief detour to create a generic database module, it’s time to continue building out 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.



Adding routing logic

Express 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 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.

services/router.js 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; 1 2 3 4 5 6 7 8 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 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 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.

services/web-server.js // *** line that requires ../config/web-server.js is here *** const router = require('./router.js'); 1 2 // *** 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).

services/web-server.js // *** line that adds morgan to app here *** // Mount the router at /api so all its routes start with /api app.use('/api', router); 1 2 3 4 // *** 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 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 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 which 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 it will end the HTTP response. The next function will only be invoked if an error occurs, which passes control 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

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 create a new file named employees.js. Copy and paste the following code into the file and save your changes.

controllers/employees.js const employees = require('../db_apis/employees.js'); async function get(req, res, next) { try { const context = {}; context.id = parseInt(req.params.id, 10); 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; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 const employees = require ( '../db_apis/employees.js' ) ; async function get ( req , res , next ) { try { const context = { } ; context . id = parseInt ( req . params . id , 10 ) ; 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 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.

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 function. 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 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, go to the db_apis directory and create a new file named employees.js. Add the following code to the file.

db_apis/employees.js 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 += `

where employee_id = :employee_id`; } const result = await database.simpleExecute(query, binds); return result.rows; } module.exports.find = find; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 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 – 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 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.