Creating a REST API with Node.js and Oracle Database

danmcghan-Oracle

I've started a new series: Creating a REST API with Node.js and Oracle Database

The current plan is to touch on the following core topics before going into details on specific features and techniques:

• Web Server Basics
• Database Basics
• Handling GET requests
• Handling PUT, POST, and DELETE requests
• Adding pagination, sorting, and filtering to GET request

I'm making the code available in our oracle-db-examples repo.

Your questions and comments are welcome!

blessed DBA

How can we handle the Large data using this this will make any slowness

danmcghan-Oracle

There are a number of variables that would come into play (result set size, number of concurrent users, available mid-tier memory, etc.) but typically streams are used to deal with very large result sets. I plan to cover streams at some point in the future. The next post will cover pagination, which can also help if the client doesn't need the entire result set.

Christopher Jones-Oracle

Do you mean data with long values, e.g. LOBs?  Or do you mean a large number of data items / rows?

3422961

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"

}

Christopher Jones-Oracle

@3422961 check the examples on using node-oracledb at https://github.com/oracle/node-oracledb/tree/master/examples particularly the webapp.js and webapppromises.js files. Note how they create and use the connection pool.

If you want another example of a web service, look at https://blogs.oracle.com/opal/a-node-oracledb-web-service-in-docker  The main server.js file is here and the complete set of files is here.

3422961

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

3422961

Hi CJ,

Please help me to resolve above query.

3422961

Hi all,

Anyone please help me to resolve my above mentioned issue.

Appreciate your response.

3422961

Hi all,

Still didnt find solution for this.Please help me to resolve this.

blessed DBA

Change the port and try ..

3725182

@3422961 Unless I'm misreading your code, you are calling connection.execute and passing 7 parameters:

1. "INSERT INTO TEST_TABLE VALUES (:s,:p)"
2. { s: JSON.stringify(req.body) }
3. { p: JSON.stringify(req.body) }
4. console.log("check1")
5. { autoCommit: true }
6. console.log("check2")
7. function (err) {...

Please review the API doc to understand the correct number and types of parameters to pass. Also, please review the link in the start of this post which contains details on how to create such a REST API correctly.

3422961

Hi,

I changed the port and tried. still am getting the same error.

blessed DBA

Can u Copy paste the error ????

3422961

Hi,

This what the error i got.

blessed DBA

Can u please share the script code ????

blessed DBA

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

use this  change

connection.query('SELECT * from employees where column_name in ', function(err, rows, fields)