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

Faster JSON with node-oracledb 5.1 and Oracle Database 21's new OSON storage format

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: Support for Oracle Database 21's faster JSON features; automatic numeric suffixes for duplicate column names; more control for fetching Oracle Database Object and Collections.



To accompany the Oracle Database 21c release, we are happy to bring you the node-oracledb 5.1 update with:

  • Enhanced JSON functionality to support Oracle Database 21's native OSON storage format for JSON. A new node-oracledb type oracledb.DB_TYPE_JSON was added.

    JavaScript objects can now be bound directly when using the new storage type:

    const sql = `INSERT INTO mytable (myjson) VALUES (:bv)`;
    const data = { "userId": 1, "userName": "Anna", "location": "Australia" };
    await connection.execute(sql, { bv: { val: data, type: oracledb.DB_TYPE_JSON } });

    To try the advantages of the new storage type, you can use Oracle Database 21c in Oracle Cloud. See my earlier blog post How to connect to Oracle Autonomous Cloud Databases. Oracle Instant Client 21 is currently available only for Linux.

    Existing node-oracledb JSON features - which also support the BLOB, CLOB and VARCHAR storage for JSON available since Oracle Database 12c - remain unchanged.

    See the node-oracledb Oracle Database JSON Data Type documentation and the Database JSON Developer's Guide for more goodness about working with JSON.

  • A new oracledb.dbObjectAsPojo setting.

    This mode specifies whether Oracle Database named objects and collections that are queried should be returned to the application as "plain old JavaScript objects" or kept as database-backed objects. Regardless of the value, the interface to access objects is the same.

    Setting dbObjectAsPojo to true can avoid overhead if object attributes are repeatedly accessed. It also allows applications to close connections before any attributes are accessed (unless LOBs are involved). If you are not accessing all attributes or objects, it may be more efficient to use the default value of false.

  • Numeric suffixes are now added to duplicate SELECT column names when using oracledb.OUT_FORMAT_OBJECT mode, allowing all columns to be represented in the JavaScript result object without needing the SQL to be altered.

    The code:

    sql = `SELECT dummy as emp1, dummy as emp1 from dual`;
    const result = await conn.execute(sql, [], { outFormat: oracledb.OUT_FORMAT_OBJECT });

    appends a numeric suffix to the duplicated column name:

    [ { EMP1: 'X', EMP1_1: 'X' } ]
  • SODA (the NoSQL-style API) now takes advantage of Oracle Database 21c's JSON storage format by default. If you're using SODA with different versions of Oracle Client library connected to Oracle Database 21, review the node-oracledb SODA Requirements.

    Now that Oracle Instant Client 21 is available, don't forget that some existing node-oracledb 5.0 SODA features such as sodaCollection.save(), sodaCollection.saveAndGet(), and sodaCollection.truncate() that require recent client libraries are easier for you to take advantage of. These also work when connected to older Oracle database versions once node-oracledb is using the new client libraries.

For all the node-oracledb changes see the CHANGELOG.

Installing or Upgrading node-oracledb

You can install or upgrade node-oracle by updating your package.json requirements:

"dependencies": {
   "oracledb": "^5.1"

See Installing node-oracledb for more details.


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.