Oracle Application Container Cloud Service: Building a RESTful API with Node.js and Express

Purpose

This tutorial shows you how to develop a RESTful API in Node.js using the Express framework and an Oracle Database Cloud Service instance to deploy it in Oracle Application Container Cloud Service.

Time to Complete

45 minutes

Background

Express is a Node.js web application framework that provides a robust set of features to develop web and mobile applications. It facilitates a rapid development of Node based Web applications.

Scenario

In this tutorial, you build a basic RESTful API that implements the CRUD (Create, Read, Update, and Delete) operations on an employee table in a Oracle Database Cloud Service instance using plain Node.js and the Express framework.

Database diagram:

View Image

The Node.js RESTful application responds to the following endpoints:

The HTML client to test the RESful API is provided in the next section (What do you need?) The zip file contains an index.html file and seven .png images. This client is developed using JQuery, Ajax and CSS.

What Do You Need?

• Node.js 4.x
  
• A text editor
• A web browser
• Oracle SQL Developer
• An active Oracle Cloud account
• An instance of Oracle Database Cloud Service
• A user and password to connect Oracle SQL Developer to your instance (If you don't have an instance, you can create one by following this tutorial.)
• cURL 7.0+ (cURL is installed by default on most UNIX and Linux distributions. For the steps to install cURL on a Windows 64-bit machine, click here).
• employee-client.zip
• node-employees-service.zip (The complete project with the source code ready to deploy in Oracle Application Container Cloud Service)

In this section, you create a connection to your Oracle Database Cloud Service instance from Oracle SQL Developer and execute the scripts to create the employee table and sequence and to insert a couple of employee rows.

In this section, you create the REST Service and you use the NPM utility to download and build dependencies for your Node.js project.

1. Open a console window and go to the folder where you want to store the Node.js application server.

   View Image

   

2. Run npm init to create the package.json file. At the prompt, enter the following values, confirm the values, and then press Enter:

   • Name: node-server
   • Version: 1.0.0 (or press Enter.)
   • Description: Employee RESTful application
   • Entry point: server.js
   • Test command (Press Enter.)
   • Git repository (Press Enter.)
   • Keywords (Press Enter.)
   • Author (Enter your name or email address.)
   • License (Press Enter.)

   View Image

   

   The package.json file is created and stored in the current folder. You can open it and modify it, if needed.

3. In the console window, download, build, and add the Express framework dependency:

   npm install --save express

   View Image

   

4. In the console window, install the body-parser dependency:

   npm install --save body-parser

   The body-parser dependency is a Node.js middleware for handling JSON, Raw, Text and URL encoded form data.

   View Image

   

   Note: If the console displays optional, dep failed or continuing output, ignore it. The output pertains to warnings or errors caused by dependencies on native binaries that couldn't be built. The libraries being used often have a JavaScript fallback node library, and native binaries are used only to optimize performance.

5. Open the generated package.json file in a text editor, and verify its contents. It should look like this:

   View package.json

   {
     "name": "node-server",
     "version": "1.0.0",
     "description": "Employee RESTful application",
     "main": "server.js",
     "scripts": {
       "test": "echo \"Error: no test specified\" && exit 1",
       "start": "node server.js"
     },
     "author": "",
     "license": "ISC",
     "dependencies": {
       "body-parser": "^1.14.1",
       "express": "^4.13.3"
     }
   }
   

6. Create a server.js file, open it in a text editor, and add the following require statements to use the node dependencies and the oracledb server component:

   var express = require('express');
   var bodyParser = require('body-parser');
   var oracledb = require('oracledb');

7. Add a PORT variable equal either to the process.env.PORT environment variable or to 8089, if the environment variable isn't set:

   The PORT environment variable is set automatically by Oracle Application Container Cloud Service.

   var PORT = process.env.PORT || 8089;

8. Create an app variable to use the express method:

   var app = express();

9. Store the database connection properties that are equal to environment variables or defaults:

   The environment variables listed are set in Oracle Application Container Cloud Service automatically when you add the Database Cloud Service binding.

   var connectionProperties = {
     user: process.env.DBAAS_USER_NAME || "oracle",
     password: process.env.DBAAS_USER_PASSWORD || "oracle",
     connectString: process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR || "localhost/xe"
   };

10. Create the doRelease method to release the database connection:

    function doRelease(connection) {
      connection.release(function (err) {
        if (err) {
          console.error(err.message);
        }
      });
    }

11. Configure your application to use bodyParser(), so that you can get the data from a POST request:

    // configure app to use bodyParser()
    // this will let us get the data from a POST
    app.use(bodyParser.urlencoded({ extended: true }));
    app.use(bodyParser.json({ type: '*/*' }));

12. Create a router object and assign it to the router variable:

    var router = express.Router();

13. Add the following response headers to support calls from external clients:

    Note: Browsers and applications usually prevent calling REST services from different sources. If you run the client on Server A and the REST services on Server B, then you must provide a list of known clients in Server B by using the Access-Control headers. Clients check these headers to allow invocation of a service and prevent cross-site scripting attacks (XSS).

    router.use(function (request, response, next) {
      console.log("REQUEST:" + request.method + "   " + request.url);
      console.log("BODY:" + JSON.stringify(request.body));
      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);
      next();
    });

