X

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

Oracle DB Named Objects and Advanced Queuing Support New in node-oracledb 4.0

Christopher Jones
Senior Principal Product Manager

 

Release announcement: A new release of node-oracledb, the Node.js module for accessing Oracle Database, is available from npm.

Top features in node-oracledb 4.0: Querying and binding of named Oracle types; Oracle Advanced Queuing API.

The team has been working hard on node-oracledb 4.0 and is very pleased to release it to you.

You can install node-oracledb 4.0 from npm. It requires Node.js 8 or higher. With Node.js 8 the minimum patch level is 8.16. With Node.js 10 the minimum patch level is 10.16.

REFACTORED IMPLEMENTATION

Internally there were significant code changes to node-oracledb. While the module is still a JavaScript wrapper over a binary layer that calls Oracle client libraries, the binary layer now uses Node.js's N-API interface instead of NAN. NAN was the original Node.js C++ wrapper to provide modules with portability when the V8 engine was updated. Using the newer N-API interface brings a number of benefits:

  • N-API has its own API versioning with a cumulative approach, allowing us to build one node-oracledb binary (per operating system architecture) that can work with current and future Node.js versions (and flavors of Node.js). As a result, when a new Node.js version arrives:

    • You can upgrade Node.js without needing to update node-oracledb. You don't have to wait for us to build a new node-oracledb binary module, or work out how to build the module yourself (not that building is difficult).

    • We don't have to wait for a NAN update which supports any Node.js API or V8 changes.

    • We don't have to update our usage of any deprecated NAN or Node.js functionality.

  • The npm download package is a quarter of the size of the node-oracledb 3.1 package, since the package doesn't need to contain a binary for each Node.js version.

  • Node-oracledb code is now pure C, instead of C++ so the C compiler needed to build the module no longer needs C++11 support. This can make it easier to build in some older environments.

There was also some modernization of the JavaScript layer. For example class methods are now configurable, e.g. via Object.defineProperty.

Node-oracledb 4.0 requires Node.js 8 or higher. With Node.js 8 the minimum patch level is 8.16. With Node.js 10 the minimum patch level is 10.16. An important N-API performance fix landed in those releases.

While the refactoring is fun for us as maintainers, and let us simplify and standardize a lot of code, there are features with a bigger impact to application developers.

NAMED ORACLE OBJECT QUERY AND BIND SUPPORT

The new support for fetching and binding of named Oracle Database types works very smoothly indeed. This makes Node.js a great environment for working with SQL or PL/SQL user-defined types, or when working with pre-created types like those used by Oracle's Spatial features.

For example, let's create some types and a table:

CREATE TYPE dbharvesttype AS VARRAY(10) OF VARCHAR2(20)
/

CREATE TYPE dbfarmtype AS OBJECT (
   farmername     VARCHAR2(20),
   harvest        dbharvesttype)
/

CREATE TABLE farmtab (id NUMBER, farm dbfarmtype);

In node-oracledb 4.0 you can map JavaScript objects for insertion into the column farm:

// A JavaScript object automatically mapped to the DBFARMTYPE object.
// The case of the properties matters.
const newFarm = {
  FARMERNAME: 'McDonald',
  HARVEST: [ 'corn', 'wheat', 'barley' ]
};

await connection.execute(
  `INSERT INTO farmtab (id, farm) VALUES (:id, :f)`,
  { id: 1,
    f: {
      type: 'DBFARMTYPE', // name of the top level DB type, case sensitive
      val: newFarm
    }
  }
);

Querying FARMTAB returns a DbObject, which is new in node-oracledb 4.0:

const result = await connection.execute(`SELECT farm FROM farmtab WHERE id = 1 `);

// a DbObject for the named Oracle type:
const farm = result.rows[0][0];

// The whole object:
// [MYUSER.DBFARMTYPE] { FARMERNAME: 'MacDonald', HARVEST: [ 'corn', 'wheat', 'barley' ] }
console.log(farm);

// Objects can be stringified:
// {"FARMERNAME":"MacDonald","HARVEST":["corn","wheat","barley"]}
console.log(JSON.stringify(farm));

// Iterate over the collection:
console.log(farm.FARMERNAME);        // "MacDonald"
for (const crop of farm.HARVEST) {
  console.log(crop);                 // "corn", "wheat", "barley"
}

Node-oracledb settings such as fetchAsString do not affect DbObjects.

There are examples in selectobject.js and selectgeometry.js, which shows using SDO_GEOMETRY.

Working with PL/SQL objects and collections is similar to the farm sample above. Look at runnable examples such as selectvarray.js and plsqlvarrayrecord.js.

When inserting a number of objects, use executeMany(), which will be more efficient than repeated calls to execute(). See plsqlrecord.js for a runnable example.

There is some overhead getting type information from the database. Node-oracledb tries to minimize this by using a cache. The cache is keyed from the fully qualified name so make sure you use the FQN when binding multiple times:

. . .
type: 'MYUSER.DBFARMTYPE', // bind type is name of the top level DB type, case sensitive
. . .

Alternatively explicitly call getDbObjectClass() and bind the prototype object class it returns:

const FarmType = connection.getDbObjectClass("DBFARMTYPE");

. . .
type: FarmType, // bind type is the prototype object from getDbObjectClass()
. . .

ORACLE ADVANCED QUEUING (AQ)

Oracle Advanced Queuing is a highly configurable and scalable messaging feature of Oracle Database. It has interfaces in various languages, letting you integrate multiple tools in your architecture to send and receive messages. Node-oracledb 4.0 has an Advanced Queuing API with support for 'RAW' and object queues. You can send and receive String, Buffer or object messages.

