6. API: Pool Class

A connection Pool object is created by calling the oracledb.createPool() method.

The Pool object obtains connections to the Oracle database using the getConnection() method to “check them out” from the pool. The node-oracledb Thick mode internally uses Oracle Call Interface Session Pooling.

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

See Connection Pooling for more information.

6.1. Pool Properties

The Pool object properties may be read to determine the current values.

pool.connectionsInUse: This read-only property is a number which specifies the number of currently active connections in the connection pool, that is, the number of connections currently “checked out” using pool.getConnection().

pool.connectionsOpen

This read-only property is a number which specifies the current number of connections in the pool that are connected through to the database. This number is the sum of connections in use by the application, and connections idle in the pool.

It may be less than poolMin if connections have been dropped, for example with await connection.close({drop: true}), or if network problems have caused connections to become unusable.

pool.connectString

This read-only property is a string which specifies the connection string used to connect to the Oracle Database Instance.

See connectString parameter of oracledb.createPool().

pool.edition

This read-only property is a string which identifies the edition name used.

See edition parameter of oracledb.createPool() and oracledb.edition.

Note

This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

pool.events

This read-only property is a boolean which denotes whether the Oracle Client events mode is enabled or not.

See events parameter of oracledb.createPool() and oracledb.events.

Note

This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

pool.externalAuth

This read-only property is a boolean which denotes whether connections are established using external authentication or not.

See externalAuth parameter of oracledb.createPool() and oracledb.externalAuth.

pool.enableStatistics

This read-only property is a boolean which identifies whether pool usage statistics are being recorded.

See enableStatistics parameter of oracledb.createPool().

pool.homogeneous

This read-only property is a boolean which identifies whether the connections in the pool all have the same credentials (a ‘homogenous’ pool), or whether different credentials can be used (a ‘heterogeneous’ pool).

See homogeneous parameter of oracledb.createPool().

pool.poolAlias

This read-only property is a number which specifies the alias of this pool in the connection pool cache. An alias cannot be changed once the pool has been created. This property will be undefined for the second and subsequent pools that were created without an explicit alias specified.

See poolAlias parameter of oracledb.createPool().

pool.poolIncrement

This read-only property is a number which specifies the number of connections that are opened whenever a connection request exceeds the number of currently open connections.

See poolIncrement parameter of oracledb.createPool() and oracledb.poolIncrement.

pool.poolMax

This read-only property is a number which specifies the maximum number of connections that can be open in the connection pool.

See poolMax parameter of oracledb.createPool() and oracledb.poolMax.

pool.poolMaxPerShard

This read-only property is a number which sets the maximum number of connections in the pool that can be used for any given shard in a sharded database. This lets connections in the pool be balanced across the shards.

See poolMaxPerShard parameter of oracledb.createPool() and oracledb.poolMaxPerShard.

Note

This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

pool.poolMin

This read-only property is a number which specifies the minimum number of connections a connection pool maintains, even when there is no activity to the target database.

See poolMin parameter of oracledb.createPool() and oracledb.poolMin.

pool.poolPingInterval

This read-only property is a number which specifies the maximum number of seconds that a connection can remain idle in a connection pool (not “checked out” to the application by getConnection()) before node-oracledb pings the database prior to returning that connection to the application.

See poolPingInterval parameter of oracledb.createPool() and oracledb.poolPingInterval.

pool.poolPingTimeout

Added in version 6.4.

This read-only property is a number which specifies the maximum number of milliseconds that a connection should wait for a response from connection.ping().

See poolPingTimeout parameter of oracledb.createPool() and oracledb.poolPingTimeout.

pool.poolTimeout

This read-only property is a number which specifies the time (in seconds) after which the pool terminates idle connections (unused in the pool). The number of connections does not drop below poolMin.

See poolTimeout parameter of oracledb.createPool() and oracledb.poolTimeout.

pool.queueMax

Added in version 5.0.

This read-only property is a number which specifies the maximum number of pending pool.getConnection() calls that can be queued.

See queueMax parameter of oracledb.createPool() and oracledb.queueMax.

pool.queueRequests: Desupported in version 3.0.

See Connection Pool Queue for more information.

pool.queueTimeout

This read-only property is a number which identifies the time (in milliseconds) that a connection request should wait in the queue before the request is terminated.

See queueTimeout parameter of oracledb.createPool() and oracledb.queueTimeout.

pool.sessionCallback

This read-only property can be a function or string. The Node.js or PL/SQL function that is invoked by pool.getConnection() when the connection is brand new.

See sessionCallback parameter of oracledb.createPool().

Also, see Connection Tagging and Session State.

pool.sodaMetaDataCache

This read-only property is a boolean which determines whether the pool has a metadata cache enabled for SODA collection access.

