REST API on Node.js and Express for data retrieved from Oracle Database with node-oracledb Database Driver running on Application Container Cloud

This article is a follow up on my previous article Linking Application Container Cloud to DBaaS – Expose REST API from node.js application leveraging node-oracle-database driver. That article describes how a simple Node.js application is configured for deployment on the Oracle Application Container Cloud and how it leverages the node-oracledb database driver that allows Node.js applications to easily connect to an Oracle Database. From the Application Container Cloud, the application discussed uses a cloud Service Binding to access a DBaaS instance also running on the Oracle Public Cloud. The Node.js application returns a JSON message containing details about departments in the DEPARTMENTS table in the HR schema of the DBaaS instance.

The Node.js application itself is very rudimentary. The way it handles the HTTP requests is quite simplistic. It does not leverage most common practices in Node.js or JavaScript. It does not handle bind parameters in the queries nor does it interpret URL path parameters or query parameters. In this article, I will move beyond my initial attempt to add a little more sophistication on all these fronts. The resulting application:

• uses bind parameters in accessing the database
• handles routing in a more elegant way (using Express)
• handles query parameters
• handles URL path segments

Add Express based Routing

To make use of Express in the application, I need to install the Express package, using npm:

npm install express –save

The –save setting causes a dependency on Express to be added in package.json:

The installation by npm downloads modules and adds them to the application file system directories:

When I package the application for deployment to the Application Container Cloud, all Express resources need to be included in the application archive.

In the code itself, express is imported by adding a require statement:

var express = require(‘express’);

The main object used for leveraging Express is usually called app:

var app = express();

From here on, the application is reorganized Express style:

var http = require('http');
var express = require('express');
var app = express();

var PORT = process.env.PORT || 8089;

app.listen(PORT, function () {
console.log('Server running, Express is listening...');
});

app.get('/', function (req, res) {
res.writeHead(200, {'Content-Type': 'text/html'});
res.write("No Data Requested, so none is returned");
res.end();
});

app.get('/departments', function(req,res){ handleAllDepartments(req, res);} );

app.get('/departments/:departmentId', function(req,res){
} );

function handleAllDepartments(request, response) {
}

 

Recognize and Handle Query Parameters and URL Path Segments

Using Express functionality it is quite straightforward to extract parameters from the HTTP request. Assuming a request such as /departments/100 or /departments?name=S%, we want to be able to extract the values 100 and S%. There is a distinction between the values passed in the URL path and those provided as query parameter

The first category is extracted using:

var departmentIdentifier = req.params.departmentId; — used to extract 100 from /departments/100 assuming /departments/:departmentId as the URL pattern Express listens to

and the second with:

var departmentName = request.query.name ; — used to extract S% from /departments?name=S% assuming /departments as the URL pattern Express listens to

The application now looks like this:

var http = require('http');
var express = require('express');
var app = express();

var PORT = process.env.PORT || 8089;

app.listen(PORT, function () {
console.log('Server running, Express is listening...');
});

app.get('/', function (req, res) {
res.writeHead(200, {'Content-Type': 'text/html'});
res.write("No Data Requested, so none is returned");
res.end();
});

app.get('/departments', function(req,res){ handleAllDepartments(req, res);} );

app.get('/departments/:departmentId', function(req,res){
var departmentIdentifier = req.params.departmentId;
} );

function handleAllDepartments(request, response) {
var departmentName = request.query.name ||'%';
} //handleAllDepartments

Use Bind Parameters in Database Queries

The parameters we extracted above are to be used in the queries executed against the database. And these parameters should be passed in as bind parameters (for reasons like SQL Injection prevention and reuse of database execution plans). Bind parameters are easily used with node-oracledb:

  var selectStatement = “SELECT department_id, department_name FROM departments where department_name like :department_name“;
