X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

Use node-oracledb 3.1's connection tagging to efficiently set session state

Christopher Jones
Senior Principal Product Manager

Release announcement: The latest version of node-oracledb, the Node.js module for accessing Oracle Database, is on npm.

Top features: Connection tagging; New-connection callback; Explicit session drop; Multi-binary install; Deferred Oracle Client library initialization.

 

 

This release of node-oracledb has features to make installation and configuration easier, and for working with pooled connections more efficiently. The highlights are below, but check the CHANGELOG for all improvements and bug fixes.

  • The available pre-built node-oracledb binaries are now bundled into the node-oracledb npm package, making installation easier and allowing applications to be more portable.

  • Loading of the Oracle Client libraries is now deferred until the first use of oracledb.getConnection(), oracledb.createPool(), oracledb.oracleClientVersion, or oracledb.oracleClientVersionString.

  • Pooled connections can now be tagged with a text string when releasing them to the connection pool. This can be used to indicate what 'session state' (e.g. ALTER SESSION settings or PL/SQL package state) the connection has. The application can later call pool.getConnection() and request a connection which has been tagged. This lets the application use a connection with a known session state.

  • A Node.js callback function can be invoked when a pooled connection is newly created and has never been acquired from the pool before, or when requested and actual tags mismatch. Alternatively a PL/SQL procedure can be invoked when requested and actual tags mismatch. This lets applications set session state before the connection is used.

  • Connections in the connection pool can now be forcefully dropped so that the next pool.getConnection() that causes the pool to grow will re-establish a new connection to the database.

Install Changes

The node-oracledb module necessarily has a binary layer that links with Oracle Client libraries to communicate to Oracle Database. (Technically this makes it an 'add-on', not a module.) From node-oracledb 3.1 the pre-built binaries we provide are now bundled into the node-oracledb npm package.

The bootstrap install script no longer does a separate request to download a binary from GitHub - this change may help anyone with network restrictions. After installation, the correct binary (if one is available for your operating system and Node.js version) is loaded by require('oracledb') at run-time. This means that you can upgrade Node.js, or copy the node_modules/oracledb directory to another operating system without always needing to re-install node-oracledb, making it easier to deploy some classes of application.

If you are deploying to a fixed environment, such as a Docker container, and really want to minimize the install footprint, you can remove binaries for other Node.js versions by running 'npm run prune' in the node_modules/oracledb directory. This saves a few megabytes.

Node-oracledb source code remains available at GitHub or git://oss.oracle.com/git/oracle/node-oracledb.git/ which you can use to build node-oracledb binaries on platforms that do not have pre-built binaries.

Deferred Loading

Connections to Oracle are often known as 'sessions'. Each connection can have what is called 'session state' controlling behavior. State can be set in various way such as via environment variables or with ALTER SESSION statements. For node-oracledb, a commonly set environment variable is ORA_SDTZ which controls the default session time zone:

$ export ORA_SDTZ=UTC
$ node myapp.js

Instead of setting the value before starting Node, some developers prefer setting it inside the application under the impression there is no possibility of mis-configuration:

process.env.ORA_SDTZ = 'UTC';
const oracledb = require('oracledb');

But a number of people set environment variables too late, or discover insurmountable operating system differences (e.g. between PATH on Windows and LD_LIBRARY_PATH on Linux, which has to be set before a process starts), and basically get confused by trying to set the environment inside applications. Overall I don't recommend this usage because I'm the person who keeps having to help these users!

Luckily for people who ignore my advice, node-oracledb 3.1's new deferred library loading change makes node-oracledb more tolerant of Oracle environment setting placement. Initialization of the Oracle Client libraries is now deferred until the first use of oracledb.getConnection(), oracledb.createPool(), oracledb.oracleClientVersion, or oracledb.oracleClientVersionString. If the Oracle Client cannot be loaded, getConnection() and createPool() will return an error via the callback. Accessing oracledb.oracleClientVersion or oracledb.oracleClientVersionString will throw an error. Previously require('oracledb') would throw an error.

Since Oracle client libraries are not loaded until a connection is used, you can now do:

const oracledb = require('oracledb');

. . .

// Some time later
process.env.ORA_SDTZ = 'UTC';

let pool = oracledb.createPool( . . . )

More practically, this change makes require('oracledb') always succeed (if the module is installed!), allowing node-oracledb constants and other attributes to be accessed even if the Oracle Client is not available. This makes it easier to include node-oracledb in multi-database applications where not all users will be accessing Oracle Database. It allows code generation tools to access node-oracledb constants without needing Oracle Client installed on the computer that generates Node.js code.

Session State

Getting back to altering session state, some applications do this with explicit ALTER SESSION commands. Pooled connections will retain this session state after they have been released back to the pool with connection.close() and the next user of the connection will see the same state. However, because the number of connections in a pool can vary over time, or connections in the pool can be recreated, there is no guarantee a subsequent getConnection() call will return a database connection that has any particular state. So in previous versions of node-oracledb, any ALTER SESSION commands had to be run after each and every pool.getConnection() call. This added load and reduced system efficiency:

// In the past with node-oracledb 3.0
connection = await pool.getConnection();
await connection.execute(`alter session set time_zone='UTC'`);
await connection.execute(`select ...`);  // actually use the connection

In node-oracledb 3.1, the new createPool() option sessionCallback can be used to set session state efficiently. You can set sessionCallback to 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 does not match the actual tag of the connection selected from the pool. It is called before getConnection() returns in these two cases. It will not be called in other cases. The big advantage is that it saves the cost of altering session state if a previous user of the connection has already set it. Also the current caller of getConnection() can always assume the correct state is set.

The following example prints "In initSession" just once - for the first getConnection() call. The second getConnection() call returns the previously used connection (because poolMax is 1) which has already been initialized. The function initSession is not called a second time:

// New in node-oracledb 3.1

const oracledb = require('oracledb');

function initSession(connection, requestedTag, cb) {
  console.log("In initSession");
  cb();
}

(async function()
{
  try {
    let pool = await oracledb.createPool({
      user: 'hr',
      password: 'welcome',
      connectString: 'localhost/xe,
      sessionCallback: initSession,
      poolMin: 1,
      poolMax: 1,
      poolIncrement: 0
    });

    let connection;

    connection = await pool.getConnection();
    await connection.close();

    connection = await pool.getConnection();
    await connection.close();

  } catch (err) {
    console.error(err.message);
  }
})();

The initSession() parameter requestedTag is only set when tagging is being used, see later.

Although a sessionCallback function can be used for logging, it is more commonly used to set session state. This state should be set in the function before it invokes its own callback cb. The following example sets two NLS settings in each pooled connection. They are only set the very first time connections are established to the database. Subsequent pool.getConnection() calls that return a previously used connection will not cause initSession to be called:

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

If you need to execute multiple SQL statements in the callback, use an anonymous PL/SQL block to avoid repeated execute() calls and save round-trips between node-oracledb and Oracle Database:

function initSession(connection, requestedTag, cb) {
  connection.execute(
    `begin
       execute immediate
         'alter session set nls_date_format = ''YYYY-MM-DD'' nls_language = AMERICAN';
       -- other SQL statements could be put here
     end;`,
    cb);
}

Let's say that you implement initSession in a micro-service. The service simply connects, does one query, and then disconnects. Compare it with node-oracledb 3.0 where you would have to execute ALTER SESSION each time you call getConnection(). The new version will only execute ALTER SESSION once for each connection in the pool. When poolMax is 4 and you call the service 1000 times, the number of statement executions required for each version can be compared:

Node-oracledb version Service Called poolMax ALTER SESSION calls SELECT calls Statement Executions
3.0 1000 4 1000 1000 2000
3.1 1000 4 4 1000 1004
 

Using sessionCallback in node-oracledb 3.1 significantly reduces the number of statement executions and therefore reduces the round-trips between node-oracledb and Oracle Database. In one basic comparison of an application like this, the average response time went down from 0.35 ms to 0.27 ms by using a sessionCallback function.

Tagging

Using a simple sessionCallback is great when you want every connection to have the same session settings. If some connections need different state to others, then you can set the desired state in a connection (e.g. with ALTER SESSION) and set an application-specific string 'tag' on the connection to record the session state. Later, other pool.getConnection() calls can request a connection with a specified tag, i.e. a connection that has a known session state:

// Get initial connection
connection = await pool.getConnection();

// Change the session state and set the tag to record that state.
await connection.execute(`ALTER SESSION SET TIME_ZONE='UTC'`);
connection.tag = "TZ=UTC";  // With 12.2 client libs the tag must be like "k=v"

. . . // Use connection

// Closing saves the current connection.tag value for that connection
await connection.close();

. . .

// Ask to be given a connection which has a specific tag.  If no
// suitable connection exists in the pool, a brand new connection may
// be returned; this won't have a tag set.
connection = await pool.getConnection({tag: "TZ=UTC"});
console.log('Connection tag is ' + connection.tag);
if (connection.tag !== "TZ=UTC") {
  await connection.execute(`ALTER SESSION SET TIME_ZONE='UTC'`);
  connection.tag = "TZ=UTC";
} // Else got an initialized session so don't need to do ALTER SESSION.
  // The aim is to avoid doing ALTER SESSION unless necessary.

. . . // Use connection

You would use tagging where the aim is to get connections which have one of several different states. Do not use tagging if all connections should have the same state - you can simply use sessionCallback as shown earlier. Also there is little point using tagging with huge numbers of different tags since the chances of getConnection() returning an already initialized connection are low, so most connections will need some kind of session reset, and the cost of tag management will add overhead to the situation. This is also true if the pool frequently grows and shrinks.

As you can guess, sessionCallback can be used with tagging to set state. When the requested tag doesn't match the connection's actual tag, the sessionCallback function will be called before pool.getConnection() returns. The function can check the requestedTag parameter against connection.tag, and then adjust the session state and connection.tag value as desired.

// The pool sessionCallback function
function initSession(connection, requestedTag, cb) {

  console.log(`initSession() requested tag: ${requestedTag}, actual tag: ${connection.tag}`);

  // requestedTag and connection.tag can be parsed to decide what state to set
  . . .
  connection.execute(`ALTER SESSION SET . . .`,
    (err) => {
      connection.tag = ". . ."; // Update connection.tag to match the new state
      cb(err);                  // Call cb() after setting any state
    }
  );
}

In initSession(), the value of requestedTag will be different to connection.tag by definition because the callback is not called if the tags are the same. The edge-case exception is if the getConnection() call asked for a tag of "" (empty string) and the connection is new and hasn't had a tag set. However, to use tagging efficiently, you should request initialized sessions, and not mix getConnection() calls that request tags with calls that request no tags.

A best-practice recommendation is to set connection.tag in the callback where you update the session state, however it can be set anytime prior to the connection being closed if needed.

The underlying Oracle Session Pool has various modes and heuristics to optimally select the connection to return from the pool. Overall, a pool.getConnection() call will try to select a connection which has the requested tag, therefore avoiding invoking the sessionCallback function. If no available connection with that tag exists in the pool, an untagged connection or a connection with a new session will be selected, and thus invoke the sessionCallback function; in this case the connection.tag property at the time the sessionCallback is invoked will be an empty string. If the optional getConnection() attribute 'matchAnyTag' is true, then a connection that has a different tag may be selected and connection.tag in the sessionCallback function will have that different tag. In summary, Oracle will try its best to reuse connections, but it is up to the sessionCallback function to check the actual and requested tags and make necessary session state changes.

When node-oracledb is using Oracle Client libraries 12.2 or later, node-oracledb always uses Oracle's multi-property tagging mode 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" or "TIME_ZONE=UTC;NLS_DATE_FORMAT=YYYY-MM-DD" (otherwise you'll get an error like 'ORA-24487: Invalid properties or values provided for OCISessionGet'). The property keys and values in a multi-property tag are case sensitive. The order of properties influences which connection is returned by the connection pool, so put the most important properties early in the tag. You can use a multi-property tag to record various session settings so your sessionCallback function can parse the tag and decide which specific settings should be reset. Note that the underlying Oracle session pool parses tag properties so "a=b;c=d" is considered the same as "c=d;a=b" when choosing which connection in the pool should be used. However the Node.js callback is invoked after a simple string comparison of the full tag strings and would treat these examples as different.

You can make the callback as complex or as simple as needed, depending on the business requirements and what tag properties are expected. This example assumes that tags have the format USER_TZ=X, where X is a valid Oracle timezone:

