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

Lucas Jellema 8
0 0
Read Time:7 Minute, 0 Second

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:

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:

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.

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

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

  1. Hi,

    I really liked to know how to use oracledb to connect to oracle 11 XE which is locally installed.

    Im getting this error:
    TypeError: Cannot read property ‘replace’ of undefined
    at handleDatabaseOperation (C:\Users\nishad.nazar\.vscode\OracleAPI\APi.js:56:68)
    at handleAllDepartments (C:\Users\nishad.nazar\.vscode\OracleAPI\APi.js:87:5)

    And I believe this line is the error:
    var connectString = process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR.replace(“PDB1”, “demos”);

    Please suggest a solution ?

  2. Hi Lucas,

    First of all, Great article.Got lot of basics info.

    Now, after execution, I get this error:
    TypeError: Cannot read property ‘replace’ of undefined
    at handleDatabaseOperation (C:\Users\nishad.nazar\.vscode\OracleAPI\APi.js:56:68)
    at handleAllDepartments (C:\Users\nishad.nazar\.vscode\OracleAPI\APi.js:87:5)

    I believe its this line in the code:
    var connectString = process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR.replace(“PDB1”, “demos”);

    Then I realised its for ACC, and Im using Oracle 11 XE, Can we create a similar NodeJS rest API for oracle 11 XE which is locally installed?

    Any help is appreciated.

    1. Hi Nishad,

      I believe you should be able to make it work for 11g XE. The Oracle Node Database Driver supports 11g XE. However, the connectstring is constructed differently 0 without PDB and Root Database of course. If you find and use the right connect string syntax, then I expect this should work on 11g XE. Good luck. Note: you could consider switching to XE 18c. Still free – a little bit bigger – much more functionality and the use of PDBs.

      Kind regards
      Lucas

  3. I am trying to execute this query but getting error “Error: ORA-01036: illegal variable name/number”.
    var selectStatement = “ALTER user hr IDENTIFIED BY :password”;

    connection.execute(selectStatement,[password],

    {
    outFormat: oracledb.OBJECT
    },

  4. Good article. One slight oversight: you don’t need the require on the http-module when you require express. Look at your final code. You never use the variable http. So why declare it.

    What do you think about the status of the node-oracledb? Is it ready for a grown-up production system?
    It’s an opensource project by Oracle, but I can’t get my finger on it if its ready to build production-ready applications.

    1. Hi Edwin,

      Thanks for the correction. You are right of course.
      The status of node-oracledb is a little hard to assess. It is quite serious for Oracle. The fact that it is open source should not distract you about Oracle’s level of involvement: it is critical to Oracle that the world of Node.js and of server side JavaScript applications will find it easy to integrate with the Oracle Database. This driver must ensure that no development team walks away from the Oracle Database in favor of another database because it is too cumbersome or unreliable and buggy to interact with the database. Rest assured that Oracle closely monitors this open source project and has resources assigned to the project. The fact that this driver is available out of the box in the new Oracle Application Container Cloud service is another indication about the importance of the driver. It would be quite unfortunate for Oracle to try to attract many customers to this cloud service, have them set up interactions from their Node.js applications with their DBaaS instance only to run into hardship.

      Having said that, how much actual use is currently made of the driver is difficult to determine. And of course, the proof is in the eating. I have not yet eaten much myself and I do not know how much others have eaten. Given that the driver is really primarily a JavaScript style wrapper around the database client that has been around for decades and powers huge numbers of serious users and applications, I would guess that you are not taking enormous risks by relying on it.

      kind regards,

      Lucas

Comments are closed.

Next Post

Deploying an Oracle JET application to Application Container Cloud and running on Node.js

An article from the Oracle A-Team (http://www.ateam-oracle.com/oracle-jet-with-nodejs/) describes how to run a sample Oracle JET application on a Node.js server. I have followed the steps in that article, ran the JET application locally and then configured the application for deployment to the Oracle Application Container Cloud. Subsequently, I created a […]
%d bloggers like this: