node-oracledb

8. Connection Handling

In applications which use connections infrequently, create a connection
with Oracledb getConnection():

var oracledb = require('oracledb');
oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "localhost/XE"
  },
  function(err, connection)
  {
    if (err) { console.error(err.message); return; }
    . . . // use connection
  });

Connections should be released with connection.close() when no
longer needed:

var oracledb = require('oracledb');
oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "localhost/XE"
  },
  function(err, connection)
  {
    if (err) { console.error(err.message); return; }
    . . .  // use connection
    connection.close(
      function(err)
      {
        if (err) { console.error(err.message); }
      });
  });

Applications which are heavy users of connections should create and
use a Connection Pool.

8.1 Connection Strings

The Oracledb getConnection() and Pool
getConnection() connectString can be an Easy
Connect string, or a Net Service Name from a local tnsnames.ora file
or external naming service, or it can be the SID of a local Oracle
database instance.

If connectString is not specified, the empty string "" is used which
indicates to connect to the local, default database.

8.1.1 Easy Connect Syntax for Connection Strings

An Easy Connect string is often the simplest to use. With Oracle Database 12c
the syntax is:
[//]host_name[:port][/service_name][:server_type][/instance_name]

For example, use "localhost/XE" to connect to the database XE on the local machine:

var oracledb = require('oracledb');
oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "localhost/XE"
  },
  . . .

Applications that request DRCP connections, for example with
myhost/XE:pooled, must use local Connection Pooling.

For more information on Easy Connect strings see
Understanding the Easy Connect Naming Method
in the Oracle documentation.

8.1.2 Net Service Names for Connection Strings

A Net Service Name, such as sales in the example below, can be used
to connect:

var oracledb = require('oracledb');
oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "sales"
  },
  . . .

This could be defined in a directory server, or in a local
tnsnames.ora file, for example:

sales =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mymachine.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

The tnsnames.ora file can be in a default location such as
$ORACLE_HOME/network/admin/tnsnames.ora or
/etc/tnsnames.ora. Alternatively set the TNS_ADMIN environment
variable and put the file in $TNS_ADMIN/tnsnames.ora.

Applications that request DRCP connections, for example where
the tnsnames.ora connection description contains (SERVER=POOLED),
must use local Connection Pooling.

For more information on tnsnames.ora files see
General Syntax of tnsnames.ora
in the Oracle documentation.

8.1.3 JDBC and Node-oracledb Connection Strings Compared

Developers familiar with Java connection strings that reference a
service name like:

jdbc:oracle:thin:@hostname:port/service_name

can use Oracle's Easy Connect syntax in node-oracledb:

var oracledb = require('oracledb');
oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "hostname:port/service_name"
  },
  . . .

Alternatively, if a JDBC connection string uses an old-style
SID,
and there is no service name available:

jdbc:oracle:thin:@hostname:port:sid

then consider creating a tnsnames.ora entry, for example:

finance =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
   (CONNECT_DATA =
     (SID = ORCL)
   )
 )

This can be referenced in node-oracledb:

var oracledb = require('oracledb');
oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "finance"
  },
  . . .

8.2 Connections and Number of Threads

If you use a large number of connections, such as via increasing
poolMax, you may want to also increase the
number of threads available to node-oracledb.

Node worker threads executing database statements on a connection will
commonly wait until round-trips between node-oracledb and the database
are complete. When an application handles a sustained number of user
requests, and database operations take some time to execute or the
network is slow, then the four default threads may all be held in
use. This prevents other connections from beginning work and stops
Node from handling more user load. Increasing the number of worker
threads may improve throughput. Do this by setting the environment
variable
UV_THREADPOOL_SIZE
before starting Node.

For example, in a Linux terminal, the number of Node worker threads
can be increased to 10 by using the following command:

$ UV_THREADPOOL_SIZE=10 node myapp.js

8.3 Connection Pooling

When applications use a lot of connections for short periods, Oracle
recommends using a connection pool for efficiency. Each node-oracledb
process can use one or more local pools of connections. Each pool can
contain one or more connections. A pool can grow or shrink, as
needed.

A connection Pool object is created by calling the
createPool() function of the Oracledb
object. Internally
OCI Session Pooling
is used.

A connection is returned with the Pool
getConnection() function:

var oracledb = require('oracledb');
oracledb.createPool (
  {
    user          : "hr"
    password      : "welcome"
    connectString : "localhost/XE"
  },
  function(err, pool)
  {
    pool.getConnection (
      function(err, connection)
      {
      . . .  // use connection
      });
  });

Connections should be released with connection.close() when no
longer needed:

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

After an application finishes using a connection pool, it should
release all connections and terminate the connection pool by calling
the pool.close() method.

The growth characteristics of a connection pool are determined by the
Pool attributes poolIncrement,
poolMax, poolMin and
poolTimeout. Note that when External
Authentication is used, the pool behavior is different, see
External Authentication.

The Pool attribute stmtCacheSize can be
used to set the statement cache size used by connections in the pool,
see Statement Caching.

8.3.1 Connection Pool Cache

Node-oracledb has an internal connection pool cache which can be used to
facilitate sharing pools across modules and simplify getting connections from
pools in the cache.

Methods that can affect or use the connection pool cache include:
- oracledb.createPool() - can add a pool to the cache
- oracledb.getPool() - retrieves a pool from the cache (synchronous)
- oracledb.getConnection() - can use a pool in the cache to retrieve connections
- pool.close() - automatically removes the pool from the cache if needed

Pools are added to the cache if a poolAlias
property is provided in the poolAttrs object when
invoking oracledb.createPool(). If a pool with the alias 'default' is not in the
cache and a pool is created without providing a pool alias, that pool will be cached
using the pool alias 'default'. The pool with this pool alias is used by default in
methods that utilize the connection pool cache.

There can be multiple pools in the cache provided each pool is created with
a unique pool alias.

Examples using the default pool

Assuming the connection pool cache is empty, the following will create a new pool
and cache it using the pool alias 'default':

var oracledb = require('oracledb');
oracledb.createPool (
  {
    user: 'hr',
    password: 'welcome',
    connectString: 'localhost/XE'
  },
  function(err, pool) {
    console.log(pool.poolAlias); // default
  }
);

Once cached, the default pool can be retrieved using oracledb.getPool() without
passing the poolAlias parameter:

var oracledb = require('oracledb');
var pool = oracledb.getPool();
pool.getConnection(function(err, conn) {
   // Use connection
});

If the pool is being retrieved only to call pool.getConnection, then the shortcut
oracledb.getConnection may be used instead:

var oracledb = require('oracledb');
oracledb.getConnection(function(err, conn) {
   // Use connection
});

Examples using multiple pools

If the application needs to use more than one pool at a time, unique pool aliases
can be used when creating the pools:

var oracledb = require('oracledb');
var hrPoolPromise = oracledb.createPool({
  poolAlias: 'pool1',
  users: 'hr',
  password: 'welcome',
  connectString: 'localhost/XE'
});
var shPoolPromise = oracledb.createPool({
  poolAlias: 'pool2',
  user: 'sh',
  password: 'welcome',
  connectString: 'localhost/XE'
});
Promise.all([hrPoolPromise, shPoolPromise])
  .then(function(pools) {
    console.log(pools[0].poolAlias); // pool1
    console.log(pools[1].poolAlias); // pool2
  })
  .catch(function(err) {
    // handle error
  })

To use the methods or attributes of a pool in the cache, a pool can be retrieved
from the cache by passing its pool alias to oracledb.getPool():

var oracledb = require('oracledb');
var pool = oracledb.getPool('pool1'); // or 'pool2'
pool.getConnection(function(err, conn) {
   // Use connection
});

The oracledb.getConnection shortcut can also be used with a pool alias:

var oracledb = require('oracledb');
oracledb.getConnection('pool1', function(err, conn) { // or 'pool2'
   // Use connection
});

8.3.2 Connection Pool Queue

By default when poolMax has been reached (meaning all connections in
a pool are in use), and more
pool.getConnection() requests are made, then
each new request will be queued until an in-use connection is released
back to the pool with connection.close(). If
poolMax has not been reached, then connections can be satisfied and
are not queued.

The pool queue can be disabled by setting the pool property
queueRequests to false. When the queue is
disabled, pool.getConnection() requests that cannot immediately be
satisfied will return an error.

The amount of time that a queued request will wait for a free
connection can be configured with queueTimeout.
When connections are timed out of the queue, they will return the
error NJS-040 to the application.

Internally the queue is implemented in node-oracledb's JavaScript top
level. A queued connection request is dequeued and passed down to
node-oracledb's underlying C++ connection pool when an active
connection is released, and the number of
connections in use drops below the value of
poolMax.