14. Create the GET method to get the list of employees:

    View code

    /**
     * GET / 
     * Returns a list of employees 
     */
    router.route('/employees/').get(function (request, response) {
      console.log("GET EMPLOYEES");
      oracledb.getConnection(connectionProperties, function (err, connection) {
        if (err) {
          console.error(err.message);
          response.status(500).send("Error connecting to DB");
          return;
        }
        console.log("After connection");
        connection.execute("SELECT * FROM employee",{},
          { outFormat: oracledb.OBJECT },
          function (err, result) {
            if (err) {
              console.error(err.message);
              response.status(500).send("Error getting data from DB");
              doRelease(connection);
              return;
            }
            console.log("RESULTSET:" + JSON.stringify(result));
            var employees = [];
            result.rows.forEach(function (element) {
              employees.push({ id: element.ID, firstName: element.FIRSTNAME, 
                               lastName: element.LASTNAME, email: element.EMAIL, 
                               phone: element.PHONE, birthDate: element.BIRTHDATE, 
                               title: element.TITLE, dept: element.DEPARTMENT });
            }, this);
            response.json(employees);
            doRelease(connection);
          });
      });
    });

15. Create the GET method to return the list of employees that match the criteria.

    /**
     * GET /searchType/searchValue 
     * Returns a list of employees that match the criteria 
     */
    router.route('/employees/:searchType/:searchValue').get(function (request, response) {
      console.log("GET EMPLOYEES BY CRITERIA");
      oracledb.getConnection(connectionProperties, function (err, connection) {
        if (err) {
          console.error(err.message);
          response.status(500).send("Error connecting to DB");
          return;
        }
    	console.log("After connection");
    	var searchType = request.params.searchType;
    	var searchValue = request.params.searchValue;
    	  
        connection.execute("SELECT * FROM employee WHERE "+searchType+" = :searchValue",[searchValue],
          { outFormat: oracledb.OBJECT },
          function (err, result) {
            if (err) {
              console.error(err.message);
              response.status(500).send("Error getting data from DB");
              doRelease(connection);
              return;
            }
            console.log("RESULTSET:" + JSON.stringify(result));
            var employees = [];
            result.rows.forEach(function (element) {
              employees.push({ id: element.ID, firstName: element.FIRSTNAME, 
    		                   lastName: element.LASTNAME, email: element.EMAIL, 
    		                   phone: element.PHONE, birthDate: element.BIRTHDATE, 
    						   title: element.TITLE, dept: element.DEPARTMENT });
            }, this);
            response.json(employees);
            doRelease(connection);
          });
      });
    }); 

16. Create the POST method to add employees:

    View code

    /**
     * POST / 
     * Saves a new employee 
     */
    router.route('/employees/').post(function (request, response) {
      console.log("POST EMPLOYEE:");
      oracledb.getConnection(connectionProperties, function (err, connection) {
        if (err) {
          console.error(err.message);
          response.status(500).send("Error connecting to DB");
          return;
        }
    
        var body = request.body;
    
        connection.execute("INSERT INTO EMPLOYEE (ID, FIRSTNAME, LASTNAME, EMAIL, PHONE, BIRTHDATE, TITLE, DEPARTMENT)"+ 
                           "VALUES(EMPLOYEE_SEQ.NEXTVAL, :firstName,:lastName,:email,:phone,:birthdate,:title,:department)",
          [body.firstName, body.lastName, body.email, body.phone, body.birthDate, body.title,  body.dept],
          function (err, result) {
            if (err) {
              console.error(err.message);
              response.status(500).send("Error saving employee to DB");
              doRelease(connection);
              return;
            }
            response.end();
            doRelease(connection);
          });
      });
    });

