Introduction
This post explains developing RESTful APIs using Node.JS, Express and Oracle Database. Let us refer to this stack as NEO. This is intended for beginners to learn the basics of these technologies using a very simple example, a light weight ‘User Profile Management’ system. Before we look at the installation and implementation, we will specify the requirement and design.
Requirement
Provide REST API for User Profile Management. User profile typically contains name, description, age, gender and country etc. The following should be possible using REST APIs.
- Create a new user profile
- Read all the user profiles
- Read a user profile
- Update an existing user profile
- Delete an user profile
Design
Design Principles
Design is very important and with a good design in place, implementation will be faster. The following basic design principles are used in the REST API design and implementation.
URI as noun
URIs represent the Resources and Collection of resources in a hierarchical directly structure.
HTTP methods as CRUD verbs
HTTP methods define the operations, as verbs. Create, Read, Update and Delete (CRUD) operations on the Resources are performed by HTTP methods POST, GET, PUT and DELETE respectively.
Standard URIs
URIs are all lower case. No query strings are in the URI. The content is passed as JSON.
JSON everywhere
Content-Type header field describes the nature of the data in the body. Our APIs will support only application/json for both request and response. In other words, our APIs will accept only JSON data in the request and provide JSON data as response. Even error messages will be JSON.
Authentication and Versioning
To keep this post simple, Authentication and Versioning aspects of the API are not covered.
Status codes
The following status codes will be used to best describe the response.
200 OK successful GET
201 Created for successful POST. URI for the created resource is specified in the Location header field
204 No Content for successful PUT and DELETE. No message body.
400 Bad Request with error, if the new resource to be created through POST already exists
404 Not Found with error, if GET or PUT has not found anything matching the Request-URI
415 Unsupported Media Type with error, if POST or PUT if the request body is not in application/json MIME type
500 Internal Server Error with error, if server encountered an unexpected error while processing the request
REST APIs
HTTP METHOD | URI | DESCRIPTION |
---|---|---|
POST | /userprofiles | Create a new user profile |
GET | /userprofiles | Read all the user profiles |
GET | /userprofiles/:USER_NAME | Read the profile of an user |
PUT | /userprofiles/:USER_NAME | Update the profile of an user |
DELETE | /userprofiles/:USER_NAME | Delete the profile of user specified in place of :user |
User Profile JSON
An example of the JSON user profile is shown below. For simplicity, the attribute names are all uppercase.
Installation
Intentionally, any scaffolding tools like yoman are not used. Manually installing the software and creating the source file keeps the project structure simple and easy to understand.
Node JS
Node JS can be downloaded and installed from https://nodejs.org/download/ . It also installs “npm”, a package manager which will be used in this example.
Express and Body parser installation
Node JS is installed globally. The rest of the dependency modules will be installed in the application directory locally. To start with, let us create the application root directory, say “userprofiles” and do “npm init”. It asks some questions and creates package.json, a file which has the metadata about the project like name, version, description etc and dependencies. Then for every dependency modules, as part of the installation we will pass “–save”, which saves the dependency in the package.json.
Express – Express is a minimal and flexible Node.js web application framework
body-parser – Node.js body parsing middleware
mkdir userprofiles cd userprofiles npm init npm install express --save npm install body-parser --save
You can notice that these are installed under userprofiles/node_modules.
node-oracledb installation
node-oracledb is the Oracle Database Node.js driver that powers high performance Node.js applications. Please refer Installation section in https://github.com/oracle/node-oracledb . It has detailed instructions for installation. After following the platform specific steps, finally the following command can be run to install the node-oracledb.
cd userprofiles npm install oracledb --save
Creating the user_profile table
Create the following table in your Oracle Database. It represents the JSON user profile that was mentioned earlier. A resource is a row in the table.
Copy-paste friendly statement available at https://github.com/sbalagop/neo/blob/master/create.sql
Development
Let us create an empty file “nserver.js” under the application root “userprofiles”. This is the only source file for the project. I prefer using Brackets editor for editing. We will run this file later using Node.js command line.
Loading modules and defining Connection string
Use the Node JS require() to load the modules. We load Express, body-parser and oracledb which we installed earlier using npm. We use express() to create an Express application. Since we only support JSON as the request body, we only use the JSON body parser.
Copy-paste friendly code available at https://github.com/sbalagop/neo/blob/master/nserver.js
Connecting to Oracle Database and executing SQL statements
While https://github.com/oracle/node-oracledb contains the detailed documentation, the basic APIs that are used in our example are explained below.
void getConnection(Object connAttrs, function(Error error, Connection conn){});
oracledb.getConnection() asynchronously obtains a connection to the Oracle DB provided in the connection string and invokes the callback function with the Connection object.
void execute(String sql, [Object bindParams, [Object options,]] function(Error error, [Object result]){});
With the Connection object available in the callback function, calling Connection.execute() will execute the SQL statement and invoke the callback function with the result.
These two APIs are used in all the HTTP methods described in the following sections.
GET /userprofiles – Read all the user profiles
app.get(‘/userprofiles’, callback) will route HTTP GET requests for the /userprofiles to the the callback function. This callback needs to return all the user profiles in the USER_PROFILE table from the Oracle DB. Once the connection is obtained, the simple SQL query “SELECT * FROM USER_PROFILES” is executed. The result is available in result.rows as an object, since we passed “outFormat: oracledb.OBJECT” option. JSON.stringify(result.rows) is used to convert the object to JSON string. The status code is set to 200 and Content-Type to application/json and response is sent back.
GET /userprofiles/:USER_NAME – Read the profile of an user
Getting the connection is the same as in the previous methods. :USER_NAME is accessible using req.params.userName. It is passed as the bind parameter value for :USER_NAME in the WHERE clause.
POST /userprofiles Create a new user profile
If the user profile is not already in the table, a new one is created. User passes the user profile data as JSON in the request body. body-parser parses the JSON and makes it accessible in req.body. INSERT statement is used to insert a new user profile row in the table. The input JSON values available in req.body are passed as bind parameter values. “isAutoCommit: true” option is passed, which will result in a transaction commit after the SQL statement is executed. We set the status to 201 and set the location of this user profile in the Location header.
Post node-oracledb 0.5, ‘isAutoCommit ‘ is renamed to ‘autoCommit’. I have updated the sample to use the ‘autoCommit’ – https://github.com/sbalagop/neo/blob/master/nserver.js
PUT /userprofiles/:USER_NAME – Update the profile of an user
We need to build the UPDATE statement and the bind parameter values with JSON parsed values in req.body. The following utility function is used to do that. Given the request it returns the UPDATE statement and the bind parameter values.
UPDATE SQL statement is executed using connection.execute(). If the user profile to be updated is not found, return 404 error. If the resource is successfully updated, status 204 is returned with an empty response body.
Post node-oracledb 0.5, ‘isAutoCommit ‘ is renamed to ‘autoCommit’. I have updated the sample to use the ‘autoCommit’ – https://github.com/sbalagop/neo/blob/master/nserver.js
DELETE /userprofiles/:USER_NAME – Delete the profile of an user
DELETE statement is passed in connection.execute() to delete the specified row. If the resource is successfully deleted, status 204 is returned with an empty response body.
Post node-oracledb 0.5, ‘isAutoCommit ‘ is renamed to ‘autoCommit’. I have updated the sample to use the ‘autoCommit’ – https://github.com/sbalagop/neo/blob/master/nserver.js
Starting the Node/Express Server
Similar to Node’s http.Server.listen(), Express’s app.listen(3000, callback) binds and listens for connections in the localhost and port 3000.
After this, save the nserver.js and run the command ‘node server.js”. It will start the server.
Code – https://github.com/sbalagop/neo
The entire project is available at https://github.com/sbalagop/neo
Enhancements
Connection pooling
Connection pooling is recommended for efficiency. Please refer to https://github.com/oracle/node-oracledb/blob/master/doc/api.md#connpooling and https://github.com/oracle/node-oracledb/blob/master/examples/webapppromises.js
Testing
I use the free Chrome App – Postman REST Client (Packaged App) to test the REST APIs. An example of POST method testing is given below.
References
- Oracle Database driver for Node.js maintained by Oracle Corp
- Oracle Node.js Developer Center
- JSONLint – The JSON Validator
- HTTP Status Code Definitions
- REST API Design Rulebook By Mark Masse – O’Reilly Media
Updated the code to release the connection.
LikeLike
good work
LikeLike
Thanks FS!
LikeLike
Thank you so, so much!
LikeLiked by 1 person
Bruno, You’re welcome!
LikeLike
Thanks! Very helpful to get a jump start on Oracle with node.js.
LikeLike
You’re welcome, Osman!
LikeLike
thank you sooooooo much 🙂
LikeLike
You’re welcome, Marwa!
LikeLike
very nice article.!!
LikeLike
Thanks, Lakshmi!
LikeLike
Updated package.json to use oracledb version 1.11.0 at https://github.com/sbalagop/neo
LikeLike
Why no connection pooling?
LikeLike
Hi David,
Connection pooling is recommended for efficiency. As I mentioned in the beginning of the post, this post is only intended for beginners to learn the basics of these technologies and to get started. I leave it to the readers to further enhance this solution as needed. Please refer to the following document for connection pooling : https://github.com/oracle/node-oracledb/blob/master/doc/api.md#connpooling
regards,
Siva
LikeLike
No worries wasn’t trying to offend. I thought it was a good article. I think the documents are very clear but some things are left to the user to figure out pooling being one of those. I found https://github.com/oracle/node-oracledb/blob/master/examples/webapppromises.js to be a great resource for it.
Thanks again, Dave
LikeLike
Hi Dave,
I appreciate your comment. I have added a section Enhancements/Connection pooling and provided links for further reading. I hope this helps others. Thanks for your input.
regards,
Siva
LikeLike
Siva –
It is a great article for the beginners like me. I just have a couple of questions while installing the oracledb.
Is it mandatory that we need to have visual studio installed or just Microsoft Visual C++ Redistributable package alone is sufficient? In both cases, what version did you use for Oracle 11g XE database as shown in your example?
LikeLike
Hi Ramu, The following links have details about installing node-oracledb in Windows.
https://github.com/oracle/node-oracledb/blob/master/INSTALL.md#instwin
or
https://community.oracle.com/docs/DOC-931127
If you have questions on node-oracledb installation, please post it in:
https://github.com/oracle/node-oracledb/issues
I used Oracle Database Express Edition 11g Release 2
regards,
Siva
LikeLike
My bad, i should have asked which version of Visual Studio you used along with 11g XE Release 2?
LikeLike
I installed node-oracledb on Mac, so Xcode was needed instead of Visual Studio. Please post your installation questions in https://github.com/oracle/node-oracledb/issues
regards,
Siva
LikeLike
Hi Siva,
Your article was very helpful. I really aprreciate it.
I wanna ask you a question about security of your code.
Are they safe from SQL injection?
I’m a beginner so I cannot judge it is safe or not.
If it is not, I hope to listen your advice about how to avoide SQL injection problems.
Thank you, Ian.
LikeLike
Hi Ian,
In this code ‘bind parameters’ are used for security. Please see the note from the documentation at: https://github.com/oracle/node-oracledb/blob/master/doc/api.md#bind
“Using bind parameters is recommended in preference to constructing SQL or PL/SQL statements by string concatenation. This is for performance and security.”
Also, see this relevant discussion:
http://stackoverflow.com/questions/38015313/preventing-sql-injection-in-node-oracledb
Hope this helps!
regards,
Siva
LikeLike
Thanks so much! It was very helpful!
LikeLike
You are welcome! I am glad it helped.
LikeLike
Hi Great Job !
Please could you just replace npm install body-parse –save by npm install body-parser –save ….the “r” is missing
Thanks !
LikeLike
Hi, Thanks. I have updated it, thanks!.
cheers!
Siva
LikeLike
Thank You Very Much… Great Man ^^
LikeLike
You’re welcome!
LikeLike
I would want to consume these API’s through a web page (HTML) which would be integrated on the Tableau.
The webpage will have one input free text option, where the user can type in to update the record.
i.e. If I click on one record on the Tableau dashboard, the API ‘GET /userprofiles/:USER_NAME – Read the profile of an user’ will be called, it will display the detailed record with one text box as free text, once the user enters text and hits submit, ‘PUT /userprofiles/:USER_NAME – Update the profile of an user’ API will be called, which will update the record. Also, my webpage is hosted in node.js server. Since I am pretty new to Node.js and found your blog really helpful, hence writing with the tad bit of ray of hope.
Thank you.
LikeLike
Hi,
Let us breakdown the requirements and design each part.
1. Hosting the HTML
It is better to host the html in a different Node.js server. So, you would have one backend Node.js server for exposing the APIs (as explained in this blog) and one frontend Node.js server for hosting the html.
2. Making the REST API call from UI
If you are already using JQuery in the frontend, it is best to use jQuery.ajax() API to make GET/POST/PUT requests. This page has examples at the end – http://api.jquery.com/jquery.ajax/. If not, you can use the XMLHttpRequest Web API – https://developer.mozilla.org/en-US/docs/Web/API/XMLHttpRequest
3. Building User Interface
If you already use a JavaScript library or framework to build user interface, then you may continue use that one. Otherwise, there are many choices – Knockout.js with jQuery, React, Backbone.js with jQuery, Oracle JET etc.
Hope this helps as a starting point.
cheers!
Siva
LikeLike
Hi Shefalijha,
I don’t know how to consume this API through webpage(HTML), would you please share me the code snippet if possible. It will be more useful for me.
LikeLike
HI shefalijha,
I would like to do the same.
Can you send me the piece of code to do that.
It will be more helpful to me.
Thanks,
Karthick
LikeLike
Thanks !!!
LikeLike
Hi,
This is soo helpful to me…Such a great one.
LikeLike
Thanks!
LikeLike
Hi,
But i got error in one point while create a record.I describe it in this link.Please help me to resolve this one.
https://community.oracle.com/thread/4133614
LikeLike
Hi ,
This is the error i got while create a record
C:\node\oracle\node_modules\oracledb\lib\util.js:80
throw new Error(getErrorMessage(errorCode, messageArg1));
^
Error: NJS-009: invalid number of parameters
Please help me to resolve this one.
LikeLike
Hi,
Still i didnt find solution,
Please help me to resolve this.
LikeLike
Thanks if I am developing front end with angular, then will these steps change?Please help, I am new to node,angular and oracle.
LikeLike
Amazing post, this help a lot. But I have one question:
How can I do pagination with OracleDB and Express.JS? Thanks a lot
LikeLike
Yoooo dat’s da best guide for express and OracleDB that I have seen. Thank you. Could do another one with Express and OracleDB too but with controllers too? Thank you so much. You’re the best!!!
LikeLike
Hi ! Thanks for the article ! I based a SO question on your examples. Feel free to contribute !
https://stackoverflow.com/questions/55106847/get-method-why-cant-i-return-the-values-from-an-oracle-view/55106986#55106986
LikeLike
Via req.body for POST requests it is ok? Because my app.post is returning undefined :((
LikeLike
When I call the service of Angular for a POST, it returns undefined in the Oracles Database
LikeLike