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
image
The –save setting causes a dependency on Express to be added in package.json:
image
The installation by npm downloads modules and adds them to the application file system directories:
image
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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:
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
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:
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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
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:
image
to retrieve all departments, and
image
to only retrieve departments for which the name starts with an S and to get all departments with a u in their name:
image
and finally to retrieve all employees in a specific department:
image


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.

Comments

Post a Comment

Popular posts from this blog

Flutter for Single-Page Scrollable Websites with Navigator 2.0

Image
  Single page scrollable websites are everywhere. In this website design pattern, all the content is presented in one long-scrolling layout that contains multiple sections. Visitors can scroll or jump to a section by clicking buttons of a sticky menu. This pattern is a good fit for small content such as brochure websites, software library documentation, portfolios, and landing pages that are used to convert users [ 1 ]. Designers also love this pattern because it is simple, clean, and enables cool scroll animations. Jetbrains Mono single-page scrollable website Hive docs multi-page scrollable website In this series of articles, we will explore how to build a  single-page scrollable website  using Flutter. We will benefit from the Navigator 2.0 API to provide a good navigation experience to the users. You can find the source code of this series’ sample apps on my  Github page . The implementation details of the sample apps will be explained in the following articles: ...
Read more

A Data Science Portfolio is More Valuable than a Resume

Image
A Data Science Portfolio is More Valuable than a Resume Benjamin Obi Tayo Ph.D. Following Nov 20  · 5 min read D ata Science is a practical field. Hands-on skills are very important especially when you are interested in working outside academia as a practicing data scientist. In academia, you need more theoretical and research skills. While in-depth knowledge in the theoretical foundations of data science is important, as a practicing data scientist, hands-on experience is very crucial and one way to showcase you hands-on skills is via building a very great portfolio. Companies interested in hiring you would definitely be asking you for a portfolio, as it gives evidence of your strengths in fundamental data science concepts. While a data science resume is important, a data science portfolio is more valuable than a resume. This article will discuss 3 important platforms that can be used for portfolio buildi...
Read more

Better File Storage in Oracle Cloud

Image
Use Oracle Application Express with Oracle Cloud Infrastructure REST APIs to upload, download, and manage big files in Oracle Cloud. By Adrian Png November 27, 2019 Oracle Cloud Infrastructure  provides a comprehensive suite of REST APIs for accessing and managing cloud resources. For example, administrators can use REST APIs to create and manage autonomous databases and initiate backups. Oracle Application Express (Oracle APEX)  makes working with REST easy. The  APEX_WEB_SERVICE  package, available since Oracle APEX 4.0, provides developers with a function for consuming REST APIs. Oracle APEX 5.0 introduced the  APEX_JSON  package, allowing easy parsing of JSON responses. Oracle APEX 18.1 introduced the Web Source Module (WSM), which enables developers to access REST services and use the data in APEX components such as reports, interactive reports, and interactive grids. These features, when used together with Oracle Cloud Infrastructure REST ...
Read more