4. Connecting to Oracle Database

Connections between node-oracledb and Oracle Database are used for executing SQL, PL/SQL, and for SODA.

By default, node-oracledb runs in a ‘Thin’ mode which connects directly to Oracle Database. This mode does not need Oracle Client libraries. However, when node-oracledb uses these libraries, then the driver is said to be in ‘Thick’ mode and has additional functionality. See Enabling node-oracledb Thick Mode.

Connections can be either:

• Standalone: These connections are useful when the application needs a single connection to a database. Connections are created by calling oracledb.getConnection().

• Pooled: These connections are important for performance when applications frequently connect and disconnect from the database. Oracle high availability features in the pool implementation mean that small pools can also be useful for applications that want a few connections available for infrequent use. Pools are created with oracledb.createPool() at application initialization time, and then pool.getConnection() can be called to obtain a connection from a pool.

Many connection behaviors can be controlled by node-oracledb options. Other settings can be configured in Oracle Net files or in connection strings. These include limiting the amount of time that opening a connection can take, or enabling network encryption.

4.1. Standalone Connections

Standalone connections are database connections that do not use a node-oracledb connection pool. They are useful for applications that use a single connection to a database. You can create connections by calling oracledb.getConnection() and passing a database username, the database password for that user, and a connect string. Node-oracledb also supports external authentication and token-based authentication so passwords do not need to be in the application.

An example passing credentials is:

const oracledb = require('oracledb');

async function run() {
    const connection = await oracledb.getConnection({
        user          : "hr",
        password      : mypw,  // contains the hr schema password
        connectString : "localhost/FREEPDB1"
    });

    const result = await connection.execute(`SELECT city FROM locations`);
    console.log("Result is:", result.rows);

    await connection.close();   // Always close connections
}

run();

Connections must be released with connection.close() when they are no longer needed. Make sure to release connections in all code paths including in error handlers.

Note

If you do not explicitly close a connection, you may experience a short delay when the application terminates. This is due to the timing behavior of Node.js garbage collection which needs to free the connection reference.

4.2. Connection Strings

The connectString property for oracledb.getConnection() and oracledb.createPool() can be one of:

• An Easy Connect string

• A Connect Descriptor string

• A Net Service Name from a local tnsnames.ora file or external naming service

• The SID of a local Oracle Database instance

If a connect string is not specified, the empty string “” is used which indicates to connect to the local, default database.

The connectionString property is an alias for connectString. Use only one of the properties.

Note

Creating a connection in node-oracledb Thin mode always requires a connection string, or the database host name and service name, to be specified. Bequeath connections cannot be made. The Thin mode does not reference Oracle environment variables such as ORACLE_SID, TWO_TASK, or LOCAL.

4.2.1. Easy Connect Syntax for Connection Strings

An Easy Connect string is often the simplest connection string to use. For example, to connect to the Oracle Database service orclpdb1 that is running on the host mydbmachine.example.com with the default Oracle Database port 1521, use:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "mydbmachine.example.com/orclpdb1"
});

If the database is using a non-default port, for example 1984, the port must be given:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "mydbmachine.example.com:1984/orclpdb1"
});

The Easy Connect syntax has been extended in recent versions of Oracle Database client since its introduction in Oracle 10g. Check the Easy Connect Naming method in Oracle Net Service Administrator’s Guide for the syntax in your version of the Oracle Client libraries. The Easy Connect syntax supports Oracle Database service names. It cannot be used with the older System Identifiers (SID).

In node-oracledb Thin mode, any unknown Easy Connect options are ignored and are not passed to the database. See Connection String Differences for more information.

If you are using node-oracledb Thick mode with Oracle Client 19c (or later), the latest Easy Connect Plus syntax allows the use of multiple hosts or ports, along with optional entries for the wallet location, the distinguished name of the database server, and even lets some network configuration options be set. Oracle’s Technical Paper on Easy Connect Plus Syntax discusses the syntax. The Easy Connect Plus syntax means that tnsnames.ora or sqlnet.ora files are not needed for some further common connection scenarios.

For example, if a firewall terminates idle connections every five minutes, you may decide it is more efficient to keep connections alive instead of having the overhead of recreation. Your connection string could be "mydbmachine.example.com/orclpdb1?expire_time=2" to send packets every two minutes with the EXPIRE_TIME feature. The general recommendation for EXPIRE_TIME is to use a value that is slightly less than half of the termination period.

Another common use case for Easy Connect Plus is to limit the amount of time required to open a connection. For example, to return an error after 15 seconds if a connection cannot be established to the database, use "mydbmachine.example.com/orclpdb1?connect_timeout=15".

4.2.2. Embedded Connect Descriptor Strings

Full Connect Descriptor strings can be embedded directly in node-oracledb applications:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mymachine.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))"
});

4.2.3. Net Service Names for Connection Strings

Connect Descriptor strings are commonly stored in optional tnsnames.ora configuration files and associated with a Net Service Name, for example:

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

Net Service Names may also be defined in a directory server.

Given a Net Service Name, node-oracledb Thin mode can connect using the following code:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "sales"
    configDir     : "/opt/oracle/config"
});

See Optional Oracle Net Configuration for more options on how node-oracledb locates the tnsnames.ora files. Note that in node-oracledb Thick mode, the configuration file must a default location or be set during initialization, not at connection time.

For general information on tnsnames.ora files, see the Oracle Net documentation on tnsnames.ora.

Note

When using node-oracledb in Thin mode, the tnsnames.ora file will not be automatically located. The file’s directory must be explicitly specified when connecting.

4.2.4. JDBC and Oracle SQL Developer Connection Strings

The node-oracledb connection string syntax is different to Java JDBC and the common Oracle SQL Developer syntax. If these JDBC connection strings reference a service name like:

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

for example:

jdbc:oracle:thin:@mydbmachine.example.com:1521/orclpdb1

then use Oracle’s Easy Connect syntax in node-oracledb:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "mydbmachine.example.com:1521/orclpdb1"
});

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

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

for example:

jdbc:oracle:thin:@mydbmachine.example.com:1521:orcl

then either embed the Connect Descriptor:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mymachine.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=ORCL)))"
});

or create a Net Service Name:

# tnsnames.ora

finance =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mydbmachine.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SID = ORCL)
    )
  )

This can be referenced in node-oracledb:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "finance"
});

4.3. Connections, Threads, and Parallelism

To scale and optimize your applications, it is useful to understand how connections interact with Node.js.

4.3.1. Connections and Worker Threads

Node.js has four background worker threads by default (not to be confused with the newer user space worker_threads module). If you are using node-oracledb Thick mode and open more than four standalone connections or pooled connections, such as by increasing pool.poolMax, then you must increase the number of worker threads available to node-oracledb.

Note

This section on Worker thread pool sizing applies only to node-oracledb Thick mode. Changing UV_THREADPOOL_SIZE is not needed for node-oracledb when using Thin mode.

A worker thread pool that is too small can cause a decrease in application performance, deadlocks, or failure in connection requests with the error NJS-040: connection request timeout or NJS-076: connection request rejected.

A Node.js worker thread is used by each node-oracledb Thick mode connection to execute a database statement. Each thread will wait until all round-trips between node-oracledb and the database for the statement 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 all available threads may be held in use. This prevents other connections from beginning work and stops Node.js from handling more user load.

The thread pool size should be equal to, or greater than, the maximum number of connections. If the application does database and non-database work concurrently, then additional threads could also be required for optimal throughput.

Increase the thread pool size by setting the environment variable UV_THREADPOOL_SIZE before starting Node.js. For example, on Linux your package.json may have a script like:

"scripts": {
    "start": "export UV_THREADPOOL_SIZE=10 && node index.js"
},
. . .

Or, on Windows:

"scripts": {
    "start": "SET UV_THREADPOOL_SIZE=10 && node index.js"
},
. . .

With these, you can start your application with npm start. This will allow up to 10 connections to be actively excuting SQL statements in parallel.

On non-Windows platforms, the value can also be set inside the application. It must be set prior to any asynchronous Node.js call that uses the thread pool:

// !! First file executed.  Non-Windows only !!

process.env.UV_THREADPOOL_SIZE = 10

// ... rest of code

If you set UV_THREADPOOL_SIZE too late in the application, or try to set it this way on Windows, then the setting will be ignored and the default thread pool size of 4 will still be used. Note that pool.getStatistics() and pool.logStatistics() can only give the value of the variable, not the actual size of the thread pool created. On Linux you can use pstack to see how many threads are actually running. Node.js will create a small number of threads in addition to the expected number of worker threads.

The libuv library used by Node.js 12.5 and earlier limits the number of threads to 128. In Node.js 12.6 onward the limit is 1024. You should restrict the maximum number of connections opened in an application, that is, poolMax, to a value lower than UV_THREADPOOL_SIZE. If you have multiple pools, make sure the sum of all poolMax values is no larger than UV_THREADPOOL_SIZE.

4.3.2. Parallelism on Each Connection

Oracle Database can only execute operations one at a time on each connection. Examples of operations include connection.execute(), connection.executeMany(), connection.queryStream(), connection.getDbObjectClass(), connection.commit(), connection.close(), SODA calls, and streaming from Lobs. Multiple connections may be in concurrent use, but each connection can only do one thing at a time. Code will not run faster when parallel database operations are attempted using a single connection.

From node-oracledb 5.2, node-oracledb function calls that use a single connection for concurrent database access will be queued in the JavaScript layer of node-oracledb. In earlier node-oracledb versions, locking occurred in the Oracle Client libraries, which meant many threads could be blocked.

It is recommended to structure your code to avoid parallel operations on a single connection. For example, avoid using Promise.all() on a single connection. Similarly, instead of using async.parallel() or async.each() which call each of their items in parallel, use async.series() or async.eachSeries(). If you want to repeat a number of INSERT or UPDATE statements, then use connection.executeMany().

To rewrite code that uses Promise.all() you could, for example, use a basic for loop with async/await to iterate through each action:

async function myfunc() {
    const stmts = [
        `INSERT INTO ADRESSES (ADDRESS_ID, CITY) VALUES (94065, 'Redwood Shores')`,
        `INSERT INTO EMPLOYEES (ADDRESS_ID, EMPLOYEE_NAME) VALUES (94065, 'Jones')`
    ];

    for (const s of stmts) {
        await connection.execute(s);
    }
}

If you use ESlint for code validation, and it warns about await in loops for code that is using a single connection, then disable the no-await-in-loop rule for these cases.

Another alternative rewrite for Promise.all() is to wrap the SQL statements in a single PL/SQL block.

Note that using functions like Promise.all() to fetch rows from nested cursor result sets can result in inconsistent data.

During development, you can set oracledb.errorOnConcurrentExecute to true to help identify application code that executes concurrent database operations on a single connection. Such uses may be logic errors such as missing await keywords that could lead to unexpected results. When errorOnConcurrentExecute is set to true, an error will be thrown so you can identify offending code. Setting errorOnConcurrentExecute is not recommended for production use in case it generates errors during normal operation. For example third-party code such as a framework may naturally use Promise.all() in its generic code. Or your application may be coded under the assumption that node-oracledb will do any necessary serialization. Note the use of errorOnConcurrentExecute will not affect parallel use of multiple connections, which may all be in use concurrently, and each of which can be doing a single operation.

4.4. Connection Pooling

Applications which frequently create and close connections should use a connection pool. This is important for performance and scalability when applications need to handle a large number of users who do database work for short periods of time but have relatively long periods when the connections are not needed. The high availability features of pools also make small pools useful for applications that want a few connections available for infrequent use and requires them to be immediately usable when acquired. Applications that would benefit from connection pooling but are too difficult to modify from standalone connections may be able to take advantage of Implicit Connection Pooling with DRCP and PRCP.

Each node-oracledb process can use one or more connection pools. Each pool can contain zero or more connections. In addition to providing an immediately available set of connections, pools provide dead connection detection and transparently handle Oracle Database High Availability events. This helps shield applications during planned maintenance and from unplanned failures. In node-oracledb Thick mode, the pool implementation uses Oracle’s session pool technology which supports additional Oracle Database features such as Application Continuity.

Pools are created by calling oracledb.createPool(). Generally, applications will create a pool once as part of initialization. After an application finishes using a connection pool, it should release all connections and terminate the connection pool by calling the pool.close() method. During runtime, some pool properties can be changed with pool.reconfigure(). Note that in node-oracledb Thick mode, the number of worker threads should be sized correctly before creating a pool.

Connections from the pool are obtained with pool.getConnection(). If all connections in a pool are being used, then subsequent getConnection() calls will be put in a queue until a connection is available. Connections must be released with connection.close() when no longer needed so they can be reused. Make sure to release connections in all code paths including in error handlers.

Each connection in a pool should be used for a given unit of work, such as a transaction or a set of sequentially executed statements. Statements should be executed sequentially, not in parallel on each connection.

For example:

const oracledb = require('oracledb');

const mypw = ...  // set mypw to the hr schema password

async function run() {
    try {
        await oracledb.createPool({
            user          : "hr",
            password      : mypw  // mypw contains the hr schema password
            connectString : "localhost/FREEPDB1"
        });

        let connection;
        try {
            // get connection from the pool and use it
            connection = await oracledb.getConnection();
            result = await connection.execute(`SELECT last_name FROM employees`);
            console.log("Result is:", result);
        } catch (err) {
            throw (err);
        } finally {
            if (connection) {
                try {
                    await connection.close(); // Put the connection back in the pool
                } catch (err) {
                    throw (err);
                }
            }
        }
    } catch (err) {
        console.error(err.message);
    } finally {
        await oracledb.getPool().close(0);
    }
}

run();

When a connection is released back to its pool, any ongoing transaction will be rolled back however it will retain session state, such as NLS settings from ALTER SESSION statements. See Connection Tagging and Session State for more information.

Connections can also be dropped completely from the pool.

The default value of poolMin is 0, meaning no connections are created when oracledb.createPool() is called. This means the credentials and connection string are not validated when the pool is created, so problems such as invalid passwords will not return an error. Credentials will be validated when a connection is later created, for example with pool.getConnection(). Validation will occur when oracledb.createPool() is called if poolMin is greater or equal to 1, since this creates one or more connections when the pool is started.

A connection pool should be started during application initialization, for example before the web server is started:

const oracledb = require('oracledb');

const mypw = ...  // set mypw to the hr schema password

// Start a connection pool (which becomes the default pool) and start the webserver
async function init() {
    try {

        await oracledb.createPool({
            user          : "hr",
            password      : mypw,               // mypw contains the hr schema password
            connectString : "localhost/FREEPDB1",
            poolIncrement : 0,
            poolMax       : 4,
            poolMin       : 4
        });

        const server = http.createServer();
        server.on('error', (err) => {
            console.log('HTTP server problem: ' + err);
        });
        server.on('request', (request, response) => {
            handleRequest(request, response);
        });
        await server.listen(3000);

        console.log("Server is running");

    } catch (err) {
        console.error("init() error: " + err.message);
    }
}

Each web request will invoke handleRequest(). In it, a connection can be obtained from the pool and used:

async function handleRequest(request, response) {

    response.writeHead(200, {"Content-Type": "text/html"});
    response.write("<!DOCTYPE html><html><head><title>My App</title></head><body>");

    let connection;
    try {

        connection = await oracledb.getConnection();  // get a connection from the default pool
        const result = await connection.execute(`SELECT * FROM locations`);

        displayResults(response, result);  // do something with the results

    } catch (err) {
        response.write("<p>Error: " + text + "</p>");
    } finally {
        if (connection) {
            try {
                await connection.close();  // always release the connection back to the pool
            } catch (err) {
                console.error(err);
            }
        }
    }

    response.write("</body></html>");
    response.end();

}

See webapp.js for a runnable example.

4.4.1. Connection Pool Sizing

The main characteristics of a connection pool are determined by its attributes poolMin, poolMax, poolIncrement, and poolTimeout.

Note

Note in node-oracledb Thick mode, the number of worker threads should be sized correctly before creating a pool. See Connections and Worker Threads

Setting poolMin causes the specified number of connections to be established to the database during pool creation. This allows subsequent pool.getConnection() calls to return quickly for an initial set of users. An appropriate poolMax value avoids overloading the database by limiting the maximum number of connections ever opened.

Pool expansion happens when pool.getConnection() is called and both the following are true:

• all the currently established connections in the pool are “checked out” of the pool by previous pool.getConnection() calls

• the number of those currently established connections is less than the pool’s poolMax setting

Pool shrinkage happens when the application returns connections to the pool, and they are then unused for more than poolTimeout seconds. Any excess connections above poolMin will be closed. When node-oracledb Thick mode is using using Oracle Client 19 or earlier, this pool shrinkage is only initiated when the pool is accessed, so a pool in a completely idle application will not shrink.

For pools created with External Authentication, with homogeneous set to false, or when using Database Resident Connection Pooling (DRCP), then the number of connections initially created is zero even if a larger value is specified for poolMin. Also in these cases the pool increment is always 1, regardless of the value of poolIncrement. Once the number of open connections exceeds poolMin then the number of open connections does not fall below poolMin.

The Oracle Real-World Performance Group’s recommendation is to use fixed size connection pools. The values of poolMin and poolMax should be the same. This avoids connection storms which can decrease throughput. See Guideline for Preventing Connection Storms: Use Static Pools, which contains more details about sizing of pools. Having a fixed size will guarantee that the database can handle the upper pool size. For example, if a pool needs to grow but the database resources are limited, then pool.getConnection() may return errors such as ORA-28547. With a fixed pool size, this class of error will occur when the pool is created, allowing you to change the size before users access the application. With a dynamically growing pool, the error may occur much later after the pool has been in use for some time.

The Real-World Performance Group also recommends keeping pool sizes small, as this may perform better than larger pools. Use pool.getStatistics() or pool.logStatistics() to monitor pool usage. The pool attributes should be adjusted to handle the desired workload within the bounds of resources available to Node.js and the database.

When the values of poolMin and poolMax are the same, poolIncrement can be set greater than zero. (In Thick mode this needs Oracle Client 18c or later). This value changes how a homogeneous pool grows when the number of connections established has become lower than poolMin, for example if network issues have caused connections to become unusable and they have been dropped from the pool. Setting poolIncrement greater than 1 in this scenario means the next pool.getConnection() call that needs to grow the pool will initiate the creation of multiple connections. That pool.getConnection() call will not return until the extra connections have been created, so there is an initial time cost. However it can allow subsequent connection requests to be immediately satisfied. In this growth scenario, a poolIncrement of 0 is treated as 1.

Make sure any firewall, resource manager or user profile IDLE_TIME does not expire idle connections, since this will require connections to be recreated which will impact performance and scalability. See Preventing Premature Connection Closing.

4.4.2. Connection Pool Closing and Draining

Closing a connection pool allows database resources to be freed. If Node.js is killed without pool.close() being called successfully, then some time may pass before the unused database-side of connections are automatically cleaned up in the database.

When pool.close() is called with no parameter, the pool will be closed only if all connections have been released to the pool with connection.close(). Otherwise an error is returned and the pool will not be closed.

An optional drainTime parameter can be used to force the pool closed even if connections are in use. This lets the pool be ‘drained’ of connections. The drainTime indicates how many seconds the pool is allowed to remain active before it and its connections are terminated. For example, to give active connections 10 seconds to complete their work before being terminated:

await pool.close(10);

When a pool has been closed with a specified drainTime, then any new pool.getConnection() calls will fail. If connections are currently 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 its connections are immediately closed.

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.

Non-zero drainTime values are recommended so that applications have the opportunity to gracefully finish database operations. However, pools can be forcibly closed by specifying a zero drain time:

await pool.close(0);

Closing the pool would commonly be one of the last stages of a Node.js application. A typical closing routine look likes:

// Close the default connection pool with 10 seconds draining, and exit
async function closePoolAndExit() {
    console.log("\nTerminating");
    try {
        await oracledb.getPool().close(10);
        process.exit(0);
    } catch(err) {
        console.error(err.message);
        process.exit(1);
    }
}

It is helpful to invoke closePoolAndExit() if Node.js is sent a signal or interrupted:

// Close the pool cleanly if Node.js is interrupted
process
    .once('SIGTERM', closePoolAndExit)
    .once('SIGINT',  closePoolAndExit);

If pool.close() is called while a pool.reconfigure() is taking place, then an error will be thrown.

4.4.3. Connection Pool Caching

When pools are created, they can be given a named alias. The alias can later be used to retrieve the related pool object for use. This facilitates sharing pools across modules and simplifies getting connections.

Pools are added to the cache by using a poolAlias property in the poolAttrs object:

