X

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

Node-oracledb 5.0 Adds Nested Cursors, Database Startup, and Prefetch Tuning

Christopher Jones
Senior Principal Product Manager

 

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

Top features: New initialization function to locate Oracle Client libraries; Customizable prefetching for query fetch tuning; Support for nested cursors; Database startup and shutdown.

 

The node-oracledb 5.0 release bring big and small enhancements to the Node.js user. See the CHANGELOG for all the detail.

 

Main Changes

  • New initialization function to locate Oracle Client libraries: A new oracledb.initOracleClient() function can optionally be used to configure use of the Oracle Client. For example, the location of the Oracle Client libraries can be passed to it. This is most useful on Windows and macOS - particularly with recent macOS changes restricting library access. There are some limitations on Linux. The function also accepts the directory name where your optional configuration files like tnsnames.ora are. This new function helps automate Node.js installation on Windows and macOS, makes it easier to have multiple Oracle Client library versions installed, and make it easier to distribute applications to users.

    As an example, if you unzipped the Oracle Instant Client libraries to C:\oracle\instantclient_19_6 on Windows, then you no longer necessarily need to add this directory to PATH. Instead you can now set the directory inside your application:

    const oracledb = require('oracledb');
    try {
      oracledb.initOracleClient({libDir: 'C:/oracle/instantclient_19_6'});
    } catch (err) {
      console.error('Whoops!');
      console.error(err);
      process.exit(1);
    }
    

    (If you use backslashes for libdir, you will need to double them).

    In node-oracledb 5 for macOS we also extended the existing Windows feature allowing Instant Client libraries to be in the same directory as the node-oracledb binary module. Instead of using initOracleClient() you could instead have a package.json script section:

       "scripts": {
          "postinstall": "ln -s $HOME/instantclient/libclntsh.dylib $(npm root)/oracledb/build/Release",
          "start": "node index.js"
       },
    

    This may not be quite as handy as putting Instant Client in ~/lib on macOS, but recent Node.js binaries no longer access that directory by default. Note you can still put files in /usr/local/lib. Apple macOS users should review the updated installation instructions and the new functionality.

    See Initializing Node-oracledb.

  • Prefetching for query tuning: A new execute() option prefetchRows can be used in conjunction with the existing fetchArraySize option to tune performance and memory usage of query row fetches. These tuning parameters let you reduce system and network load by adjusting the internal batch sizes used for fetching rows after you execute a query. By setting these parameters you can balance network costs against application memory usage and data copies. The values do not affect how, or when, rows are returned to the application.

    As an example, if your query is returning a small, fixed number of rows, then you might do something like:

    const myoffset = 0;       // do not skip any rows (start at row 1)
    const mymaxnumrows = 20;  // get 20 rows
    
    sql = `SELECT last_name
           FROM employees
           ORDER BY last_name
           OFFSET :offset ROWS FETCH NEXT :maxnumrows ROWS ONLY`;
    
    binds = { offset: myoffset, maxnumrows: mymaxnumrows };
    
    options = { prefetchRows: mymaxnumrows + 1, fetchArraySize: mymaxnumrows };
    
    result = await connection.execute(sql, binds, options);
    

    In this example, prefetching all the rows lets the complete internal execution and data fetch phases use just a single round-trip to the database. In previous node-oracledb versions only two rows would have been prefetched and a second round-trip would have fetched the remaining rows. Note the prefetchRows value is one greater than the number of rows being returned by the query, which removes the need to do an extra round-trip to check for end-of-fetch. The best values for prefetchRows and fetchArraySize for your queries should be determined by tuning. In some cases you may find that only changing fetchArraySize while letting prefetchRows remain its default value of 2 is best.

    See Tuning Fetch Performance.

  • Support for nested cursors: Queries like:

    const sql = `SELECT department_name,
                        CURSOR(SELECT salary, commission_pct
                               FROM employees e
                               WHERE e.department_id = d.department_id
                               ORDER BY salary) as nc
                 FROM departments d
                 ORDER BY department_name`;
    
    const result = await connection.execute(sql);
    console.dir(result.rows, {depth: null});
    

    will produce output like:

    [
      [ 'Accounting', [ [ 8300, null ], [ 12008, null ] ] ],
      [ 'Administration', [ [ 4400, null ] ] ],
      [ 'Benefits', [] ],
      [ 'Construction', [] ],
      [ 'Contracting', [] ],
      [ 'Control And Credit', [] ],
      [ 'Corporate Tax', [] ],
      [
        'Executive',
        [ [ 17000, null ], [ 17000, null ], [ 24000, null ] ]
      ],
      [
        'Finance',
        [
          [ 6900, null ],
          [ 7700, null ],
          [ 7800, null ],
          [ 8200, null ],
          [ 9000, null ],
          [ 12008, null ]
        ]
      ],
      . . .
    

    See Fetching Nested Cursors.

  • Database Startup and Shutdown: New startup and shutdown functions allow simple and flexible control over the database. See Database Start Up and Shut Down

  • A new pool attribute queueMax : This helps protect against connection storms by limiting the number of pool.getConnection() requests that can be queued when all pooled connections are already in use. Any connection requests exeeding the limit will immediately fail without waiting for the queueTimeout period. The default queueMax is 500. You can set it to 0 to disable any queuing, or set it to -1 to allow all requests to be queued.

  • Removed support for custom Promise libraries: The internal JavaScript implementation was refactored to use the cleaner async/await model and support for custom Promise libraries was necessarily removed. Use the native Node.js Promise implementation instead.

Our test suite and documentation was also improved in this release. Check it out!

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.

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

 

Top features: New initialization function to locate Oracle Client libraries; Customizable prefetching for query fetch tuning; Support for nested cursors; Database startup and shutdown.

The node-oracledb 5.0 release bring big and small enhancements to the Node.js user. See the CHANGELOG for all the detail.

Main Changes

  • New initialization function to locate Oracle Client libraries: A new oracledb.initOracleClient() function can optionally be used to configure use of the Oracle Client. For example, the location of the Oracle Client libraries can be passed to it. This is most useful on Windows and macOS - particularly with recent macOS changes restricting library access. There are some limitations on Linux. The function also accepts the directory name where your optional configuration files like tnsnames.ora are. This new function helps automate Node.js installation on Windows and macOS, makes it easier to have multiple Oracle Client library versions installed, and make it easier to distribute applications to users.

    As an example, if you unzipped the Oracle Instant Client libraries to C:\oracle\instantclient_19_6 on Windows, then you no longer necessarily need to add this directory to PATH. Instead you can now set the directory inside your application:

    const oracledb = require('oracledb');
    try {
      oracledb.initOracleClient({libDir: 'C:/oracle/instantclient_19_6'});
    } catch (err) {
      console.error('Whoops!');
      console.error(err);
      process.exit(1);
    }
    

    (If you use backslashes for libdir, you will need to double them).

    In node-oracledb 5 for macOS we also extended the existing Windows feature allowing Instant Client libraries to be in the same directory as the node-oracledb binary module. Instead of using initOracleClient() you could instead have a package.json script section:

       "scripts": {
          "config-ic": "ln -s /Users/cjones/instantclient/libclntsh.dylib $(npm root)/oracledb/build/Release",
          "start": "node index.js"
       },
    

    Then, after installing with npm install, you can run npm run config-ic. This may not be quite as handy as putting Instant Client in ~/lib on macOS, but recent Node.js binaries no longer access that directory by default. Apple macOS users should review the updated installation instructions and the new functionality.

    See Initializing Node-oracledb.

  • Prefetching for query tuning: A new execute() option prefetchRows can be used in conjunction with the existing fetchArraySize option to tune performance and memory usage of query row fetches. These tuning parameters let you reduce system and network load by adjusting the internal batch sizes used for fetching rows after you execute a query. By setting these parameters you can balance network costs against application memory usage and data copies. The values do not affect how, or when, rows are returned to the application.

    As an example, if your query is returning a small, fixed number of rows, then you might do something like:

    const myoffset = 0;       // do not skip any rows (start at row 1)
    const mymaxnumrows = 20;  // get 20 rows
    
    sql = `SELECT last_name
           FROM employees
           ORDER BY last_name
           OFFSET :offset ROWS FETCH NEXT :maxnumrows ROWS ONLY`;
    
    binds = { offset: myoffset, maxnumrows: mymaxnumrows };
    
    options = { prefetchRows: mymaxnumrows + 1, fetchArraySize: mymaxnumrows };
    
    result = await connection.execute(sql, binds, options);
    

    In this example, prefetching all the rows lets the complete internal execution and data fetch phases use just a single round-trip to the database. In previous node-oracledb versions only two rows would have been prefetched and a second round-trip would have fetched the remaining rows. Note the prefetchRows value is one greater than the number of rows being returned by the query, which removes the need to do an extra round-trip to check for end-of-fetch. The best values for prefetchRows and fetchArraySize for your queries should be determined by tuning. In some cases you may find that only changing fetchArraySize while letting prefetchRows remain its default value of 2 is best.

    See Tuning Fetch Performance.

  • Support for nested cursors: Queries like:

    const sql = `SELECT department_name,
                        CURSOR(SELECT salary, commission_pct
                               FROM employees e
                               WHERE e.department_id = d.department_id
                               ORDER BY salary) as nc
                 FROM departments d
                 ORDER BY department_name`;
    
    const result = await connection.execute(sql);
    console.dir(result.rows, {depth: null});
    

    will produce output like:

    [
      [ 'Accounting', [ [ 8300, null ], [ 12008, null ] ] ],
      [ 'Administration', [ [ 4400, null ] ] ],
      [ 'Benefits', [] ],
      [ 'Construction', [] ],
      [ 'Contracting', [] ],
      [ 'Control And Credit', [] ],
      [ 'Corporate Tax', [] ],
      [
        'Executive',
        [ [ 17000, null ], [ 17000, null ], [ 24000, null ] ]
      ],
      [
        'Finance',
        [
          [ 6900, null ],
          [ 7700, null ],
          [ 7800, null ],
          [ 8200, null ],
          [ 9000, null ],
          [ 12008, null ]
        ]
      ],
      . . .
    

    See Fetching Nested Cursors.

  • Database Startup and Shutdown: New startup and shutdown functions allow simple and flexible control over the database. See Database Start Up and Shut Down

  • A new pool attribute queueMax : This helps protect against connection storms by limiting the number of pool.getConnection() requests that can be queued when all pooled connections are already in use. Any connection requests exeeding the limit will immediately fail without waiting for the queueTimeout period. The default queueMax is 500. You can set it to 0 to disable any queuing, or set it to -1 to allow all requests to be queued.

  • Removed support for custom Promise libraries: The internal JavaScript implementation was refactored to use the cleaner async/await model and support for custom Promise libraries was necessarily removed. Use the native Node.js Promise implementation instead.

Our test suite and documentation was also improved in this release. Check it out!

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.