For example, if a 'RAW' queue has been configured in the database, you can send a simple string:

const queue = await connection.getQueue('MYQUEUE');
const messageString = 'This is my message';
await queue.enqOne(messageString);
await connection.commit();

To receive it, another application can execute:

const queue = await connection.getQueue('MYQUEUE');
const msg = await queue.deqOne();
await connection.commit();
console.log(msg.payload.toString());

Messages are received as AqMessage objects. In these, string messages are encoded as UTF-8 buffers so toString() was used to extract the message text.

AQ has a lot of options that can be set on queues and on messages. For example, you might want to send messages without committing the whole transaction on the connection:

const queue = await connection.getQueue('MYQUEUE');
queue.enqOptions.visibility = oracledb.AQ_VISIBILITY_IMMEDIATE;
await queue.enqOne('This is my message');

Or you may want to wait no longer than five seconds if no messages are in the queue:

const queue = await connection.getQueue('MYQUEUE');
Object.assign(queue.deqOptions, { wait: 5 } );
const msg = await queue.deqOne();
await connection.commit();
if (msg)
  console.log(msg.payload.toString());

Note the direct assignment to the visibility property, or the use of Object.assign for setting the queue options.

At the message level you could expire messages from the queue if they have not been dequeued within a certain time, for example ten seconds:

const queue = await connection.getQueue('MYQUEUE');
const message = {
  payload: 'This is my other message',
  expiration: 10
};
await queue.enqOne(message);
await connection.commit();

These examples show enqOne() takes either a string message directly, or it takes a JavaScript object with a payload property that is the string message, like the very last example above. The same goes for Buffers and DbObjects: they are either passed directly to enqOne() or as the value of a payload property in a JavaScript object argument.

See aqraw.js, aqoptions.js, and aqobject.js for runnable examples.

Node-oracledb 4.0 also has batch enqueue and dequeue methods, see aqmulti.js.

IMPLICIT RESULTS

Support for Implicit Results has arrived in node-oracledb 4.0. This makes it even easier to return query result sets from PL/SQL to node-oracledb. With Implicit Results you don't need bind variables. The DBMS_SQL.RETURN_RESULT interface lets a cursor be returned via node-oracledb's new results.implicitResults property:

result = await connection.execute(
  `DECLARE
    c1 SYS_REFCURSOR;
    c2 SYS_REFCURSOR;
  BEGIN

    OPEN c1 FOR SELECT city, postal_code FROM locations;
    DBMS_SQL.RETURN_RESULT(c1);

    OPEN C2 FOR SELECT employee_id, last_name FROM employees;
    DBMS_SQL.RETURN_RESULT(c2);

  END;`,

  [],   // no binds needed

  { resultSet: true }
);

// Iterate over all the ResultSets
for (const rs of result.implicitResults) {
  while ((row = await rs.getRow()))
    console.log(row);
  await rs.close();
}

See impres.js for a runnable example.

A FEW MORE CHANGES

  • One important change is that the Type Constants and Oracle Database Type Constants were renumbered to allow for future bind enhancements. Make sure you use the constant names in applications, not their values.

  • There is a new connection.currentSchema property for setting the schema qualifier to be used when a qualifier is omitted from SQL statements. This is an efficient alternative to ALTER SESSION SET CURRENT_SCHEMA.

  • The relative performance of fetching query row output as JavaScript objects vs arrays via the outFormat setting has improved. It is still faster to fetch each row as an array, so that remains the default mode.

  • We introduced new constants oracledb.OUT_FORMAT_ARRAY and oracledb.OUT_FORMAT_OBJECT to replace the slightly ambiguously named oracledb.ARRAY and oracledb.OBJECT, which are still usable but now deprecated.

    The outFormat setting continues to affect the row representation, not the representation of each column item. Look at the results of querying the ID and FARM columns from the FARMTAB table of the first Oracle object example above. With an execute() query option { outFormat: oracledb.OUT_FORMAT_OBJECT }, each queried row is like:

    {
      ID: 1,
      FARM: [MYUSER.DBFARMTYPE] { FARMERNAME: 'McDonald', HARVEST: [ 'corn', 'wheat', 'barley' ] }
    }
    

    With the default oracledb.OUT_FORMAT_ARRAY, each queried row is an array of values, one per column:

    [
      1,
      [MYUSER.DBFARMTYPE] { FARMERNAME: 'McDonald', HARVEST: [ 'corn', 'wheat', 'barley' ] }
    ]
      

    In both cases the FARM column is a DbObject type. The only difference is how you access the column from the execute() row results, either as an object reference result.rows[i].FARM or as array reference result.rows[i][1].

  • If you missed the recent announcement: the existing SODA APIs for NoSQL-style document access are out of preview status and are now supported with Oracle Client 18.5 and Oracle Client 19.3, or later, when using Oracle Database 18 and above. This is also true for SODA in node-oracledb 3.1.2.

    SODA Bulk insert methods were added in 4.0. These are in preview status.

  • We updated examples to use the Async/Await style of programming, which is generally cleaner and easier to follow. I still see people using multiple programming styles in their code, presumably from trying to integrate examples from different sources or times. This gets mighty confusing for them. Where possible, start moving to async/await - but don't forget to use 'await' where its needed.

See the CHANGELOG and Migrating from node-oracledb 3.1 for other changes and more information about node-oracledb 4.0.

CONCLUSION

Node-oracledb 4.0 is a big release with some significant features, making it even easier to build sophisticated applications in Node.js.

A special shout out to our testers who have been putting in extra effort. Along with their other projects, they not only test the parts of node-oracledb you see, but also make sure that future Oracle Database features and versions have great Node.js support.

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.