Calling node-oracledb's createPool Method

Question

I am creating a RESTful API that uses Node.js and Express. My application uses an Oracle database so I installed the node-oracledb module from npm. I've looked through the documentation and viewed some of the samples presented in the module's github page; however, I don't see any examples where a connection pool is used. Correct me if I'm wrong, but for applications that will make many calls to the database the recommendation is to use connection pools as opposed to using standalone connections. Below is a sample of the code I wrote:

createPool = function(poolAttrs, fetchPool){
  oracledb.createPool(poolAttrs, function(error, pool){
    if(error){
      console.error(`Could not create pool using specified attributes: `, error.message);
    }
    else{
      console.log(`Pool created successfully using poolAlias: ${poolAttrs.poolAlias}`);
      fetchPool(pool);
    }
  });
};

createConnection = function(poolAlias, connection){
  oracledb.getConnection(poolAlias, function(error, conn){
    if(error){
      console.error(`Could not get connection: `, error.message);
    } else {
      console.log(`New connection obtained: ${conn}`);
      connection(conn);
    }
  });
};

executeQuery = function(queryString, poolAlias){
  console.log(queryString);
  var conn = createConnection(poolAlias, function connection(conn){
    conn.execute(queryString, function(error, result){
      if(error){
        console.error(`Could not execute query: `, error.message);
      } else {
        console.log(result);
      }
      conn.close(function(error){
        if(error){
          console.error(`Could not close connection.`);
        } else {
          console.log(`Connection successfully closed.`);
        }
      })
    });
  });
}

closePool = function(pool){
  pool.close(60, function(error){
    if(error){
      console.error(`Could not close connection pool ${pool.poolAlias}`, error.message);
    } else {
      console.log(`Pool closed successfully.`);
    }
  });
};

createPool(attrs, function fetchPool(pool){
  var poolAlias = pool.poolAlias;
  console.log(poolAlias);
});

executeQuery(queryString, attrs.poolAlias);

When I run this code I get the following error:

Could not get connection:  NJS-047: poolAlias "amDBpool" not found in the connection pool cache

I know why the error is happening. As my understanding of callbacks go, I know that calling the asynchronous createPool() function with the fetchPool(pool) callback registers this callback (fetchPool) in the callback stack. All synchronous code will execute before it. So when I call executeQuery and the function reaches the line of execution where it calls createConnection(poolAlias...) the poolAlias variable is null since the createPool function is still waiting in the callback stack to execute. Hence, no pool with the alias "poolAlias" exists and the call fails. I know that I could stick the call to createPool inside the executeQuery method, but wouldn't that try to create a new pool every time I execute a query? My question is, is there a way to test inside the executeQuery method that the pool exists, and if it does then not try to recreate it. Other than this, the only other way to do this would be with an Promises or Async/Await right?

Answer 1

The node-oracledb examples using a connection pool are the connectionpool.js and examples/webap.js files. Yes, a connection pool would be a good idea in a web service. Also I recommend using the async/await style of programming.

You can see the examples creates a connection pool before doing anything else:

await oracledb.createPool({
  user: dbConfig.user,
  password: dbConfig.password,
  connectString: dbConfig.connectString
});

The returned pool from the oracledb.createPool() call is ignored because the pool is later accessed via the pool cache since no credentials are passed to getConnection():

let connection = await oracledb.getConnection();

This uses the default pool alias (which happens to be the string "default"), so no alias is specified when the pool is created or used.

Documentation and tips on using node-oracledb connection pools is in Connection Pooling.

You may be interested in Creating a REST API with Node.js and Oracle Database, A node-oracledb Web Service in Docker and Demo: GraphQL with Oracle Database and node-oracledb