connection.execute(   selectStatement
, [departmentName], {
outFormat: oracledb.OBJECT // Return the result as Object
}

        ,…

Bind parameters are defined in the query in the familiar way: using identifiers prefixed with a colon.

The second parameter in the call to connection.execute is an array with the values of the bind parameters. In this case – with a single bind parameter defined in the query – there has to be a single value in this array. There are no requirements on the naming of the bind parameter.

The entire application is now defined as follows:

var http = require('http');
var oracledb = require('oracledb');
var express = require('express');
var app = express();

var PORT = process.env.PORT || 8089;

app.listen(PORT, function () {
console.log('Server running, Express is listening...');
});

app.get('/', function (req, res) {
res.writeHead(200, {'Content-Type': 'text/html'});
res.write("No Data Requested, so none is returned");
res.end();
});

app.get('/departments', function(req,res){ handleAllDepartments(req, res);} );

app.get('/departments/:departmentId', function(req,res){
var departmentIdentifier = req.params.departmentId;
handleDatabaseOperation( req, res, function (request, response, connection) {
var selectStatement = "SELECT employee_id, first_name, last_name, job_id FROM employees where department_id= :department_id";
connection.execute( selectStatement
, [departmentIdentifier], {
outFormat: oracledb.OBJECT // Return the result as Object
}, function (err, result) {
if (err) {
console.log('Error in execution of select statement'+err.message);
response.writeHead(500, {'Content-Type': 'application/json'});
response.end(JSON.stringify({
status: 500,
message: "Error getting the employees for the department "+departmentIdentifier,
detailed_message: err.message
})
);
} else {
console.log('db response is ready '+result.rows);
response.writeHead(200, {'Content-Type': 'application/json'});
response.end(JSON.stringify(result.rows));
}
doRelease(connection);
}
);
});
} );

function handleDatabaseOperation( request, response, callback) {
console.log(request.method + ":" + request.url );
response.setHeader('Access-Control-Allow-Origin', '*');
response.setHeader('Access-Control-Allow-Methods', 'GET, POST, OPTIONS, PUT, PATCH, DELETE');
response.setHeader('Access-Control-Allow-Headers', 'X-Requested-With,content-type');
response.setHeader('Access-Control-Allow-Credentials', true);

console.log('Handle request: '+request.url);
var connectString = process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR.replace("PDB1", "demos");
console.log('ConnectString :' + connectString);
oracledb.getConnection(
{
user : process.env.DB_USER || "hr",
password : process.env.DB_PASSWORD || "hr",
connectString : connectString
},
function(err, connection)
{
if (err) {
console.log('Error in acquiring connection ...');
console.log('Error message '+err.message);

// Error connecting to DB
response.writeHead(500, {'Content-Type': 'application/json'});
response.end(JSON.stringify({
status: 500,
message: "Error connecting to DB",
detailed_message: err.message
}
));
return;
}
// do with the connection whatever was supposed to be done
console.log('Connection acquired ; go execute ');
callback(request, response, connection);
});
}//handleDatabaseOperation

function handleAllDepartments(request, response) {
handleDatabaseOperation( request, response, function (request, response, connection) {
var departmentName = request.query.name ||'%';

var selectStatement = "SELECT department_id, department_name FROM departments where department_name like :department_name";
connection.execute( selectStatement
, [departmentName], {
outFormat: oracledb.OBJECT // Return the result as Object
}, function (err, result) {
if (err) {
console.log('Error in execution of select statement'+err.message);
response.writeHead(500, {'Content-Type': 'application/json'});
response.end(JSON.stringify({
status: 500,
message: "Error getting the departments",
detailed_message: err.message
})
);
} else {
console.log('db response is ready '+result.rows);
response.writeHead(200, {'Content-Type': 'application/json'});
response.end(JSON.stringify(result.rows));
}
doRelease(connection);
}
);

});
} //handleAllDepartments

function doRelease(connection)
{
connection.release(
function(err) {
if (err) {
console.error(err.message);
}
});
}

Invoke the REST API

With the implementation of the dataApi.js application it now supports the following calls:

to retrieve all departments, and

to only retrieve departments for which the name starts with an S and to get all departments with a u in their name:

and finally to retrieve all employees in a specific department:

 

 

Resources

Home of Express.

Samples for using node-oracledb to interact with an Oracle Database

My previous article Linking Application Container Cloud to DBaaS – Expose REST API from node.js application leveraging node-oracle-database driver – to explain the basics for creating a Node.js application using node-oracledb and for configuring a Node.js application on Oracle Application Container Cloud to link with a DBaaS instance.

Stack Overflow on recognizing URL segments and query parameters http://stackoverflow.com/questions/14417592/node-js-difference-between-req-query-and-req-params

Download the dataApi.zip Node.js application.