Creating a REST API with Node.js and Oracle Database

Hi all,

I try to make API with node js, express, and OracleDB. My sample code in server.js is below.I got an error while create an api in command line interface which is Connection.connect is not a function. Please help me to resolve this one.

server.js

var express = require('express');

var oracledb = require('oracledb');

var app = express();

var dbConfig = require('./dbconfig.js');

// Get a non-pooled connection

var connection = oracledb.getConnection(

  {

    user          : dbConfig.user,

    password      : dbConfig.password,

    connectString : dbConfig.connectString

  })

  connection.connect(function(err){

if(!err) {

    console.log("Database is connected ... nn");   

} else {

    console.log("Error connecting database ... nn");   

}

});

app.get("/",function(req,res){

connection.query('SELECT * from employees', function(err, rows, fields) {

connection.end();

  if (!err)

    console.log('The solution is: ', rows);

  else

    console.log('Error while performing Query.');

  });

});

app.listen(3000);

----------------------------------------------------------

dbconfig.js 

module.exports = {

  user          : "xxx",

  password      : "xxxx",

  connectString : "localhost/XE"

}

Hi CJ,

Thanks for your response.It really helpful to me.

But am getting stuck in one point.

I received following error while create a record using POST method.Please help me to resolve this.

Here is my script;

var express = require('express');

var oracledb = require('oracledb');

var app = express();

var dbConfig = require('./dbconfig.js');

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

var port = 3000;

app.use(bodyParser.json()); // Use body parser to parse JSON body

oracledb.outFormat = oracledb.OBJECT;

// Get a non-pooled connection

function run() {

oracledb.createPool({

      user          : dbConfig.user,

    password      : dbConfig.password,

    connectString : dbConfig.connectString

    },

    function(err) {

      if (err)

        console.error("createPool() error: " + err.message);

      else

        var server = app.listen(port,

          function () {

            console.log('Server is listening on port ' + server.address().port);

          });

    });

}

function doGetConnection(res, cb) {

  oracledb.getConnection(function (err, connection) {

    if (err) {

      res.set('Content-Type', 'application/json');

      res.status(500).send(JSON.stringify({

        status: 500,

        message: "Error getting DB connection",

        detailed_message: err.message

      }));

    } else {

      cb(err, connection);

  console.log("  Connection is connected");

    }

  });

}

app.get('/index.htm', function (req, res) {

   res.sendFile( __dirname + "/" + "index.htm" );

})

app.post('/process_post', function (req, res) {

console.log("contenttype"+req.get('Content-Type'))

  doGetConnection(res, function(err, connection) {

    if (err)

      return;

    connection.execute(

      "INSERT INTO TEST_TABLE VALUES (:s,:p)",

      { s: JSON.stringify(req.body) },

  { p: JSON.stringify(req.body) },

  console.log("check1"),

      { autoCommit: true },

   console.log("check2"),

      function (err) {

  console.log("check3");

        if (err) {

console.log("check4");

          res.set('Content-Type', 'application/json');

          res.status(400).send(JSON.stringify({

            status: 400,

            message: "Input Error",

            detailed_message: err.message

          }));

        } else {

          // Successfully created the resource

          res.status(201).set('Location', '/process_post/' + req.body.FIRST_NAME+req.body.LAST_NAME).end();

        }

        doRelease(connection, "POST /process_post");

      });

  });

});

function doRelease(connection, message) {

  connection.close(

    function(err) {

      if (err)

        console.error(err);

      else

        console.log(message + " : Connection released");

    });

}

run();

--------------------------------------------------------------

TABLE SCRIPT

CREATE TABLE TEST_TABLE ( "FIRST_NAME" VARCHAR2(200 BYTE), "LAST_NAME" VARCHAR2(200 BYTE) )

Hi all,

Anyone please help me to resolve my above mentioned issue.

Appreciate your response.

Change the port and try ..