Developing RESTful APIs using Node.js, Express and Oracle Database (NEO)


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.


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


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.



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


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.

Loading modules

Copy-paste friendly code available at

Connecting to Oracle Database and executing SQL statements

While 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’ –

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’ –

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’ –

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.

Server Command

Code –

The entire project is available at


Connection pooling

Connection pooling is recommended for efficiency. Please refer to and


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.



  1. Oracle Database driver for Node.js maintained by Oracle Corp
  2. Oracle Node.js Developer Center
  3. JSONLint – The JSON Validator
  4. HTTP Status Code Definitions
  5. REST API Design Rulebook By Mark Masse – O’Reilly Media

44 thoughts on “Developing RESTful APIs using Node.js, Express and Oracle Database (NEO)

    1. 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 :



  1. 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?


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


    1. Hi Ian,
      In this code ‘bind parameters’ are used for security. Please see the note from the documentation at:

      “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:

      Hope this helps!



  3. Hi Great Job !
    Please could you just replace npm install body-parse –save by npm install body-parser –save ….the “r” is missing
    Thanks !


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


    1. 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 – If not, you can use the XMLHttpRequest Web API –

      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.



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


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


  5. Hi ,
    This is the error i got while create a record
    throw new Error(getErrorMessage(errorCode, messageArg1));

    Error: NJS-009: invalid number of parameters

    Please help me to resolve this one.


  6. Thanks if I am developing front end with angular, then will these steps change?Please help, I am new to node,angular and oracle.


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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s