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

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.

JSON

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.

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

Loading modules

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_ALL

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.

GET_1

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

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

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.

PUT

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.

DELETE

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.

Server

After this, save the nserver.js and run the command ‘node server.js”. It will start the server.

Server Command

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.

Postman

References

  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
Advertisements

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

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

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s