async function init() {
try {
    await oracledb.createPool({ // no need to store the returned pool
        user: 'hr',
        password: mypw,  // mypw contains the hr schema password
        connectString: 'localhost/FREEPDB1',
        poolAlias: 'hrpool'
    });

    // do stuff
    . . .

    // get the pool from the cache and use it
    const pool = oracledb.getPool('hrpool');
    . . .
}

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

If a pool is created without providing a pool alias:

• If no other pool in the cache already has the alias of ‘default’, then the new pool will be cached using the pool.poolAlias ‘default’.

  This pool is used by default in methods that utilize the connection pool cache.

• If an existing pool in the cache already has the alias ‘default’, then pool.poolAlias of the new pool will be undefined and the pool will be not stored in the pool cache. The application must retain a variable for subsequent pool use: const pool = await oracledb.createPool({   . . . }).

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.

• oracledb.getConnection(): Can use a pool in the cache to retrieve connections.

• pool.close(): Automatically removes a pool from the cache.

4.4.3.1. 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’:

async function init() {
    try {
        await oracledb.createPool({
            user: 'hr',
            password: mypw,  // mypw contains the hr schema password
            connectString: 'localhost/FREEPDB1'
        });

        . . .
}

If you are using callbacks, note that createPool() is not synchronous.

Connections can be returned by using the shortcut to oracledb.getConnection() that returns a connection from a pool:

const connection = await oracledb.getConnection();

. . . // Use connection from the previously created 'default' pool

await connection.close(); // always release the connection back to the pool

The default pool can also be retrieved using oracledb.getPool() without passing the poolAlias parameter:

const pool = oracledb.getPool();
console.log(pool.poolAlias); // 'default'
const connection = await pool.getConnection();

. . . // Use connection

await connection.close();

4.4.3.2. 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:

await oracledb.createPool({
    user: 'hr',
    password: myhrpw,  // myhrpw contains the hr schema password
    connectString: 'localhost/FREEPDB1',
    poolAlias: 'hrpool'
});

await oracledb.createPool({
    user: 'sh',
    password: myshpw,  // myshpw contains the sh schema password
    connectString: 'localhost/FREEPDB1',
    poolAlias: 'shpool'
});

. . .

To get a connection from a pool, pass the pool alias:

const connection = await oracledb.getConnection('hrpool');

. . . // Use connection from the pool

await connection.close(); // always release the connection back to the pool

From node-oracledb 3.1.0 you can alternatively pass the alias as an attribute of the options:

const connection = await oracledb.getConnection({ poolAlias: 'hrpool' });

. . . // Use connection from the pool

await connection.close(); // always release the connection back to the pool

The presence of the poolAlias attribute indicates the previously created connection pool should be used instead of creating a standalone connection. This syntax is useful when you want to pass other attributes to a pooled getConnection() call, such as for proxy connections or with connection tagging:

const connection = await oracledb.getConnection({ poolAlias: 'hrpool', tag: 'loc=cn;p=1' });

. . . // Use connection from the pool

await connection.close(); // always release the connection back to the pool

To use the default pool in this way you must explicitly pass the alias default:

const connection = await oracledb.getConnection({ poolAlias: 'default', tag: 'loc=cn;p=1' });

. . . // Use connection from the pool

await connection.close(); // always release the connection back to the pool

A specific pool can be retrieved from the cache by passing its pool alias to oracledb.getPool():

const pool = oracledb.getPool('hrpool');
const connection = await pool.getConnection();

. . . // Use connection from the pool

await connection.close();

4.4.4. Connection Pool Queue

The number of users that can concurrently do database operations is limited by the number of connections in the pool. The maximum number of connections is poolMax. Node-oracledb queues any additional pool.getConnection() requests to prevent users from immediately getting an error that the database is not available. The connection pool queue allows applications to gracefully handle more users than there are connections in the pool, and to handle connection load spikes without having to set poolMax too large for general operation.

If the application has called pool.getConnection() (or oracledb.getConnection() calls that use a pool) enough times so that all connections in the pool are in use, and further getConnection() calls are made, then each of those new getConnection() requests will be queued and will not return until an in-use connection is released back to the pool with connection.close(). If, instead, poolMax has not been reached, then the additional connection requests can be immediately satisfied and are not queued.

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, the pool.getConnection() call returns the error NJS-040: connection request timeout to the application.

If more than oracledb.queueMax pending connection requests are in the queue, then pool.getConnection() calls will immediately return an error NJS-076: connection request rejected. Pool queue length queueMax reached and will not be queued. Use this to protect against connection request storms. The setting helps applications return errors early when many connections are requested concurrently. This avoids connection requests blocking (for up to queueTimeout seconds) while waiting an available pooled connection. It lets you see when the pool is too small.

You may also experience NJS-040 or NJS-076 errors if your application is not correctly closing connections, or if are using node-oracledb Thick mode and UV_THREADPOOL_SIZE is too small.

4.4.5. Connection Pool Monitoring

Connection pool usage should be monitored to choose the appropriate settings for your workload. If the current settings are non optimal, then pool.reconfigure() can be called to alter the configuration.

Pool attributes connectionsInUse and connectionsOpen always provide basic information about an active pool:

const pool = await oracledb.createPool(...);

. . .

console.log(pool.connectionsOpen);   // how big the pool actually is
console.log(pool.connectionsInUse);  // how many of those connections are held by the application

Statistics are calculated from the time the pool was created, or since pool.reconfigure() was used to reset the statistics.

The recording of pool queue statistics, pool settings, and related environment variables can be enabled by setting enableStatistics to true when using oracledb.createPool() or pool.reconfigure().

To enable recording of statistics when creating the pool:

const pool = await oracledb.createPool({
    enableStatistics : true,   // default is false
    user             : "hr",
    password         : mypw,   // mypw contains the hr schema password
    connectString    : "localhost/FREEPDB1"
});
. . .

Statistics can alternatively be enabled on a running pool with:

await pool.reconfigure({ enableStatistics: true });

Applications can then get the current statistics by calling pool.getStatistics() which returns a PoolStatistics Class object. Attributes of the object can be accessed individually for your tracing requirements. The complete statistics can be printed by calling poolstatistics.logStatistics().

const poolstatistics = pool.getStatistics();

console.log(poolstatistics.currentQueueLength);  // print one attribute
poolstatistics.logStatistics();                  // print all statistics to the console

Alternatively the statistics can be printed directly by calling pool.logStatistics().

pool.logStatistics();    // print all statistics to the console

The output of poolstatistics.logStatistics() and pool.logStatistics() is identical.

For efficiency, the minimum, maximum, average, and sum of times in the pool queue are calculated when requests are removed from the queue. They include times for connection requests that were dequeued when a pool connection became available, and also for connection requests that timed out. They do not include times for connection requests still waiting in the queue.

The sum of ‘requests failed’, ‘requests exceeding queueMax’, and ‘requests exceeding queueTimeout’ is the number of pool.getConnection() calls that failed.

The PoolStatistics object and logStatistics() function record the following:

Table 4.1 PoolStatistics Class Attribute and Equivalent logStatistics() Label

Pool Statistics Class Attribute	logStatistics() Label	Description
thin	thin mode	Indicates whether the driver is in Thin or Thick mode.
gatheredDate	gathered at	The time the statistics were taken.
upTime	up time (milliseconds)	The number of milliseconds since this pool was created.
upTimeSinceReset	up time from last reset (milliseconds)	The number of milliseconds since the statistics were initialized or reset.
connectionRequests	connection requests	The number of getConnection() requests made to this pool.
requestsEnqueued	requests enqueued	The number of getConnection() requests that were added to this pool’s queue (waiting for the application to return an in-use connection to the pool) because every connection in this pool was already being used.
requestsDequeued	requests dequeued	The number of getConnection() requests that were dequeued when a connection in this pool became available for use.
failedRequests	requests failed	The number of getConnection() requests that failed due to an Oracle Database error. Does not include queueMax or queueTimeout errors.
rejectedRequests	requests exceeding queueMax	The number of getConnection() requests rejected because the number of connections in the pool queue exceeded the queueMax limit.
requestTimeouts	requests exceeding queueTimeout	The number of queued getConnection() requests that were timed out from the pool queue because they exceeded the queueTimeout time.
currentQueueLength	current queue length	The current number of getConnection() requests that are waiting in the pool queue.
maximumQueueLength	maximum queue length	The maximum number of getConnection() requests that were ever waiting in the pool queue at one time.
timeInQueue	sum of time in queue (milliseconds)	The sum of the time (milliseconds) that dequeued requests spent in the pool queue.
minimumTimeInQueue	minimum time in queue (milliseconds)	The minimum time (milliseconds) that any dequeued request spent in the pool queue.
maximumTimeInQueue	maximum time in queue (milliseconds)	The maximum time (milliseconds) that any dequeued request spent in the pool queue.
averageTimeInQueue	average time in queue (milliseconds)	The average time (milliseconds) that dequeued requests spent in the pool queue.
connectionsInUse	pool connections in use	The number of connections from this pool that getConnection() returned successfully to the application and have not yet been released back to the pool.
connectionsOpen	pool connections open	The number of idle or in-use connections to the database that the pool is currently managing.
connectString	connectString	The connection string that is used to connect to the Oracle Database instance.
edition	edition	The edition name used.
events	events	Denotes whether the Oracle Client events mode is enabled or not.
externalAuth	externalAuth	Denotes whether connections are established using external authentication or not.
homogeneous	homogeneous	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).
poolAlias	poolAlias	The alias of this pool in the connection pool cache.
poolIncrement	poolIncrement	The number of connections that are opened whenever a connection request exceeds the number of currently open connections.
poolMax	poolMax	The maximum number of connections that can be open in the connection pool.
poolMaxPerShard	poolMaxPerShard	The maximum number of connections in the pool that can be used for any given shard in a sharded database.
poolMin	poolMin	The minimum number of connections a connection pool maintains, even when there is no activity to the target database.
poolPingInterval	poolPingInterval (seconds)	The maximum number of seconds that a connection can remain idle in a connection pool before node-oracledb pings the database prior to returning that connection to the application.
poolPingTimeout	poolPingTimeout (milliseconds)	The number of milliseconds that a connection should wait for a response from connection.ping().
poolTimeout	poolTimeout (seconds)	The time (in seconds) after which the pool terminates idle connections (unused in the pool).
queueMax	queueMax	The maximum number of pending pool.getConnection() calls that can be queued.
queueTimeout	queueTimeout (milliseconds)	The time (in milliseconds) that a connection request should wait in the queue before the request is terminated.
sessionCallback	sessionCallback	The Node.js or PL/SQL function that is invoked by pool.getConnection() when the connection is brand new.
sodaMetaDataCache	sodaMetaDataCache	Determines whether the pool has a metadata cache enabled for SODA collection access.
stmtCacheSize	stmtCacheSize	The number of statements to be cached in the statement cache of each connection.
user	user	The database username for connections in the pool.
threadPoolSize	UV_THREADPOOL_SIZE	The value of process.env.UV_THREADPOOL_SIZE which is the number of worker threads for this process. Note this shows the value of the variable, however if this variable was set after the thread pool started, the thread pool will still be the default size of 4. This attribute only affects the node-oracledb Thick mode.

4.4.6. Connection Pool Pinging

When a connection is aquired from a pool with getConnection(), node-oracledb does some internal checks to validate if the about-to-be-returned connection is usable. If it is not usable, node-oracledb can replace it with a different connection before returning this to the application.

Connections may become unusable for various reasons including network dropouts, database instance failures, session termination from the database resource manager or user resource profile IDLE_TIME, or from a DBA issuing an ALTER SYSTEM KILL SESSION command.

By default, idle connections in the pool are unaware of these events. So, a getConnection() call could return an unusable connection to the application and errors would only occur when it is later used. The internal pool validation checks help provide tolerance against this situation so that statement execution using a connection is more likely to succeed.

Each time getConnection() is called, a lightweight connection validity check occurs. (In node-oracledb Thick mode, this requires Oracle Client library version 12.2 or later). The lightweight check allows node-oracledb to detect and replace connections that have become unusable due to some network errors.

An additional internal check performed by getConnection() can be configured during pool creation. This extra check helps detect errors such as the connection having exceeded the user profile resource limits, or from an explicit session closure from a DBA. This extra check performs a round-trip ping to the database which impacts performance. So, it is not done for each getConnection() call by default.

The frequency of pinging can be controlled with the oracledb.poolPingInterval property or during pool creation to meet your quality of service requirements.

The default poolPingInterval value is 60 seconds, which is suitable for most active applications. Possible values are:

Table 4.2 poolPingInterval Value

poolPingInterval Value	Behavior of a Pool getConnection() Call
n < 0	Never checks for connection validity
n = 0	Always checks for connection validity
n > 0	Checks validity if the connection has been idle in the pool (not “checked out” to the application by getConnection()) for at least n seconds

When getConnection() is called to return a pooled connection, and the connection has been idle in the pool (not “checked out” to the application by getConnection()) for the specified poolPingInterval time, then an internal “ping” will be performed first. If the ping detects the connection is invalid then node-oracledb internally drops the unusable connection and obtains another from the pool. This second connection may also need a ping. This ping-and-release process may be repeated until:

• an existing connection that does not qualify for pinging is obtained. The getConnection() call returns this to the application. Note that since a ping may not have been performed, the connection is not guaranteed to be usable.

• a new, usable connection is opened. This is returned to the application.

• a number of unsuccessful attempts to find a valid connection have been made, after which an error is returned to the application.

Pools in active use may never have connections idle longer than poolPingInterval, so pinging often only occurs for infrequently accessed connection pools.

Because a ping may not occur every time a connection is returned from pool.getConnection(), and also it is possible for network outages to occur after getConnection() is called, applications should continue to use appropriate statement execution error checking.

For ultimate scalability, disable explicit pool pinging by setting poolPingInterval to a negative value, and make sure the firewall, database resource manager, or user profile are not expiring idle connections. See Preventing Premature Connection Closing. When using node-oracledb Thick mode, use use Oracle client 12.2 (or later) libraries.

In all cases, when a bad connection is released back to the pool with connection.close(), the connection is automatically destroyed. This allows a valid connection to the database to be opened by some subsequent getConnection() call.

Explicit pings can be performed at any time with connection.ping().

The time to wait for a response from connection.ping() can be controlled with the oracledb.poolPingTimeout property or with the poolPingTimeout property during pool creation.

The default poolPingTimeout value is 5000 milliseconds. The behavior of a pool getConnection() call differs based on the value specified in the poolPingTimeout property as detailed below.

Table 4.3 poolPingTimeout Value

poolPingTimeout Value	Behavior of a Pool getConnection() Call
n < 0	Returns the error NJS-007: invalid value for "poolPingTimeout" in parameter 1 if the poolPingTimeout property in oracledb.createPool() is set to a negative value. Returns the error NJS-004: invalid value for property "poolPingTimeout" if oracledb.poolPingTimeout is set to a negative value.
n = 0	Waits until connection.ping() succeeds with a response or fails with an error.
n > 0	Waits for connection.ping() to respond by n milliseconds. If ping() does not respond by n milliseconds, then the connection is forcefully closed.

4.4.7. Connection Tagging and Session State

Applications can set “session” state in each connection. For all practical purposes, connections are synonymous with sessions. Examples of session state are NLS settings from ALTER SESSION statements. Pooled connections will retain their session state after they have been released back to the pool with connection.close(). However, because pools can grow, or connections in the pool can be recreated, there is no guarantee a subsequent pool.getConnection() call will return a database connection that has any particular state.

The oracledb.createPool() option attribute sessionCallback can be used to set session state efficiently so that connections have a known session state. The sessionCallback can be a Node.js function that will be called whenever pool.getConnection() will return a newly created database connection that has not been used before. It is also called when connection tagging is being used and the requested tag is not identical to the tag in the connection returned by the pool. It is called before pool.getConnection() returns in these two cases. It will not be called in other cases. Using a callback saves the cost of setting session state if a previous user of a connection has already set it. The caller of pool.getConnection() can always assume the correct state is set. The sessionCallback can also be a PL/SQL procedure.

Connection tagging and sessionCallback were introduced in node-oracledb 3.1.

There are three common scenarios for sessionCallback:

• When all connections in the pool should have the same state use a simple Node.js Session Callback without tagging.

• When connections in the pool require different state for different users use a Node.js Session Tagging Callback.

• With DRCP, use a PL/SQL Session Tagging Callback.

4.4.7.1. Connection Tagging

Connection tagging is used when connections in a pool should have differing session states. In order to retrieve a connection with a desired state, the tag attribute in getConnection() needs to be set.

Note

In this release, connection tagging is only supported in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

Pooled connections can be tagged to record their session state by setting the property connection.tag to a user chosen string that represents the state you have set in the connection. A pool.getConnection({tag: 'mytag'}) call can request a connection that has the specified tag. If no available connections with that tag exist in the pool, an untagged connection or a newly created connection will be returned. If the optional getConnection() attribute matchAnyTag is true, then a connection that has a different tag may be returned.

The sessionCallback function is invoked before pool.getConnection() returns if the requested tag is not identical to the actual tag of the pooled connection. The callback can compare the requested tag with the current actual tag in connection.tag. Any desired state change can be made to the connection and connection.tag can be updated to record the change. The best practice recommendation is to set the tag in the callback function but, if required, a tag can be set anytime prior to closing the connection. To clear a connection’s tag set connection.tag to an empty string.

You would use tagging where you want pool.getConnection() to return a connection which has one of several different states. If all connections should have the same state then you can simply set sessionCallback and not use tagging. Also, it may not be worthwhile using a large number of different tags, or using tagging where connections are being dropped and recreated frequently since the chance of pool.getConnection() returning an already initialized connection with the requested tag could be low, so most pool.getConnection() calls would return a connection needing its session reset, and tag management will just add overhead.

When node-oracledb is using Oracle Client libraries 12.2 or later, then node-oracledb uses ‘multi-property tags’ and the tag string must be of the form of one or more “name=value” pairs separated by a semi-colon, for example "loc=uk;lang=cy". The Oracle session pool used by node-oracledb has various heuristics to determine which connection is returned to the application. Refer to the multi-property tags documentation. The callback function can parse the requested multi-property tag and compare it with the connection’s actual properties in connection.tag to determine what exact state to set and what value to update connection.tag to.

4.4.7.2. Node.js Session Callback

When all connections in the pool should have the same state, a simple callback can be used.

This example sets two NLS settings in each pooled connection. They are only set the very first time connections are established to the database. The requestedTag parameter is ignored because it is only valid when tagging is being used:

function initSession(connection, requestedTag, callbackFn) {
connection.execute(
    `alter session set nls_date_format = 'YYYY-MM-DD' nls_language = AMERICAN`,
    callbackFn);
}

try {
    const pool = await oracledb.createPool({
        user: 'hr',
        password: mypw,  // mypw contains the hr schema password
        connectString: 'localhost/FREEPDB1',
        sessionCallback: initSession
    });
    . . .
}

Note that a single ALTER SESSION statement is used to set multiple properties, avoiding round-trips of repeated execute() calls. If you need to execute multiple SQL statements, then use an anonymous PL/SQL block for the same reason:

function initSession(connection, requestedTag, callbackFn) {
    connection.clientId = "Chris";
    connection.execute(
        `begin
            execute immediate 'alter session set nls_date_format = ''YYYY-MM-DD'' nls_language = AMERICAN';
            insert into user_log (id, ts) values (sys_context('userenv', 'client_identifier'), systimestamp);
            commit;
         end;`,
        callbackFn);
}

See sessionfixup.js for a runnable example.

4.4.7.3. Node.js Session Tagging Callback

When connections in the pool require different state for different users and you are not using DRCP, then use a JavaScript callback with tagging.

This example Node.js callback function ensures the connection contains valid settings for an application-specific “USER_TZ=X” property where X is a valid Oracle timezone:

function initSession(connection, requestedTag, callbackFn) {
    const tagParts = requestedTag.split('=');
    if (tagParts[0] != 'USER_TZ') {
        callbackFn(new Error('Error: Only property USER_TZ is supported'));
        return;
    }

    connection.execute(
        `ALTER SESSION SET TIME_ZONE = '${tagParts[1]}'`,
        (err) => {
            // Record the connection's new state and return
            connection.tag = requestedTag;
            callbackFn(err);
        }
    );
}

try {
    await oracledb.createPool({
        user: 'hr',
        password: mypw,  // mypw contains the hr schema password
        connectString: 'localhost/FREEPDB1',
        sessionCallback: initSession
    });

    // Get a connection with a given tag (and corresponding session state) from the pool
    const connection = await oracledb.getConnection({poolAlias: 'default', tag: "USER_TZ=UTC" });

    . . . // Use the connection

    // The connection will be returned to the pool with the tag value of connection.tag
    await connection.close(); // always release the connection back to the pool

    . . .

The initSession() session callback function is only invoked by getConnection() if the node-oracledb connection pool cannot find a connection with the requested tag. The session callback function adjusts the connection state and records the matching tag.

Other parts of the application may request connections with different tags. Eventually the pool would contain connections with various different states (and equivalent tags). Each getConnection() call will attempt to return a connection which already has the requested tag. If a matching free connection cannot be found, the pool may grow or the session state from another connection is cleared. Then initSession() is called so that the desired connection state can be set.

For runnable examples, see sessiontagging1.js and sessiontagging2.js.

4.4.7.4. PL/SQL Session Tagging Callback

Note

In this release, PL/SQL callbacks are only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

When using DRCP, tagging is most efficient when using a PL/SQL callback.

When node-oracledb is using Oracle Client libraries 12.2 or later, sessionCallback can be a string containing the name of a PL/SQL procedure that is called when the requested tag does not match the actual tag in the connection. When the application uses DRCP connections, a PL/SQL callback can avoid the round-trip calls that a Node.js function would require to set session state. For non-DRCP connections, the PL/SQL callback will require a round-trip from the application.

After a PL/SQL callback completes and pool.getConnection() returns, connection.tag will have the same property values as the requested tag. The property order may be different. For example you may request “USER_TZ=UTC;LANGUAGE=FRENCH” but connection.tag may be “LANGUAGE=FRENCH;USER_TZ=UTC”. When matchAnyTag is true, then various heuristics are used to determine which connection in the pool to use. See the multi-property tags documentation. Additional properties may be present in connection.tag.

There is no direct way for Node.js to know if the PL/SQL procedure was called or what session state it changed. After pool.getConnection() returns, care must be taken to set connection.tag to an appropriate value.

A sample PL/SQL callback procedure looks like:

CREATE OR REPLACE PACKAGE myPackage AS
  TYPE property_t IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(64);
  PROCEDURE buildTab(
    tag          IN  VARCHAR2,
    propertyTab  OUT property_t
  );
  PROCEDURE myPlsqlCallback (
    requestedTag IN  VARCHAR2,
    actualTag    IN  VARCHAR2
  );
END;
/

CREATE OR REPLACE PACKAGE BODY myPackage AS

  -- Parse the "property=value" pairs in the tag
  PROCEDURE buildTab(tag IN VARCHAR2, propertyTab OUT property_t) IS
    property  VARCHAR2(64);
    propertyName  VARCHAR2(64);
    propertyValue VARCHAR2(64);
    propertyEndPos NUMBER := 1;
    propertyStartPos NUMBER := 1;
    propertyNameEndPos NUMBER := 1;
  begin
    WHILE (LENGTH(tag) > propertyEndPos)
    LOOP
      propertyEndPos := INSTR(tag, ';', propertyStartPos);
      IF (propertyEndPos = 0) THEN
        propertyEndPos := LENGTH(tag) + 1;
      END IF;
      propertyNameEndPos := INSTR(tag, '=', propertyStartPos);
      propertyName := SUBSTR(tag, propertyStartPos,
                   propertyNameEndPos - propertyStartPos);
      propertyValue := SUBSTR(tag, propertyNameEndPos + 1,
                    propertyEndPos - propertyNameEndPos - 1);
      propertyTab(propertyName) := propertyValue;
      propertyStartPos := propertyEndPos + 1;
    END LOOP;
  END;

  PROCEDURE myPlsqlCallback (
    requestedTag IN VARCHAR2,
    actualTag IN VARCHAR2
  ) IS
    reqPropTab property_t;
    actPropTab property_t;
    propertyName VARCHAR2(64);
  BEGIN
    buildTab(requestedTag, reqPropTab);
    buildTab(actualTag, actPropTab);

    -- Iterate over requested properties to set state when it's not
    -- currently set, or not set to the desired value
    propertyName := reqPropTab.FIRST;
    WHILE (propertyName IS NOT NULL)
    LOOP
      IF ((NOT actPropTab.exists(propertyName)) OR
         (actPropTab(propertyName) != reqPropTab(propertyName))) THEN
        IF (propertyName = 'SDTZ') THEN
          EXECUTE IMMEDIATE
            'ALTER SESSION SET TIME_ZONE=''' || reqPropTab(propertyName) || '''';
        ELSE
          RAISE_APPLICATION_ERROR(-20001,'Unexpected session setting requested');
        END IF;
      END IF;
      propertyName := reqPropTab.NEXT(propertyName);
    END LOOP;
    -- Could iterate over other actual properties to set any to a default state
  END;

END myPackage;
/

This could be used in your application like:

const sessionTag = "SDTZ=UTC";

try {
    const pool = await oracledb.createPool({
                 user: 'hr',
                 password: mypw,  // mypw contains the hr schema password
                 connectString: 'localhost/FREEPDB1',
                 sessionCallback: "myPackage.myPlsqlCallback"
                });
    . . .

    const connection = await pool.getConnection({tag: sessionTag});

    . . . // The value of connection.tag will be sessionTag
         // Use connection.

    await connection.close();
}

4.4.8. Heterogeneous and Homogeneous Connection Pools

By default, connection pools are ‘homogeneous’ meaning that all connections use the same database credentials. Both node-oracledb Thin and Thick modes support homogeneous pools.

4.4.8.1. Creating Heterogeneous Pools

The node-oracledb Thick mode additionally supports heterogeneous pools, allowing different user names and passwords to be passed each time a connection is acquired from the pool with pool.getConnection().

To create a heterogeneous pool, set the createPool() parameter, homogeneous, to false.

When a heterogeneous pool is created by setting homogeneous to false and no credentials supplied during pool creation, then a user name and password may be passed to pool.getConnection():

const pool = await oracledb.createPool({
    connectString : "localhost/FREEPDB1",  // no user name or password
    homogeneous   : false,
    . . .  // other pool options such as poolMax
});

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

. . . // use connection

await connection.close();

The connectString is required during pool creation since the pool is created for one database instance.

Different user names may be used each time pool.getConnection() is called.

When applications want to use connection pools but are not able to use connection.clientId to distinguish application users from database schema owners then a ‘heterogeneous’ connection pool might be an option.

To use heterogeneous pools with the connection pool cache, the alias should be explicitly stated, even if it is the default pool:

const connection = await oracledb.getConnection({
    poolAlias: "default",
    user     : "hr",
    password : mypw,  // mypw contains the hr schema password
});

For heterogeneous pools, 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.

4.5. Connecting Using Proxy Authentication

Proxy authentication allows a user (the “session user”) to connect to Oracle Database using the credentials of a “proxy user”. Statements will run as the session user. Proxy authentication is generally used in three-tier applications where one user owns the schema while multiple end-users access the data. For more information about proxy authentication, see the Oracle documentation.

An alternative to using proxy users is to set connection.clientId after connecting and use its value in statements and in the database, for example for monitoring.

Pool proxy authentication requires a heterogeneous pool.

To grant access, typically a DBA would execute:

ALTER USER sessionuser GRANT CONNECT THROUGH proxyuser;

For example, to allow a user called MYPROXYUSER to access the schema of HR:

SQL> CONNECT system

SQL> ALTER USER hr GRANT CONNECT THROUGH myproxyuser;

SQL> CONNECT myproxyuser[hr]/myproxyuserpassword

SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') AS SESSION_USER,
  2         SYS_CONTEXT('USERENV', 'PROXY_USER')   AS PROXY_USER
  3  FROM DUAL;

SESSION_USER         PROXY_USER
-------------------- --------------------
HR                   MYPROXYUSER

See the Client Access Through a Proxy section in the Oracle Call Interface manual for more details about proxy authentication.

To use the proxy user with a node-oracledb heterogeneous connection pool you could do:

const myproxyuserpw = ... // the password of the 'myproxyuser' proxy user

const pool = await oracledb.createPool({ connectString: "localhost/orclpdb1", homogeneous: false });
const connection = await pool.getConnection({ user: 'myproxyuser[hr]', password: myproxyuserpw});

. . . // connection has access to the HR schema objects

await connection.close();

Other proxy cases are supported such as:

const myproxyuserpw = ... // the password of the 'myproxyuser' proxy user

const pool = await oracledb.createPool({
    user          : "myproxyuser",
    password      : myproxyuserpw,
    connectString : "localhost/FREEPDB1",
    homogeneous   : false,
    . . .  // other pool options such as poolMax can be used
});

const connection = await pool.getConnection({ user : 'hr' });  // the session user

. . . // connection has access to the HR schema objects

await connection.close();

4.6. Connecting Using External Authentication

External Authentication allows applications to use an external password store (such as an 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.

Note

Connecting to Oracle Database using external authentication is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

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 oracledb.createPool() calls, respectively.

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.

For a standalone connection, you can authenticate as an externally identified user like:

const config = { connectString: "localhost/orclpdb1", externalAuth: true };
const connection = await oracledb.getConnection(config);

. . . // connection has access to the schema objects of the externally identified user

If a user HR has been given the CONNECT THROUGH grant from the externally identified user MYPROXYUSER:

ALTER USER hr GRANT CONNECT THROUGH myproxyuser;

then to specify that the session user of the connection should be HR, use:

const config = { connectString: "localhost/orclpdb1", user: "[hr]", externalAuth: true };
const connection = await oracledb.getConnection(config);

. . . // connection has access to the HR schema objects

For a Pool, you can authenticate as an externally identified user like:

const config = { connectString: "localhost/orclpdb1", externalAuth: true };
const pool = await oracledb.createPool(config);
const connection = await pool.getConnection();

. . . // connection has access to the schema objects of the externally identified user

await connection.close();

If a user HR has been given the CONNECT THROUGH grant from the externally identified user, then to specify that the session user of the connection should be HR, use:

const config = { connectString: "localhost/orclpdb1", externalAuth: true };
const pool = await oracledb.createPool(config);
const connection = await pool.getConnection({ user: "[hr]" });

. . . // connection has access to the HR schema objects

await connection.close();

Note this last case needs Oracle Client libraries version 18 or later.

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 oracledb.poolTimeout seconds, then the number of open connections does not fall below poolMin.

4.7. Token-Based Authentication

Token-Based Authentication allows users to connect to a database by using an encrypted authentication token without having to enter a database username and password. The authentication token must be valid and not expired for the connection to be successful. Users already connected will be able to continue work after their token has expired but they will not be able to reconnect without getting a new token.

The two authentication methods supported by node-oracledb are Open Authorization OAuth 2.0 and Oracle Cloud Infrastructure (OCI) Identity and Access Management IAM.

Token-based authentication can be used for both standalone connections and connection pools.

4.7.1. OAuth 2.0 Token-Based Authentication

Oracle Cloud Infrastructure (OCI) users can be centrally managed in a Microsoft Azure Active Directory (Azure AD) service. Open Authorization (OAuth 2.0) token-based authentication allows users to authenticate to Oracle Database using Azure AD OAuth 2.0 tokens. Your Oracle Database must be registered with Azure AD. Both Thin and Thick modes of the node-oracledb driver support OAuth 2.0 token-based authentication.

See Authenticating and Authorizing Microsoft Azure Active Directory Users for Oracle Autonomous Databases for more information.

When using node-oracledb in Thick mode, Oracle Client libraries 19.15 (or later), or 21.7 (or later) are needed.

4.7.1.1. OAuth 2.0 Token Generation

Authentication tokens can be obtained in several ways. For example, you can use a curl command against the Azure Active Directory API such as:

curl -X POST -H 'Content-Type: application/x-www-form-urlencoded'
https://login.microsoftonline.com/[<TENANT_ID>]/oauth2/v2.0/token
-d 'client_id = <APP_ID>'
-d 'scope = <SCOPES>'
-d 'username = <USER_NAME>'
-d 'password = <PASSWORD>'
-d 'grant_type = password'
-d 'client_secret = <SECRET_KEY>'

Substitute your own values as appropriate for each argument.

This returns a JSON response containing an access_token attribute. See Microsoft identity platform and OAuth 2.0 authorization code flow for more details. This attribute can be passed as the oracledb.getConnection() attribute accessToken or as the oracledb.createPool() attribute accessToken.

Alternatively, authentication tokens can be generated by calling the Azure Active Directory REST API, for example:

function getOauthToken() {
    const requestParams = {
        client_id     : <CLIENT_ID>,
        client_secret : <CLIENT_SECRET>,
        grant_type    : 'client_credentials',
        scope         : <SCOPES>,
    };
    const tenantId = <TENANT_ID>;
    const url = `https://login.microsoftonline.com/${tenantId}/oauth2/v2.0/token`;
    return new Promise(function(resolve, reject) {
        request.post({
            url       : url,
            body      : queryString.stringify(requestParams),
            headers   : { 'Content-Type': 'application/x-www-form-urlencoded' }
        }, function(err, response, body) {
            if (err) {
                reject(err);
            } else {
                resolve(JSON.parse(body).access_token);
            }
        });
    });
}

Substitute your own values as appropriate for each argument. The use of getOauthToken() is shown in subsequent examples.

Alternatively, OAuth 2.0 authentication tokens can be generated in the node-oracledb driver using the Azure Software Development Kit (SDK). This was introduced in node-oracledb 6.3. To use the Azure SDK, you must install the Microsoft Authentication Library for Node (msal-node) package which can be done with the following command:

npm install @azure/msal-node

Authentication tokens generated by the Azure SDK can be read by your application. For example:

async function getToken(accessTokenConfig) {
    ... // Azure-specific authentication types
}

See sampleazuretokenauth.js for a runnable example using the Azure SDK. The use of getToken() and accessTokenConfig is shown in subsequent examples.

4.7.1.2. OAuth 2.0 Standalone Connections

Standalone connections can be created using OAuth2 token-based authentication, for example:

let accessTokenStr;  // the token string. In this app it is also the token "cache"

async function tokenCallback(refresh) {
    if (refresh || !accessTokenStr) {
        accessTokenStr = await getOauthToken(); // getOauthToken() was shown earlier
    }
    return accessTokenStr;
}

async function init() {
    try {
        await oracledb.getConnection({
            accessToken   : tokenCallback,    // the callback returning the token
            externalAuth  : true,             // must specify external authentication
            connectString : connect_string    // Oracle Autonomous Database connection string
        });
    } catch (err) {
        console.error(err);
    }
}

In this example, the global variable accessTokenStr is used to “cache” the access token string so any subsequent callback invocation will not necessarily have to incur the expense of externally getting a token. For example, if the application opens two connections for the same user, the token acquired for the first connection can be reused without needing to make a second REST call.

The getConnection() function’s accessToken attribute in this example is set to the callback function that returns an OAuth 2.0 token used by node-oracledb for authentication. This function tokenCallback() will be invoked when getConnection() is called. If the returned token is found to have expired, then tokenCallback() will be called a second time. If the second invocation of tokenCallback() also returns an expired token, then the connection will fail.

The refresh parameter is set internally by the node-oracledb driver depending on the status and validity of the authentication token provided by the application. The value of the refresh parameter will be different every time the callback is invoked:

• When refresh is true, the token is known to have expired so the application must get a new token. This is then stored in the global variable accessTokenStr and returned.

• When refresh is false, the application can return the token stored in accessTokenStr, if it is set. But if it is not set (meaning there is no token cached), then the application externally acquires a token, stores it in accessTokenStr, and returns it.

If you set the accessTokenConfig property in addition to the accessToken, externalAuth, and connectString properties during standalone connection creation, then you can use the Azure SDK to generate tokens in the callback method. For example:

let accessTokenData;  // The token string

async function callbackfn(refresh, accessTokenConfig) {
    if (refresh || !accessTokenData) {
        accessTokenData = await getToken(accessTokenConfig); // getToken() was shown earlier
    }
    return accessTokenData;
}

async function init() {
    try {
        await oracledb.getConnection({
            accessToken   : callbackfn,        // the callback returning the token
            accessTokenConfig : {
                                    ...        // Azure-specific parameters to be set
                                               // when using Azure SDK
                                }
            externalAuth  : true,              // must specify external authentication
            connectString : '...'              // Oracle Autonomous Database connection string
        });
    } catch (err) {
        console.error(err);
    }
}

See sampleazuretokenauth.js for a runnable example using the Azure SDK. The callback and refresh parameter descriptions are detailed in the example above.

4.7.1.3. OAuth 2.0 Connection Pooling

Pooled connections can be created using OAuth 2.0 token-based authentication, for example:

let accessTokenStr;  // The token string. In this app it is also the token "cache"

async function tokenCallback(refresh) {
    if (refresh || !accessTokenStr) {
        accessTokenStr = await getOauthToken(); // getOauthToken() was shown earlier
    }
    return accessToken;
}

async function init() {
    try {
        await oracledb.createPool({
            accessToken   : tokenCallback,        // the callback returning the token
            externalAuth  : true,                 // must specify external authentication
            homogeneous   : true,                 // must use an homogeneous pool
            connectString : '...'                 // Oracle Autonomous Database connection string
        });
    } catch (err) {
        console.error(err);
    }
}

See OAuth 2.0 Standalone Connections for a description of the callback and refresh parameter. With connection pools, the accessToken attribute sets a callback function which will be invoked at the time the pool is created (even if poolMin is 0). It is also called when the pool needs to expand (causing new connections to be created) and the current token has expired.

If you set the accessTokenConfig property in addition to the accessToken, externalAuth, homogeneous, and connectString properties during connection pool creation, then you can use the Azure SDK to generate tokens in the callback method. For example:

let accessTokenData;  // The token string

async function callbackfn(refresh, accessTokenConfig) {
    if (refresh || !accessTokenData) {
        accessTokenData = await getToken(accessTokenConfig);  // getToken() was shown earlier
    }
    return accessTokenData;
}

async function init() {
    try {
        await oracledb.createPool({
            accessToken   : tokenCallback,        // the callback returning the token
            accessTokenConfig : {
                                    ...           // Azure-specific parameters to be set
                                                  // when using Azure SDK
                                }
            externalAuth  : true,                 // must specify external authentication
            homogeneous   : true,                 // must use an homogeneous pool
            connectString : '...'                 // Oracle Autonomous Database connection string
        });
    } catch (err) {
        console.error(err);
    }
}

See sampleazuretokenauth.js for a runnable example using the Azure SDK. See OAuth 2.0 Standalone Connections for a description of the callback and refresh parameter.

4.7.1.4. OAuth 2.0 Connection Strings

Applications built with node-oracledb 5.5, or later, should use the connection or pool creation parameters described earlier. However, if you cannot use them, you can use OAuth 2.0 Token Authentication by configuring Oracle Net options.

Note

In this release, OAuth 2.0 connection strings are only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

This requires Oracle Client libraries 19.15 (or later), or 21.7 (or later).

Save the generated access token to a file and set the connect descriptor TOKEN_LOCATION option to the directory containing the token file. The connect descriptor parameter TOKEN_AUTH must be set to OAUTH, the PROTOCOL value must be TCPS, the SSL_SERVER_DN_MATCH value should be ON, and the parameter SSL_SERVER_CERT_DN should be set. For example, your tnsnames.ora file might contain:

db_alias =
  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(PORT=1522)(HOST=abc.oraclecloud.com))
    (CONNECT_DATA=(SERVICE_NAME=db_low.adb.oraclecloud.com))
      (SECURITY=
        (SSL_SERVER_DN_MATCH=ON)
        (SSL_SERVER_CERT_DN="CN=efg.oraclecloud.com, O=Oracle Corporation, L=Redwood City, ST=California, C=US")
        (TOKEN_AUTH=OAUTH)
        (TOKEN_LOCATION='/opt/oracle/token')
        ))

You can alternatively set TOKEN_AUTH and TOKEN_LOCATION in a sqlnet.ora file. The TOKEN_AUTH and TOKEN_LOCATION values in a connection string take precedence over the sqlnet.ora settings.

See Oracle Net Services documentation for more information.

4.7.2. IAM Token-Based Authentication

Token-based authentication allows Oracle Cloud Infrastructure users to authenticate to Oracle Database with Oracle Identity Access Management (IAM) tokens. Both Thin and Thick modes of the node-oracledb driver support IAM token-based authentication.

When using node-oracledb in Thick mode, Oracle Client libraries 19.14 (or later), or 21.5 (or later) are needed.

See Configuring the Oracle Autonomous Database for IAM Integration for more information.

4.7.2.1. IAM Token Generation

Authentication tokens can be obtained in several ways. For example you can use the Oracle Cloud Infrastructure command line interface (OCI CLI) command run externally to Node.js:

oci iam db-token get

On Linux a folder .oci/db-token will be created in your home directory. It will contain the token and private key files needed by node-oracledb.

See Working with the Command Line Interface for more information on the OCI CLI.

Alternatively, IAM authentication tokens can be generated in the node-oracledb driver using the Oracle Cloud Infrastructure (OCI) SDK. This was introduced in node-oracledb 6.3. To use the OCI SDK, you must install the oci-sdk package which can be done with the following command:

npm install oci-sdk

See sampleocitokenauth.js for a runnable example using the OCI SDK.

4.7.2.2. IAM Token and Private Key Extraction

Token and private key files created externally can be read by Node.js applications, for example like:

function getIAMToken() {
    const tokenPath = '/home/cjones/.oci/db-token/token';
    const privateKeyPath = '/home/cjones/.oci/db-token/oci_db_key.pem';

    let token = '';
    let privateKey = '';
    try {
        // Read the token file
        token = fs.readFileSync(tokenPath, 'utf8');
        // Read the private key file
        const privateKeyFileContents = fs.readFileSync(privateKeyPath, 'utf-8');
        privateKeyFileContents.split(/\r?\n/).forEach(line => {
            if (line != '-----BEGIN PRIVATE KEY-----' &&
                line != '-----END PRIVATE KEY-----')
            privateKey = privateKey.concat(line);
        });
    } catch (err) {
        console.error(err);
    } finally {
        const tokenBasedAuthData = {
            token       : token,
            privateKey  : privateKey
        };
        return tokenBasedAuthData;
    }
}

The token and key can be used during subsequent authentication.

Token and private key values generated by the OCI SDK can be read by your application. For example:

async function getToken(accessTokenConfig) {
    ... // OCI-specific authentication details
}

See sampleocitokenauth.js for a runnable example using the OCI SDK. The use of getToken() and accessTokenConfig is shown in subsequent examples.

4.7.2.3. IAM Standalone Connections

Standalone connections can be created in the node-oracledb Thin and Thick modes using IAM token-based authentication.

let accessTokenObj;  // the token object. In this app it is also the token "cache"

function tokenCallback(refresh) {
    if (refresh || !accessTokenObj) {
        accessTokenObj = getIAMToken();     // getIAMToken() was shown earlier
    }
    return accessTokenObj;
}

async function init() {
    try {
        await oracledb.getConnection({
            accessToken    : tokenCallback,  // the callback returns the token object
            externalAuth   : true,           // must specify external authentication
            connectString  : '...'           // Oracle Autonomous Database connection string
        });
    } catch (err) {
        console.error(err);
    }
}

In this example, the global object accessTokenObj is used to “cache” the IAM access token and private key (using the attributes token and privateKey) so any subsequent callback invocation will not necessarily have to incur the expense of externally getting them. For example, if the application opens two connections for the same user, the token and private key acquired for the first connection can be reused without needing to make a second REST call.

The getConnection() function’s accessToken attribute in this example is set to the callback function that returns an IAM token and private key used by node-oracledb for authentication. This function tokenCallback() will be invoked when getConnection() is called. If the returned token is found to have expired, then tokenCallback() will be called a second time. If the second invocation of tokenCallback() also returns an expired token, then the connection will fail.

The refresh parameter is set internally by the node-oracledb driver depending on the status and validity of the authentication token provided by the application. The value of the refresh parameter will be different every time the callback is invoked:

• When refresh is true, the token is known to have expired so the application must get a new token and private key. These are then stored in the global object accessTokenObj and returned.

• When refresh is false, the application can return the token and private key stored in accessTokenObj, if it is set. But if it is not set (meaning there is no token or key cached), then the application externally acquires a token and private key, stores them in accessTokenObj, and returns it.

If you set the accessTokenConfig property in addition to the accessToken, externalAuth, and connectString properties during standalone connection creation, then you can use the OCI SDK to generate tokens in the callback method. For example:

let accessTokenData;  // The token string

async function callbackfn(refresh, accessTokenConfig) {
    if (refresh || !accessTokenData) {
        accessTokenData = await getToken(accessTokenConfig); // getToken() was shown earlier
    }
    return accessTokenData;
}

async function init() {
    try {
        await oracledb.getConnection({
            accessToken   : callbackfn,        // the callback returning the token
            accessTokenConfig : {
                                    ...        // OCI-specific parameters to be set
                                               // when using OCI SDK
                                }
            externalAuth  : true,              // must specify external authentication
            connectString : '...'              // Oracle Autonomous Database connection string
        });
    } catch (err) {
        console.error(err);
    }
}

See sampleocitokenauth.js for a runnable example using the OCI SDK. The callback and refresh parameter descriptions are detailed in the example above.

4.7.2.4. IAM Connection Pooling

Pooled connections can be created using IAM token-based authentication, for example:

let accessTokenObj;  // The token string. In this app it is also the token "cache"

function tokenCallback(refresh) {
    if (refresh || !accessTokenObj) {
        accessTokenObj = getIAMToken();      // getIAMToken() was shown earlier
    }
    return accessToken;
}

async function init() {
    try {
        await oracledb.createPool({
            accessToken   : tokenCallback,     // the callback returning the token
            externalAuth  : true,              // must specify external authentication
            homogeneous   : true,              // must use an homogeneous pool
            connectString : connect_string     // Oracle Autonomous Database connection string
        });
    } catch (err) {
        console.error(err);
    }
}

See IAM Standalone Connections for a description of the callback and refresh parameter. With connection pools, the accessToken attribute sets a callback function which will be invoked at the time the pool is created (even if poolMin is 0). It is also called when the pool needs to expand (causing new connections to be created) and the current token has expired.

If you set the accessTokenConfig property in addition to the accessToken, externalAuth, homogeneous, and connectString properties during connection pool creation, then you can use the OCI SDK to generate tokens in the callback method. For example:

let accessTokenData;  // The token string

async function callbackfn(refresh, accessTokenConfig) {
    if (refresh || !accessTokenData) {
        accessTokenData = await getToken(accessTokenConfig);
    }
    return accessTokenData;
}

async function init() {
    try {
        await oracledb.createPool({
            accessToken   : tokenCallback,        // the callback returning the token
            accessTokenConfig : {
                                    ...           // OCI-specific parameters to be set
                                                  // when using Azure SDK
                                }
            externalAuth  : true,                 // must specify external authentication
            homogeneous   : true,                 // must use an homogeneous pool
            connectString : '...'                 // Oracle Autonomous Database connection string
        });
    } catch (err) {
        console.error(err);
    }
}

See sampleocitokenauth.js for a runnable example using the OCI SDK. See IAM Standalone Connections for a description of the callback and refresh parameter.

4.7.2.5. IAM Connection Strings

Applications built with node-oracledb 5.4, or later, should use the connection or pool creation parameters described earlier. However, if you cannot use them, you can use IAM Token Authentication by configuring Oracle Net options.

Note

In this release, IAM connection strings are only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

This requires Oracle Client libraries 19.14 (or later), or 21.5 (or later).

Save the generated access token to a file and set the connect descriptor TOKEN_LOCATION option to the directory containing the token file. The connect descriptor parameter TOKEN_AUTH must be set to OCI_TOKEN, the PROTOCOL value must be TCPS, the SSL_SERVER_DN_MATCH value should be ON, and the parameter SSL_SERVER_CERT_DN should be set. For example, if the token and private key are in the default location used by the OCI CLI, your tnsnames.ora file might contain:

db_alias =
  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(PORT=1522)(HOST=abc.oraclecloud.com))
    (CONNECT_DATA=(SERVICE_NAME=db_low.adb.oraclecloud.com))
      (SECURITY=
        (SSL_SERVER_DN_MATCH=ON)
        (SSL_SERVER_CERT_DN="CN=efg.oraclecloud.com, O=Oracle Corporation, L=Redwood City, ST=California, C=US")
        (TOKEN_AUTH=OCI_TOKEN)
        ))

This reads the IAM token and private key from the default location, for example ~/.oci/db-token/ on Linux.

If the token and private key files are not in the default location then their directory must be specified with the TOKEN_LOCATION parameter. For example in a tnsnames.ora file:

db_alias =
  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(PORT=1522)(HOST=abc.oraclecloud.com))
    (CONNECT_DATA=(SERVICE_NAME=db_low.adb.oraclecloud.com))
      (SECURITY=
        (SSL_SERVER_DN_MATCH=ON)
        (SSL_SERVER_CERT_DN="CN=efg.oraclecloud.com, O=Oracle Corporation, L=Redwood City, ST=California, C=US")
        (TOKEN_AUTH=OCI_TOKEN)
        (TOKEN_LOCATION='/opt/oracle/token')
        ))

You can alternatively set TOKEN_AUTH and TOKEN_LOCATION in a sqlnet.ora file. The TOKEN_AUTH and TOKEN_LOCATION values in a connection string take precedence over the sqlnet.ora settings.

See Oracle Net Services documentation for more information.

4.8. Database Resident Connection Pooling (DRCP)

Database Resident Connection Pooling (DRCP) enables database resource sharing for applications which use a large number of connections 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 support is available in both Thin and Thick modes.

DRCP is generally used only when the database host does not have enough memory to keep all connections open concurrently. For example, if your application runs as 10 Node.js processes each with a connection pool having poolMax of 50, then the database host must be able to have 10 * 50 = 500 database server processes open at the same time. If the database host does not have enough memory for these 500 server processes, then DRCP may be a solution because a smaller pool of server processes will be shared between all the Node.js connections.

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

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

To use DRCP in node-oracledb:

1. The DRCP pool must be started in the database, for example:

   SQL> EXECUTE DBMS_CONNECTION_POOL.START_POOL();

2. The oracledb.connectionClass property should be set by the node-oracledb application. If it is set, then the connection class specified in this property is used in both standalone and pooled connections.

   You can also specify the connection class in a connection string by setting the POOL_CONNECTION_CLASS parameter. If this parameter is set, then this connection class is used in both standalone and pooled connections.

   If both the oracledb.connectionClass property and the POOL_CONNECTION_CLASS connection string parameter are set, then the POOL_CONNECTION_CLASS parameter has the highest priority and overrides the default or application specified values.

   If oracledb.connectionClass and POOL_CONNECTION_CLASS connection string parameter are not set, then:

   • For standalone connections, the session request is sent to the shared connection class in DRCP.

   • For pooled connections, the pool generates a unique connection class if a previously generated connection class does not exist. This connection class is used when acquiring connections from the pool. The node-oracledb Thin mode generates a connection class with the prefix “NJS” while the Thick mode generates a connection class with the prefix “OCI”.

   If the connection class is not set, the pooled server session memory will not be reused optimally, and the statistic views will record large values for NUM_MISSES.

3. The pool.createPool() or oracledb.getConnection() property connectString (or its alias connectionString) 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).

For efficiency, it is recommended that DRCP connections should be used with node-oracledb’s local connection pool.

Setting the Connection Class and Purity in the Connection String

Using node-oracledb Thin mode with Oracle Database 21c or later, you can specify the connection class and pool purity in an Easy Connect String or a Full Connect Descriptor string. For node-oracledb Thick mode, you require Oracle Database 21c (or later) and Oracle Client 19c (or later).

The connection class can be specified in a connection string by setting the POOL_CONNECTION_CLASS parameter. The value for POOL_CONNECTION_CLASS can be any string conforming to connection class semantics and is case-sensitive.

The pool purity specifies whether the node-oracledb application must use a new session or reuse a pooled session. You can specify the pool purity in a connection string by setting the POOL_PURITY parameter. The valid values for POOL_PURITY are SELF and NEW. These values are not case-sensitive. The value NEW indicates that the application must use a new session. The value SELF allows the application to reuse both the pooled server process and session memory, giving maximum benefit from DRCP. See the Oracle documentation on benefiting from scalability. If this parameter is not defined in the connect string, then by default the pool purity is NEW for standalone connections and SELF for pooled connections.

An example of setting the connection class and pool purity in an Easy Connect string is shown below:

dsn = "localhost/orclpdb:pooled?pool_connection_class=MYAPP&pool_purity=self"

An example of setting the connection class and pool purity in an Full Connect Descriptor string is shown below:

db_alias =
    (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(PORT=1522)(HOST=abc.oraclecloud.com))
      (CONNECT_DATA=(SERVICE_NAME=cdb1_pdb1.regress.rdbms.dev.us.oracle.com)(SERVER=POOLED)
      (POOL_CONNECTION_CLASS=cclassname)(POOL_PURITY=SELF)))

Monitoring 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 technical paper Extreme Oracle Database Connection Scalability with Database Resident Connection Pooling (DRCP). This paper also gives more detail on configuring DRCP.

4.8.1. Implicit Connection Pooling with DRCP and PRCP

Starting from Oracle Database 23c, Node.js applications that use DRCP or Proxy Resident Connection Pooling (PRCP) in Oracle Connection Manager in Traffic Director Mode (CMAN-TDM) can enable implicit connection pooling with DRCP and PRCP. With this feature, applications do not need to explicitly close or release a connection to return the connection back to the DRCP or PRCP pool. Applications that do not use client-side connection pooling can take advantage of the implicit connection pooling feature. Both node-oracledb Thin and Thick modes support implicit connection pooling. The Thick mode requires Oracle 23c Client libraries for implicit connection pooling support. The Thin mode works with implicit connection pooling from node-oracledb 6.4 onwards.

Implicit connection pooling uses two types of boundary values to determine when the connection should be released back to the DRCP or PRCP pool. The boundary value can be specified in the POOL_BOUNDARY parameter in the Easy Connect string or the Connect Descriptor string. The two boundary values which can be specified in the POOL_BOUNDARY parameter are:

• STATEMENT: If this boundary value is specified, then the connection is released back to the DRCP or PRCP connection pool when the connection is implicitly stateless. A connection is implicitly stateless when all open cursors in a session have been fetched through to completion, and there are no active transactions, temporary tables, or temporary LOBs.

• TRANSACTION: If this boundary value is specified, then the connection is released back to the DRCP or PRCP connection pool when a commit or rollback is explicitly performed on the transaction in progress on the connection. It is recommended to not set the Connection.autocommit property to true when using implicit connection pooling.

To use implicit connection pooling with DRCP or PRCP, you must specify the server type as pooled and set the POOL_BOUNDARY attribute to either STATEMENT or TRANSACTION in:

• The Easy Connect string. For example, to use implicit connection pooling with the statement boundary value:

  dsn = localhost:1521/orclpdb:pooled?pool_boundary=statement
  

• Or the CONNECT_DATA section of the Connect Descriptor string. For example, to use implicit connection pooling with the transaction boundary value:

tnsalias = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mymachine.example.com)
            (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)
            (SERVER=POOLED)(POOL_BOUNDARY=TRANSACTION)))

Note

• Implicit connection pooling is disabled if the application sets the POOL_BOUNDARY attribute to TRANSACTION or STATEMENT but does not set the SERVER=POOLED attribute in the connection string.

• For all the POOL_BOUNDARY options, the default purity is set to SELF. You can specify the purity using the POOL_PURITY parameter in the connection string to override the default purity value.

Note that it is recommended to use Connection Pooling over implicit connection pooling.

4.9. Privileged Connections

Database privileges such as SYSDBA can be obtained when using standalone connections. Use one of the Privileged Connection Constants with the connection privilege property, for example:

const connection = await oracledb.getConnection({
    user          : "sys",
    password      : "secret",
    connectString : "localhost/orclpdb1",
    privilege     : oracledb.SYSDBA
});

console.log("I have power");

Note that if node-oracledb is using the Oracle Client libraries located in the Oracle Database installation, that is on the same machine as the database and is not using Oracle Instant Client, then operating system privileges may be used for authentication. In this case the password value is ignored. For example on Linux, membership of the operating system dba group allows SYSDBA connections.

Administrative privileges can allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself. Care must be taken with authentication to ensure security. See the Database Administrator’s Guide for information.

4.10. Securely Encrypting Network Traffic to Oracle Database

You can encrypt the data transferred between Oracle Database and node-oracledb so that unauthorized parties are not able to view plain text values as the data passes over the network.

Both node-oracledb Thin and Thick modes support TLS. Refer to the Oracle Database Security Guide for more configuration information.

4.10.1. Native Network Encryption

With Oracle Database’s native network encryption, the client and database server negotiate a key using Diffie-Hellman key exchange. There is protection against man-in-the-middle attacks.

Note

Oracle native network encryption is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

Native network encryption can be configured by editing Oracle Net’s optional sqlnet.ora configuration file. The file on either the database server and/or on each node-oracledb ‘client’ machine can be configured. Parameters control whether data integrity checking and encryption is required or just allowed, and which algorithms the client and server should consider for use.

As an example, to ensure all connections to the database are checked for integrity and are also encrypted, create or edit the Oracle Database $ORACLE_HOME/network/admin/sqlnet.ora file. Set the checksum negotiation to always validate a checksum and set the checksum type to your desired value. The network encryption settings can similarly be set. For example, to use the SHA512 checksum and AES256 encryption use:

SQLNET.CRYPTO_CHECKSUM_SERVER = required
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA512)
SQLNET.ENCRYPTION_SERVER = required
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)

If you definitely know that the database server enforces integrity and encryption, then you do not need to configure node-oracledb separately. However you can also, or alternatively, do so depending on your business needs. Create a file sqlnet.ora on your client machine and locate it with other Optional Oracle Net Configuration:

SQLNET.CRYPTO_CHECKSUM_CLIENT = required
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA512)
SQLNET.ENCRYPTION_CLIENT = required
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)

The client and server sides can negotiate the protocols used if the settings indicate more than one value is accepted.

Note these are example settings only. You must review your security requirements and read the documentation for your Oracle version. In particular, review the available algorithms for security and performance.

The NETWORK_SERVICE_BANNER column of the database view V$SESSION_CONNECT_INFO can be used to verify the encryption status of a connection. For example with SQL*Plus:

SQL> SELECT network_service_banner FROM v$session_connect_info;

If the connection is encrypted, then this query prints an output that includes the available encryption service, the crypto-checksumming service, and the algorithms in use, such as:

NETWORK_SERVICE_BANNER
-------------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.1.0.0 - Production
AES256 Encryption service adapter for Linux: Version 19.0.1.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.1.0.0 - Production
SHA256 Crypto-checksumming service adapter for Linux: Version 19.0.1.0.0 - Production

If the connection is unencrypted, then the query will only print the available encryption and crypto-checksumming service messages. For example:

NETWORK_SERVICE_BANNER
-------------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.1.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.1.0.0 - Production

For more information about Oracle Data Network Encryption and Integrity, and for information about configuring TLS network encryption, refer to the Oracle Database Security Guide. This manual also contains information about other important security features that Oracle Database provides, such Transparent Data Encryption of data-at-rest in the database.

4.11. Changing Passwords and Connecting with an Expired Password

4.11.1. Changing Passwords

Database passwords can be changed with connection.changePassword(). For example:

const currentpw = ...  // the current password for the hr schema
const newpw = ...      // the new hr schema password

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : currentpw,
    connectString : "localhost/orclpdb1"
});

await connection.changePassword("hr", currentpw, newpw);

Only DBAs or users with the ALTER USER privilege can change the password of another user. In this case, the old password value is ignored and can be an empty string:

const newpw = ... // the new password

const connection = await oracledb.getConnection({
    user          : "system",  // a privileged user
    password      : mypw,      // mypw contains the system schema password
    connectString : "localhost/orclpdb1"
});

await connection.changePassword('hr', '', newpw);

4.11.2. Connecting with an Expired Password

When creating a standalone (non-pooled) connection, the user’s password can be changed at the time of connection. This is most useful when the user’s password has expired, because it allows a user to connect without requiring a DBA to reset their password.

Both the current and new passwords must be given when connecting. For example:

const oldpw = ...  // the hr schema's old password
const newpw = ...  // the new password

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : oldpw,
    newPassword   : newpw,
    connectString : "localhost/orclpdb1"
});

4.12. Connections and High Availability

To make highly available applications, use the latest versions of Oracle node-oracledb and Oracle Database. If you are using node-oracledb Thick mode, then also use the latest Oracle Client libraries which have improved implementations to make connections efficient and available. In addition, features like Connection Pool Pinging, Fast Application Notification (FAN), Application Continuity, and Oracle Net Services settings can all help high availability, often without the application being aware of any issue. Some of these features are only supported in node-oracledb Thick mode.

For application high availability, use a connection pool. Pools provide immediately available connections. Also the internal pool implementation supports a number of Oracle Database high availability features for planned and unplanned database instance downtime. Use a fixed size pool to avoid connection storms.

Configuring TCP timeouts can help avoid application hangs if there is a network failure. FAN is also useful.

Oracle Net options may be useful for high availability and performance tuning. Connection establishment timeouts can be set. The database’s listener.ora file can have RATE_LIMIT and QUEUESIZE parameters that can help handle connection storms. In the bigger picture, Oracle Net can be used to configure database service settings, such as for failover using Oracle RAC or a standby database.

Database Resident Connection Pooling (DRCP) may be useful to reduce load on a database host. It can also help reduce connection time when a number of Node.js processes are used to scale up an application.

Finally, applications should always check for execution errors, and perform appropriate application-specific recovery.

4.12.1. Preventing Premature Connection Closing

When connections are idle, external events may disconnect them from the database. Unnecessarily having to re-establish connections can impact scalability, cause connection storms, or lead to application errors when invalid connections are attempted to be used.

There are three components to a node-oracledb connection:

1. The memory structure in node-oracledb that is returned by a getConnection() call. It may be a standalone connection or stored in a connection pool.

2. The underlying network connection between the database and the node-oracledb Thin mode network handling code or Oracle Client libraries.

3. A server process, or thread, on the database host to handle database processing.

Node-oracledb connections may become unusable due to network dropouts, database instance failures, exceeding user profile resource limits, or by explicit session closure of the server process from a DBA. By default, idle connections (the memory structures) in connection pools are unaware of these events. A subsequent pool.getConnection() call could successfully return a “connection” to the application that will not be usable. An error would only occur when later calling functions like connection.execute(). Similarly, using a standalone connection where the network has dropped out, or the database instance is unavailable, will return an error.

To avoid the overhead of connection re-creation, disable any firewall that is killing idle connections. Also disable the database resource manager and any user resource profile IDLE_TIME setting so they do not terminate sessions. These issues can be hidden by node-oracledb’s automatic connection re-establishment features so it is recommended to use AWR to check the connection rate, and then fix underlying causes.

You can use an ‘expire time’ setting to prevent firewalls from terminating idle connections and to adjust keepalive timeouts. The general recommendation is to use a value that is slightly less than half of the termination period. In node-oracledb Thin mode you can set the value in the connection string or with expireTime when connecting. This setting can also aid detection of a terminated remote database server.

With node-oracledb Thick mode, when using Oracle Client 19c, EXPIRE_TIME can be used in tnsnames.ora connect descriptors or in Easy Connect strings. With Oracle Client 21c the setting can alternatively be in the application’s sqlnet.ora file. In older versions of Oracle Client, a tnsnames.ora connect descriptor option ENABLE=BROKEN can be used instead of EXPIRE_TIME.

If the network or the database server processes used by node-oracledb connections cannot be prevented from becoming unusable, tune Connection Pool Pinging. Another case where this internal pinging is helpful is during development, where a laptop may go offline for an extended time.

4.12.2. Fast Application Notification (FAN)

Users of Oracle Database FAN must connect to a FAN-enabled database service. The application should have oracledb.events is set to true. This value can also be changed via Oracle Client Configuration.

Note

In this release, FAN is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

FAN support is useful for planned and unplanned outages. It provides immediate notification to node-oracledb following outages related to the database, computers, and networks. Without FAN, node-oracledb can hang until a TCP timeout occurs and a network error is returned, which might be several minutes.

FAN allows node-oracledb to provide high availability features without the application being aware of an outage. Unused, idle connections in a connection pool will be automatically cleaned up. A future pool.getConnection() call will establish a fresh connection to a surviving database instance without the application being aware of any service disruption.

To handle errors that affect active connections, you can add application logic to re-connect (this will connect to a surviving database instance) and replay application logic without having to return an error to the application user. Alternatively, use Application Continuity.

FAN benefits users of Oracle Database’s clustering technology (Oracle RAC) because connections to surviving database instances can be immediately made. Users of Oracle’s Data Guard with a broker will get FAN events generated when the standby database goes online. Standalone databases will send FAN events when the database restarts.

For a more information on FAN see the technical paper on Fast Application Notification.

4.12.3. Runtime Load Balancing (RLB)

Oracle Database RAC users with Oracle Database (RLB) advisory events configured should use node-oracledb Connection Pooling and make sure oracledb.events is true. The events mode can also be changed via Oracle Client Configuration.

Note

In this release, RLB is only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

RLB allows optimal use of database resources by balancing database requests across RAC instances.

For a more information on RLB, see the technical paper on Fast Application Notification.

4.12.4. Application Continuity

Node-oracledb OLTP applications can take advantage of continuous availability with the Oracle Database features Application Continuity (AC) and Transparent Application Continuity (TAC). These help make unplanned database service downtime transparent to applications.

Note

In this release, Oracle AC and TAC functionalities are only supported in node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

See the technical papers Application Checklist for Continuous Service for MAA Solutions, Continuous Availability Application Continuity for the Oracle Database, and Continuous Availability Best Practices for Applications Using Autonomous Database - Dedicated.

When AC or TAC are configured on the database service, they are transparently available to node-oracledb applications.

4.12.5. Database Call Timeouts

4.12.5.1. Limiting the time to open new connections

To limit the amount of time taken to establish new connections to Oracle Database:

• In node-oracledb Thin mode: You can use the connection attributes connectTimeout or transportConnectTimeout, or use the CONNECT_TIMEOUT timeout parameter in a connection string.

• In node-oracledb Thick mode: You can use SQLNET.OUTBOUND_CONNECT_TIMEOUT in a sqlnet.ora file or CONNECT_TIMEOUT in a connection string. When node-oracledb Thick mode uses Oracle Client libraries 19c or later, timeouts can be passed in Easy Connect strings, for example to timeout after 15 seconds: "mydbmachine.example.com/orclpdb1?connect_timeout=15".

When using a connection pool, these values affect the time taken to establish each connection stored in the pool. The queueTimeout and queueMax settings control higher-level pool behavior.

4.12.5.2. Limiting the time taken to execute statements

To limit the amount of time taken to execute statements on connections:

• In node-oracledb Thin mode: You can use connection.callTimeout which is described below.

• In node-oracledb Thick mode: You can use Oracle Net settings like SQLNET.RECV_TIMEOUT and SQLNET.SEND_TIMEOUT in a sqlnet.ora file. Or you can use the connection.callTimeout attribute which is available when node-oracledb uses Oracle Client libraries version 18, or later. The necessary out-of-band break setting is automatically configured when using Oracle Client 19 and Oracle Database 19, or later. With older Oracle versions on systems that drop (or in-line) out-of-band breaks, you may need to add DISABLE_OOB=ON to a sqlnet.ora file.

The connection.callTimeout attribute is a millisecond timeout for executing database calls on a connection. The connection.callTimeout period is on each individual round-trip between node-oracledb and Oracle Database. Each node-oracledb method or operation may require zero or more round-trips to Oracle Database. The callTimeout value applies to each round-trip individually, not to the sum of all round-trips. Time spent processing in node-oracledb before or after the completion of each round-trip is not counted.

• If the time from the start of any one round-trip to the completion of that same round-trip exceeds callTimeout milliseconds, then the operation is halted and an error is returned.

• In the case where a node-oracledb operation requires more than one round-trip and each round-trip takes less than callTimeout milliseconds, then no timeout will occur, even if the sum of all round-trip calls exceeds callTimeout.

• If no round-trip is required, the operation will never be interrupted.

After a timeout occurs, node-oracledb attempts to clean up the internal connection state. The cleanup is allowed to take another callTimeout milliseconds.

If the cleanup was successful, a DPI-1067 error will be returned and the application can continue to use the connection.

For small values of callTimeout, the connection cleanup may not complete successfully within the additional callTimeout period. In this case a DPI-1080 error is returned and the connection will no longer be usable. The application should then close the connection.

4.13. Connecting to Oracle Real Application Clusters (RAC)

Oracle Real Application Clusters (RAC) allow a single Oracle Database to be run across multiple servers. This maximizes availability and enables horizontal scalability.

The Thin and Thick modes of node-oracledb can connect to Oracle RAC by using a standard RAC connection string. Best practice is to use a Connection Pool with events enabled. See the section Connections and High Availability.

Also see the technical papers Application Checklist for Continuous Service for MAA Solutions and Continuous Availability Application Continuity for the Oracle Database.

4.14. Connecting to Oracle Cloud Autonomous Databases

Node.js applications can connect to Oracle Autonomous Database (ADB) in Oracle Cloud using one-way TLS (Transport Layer Security) or mutual TLS (mTLS). One-way TLS and mTLS provide enhanced security for authentication and encryption.

A database username and password are still required for your application connections. If you need to create a new database schema so you do not login as the privileged ADMIN user, refer to the relevant Oracle Cloud documentation, for example see Create Database Users in the Oracle Autonomous Database manual.

When using node-oracledb Thin mode, Node.js flags can be used to set the minimum TLS version used to connect to Oracle Database. For example, node --tls-min-v1.3 examples/select1.js.

4.14.1. One-way TLS Connection to Oracle Autonomous Database

With one-way TLS, node-oracledb applications can connect to Oracle ADB without using a wallet. Both Thin and Thick modes of the node-oracledb driver support one-way TLS. Applications that use the node-oracledb Thick mode can connect to the Oracle ADB through one-way TLS only when using Oracle Client library versions 19.14 (or later) or 21.5 (or later).

To enable one-way TLS for an ADB instance, complete the following steps in an Oracle Cloud console in the Autonomous Database Information section of the ADB instance details:

1. Click the Edit link next to Access Control List to update the Access Control List (ACL). The Edit Access Control List dialog box is displayed.

2. In the Edit Access Control List dialog box, select the type of address list entries and the corresponding values. You can include the required IP addresses, hostnames, or Virtual Cloud Networks (VCNs). The ACL limits access to only the IP addresses or VCNs that have been defined and blocks all other incoming traffic.

3. Navigate back to the ADB instance details page and click the Edit link next to Mutual TLS (mTLS) Authentication. The Edit Mutual TLS Authentication is displayed.

4. In the Edit Mutual TLS Authentication dialog box, deselect the Require mutual TLS (mTLS) authentication check box to disable the mTLS requirement on Oracle ADB and click Save Changes.

5. Navigate back to the ADB instance details page and click DB Connection on the top of the page. A Database Connection dialog box is displayed.

6. In the Database Connection dialog box, select TLS from the Connection Strings drop-down list.

7. Copy the appropriate Connection String of the database instance used by your application.

Applications can connect to your Oracle ADB instance using the database credentials and the copied connect descriptor. For example, to connect to the Oracle ADB instance:

For example:

const cs = `(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)
            (host=abc.oraclecloud.com))(connect_data=(service_name=xyz.adb.oraclecloud.com))
            (security=(ssl_server_dn_match=yes)))`;

connection = await oracledb.getConnection({
    user: "scott",
    password: mypw,  // mypw contains the scott schema password
    connectString: cs
});

You can download the ADB connection wallet using the DB Connection button and extract the tnsnames.ora file, or create one yourself if you prefer to keep connection strings out of application code. See Net Service Names for Connection Strings for details on adding connection strings to a Net Service Name in a tnsnames.ora file.

4.14.2. Mutual TLS connections to Oracle Cloud Autonomous Database

To enable connections from node-oracledb to Oracle Autonomous Database in Oracle Cloud using mutual TLS (mTLS), a wallet needs to be downloaded from the cloud console. mTLS is sometimes called Two-way TLS.

4.14.2.1. Install the Wallet and Network Configuration Files

From the Oracle Cloud console for the database, download the wallet zip file using the DB Connection button. The zip contains the wallet and network configuration files. When downloading the zip, the cloud console will ask you to create a wallet password. This password is used by node-oracledb in Thin mode, but not in Thick mode.

Note: Keep the wallet files in a secure location and share them only with authorized users.

In the examples used in the sections that follow, consider that you have created a database called CJDB1 with the Always Free services from the Oracle Cloud Free Tier, then you might decide to use the connection string called cjdb1_high in the tnsnames.ora file.

In node-oracledb Thin Mode

For node-oracledb in Thin mode, only two files from the zip are needed:

• tnsnames.ora - Maps net service names used for application connection strings to your database services.

• ewallet.pem - Enables SSL/TLS connections in Thin mode. Keep this file secure.

If you do not have a PEM file, see Creating a PEM File for node-oracledb Thin Mode.

Unzip the wallet zip file and move the required files to a location such as /opt/OracleCloud/MYDB.

You can establish a connection to the database by using your database credentials and setting the connectString parameter to the desired network alias from the tnsnames.ora file. The configDir parameter indicates the directory containing tnsnames.ora. The walletLocation parameter is the directory containing the PEM file. In this example, the files are in the same directory. The walletPassword parameter should be set to the password created in the cloud console when downloading the wallet. For example, to connect as the ADMIN user using the cjdb1_high connection string:

connection = await oracledb.getConnection({
    user: "admin",
    password: mypw,
    configDir: "/opt/OracleCloud/MYDB",
    walletLocation: "/opt/OracleCloud/MYDB",
    walletPassword: wp
});

In node-oracledb Thick Mode

For node-oracledb in Thick mode, only these files from the zip are needed:

• tnsnames.ora - Maps net service names used for application connection strings to your database services.

• sqlnet.ora - Configures Oracle Network settings.

• cwallet.sso - Enables SSL/TLS connections in Thick mode. Keep this file secure.

Unzip the wallet zip file. There are two options for placing the required files:

• Move the three files to the network/admin directory of the client libraries used by your application. For example if you are using Instant Client 19c and it is in $HOME/instantclient_19_11, then you would put the wallet files in $HOME/instantclient_19_11/network/admin/.

  Connection can be made using your database credentials and setting the connectString parameter to the desired network alias from the tnsnames.ora file. For example, to connect as the ADMIN user using the cjdb1_high network service name:

  connection = await oracledb.getConnection({
      user: "admin",
      password: mypw, // mypw contains the admin schema password
      connectString: "cjdb1_high"
  });
  

• Alternatively, move them the three files to any accessible directory, for example /opt/OracleCloud/MYDB.

  Then edit sqlnet.ora and change the wallet location directory to the directory containing the cwallet.sso file. For example:

  WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/opt/OracleCloud/MYDB")))
  SSL_SERVER_DN_MATCH=yes
  

  Since the tnsnames.ora and sqlnet.ora files are not in the default location, your application needs to indicate where they are, either with the configDir parameter to initOracleClient(), or using the TNS_ADMIN environment variable. See Optional Oracle Net Configuration. Neither of these settings are needed, and you do not need to edit sqlnet.ora, if you have put all the files in the network/admin directory.

For example, to connect as the ADMIN user using the cjdb1_high network service name:

const oracledb = require('oracledb');

oracledb.initOracleClient({configDir: '/opt/OracleCloud/MYDB'});
connection = await oracledb.getConnection({
    user: "admin",
    password: mpw,
    connectString: "cjdb1_high"
});

In node-oracle Thick mode, to create mTLS connections in one Node.js process to two or more Oracle Autonomous Databases, move each cwallet.sso file to its own directory. For each connection use different connection string WALLET_LOCATION parameters to specify the directory of each cwallet.sso file. It is recommended to use Oracle Client libraries 19.17 (or later) when using multiple wallets.

If you need to create a new database schema so you do not login as the privileged ADMIN user, refer to the relevant Oracle Cloud documentation, for example see Create Database Users in the Oracle Autonomous Transaction Processing Dedicated Deployments manual.

4.14.2.2. Access Through a Proxy

If you are behind a firewall, you can tunnel TLS/SSL connections via a proxy using HTTPS_PROXY in the connect descriptor. Successful connection depends on specific proxy configurations. Oracle does not recommend doing this when performance is critical.

In node-oracledb Thin Mode

The proxy settings can be passed during connection creation:

connection = await oracledb.getConnection({
    user: "admin",
    password: mypw,
    connectString: "cjdb1_high",
    configDir: "/opt/OracleCloud/MYDB",
    walletLocation: "/opt/OracleCloud/MYDB",
    walletPassword: wp,
    httpsProxy: 'myproxy.example.com',
    httpsProxyPort: 80
});

Alternatively, edit tnsnames.ora and add an HTTPS_PROXY proxy name and HTTPS_PROXY_PORT port to the connect descriptor address list of any service name you plan to use, for example:

cjdb1_high = (description=
    (address=
    (https_proxy=myproxy.example.com)(https_proxy_port=80)
    (protocol=tcps)(port=1522)(host= . . . )))

connection = await oracledb.getConnection({
    user: "admin",
    password: mypw,
    connectString: "cjdb1_high",
    configDir: "/opt/OracleCloud/MYDB",
    walletLocation: "/opt/OracleCloud/MYDB",
    walletPassword: wp,
});

In node-oracledb Thick Mode

Edit sqlnet.ora and add a line:

SQLNET.USE_HTTPS_PROXY=on

Edit tnsnames.ora and add an HTTPS_PROXY proxy name and HTTPS_PROXY_PORT port to the connect descriptor address list of any service name you plan to use, for example:

cjdb1_high = (description=
  (address=(https_proxy=myproxy.example.com)(https_proxy_port=80)
  (protocol=tcps)(port=1522)(host=  . . .

4.14.2.3. Using the Easy Connect Syntax with Autonomous Database

You can optionally use the Easy Connect syntax to connect to Oracle Autonomous Database. When using node-oracledb Thick mode this requires using Oracle Client libraries 19c or later.

The mapping from a cloud tnsnames.ora entry to an Easy Connect Plus string is:

protocol://host:port/service_name?wallet_location=/my/dir&retry_count=N&retry_delay=N

For example, if your tnsnames.ora file had an entry:

cjjson_high = (description=(retry_count=20)(retry_delay=3)
    (address=(protocol=tcps)(port=1522)
    (host=efg.oraclecloud.com))
    (connect_data=(service_name=abc_cjjson_high.adb.oraclecloud.com))
    (security=(ssl_server_cert_dn="CN=efg.oraclecloud.com, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

Then your applications can connect using the connection string:

cs = "tcps://efg.oraclecloud.com:1522/abc_cjjson_high.adb.oraclecloud.com?wallet_location=/Users/cjones/Cloud/CJJSON&retry_count=20&retry_delay=3"
connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,
    connectString : cs
});

The walletLocation parameter needs to be set to the directory containing the cwallet.sso or ewallet.pem file from the wallet zip. The other wallet files, including tnsnames.ora, are not needed when you use the Easy Connect Plus syntax.

You can optionally add other Easy Connect parameters to the connection string, for example:

cs = cs + "&https_proxy=myproxy.example.com&https_proxy_port=80"

With node-oracledb Thin mode, the wallet password needs to be passed as a connection parameter.

4.14.2.4. Creating a PEM File for node-oracledb Thin Mode

For mutual TLS in node-oracledb Thin mode, the certificate must be Privacy Enhanced Mail (PEM) format. If you are using Oracle Autonomous Database and your wallet zip file does not already include a PEM file, then you can convert the PKCS12 ewallet.p12 file to PEM format using third party tools. For example, using OpenSSL:

openssl pkcs12 -in ewallet.p12 -out wallet.pem

Once the PEM file has been created, you can use it by passing its directory location as the walletLocation parameter to oracledb.getconnection() or oracledb.createPool(). These methods also accept a walletPassword parameter, which can be the passphrase that was specified when the above openSSL command was run. See Mutual TLS connections to Oracle Cloud Autonomous Database.

4.15. Connecting using Multiple Wallets

You can make multiple connections with different wallets in one Node.js process.

In node-oracledb Thin mode

To use multiple wallets in node-oracledb Thin mode, pass the different connection strings, wallet locations, and wallet password (if required) in each oracledb.getConnection() call or when creating a connection pool:

connection = await oracledb.getConnection({
    user: "user_name",
    password: userpw,
    connectString: "cjdb1_high",
    configDir: "/opt/OracleCloud/MYDB",
    walletLocation: "/opt/OracleCloud/MYDB",
    walletPassword: walletpw
});

The configDir parameter is the directory containing the tnsnames.ora file. The walletLocation parameter is the directory containing the ewallet.pem file. If you are using Oracle Autonomous Database, both of these paths are typically the same directory where the wallet.zip file was extracted.

In node-oracledb Thick mode

To use multiple wallets in node-oracledb Thick mode, a TCPS connection string containing the MY_WALLET_DIRECTORY option needs to be created:

ocidbdemo_high = (description=(retry_count=1)(retry_delay=3)
(address=(protocol=tcps)(port=1522)(host=efg.oraclecloud.com))
(connect_data=(service_name=abc_ocidbdemo_high.adb.oraclecloud.com))
(security=(ssl_server_cert_dn="CN=ijk.oraclecloud.com, O=Oracle Corporation, L=Redwood City, ST=California, C=US")
(my_wallet_directory="/home/user1/Wallet_OCIDBDemo")))

Note

Use Oracle Client libraries 19.17, or later, or use Oracle Client 21c. They contain important bug fixes for using multiple wallets in the one process.

4.16. Connecting to Sharded Databases

Oracle Sharding can be used to horizontally partition data across independent databases. A database table can be split so each shard contains a table with the same columns but a different subset of rows. These tables are known as sharded tables. Sharding is configured in Oracle Database, see the Oracle Sharding manual. Sharding requires Oracle Database and client libraries 12.2, or later.

Note

In this release, sharding is only supported in the node-oracledb Thick mode. See Enabling node-oracledb Thick Mode.

When a connection is opened in node-oracledb using oracledb.getConnection(), the shardingKey and superShardingKey properties can be used to route the connection directly to a given shard. A sharding key is always required. A super sharding key is additionally required when using composite sharding, which is when data has been partitioned by a list or range (the super sharding key), and then further partitioned by a sharding key.

When creating a connection pool, the property poolMaxPerShard can be set. This is used to balance connections in the pool equally across shards. It requires Oracle Client libraries 18.3 or later.

When connected to a shard, queries only returns data from that shard. For queries that need to access data from multiple shards, connections can be established to the coordinator shard catalog database. In this case, no shard key or super shard key is used.

The sharding and super sharding key properties are arrays of values, that is multiple values can be used. Array key values may be of type String (mapping to VARCHAR2 sharding keys), Number (NUMBER), Date (DATE), or Buffer (RAW). Multiple types may be used in each array. Sharding keys of TIMESTAMP type are not supported by node-oracledb.

4.16.1. Examples to Connect to a Shard Based on the Sharding Key Type

The examples listed in this section show how to establish connections to a database shard based on the sharding key type.

VARCHAR2

If sharding has been configured on a single VARCHAR2 column:

CREATE SHARDED TABLE customers (
    cust_id NUMBER,
    cust_name VARCHAR2(30),
    class VARCHAR2(10) NOT NULL,
    signup_date DATE,
    cust_code RAW(20),
    CONSTRAINT cust_name_pk PRIMARY KEY(cust_name))
    PARTITION BY CONSISTENT HASH (cust_name)
    PARTITIONS AUTO TABLESPACE SET ts1;

then a direct connection to a shard can be made by passing a single sharding key:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "localhost/orclpdb1",
    shardingKey   : ["SCOTT"]
});

NUMBER

If sharding has been configured on a single NUMBER column:

CREATE SHARDED TABLE customers (
    cust_id NUMBER,
    cust_name VARCHAR2(30),
    class VARCHAR2(10) NOT NULL,
    signup_date DATE,
    cust_code RAW(20),
    CONSTRAINT cust_id_pk PRIMARY KEY(cust_id))
    PARTITION BY CONSISTENT HASH (cust_id)
    PARTITIONS AUTO TABLESPACE SET ts1;

then a direct connection to a shard can be made by passing a single sharding key:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "localhost/orclpdb1",
    shardingKey   : [110]
});

Multiple Keys

If database shards have been partitioned with multiple keys such as:

CREATE SHARDED TABLE customers (
    cust_id NUMBER NOT NULL,
    cust_name VARCHAR2(30) NOT NULL,
    class VARCHAR2(10) NOT NULL,
    signup_date DATE,
    cust_code RAW(20),
    CONSTRAINT cust_pk PRIMARY KEY(cust_id, cust_name));
    PARTITION BY CONSISTENT HASH (cust_id, cust_name)
    PARTITIONS AUTO TABLESPACE SET ts1;

then direct connection to a shard can be established by specifying multiple keys, for example:

const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "localhost/orclpdb1",
    shardingKey   : [70, "SCOTT"]
});

DATE

If the sharding key is a DATE column:

CREATE SHARDED TABLE customers (
    cust_id NUMBER,
    cust_name VARCHAR2(30),
    class VARCHAR2(10) NOT NULL,
    signup_date DATE,
    cust_code RAW(20),
    CONSTRAINT signup_date_pk PRIMARY KEY(signup_date))
    PARTITION BY CONSISTENT HASH (signup_date)
    PARTITIONS AUTO TABLESPACE SET ts1;

then direct connection to a shard needs a Date key that is in the session time zone. For example if the session time zone is set to UTC (see Fetching Dates and Timestamps) then Dates must also be in UTC:

key = new Date ("2019-11-30Z");   // when session time zone is UTC
const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "localhost/orclpdb1",
    shardingKey   : [key]
});

RAW

If the sharding key is a RAW column:

CREATE SHARDED TABLE customers (
    cust_id NUMBER,
    cust_name VARCHAR2(30),
    class VARCHAR2(10) NOT NULL,
    signup_date DATE,
    cust_code RAW(20),
    CONSTRAINT cust_code_pk PRIMARY KEY(cust_code))
    PARTITION BY CONSISTENT HASH (cust_code)
    PARTITIONS AUTO TABLESPACE SET ts1;

then direct connection to a shard could be established by:

const data = [0x00, 0x01, 0x02];
const key = Buffer.from(data);
const connection = await oracledb.getConnection({
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "localhost/orclpdb1",
    shardingKey   : [key]
});

Composite Sharding

If composite sharding (requires both sharding key and super sharding key) was in use, for example:

CREATE SHARDED TABLE customers (
    cust_id NUMBER NOT NULL,
    cust_name VARCHAR2(30) NOT NULL,
    class VARCHAR2(10) NOT NULL,
    signup_date DATE,
    cust_code RAW(20),
    PARTITIONSET BY LIST (class)
    PARTITION BY CONSISTENT HASH (cust_name)
    PARTITIONS AUTO (PARTITIONSET gold VALUES ('gold') TABLESPACE SET ts1,
    PARTITIONSET silver VALUES ('silver') TABLESPACE SET ts2);
)

then direct connection to a shard can be established by specifying a super sharding key and sharding key, for example:

const connection = await oracledb.getConnection({
    user            : "hr",
    password        : mypw,  // mypw contains the hr schema password
    connectString   : "localhost/orclpdb1",
    superShardingKey: ["gold"]
    shardingKey     : ["SCOTT"],
});