// See examples/sessiontagging1.js

// The connection callback function
function initSession(connection, requestedTag, cb) {
  console.log(`In initSession. requested tag: ${requestedTag}, actual tag: ${connection.tag}`);

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

  // Execute the session state change.
  connection.execute(
    `ALTER SESSION SET TIME_ZONE = '${tagParts[1]}'`,
    (err) => {
      connection.tag = requestedTag; // Record the new state of the connection
      cb(err);
    }
  );
}

// Start a connection pool
try {
  await oracledb.createPool({
               user: 'hr',
               password: 'welcome',
               connectString: 'localhost/XE',
               sessionCallback: initSession
             });
} catch (err) {
  console.error('init() error: ' + err.message);
}

// . . . Later use the pool

// Request a connection from the pool cache with a given tag, but
// accept any tag being returned.
let connection = await oracledb.getConnection(
      {poolAlias: 'default', tag: "USER_TZ=UTC", matchAnyTag: true});

// What happens:
// (i)   If a brand new connection was created in the pool, initSession() is
//       called to set the state and set connection.tag.
// (ii)  If a matching connection was found in the pool, connection.tag
//       will equal "USER_TZ=UTC".  In this case initSession() is not called.
// (iii) If another connection is returned, it will have no tag (if it
//       is a new or recreated connection) or a different tag (because
//       matchAnyTag was true) that another user has set.  In this case,
//       initSession() is called to set the state and update connection.tag.

// Use the connection, which is in the UTC timezone
let result = connection.execute(. . .);

// Closing the connection retains the value of connection.tag
await connection.close();

. . .
}

A more complete example of a callback is shown in examples/sessiontagging2.js.

When using Oracle Client 12.2, a PL/SQL session state fix-up procedure can alternatively be called instead of the Node.js function. The PL/SQL procedure is called only when the properties in the requested connection tag do not match the properties in the actual tag. Instead of setting sessionCallback to a Node.js function, set it to a string containing the name of your PL/SQL procedure:

try {
  let pool = await oracledb.createPool({
               user: 'hr',
               password: 'welcome',
               connectString: 'localhost/XE',
               sessionCallback: "myPackage.myPlsqlCallback"
             });
  . . .

  let connection = await pool.getConnection({tag: "DF=YYYY-MM-DD"});

  . . . // Use connection
        // The session state will be whatever myPlsqlCallback sets it to.
        // connection.tag will always be the requested tag "DF=YYYY-MM-DD"

  await conn.close();
}

The PL/SQL callback declaration is:

PROCEDURE myPlsqlCallback (
  requestedTag IN  VARCHAR2,
  actualTag    IN  VARCHAR2
);

When DRCP connections are being used, a PL/SQL callback procedure does not need any round-trips between Node.js and the database. In comparison, a complex (or badly coded) Node.js function could require lots of round-trips. A PL/SQL callback can also be specified if DRCP is not being used; in this case invoking the PL/SQL callback requires just one round-trip.  An example PL/SQL procedure is shown in the documentation.  

Since DRCP sessions are a database-server resource that can be shared between a number of applications, multi-property tagging can be particularly useful to let your callback procedure decide which parts of the session state are relevant for your application, and which parts can be ignored.

Dropping Pooled Connections

Sometimes, instead of releasing a connection back to a pool for reuse, you just want to get rid of it. Perhaps you have set some special session state and it's easier to drop the connection now so that the sessionCallback function can reset the normal state when the connection is recreated. In node-oracledb 3.1 you can now force connections to be dropped from the pool:

await connection.close({drop: true});

Remember that normally you don't want to do this, because it means a new connection will have to be created and initialized the next time one is needed.

For non-pooled connections, using a simple connection.close() continues to completely terminate a connection.

Summary

Overall, the node-oracledb 3.1 release brings some welcome usability and performance benefits giving you greater control over your applications in dynamic environments.

Resources

Node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Node-oracledb change log is here.

Issues and questions about node-oracledb can be posted on GitHub or Slack (link to join Slack).

Follow us on Twitter or Facebook.

Finally, contributions to node-oracledb are more than welcome, see CONTRIBUTING.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services