See sodaMetaDataCache parameter of oracledb.createPool().

Also, see Using the SODA Metadata Cache.

Note

This property can only be used in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

pool.status

This read-only property is a number and can be one of the oracledb.POOL_STATUS_OPEN, POOL_STATUS_DRAINING, or POOL_STATUS_CLOSED constants indicating whether the pool is open, being drained of in-use connections, or has been closed.

See Connection Pool Closing and Draining.

pool.stmtCacheSize

This read-only property is a number which identifies the number of statements to be cached in the statement cache of each connection.

See stmtCacheSize parameter of oracledb.createPool() and oracledb.stmtCacheSize.

pool.thin

Added in version 6.0.

This read-only attribute is a boolean that identifies the node-oracledb mode in which the pool was created. If the value is true, it indicates that the pool was created in node-oracledb Thin mode. If the value is false, it indicates that the pool was created in node-oracledb Thick mode.

The default value is true.

See oracledb.thin.

pool.user

This read-only property is a string which specifies the database username for connections in the pool.

See user parameter of oracledb.createPool().

6.2. Pool Methods

pool.close()

Added in version 1.9.

Promise:

promise = close([Number drainTime]);

Closes connections in the pool and terminates the connection pool.

If a drainTime is not given, then any open connections should be released with connection.close() before pool.close() is called, otherwise the pool close will fail and the pool will remain open.

If a drainTime is specified, then any new pool.getConnection() calls will fail. If connections are in use by the application, they can continue to be used for the specified number of seconds, after which the pool and all open connections are forcibly closed. Prior to this time limit, if there are no connections currently “checked out” from the pool with getConnection(), then the pool and any connections that are idle in the pool are immediately closed. Non-zero drainTime values are strongly recommended so applications have the opportunity to gracefully finish database operations. A drainTime of 0 may be used to close a pool and its connections immediately.

In network configurations that drop (or in-line) out-of-band breaks, forced pool termination may hang unless you have DISABLE_OOB=ON in a sqlnet.ora file, see Optional Oracle Net Configuration.

When the pool is closed, it will be removed from the connection pool cache.

If pool.close() is called while the pool is already closed, draining, or reconfiguring, then an error will be thrown.

This method has replaced the obsolete equivalent alias pool.terminate() which will be removed in a future version of node-oracledb.

The parameters of the pool.close() method are:

Table 6.1 pool.close() Parameters
Parameter	Data Type	Description
`drainTime`	Number	The number of seconds before the pool and connections are force closed. If `drainTime` is 0, the pool and its connections are closed immediately. Added in version 3.0.

Callback:

If you are using the callback programming style:

close([Number drainTime,] function(Error error){});

See pool.close() Parameters for information on the drainTime parameter.

The parameters of the callback function function(Error error) are:

Callback Function Parameter	Description
Error `error`	If `close()` succeeds, `error` is NULL. If an error occurs, then `error` contains the error message.

pool.getConnection()

Promise:

promise = getConnection([Object poolAttrs]);

Obtains a connection from the connection pool.

If a previously opened connection is available in the pool, that connection is returned. If all connections in the pool are in use, a new connection is created and returned to the caller, as long as the number of connections does not exceed the specified maximum for the pool. If the pool is at its maximum limit, the getConnection() call results in an error, such as ORA-24418: Cannot open further sessions.

By default pools are created with homogeneous set to true. The user name and password are supplied when the pool is created. Each time pool.getConnection() is called, a connection for that user is returned:

const connection = await pool.getConnection();

If a heterogeneous pool was created by setting homogeneous to false during creation and credentials were omitted, then the user name and password may be used in pool.getConnection() like:

const connection = await pool.getConnection(
{
     user     : 'hr',
     password : mypw,  // mypw contains the hr schema password
}
);,

In this case, different user names may be used each time pool.getConnection() is called. Proxy users may also be specified.

See Connection Handling for more information on connections.

See Heterogeneous Connection Pools and Pool Proxy Authentication for more information on heterogeneous pools.

The parameters of the pool.getConnection() method are:

Table 6.2 pool.getConnection() Parameters
Parameter	Data Type	Description
`poolAttrs`	Object	This parameter can contain a `tag` property when connection tagging is in use. It can also contain shardingKey and superShardingKey properties, when using database sharding. When getting connections from heterogeneous pools, this parameter can contain `user` (or `username`) and `password` properties for true heterogeneous pool usage, or it can contain a `user` property when a pool proxy user is desired. See Connection Attributes for information on these attributes.

Callback:

If you are using the callback programming style:

getConnection([Object poolAttrs,] function(Error error, Connection connection){});

See pool.getConnection() Parameters for information on the parameters.