8.3.3 Connection Pool Monitoring and Throughput

Connection pool usage should be monitored to choose the appropriate
connection pool settings for your workload.

The Pool attributes connectionsInUse
and connectionsOpen provide basic
information about an active pool.

When using a pool queue, further statistics
can be enabled by setting the createPool()
poolAttrs parameter _enableStats to true. Statistics
can be output to the console by calling the Pool _logStats()
method. The underscore prefixes indicate that these are private
attributes and methods. This interface may be altered or
enhanced in the future.

To enable recording of queue statistics:

oracledb.createPool (
  {
    queueRequests : true,  // default is true
    _enableStats  : true,   // default is false
    user          : "hr",
    password      : "welcome",
    connectString : "localhost/XE"
  },
  function(err, pool)
  {
  . . .

The application can later, on some developer-chosen event, display the
current statistics to the console by calling:

pool._logStats();

The current implementation of _logStats() displays pool queue
statistics, pool settings, and related environment variables.

Statistics

The statistics displayed by _logStats() in this release are:

Note that for efficiency, the minimum, maximum, average, and sum of
times in the queue are calculated when requests are removed from the
queue. They do not take into account times for connection requests
still waiting in the queue.

Attribute Values

The _logStats() method also shows attribute values in effect for the pool:

Related Environment Variables

One related environment variable is is shown by _logStats():

8.4 Database Resident Connection Pooling (DRCP)

Database Resident Connection Pooling (DRCP)
enables database resource sharing for applications that run in
multiple client processes or run on multiple middle-tier application
servers. DRCP reduces the overall number of connections that a
database must handle.

DRCP is useful for applications which share the same database credentials, have
similar session settings (for example date format settings and PL/SQL
package state), and where the application gets a database connection,
works on it for a relatively short duration, and then releases it.

To use DRCP in node-oracledb:

1. The DRCP pool must be started in the database: SQL> execute dbms_connection_pool.start_pool();
2. The connectionClass should be set by the node-oracledb application. If it is not set, the pooled server session memory will not be reused optimally.
3. The getConnection() property connectString must specify to use a pooled server, either by the Easy Connect syntax like myhost/sales:POOLED, or by using a tnsnames.ora alias for a connection that contains (SERVER=POOLED).

DRCP connections can only be used with node-oracledb's local
connection pool. If the non-local pool connection
method oracledb.getConnection() is called and the connectString
indicates a DRCP server should be used, then an error ORA-56609:
Usage not supported with DRCP occurs.

The DRCP 'Purity' is SELF for DRCP
pool.getConnection() connections. This allows
reuse of the pooled server process and session memory, giving maximum
benefit from DRCP. See the Oracle documentation on
benefiting from scalability.

The
Oracle DRCP documentation
has more details, including when to use, and when not to use DRCP.

There are a number of Oracle Database V$ views that can be used to
monitor DRCP. These are discussed in the Oracle documentation and in the
Oracle white paper
PHP Scalability and High Availability.
This paper also gives more detail on configuring DRCP.

8.5 External Authentication

External Authentication allows applications to use an external
password store (such as
Oracle Wallet),
the
Secure Socket Layer
(SSL), or the
operating system
to validate user access. One of the benefits is that database
credentials do not need to be hard coded in the application.

To use external authentication, set the Oracledb
externalAuth property to true. This property can
also be set in the connAttrs or poolAttrs parameters of the
Oracledb getConnection() or
createPool() calls, respectively. The user and
password properties should not be set, or should be empty strings:

var oracledb = require('oracledb');
oracledb.getConnection(
  {
    externalAuth: true,
    connectString: "localhost/orcl"
  },
  . . .

When externalAuth is set, any subsequent connections obtained using
the Oracledb getConnection() or Pool
getConnection() calls will use external
authentication. Setting this property does not affect the operation
of existing connections or pools.

Using externalAuth in the connAttrs parameter of a Pool
getConnection() call is not possible. The connections from a Pool
object are always obtained in the manner in which the pool was
initially created.

For pools created with external authentication, the number of
connections initially created is zero even if a larger value is
specified for poolMin. The pool increment is
always 1, regardless of the value of
poolIncrement. Once the number
of open connections exceeds poolMin and connections are idle for
more than the poolTimeout seconds, then the
number of open connections does not fall below poolMin.