17. Create the PUT method to update the employee by ID:

    View code

    /**
     * PUT / 
     * Update a employee 
     */
    router.route('/employees/:id').put(function (request, response) {
      console.log("PUT EMPLOYEE:");
      oracledb.getConnection(connectionProperties, function (err, connection) {
        if (err) {
          console.error(err.message);
          response.status(500).send("Error connecting to DB");
          return;
        }
    
        var body = request.body;
        var id = request.params.id;
    
        connection.execute("UPDATE EMPLOYEE SET FIRSTNAME=:firstName, LASTNAME=:lastName, PHONE=:phone, BIRTHDATE=:birthdate,"+
                           " TITLE=:title, DEPARTMENT=:department, EMAIL=:email WHERE ID=:id",
          [body.firstName, body.lastName,body.phone, body.birthDate, body.title, body.dept, body.email,  id],
          function (err, result) {
            if (err) {
              console.error(err.message);
              response.status(500).send("Error updating employee to DB");
              doRelease(connection);
              return;
            }
            response.end();
            doRelease(connection);
          });
      });
    });

18. Create the DELETE method to remove employees by ID:

    View code

    /**
     * DELETE / 
     * Delete a employee 
     */
    router.route('/employees/:id').delete(function (request, response) {
      console.log("DELETE EMPLOYEE ID:"+request.params.id);
      oracledb.getConnection(connectionProperties, function (err, connection) {
        if (err) {
          console.error(err.message);
          response.status(500).send("Error connecting to DB");
          return;
        }
    
        var body = request.body;
        var id = request.params.id;
        connection.execute("DELETE FROM EMPLOYEE WHERE ID = :id",
          [id],
          function (err, result) {
            if (err) {
              console.error(err.message);
              response.status(500).send("Error deleting employee to DB");
              doRelease(connection);
              return;
            }
            response.end();
            doRelease(connection);
          });
      });
    });

19. Set up and start the server:

    app.use(express.static('static'));
    app.use('/', router);
    app.listen(PORT);

The completed server.js should look like this:

var express = require('express');
var app = express();
var bodyParser = require('body-parser');

var oracledb = require('oracledb');
oracledb.autoCommit = true;

var connectionProperties = {
  user: process.env.DBAAS_USER_NAME || "oracle",
  password: process.env.DBAAS_USER_PASSWORD || "oracle",
  connectString: process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR || "129.152.132.76:1521/ORCL"
};

function doRelease(connection) {
  connection.release(function (err) {
    if (err) {
      console.error(err.message);
    }
  });
}

// configure app to use bodyParser()
// this will let us get the data from a POST
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json({ type: '*/*' }));

var PORT = process.env.PORT || 8089;

var router = express.Router();

router.use(function (request, response, next) {
  console.log("REQUEST:" + request.method + "   " + request.url);
  console.log("BODY:" + JSON.stringify(request.body));
  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);
  next();
});

/**
 * GET / 
 * Returns a list of employees 
 */
router.route('/employees/').get(function (request, response) {
  console.log("GET EMPLOYEES");
  oracledb.getConnection(connectionProperties, function (err, connection) {
    if (err) {
      console.error(err.message);
      response.status(500).send("Error connecting to DB");
      return;
    }
    console.log("After connection");
    connection.execute("SELECT * FROM employee",{},
      { outFormat: oracledb.OBJECT },
      function (err, result) {
        if (err) {
          console.error(err.message);
          response.status(500).send("Error getting data from DB");
          doRelease(connection);
          return;
        }
        console.log("RESULTSET:" + JSON.stringify(result));
        var employees = [];
        result.rows.forEach(function (element) {
          employees.push({ id: element.ID, firstName: element.FIRSTNAME, 
                           lastName: element.LASTNAME, email: element.EMAIL, 
                           phone: element.PHONE, birthDate: element.BIRTHDATE, 
                           title: element.TITLE, dept: element.DEPARTMENT });
        }, this);
        response.json(employees);
        doRelease(connection);
      });
  });
});

/**
 * GET /searchType/searchValue 
 * Returns a list of employees that match the criteria 
 */
router.route('/employees/:searchType/:searchValue').get(function (request, response) {
  console.log("GET EMPLOYEES BY CRITERIA");
  oracledb.getConnection(connectionProperties, function (err, connection) {
    if (err) {
      console.error(err.message);
      response.status(500).send("Error connecting to DB");
      return;
    }
    console.log("After connection");
    var searchType = request.params.searchType;
    var searchValue = request.params.searchValue;
      
    connection.execute("SELECT * FROM employee WHERE "+searchType+" = :searchValue",[searchValue],
      { outFormat: oracledb.OBJECT },
      function (err, result) {
        if (err) {
          console.error(err.message);
          response.status(500).send("Error getting data from DB");
          doRelease(connection);
          return;
        }
        console.log("RESULTSET:" + JSON.stringify(result));
        var employees = [];
        result.rows.forEach(function (element) {
          employees.push({ id: element.ID, firstName: element.FIRSTNAME, 
                           lastName: element.LASTNAME, email: element.EMAIL, 
                           phone: element.PHONE, birthDate: element.BIRTHDATE, 
                           title: element.TITLE, dept: element.DEPARTMENT });
        }, this);
        response.json(employees);
        doRelease(connection);
      });
  });
});