The parameters of the callback function function(Error error, Connection connection) are:

Callback Function Parameter	Description
Error `error`	If `getConnection()` succeeds, `error` is NULL. If an error occurs, then `error` contains the error message.
Connection `connection`	The newly created connection. If `getConnection()` fails, `connection` will be NULL. See Connection class for more details.

pool.getStatistics()

Added in version 5.2.

getStatistics();

Returns a PoolStatistics object containing pool queue statistics, pool settings, and related environment variables. The object is described in Connection Pool Monitoring. Note that this is a synchronous method.

Recording of statistics must have previously been enabled with enableStatistics during pool creation or with pool.reconfigure(). If the pool is open, but enableStatistics is false, then null will be returned.

If getStatistics() is called while the pool is closed, draining, or reconfiguring, then an error will be thrown.

pool.logStatistics()

Added in version 5.2.

logStatistics();

Displays pool queue statistics, pool settings, and related environment variables to the console. Recording of statistics must have previously been enabled with enableStatistics during pool creation or with pool.reconfigure(). Note that this is a synchronous method.

An error will be thrown if logStatistics() is called while the pool is closed, draining, reconfiguring, or when enableStatistics is false.

See Connection Pool Monitoring.

The obsolete function _logStats() can still be used, but it will be removed in a future version of node-oracledb.

pool.reconfigure()

Promise:

promise = reconfigure(Object poolAttrs);

Allows a subset of pool creation properties to be changed without needing to restart the pool or restart the application. Properties such as the maximum number of connections in the pool, or the statement cache size used by connections can be changed.

Properties are optional. Unspecified properties will leave those pool properties unchanged. The properties are processed in two stages. After any size change has been processed, reconfiguration on the other properties is done sequentially. If an error such as an invalid value occurs when changing one property, then an error will be thrown but any already changed properties will retain their new values.

During reconfiguration, pool.status will be POOL_STATUS_RECONFIGURING and

Any pool.getConnection() call will be queued until after the pool has been reconfigured and a connection is available. Queuing of these requests is subject to the queue queueTimeout and queueMax settings in effect when pool.getConnection() is called.
Closing connections with connection.close() will wait until reconfiguration is complete.
Trying to close the pool during reconfiguration will throw an error.

Example

await pool.reconfigure({poolMin: 5, poolMax: 10, increment: 5});

The parameters of the pool.reconfigure() method are:

Table 6.3 pool.reconfigure() Parameters
Parameter	Data Type	Description
`poolAttrs`	Object	The following `oracledb.createPool()` properties can be changed with `pool.reconfigure()` in both Thin and Thick modes unless otherwise specified: enableStatistics poolIncrement poolMax poolMaxPerShard in only Thick mode poolMin poolPingInterval poolTimeout queueMax queueRequests queueTimeout sodaMetaDataCache in only Thick mode stmtCacheSize A `resetStatistics` property can also be set to true. This zeros the current pool statistics, with the exception of `queueMax` which is set to the current queue length. Statistics are also reset when statistics recording is turned on with the `enableStatistics` property. Changing `queueMax`, `queueTimeout`, or resetting statistics does not affect any currently queued connection requests. If connections are not made available to currently queued requests, those queued requests will timeout based on the `queueTimeout` value in effect when they were originally added to the connection pool queue. If pool statistics are enabled, then these failed requests will be counted in requestTimeouts and included in the queue time statistics.

Callback:

If you are using the callback programming style:

reconfigure(Object poolAttrs, function(Error error){});

See pool.reconfigure() Parameters for information on the poolAttrs parameter.

The parameters of the callback function function(Error error) are:

Callback Function Parameter	Description
Error `error`	If `reconfigure()` succeeds, `error` is NULL. If an error occurs, then `error` contains the error message.

pool.setAccessToken()

Deprecated since version 5.5.

Added in version 5.4.

This method can be used to set an IAM access token and private key after pool creation. It is useful if the IAM token is known to have expired, and you are not using accessTokenCallback.

It can also be useful in tests to set an expired token so that token expiry code paths can be tested.

The parameters of the pool.setAccessToken() method are:

Table 6.4 pool.setAccessToken() Parameters
Parameter	Data Type	Description
`tokenAttrs`	Object	The `tokenAttrs` parameter object provides IAM token-based authentication properties. The properties of the `tokenAttrs` object are detailed in the tokenAttrs Parameter Properties table. Both properties must be set. The values can be obtained, for example, using the Oracle Cloud Infrastructure Command Line Interface (OCI CLI).

The properties of the tokenAttrs parameter are:

Table 6.5 `tokenAttrs` Parameter Properties
Attribute	Description
`token`	The database authentication token string.
`privateKey`	The database authentication private key string.