/**
 * POST / 
 * Saves a new employee 
 */
router.route('/employees/').post(function (request, response) {
  console.log("POST EMPLOYEE:");
  oracledb.getConnection(connectionProperties, function (err, connection) {
    if (err) {
      console.error(err.message);
      response.status(500).send("Error connecting to DB");
      return;
    }

    var body = request.body;

    connection.execute("INSERT INTO EMPLOYEE (ID, FIRSTNAME, LASTNAME, EMAIL, PHONE, BIRTHDATE, TITLE, DEPARTMENT)"+ 
                       "VALUES(EMPLOYEE_SEQ.NEXTVAL, :firstName,:lastName,:email,:phone,:birthdate,:title,:department)",
      [body.firstName, body.lastName, body.email, body.phone, body.birthDate, body.title,  body.dept],
      function (err, result) {
        if (err) {
          console.error(err.message);
          response.status(500).send("Error saving employee to DB");
          doRelease(connection);
          return;
        }
        response.end();
        doRelease(connection);
      });
  });
});

/**
 * PUT / 
 * Update a employee 
 */
router.route('/employees/:id').put(function (request, response) {
  console.log("PUT EMPLOYEE:");
  oracledb.getConnection(connectionProperties, function (err, connection) {
    if (err) {
      console.error(err.message);
      response.status(500).send("Error connecting to DB");
      return;
    }

    var body = request.body;
    var id = request.params.id;

    connection.execute("UPDATE EMPLOYEE SET FIRSTNAME=:firstName, LASTNAME=:lastName, PHONE=:phone, BIRTHDATE=:birthdate,"+
                       " TITLE=:title, DEPARTMENT=:department, EMAIL=:email WHERE ID=:id",
      [body.firstName, body.lastName,body.phone, body.birthDate, body.title, body.dept, body.email,  id],
      function (err, result) {
        if (err) {
          console.error(err.message);
          response.status(500).send("Error updating employee to DB");
          doRelease(connection);
          return;
        }
        response.end();
        doRelease(connection);
      });
  });
});


/**
 * DELETE / 
 * Delete a employee 
 */
router.route('/employees/:id').delete(function (request, response) {
  console.log("DELETE EMPLOYEE ID:"+request.params.id);
  oracledb.getConnection(connectionProperties, function (err, connection) {
    if (err) {
      console.error(err.message);
      response.status(500).send("Error connecting to DB");
      return;
    }

    var body = request.body;
    var id = request.params.id;
    connection.execute("DELETE FROM EMPLOYEE WHERE ID = :id",
      [id],
      function (err, result) {
        if (err) {
          console.error(err.message);
          response.status(500).send("Error deleting employee to DB");
          doRelease(connection);
          return;
        }
        response.end();
        doRelease(connection);
      });
  });
});

app.use(express.static('static'));
app.use('/', router);
app.listen(PORT);
                        

In this section, you add the client files to your project and you update the index.html file to connect the client to your RESTful application.

1. Create a folder named static in the root directory of your project.

2. Unzip the employee-client.zip file in the static directory.

   Note: Make sure the index.html is in the static directory and not in a subfolder.

3. Open the index.html in a text editor.

4. Edit the server variable, set the URL of your application, and save the file.

   Note: Replace identity-domain with the entity domain of your cloud account.

   https://employees-service-identity-domain.apaas.us2.oraclecloud.com 

To ensure that your server application runs correctly in the cloud, you must:

• Bundle the application in a .zip file that includes all dependencies.
  Note: Don't bundle database drivers for Oracle Enterprise Cloud Service.
• Include a manifest.json file that specifies the command which Oracle Application Container Cloud Service should run.
• Ensure your application listens to requests on a port provided by the PORT environment variable. Oracle Application Container Cloud Service uses this port to redirect requests made to your application.

1. Open a web browser and enter the URL of the employee REST service.

   Note: Replace identity-domain with the entity domain of your cloud account.

   URL:

   https://employees-service-identity-domain.apaas.us2.oraclecloud.com/employees

   View Image

   

2. Remove the /employees part of the URL to execute the index.html.

   View Image

   

3. Click Add New.

   View Image

   

4. Fill the form and click Add.

   View Image

   

5. Select the card of the new employee.

   View Image

   

6. Click Edit.

   View Image

   

7. Change the value of the Department field and click Update.

   View Image

   

   The employee card is updated.

   View Image

   

8. Click the card of the employee and click Delete.

   View Image

   

9. Click Delete and then click Ok to confirm.

   View Image

   

   View Image

   

• Node.js website nodejs.org

• Using Oracle Application Container Cloud Service