29. Upgrading to the Latest node-oracledb Releases

29.1. Upgrading from node-oracledb 6.10 to 7.0

Review the node-oracledb Release Notes and take advantage of new features.
In node-oracledb Thin mode, you can now use Oracle AI Database 26ai Pipelining.
In node-oracledb Thin mode, the new method connection.directPathLoad() can be used to perform a direct path load into a table.
The new methods oracledb.isSimpleSqlName() and oracledb.isQualifiedSqlName() can be used to validate simple and qualified SQL names. To safely quote SQL literals and identifiers, you can use the new methods oracledb.enquoteLiteral() and oracledb.enquoteName().
The new attribute oracledb.thickModeDSNPassthrough can be used to control connect string parsing. When set to false, Thick mode behaves similar to Thin mode when resolving Oracle Database Easy Connect strings, processing “njs.” prefixed query parameters, and applying descriptor overrides. The default value true retains the previous Thick mode behavior of passing the connect string directly down to the Oracle Client libraries.
With the new methods connection.appContext() and connection.clearAppContext() methods, you can set and clear an application context set on a connection.
With the new connection properties connection.pdbName and connection.dbUniqueName, you can identify the pluggable database name and database unique name respectively on a connection.
The new method lob.trim() can be used to trim a non-BFILE LOB to the new specified size.
You can now use the configuration information stored in AWS Simple Storage Service (S3) and AWS Secrets Manager Centralized Configuration Providers, and connect to Oracle Database.
In Token-based Authentication, you can now include proxy session users by supplying user name inside square brackets (for example, user: "[session_user]") alongside an authentication token when using external authentication.
You can now use Explicit Resource Management with Connection, Pool, and Resultset objects.
With the new methods connection.setEndUserSecurityContext() and connection.clearEndUserSecurityContext() in node-oracledb Thin mode, you can set and clear an end user security context on a connection.
The interval datatypes (interval year-to-month and interval day-to-second) are now normalized before being sent to the database. This prevents invalid data from being stored in the database.
The support for Oracle Client libraries earlier than 19c was dropped.
Pre-built Thick mode binaries for macOS Intel x86-64 are no longer included in the node-oracledb installation starting from this release.

29.2. Upgrading from node-oracledb 6.9 to 6.10

Review the node-oracledb Release Notes and take advantage of new features.
The new deliveryMode property in aqQueue.deqOptions can be used to set the delivery mode when dequeing messages.
The new extended metadata information attribute dbColumnName for a fetched column provides the actual database column name.
You can now store the duration that node-oracledb should keep the configuration information cached in Centralized Configuration Providers by using the config_time_to_live key.
You can now use Oracle Advanced Queuing in node-oracledb Thin mode.
In node-oracledb Thin mode, you can pass a proxy user with external authentication in oracledb.createPool().
In node-oracleb Thick mode, you can now work directly with JSON data types in SODA starting with Oracle Client version 23 which allows for seamless transfer of extended data types in JSON values.
The method sodaDocument.getContent() now returns non-JSON content as a JavaScript Buffer.

29.3. Upgrading from node-oracledb 6.8 to 6.9

Review the node-oracledb Release Notes and take advantage of new features.
With the new methods connection.beginSessionlessTransaction(), connection.resumeSessionlessTransaction(), and connection.suspendSessionlessTransaction(), you can use Oracle Database 26ai Sessionless Transactions.
With the new property ltxid, you can now use Transaction Guard.
The new attributes, precision, scale, and maxSize, of the dbObject.attributes property provide additional metadata about the database object.
The new property, pool.maxLifetimeSession, determines the number of seconds that a connection can exist in a connection pool after first being created.
With Centralized Configuration Providers:
- You can now use the configuration information stored in File Configuration Provider, OCI Vault, and Azure Key Vault and connect to Oracle Database.
- You can now use the following node-oracledb centralized configuration provider plugins to access the configuration information stored in the respective configuration provider and connect to Oracle Database:
  - ociobject plugin for OCI Object Storage centralized configuration provider.
  - ocivault plugin for OCI Vault centralized configuration provider.
  - azure plugin for Azure App Configuration centralized configuration provider.
  - azurevault plugin for Azure Key Vault centralized configuration provider.
  To use centralized configuration provider plugins, you must add the following line to your code:
```
require('oracledb/plugins/configProviders/<configProviderPlugin>');
```
The new method oracledb.registerConfigurationProviderHook() registers centralized configuration provider extension modules.
Take advantage of Instance Principal authentication when using Oracle Cloud Infrastructure (OCI) Cloud Native token-based authentication.
The application context can now be set when creating database connections.
A new second argument for the fetchTypeHandler call contains the metadata of all the result columns.
The new property, error.isRecoverable, checks if an error is recoverable.
In node-oracledb Thick mode, the BFILE method lob.setDirFileName() can now be used.
In node-oracledb Thick mode, the new property, enqTime, for Advanced Queuing (AQ) messages specifies the time when the message was enqueued.

29.4. Upgrading from node-oracledb 6.7 to 6.8

Review the node-oracledb Release Notes and take advantage of new features.
With the new Oracledb IntervalYM Class and Oracledb IntervalDS Class, you can fetch, insert, and update INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND database column types respectively. See Using INTERVAL Data.
Using the Oracledb SparseVector Class, you can fetch, insert, and update Oracle Database 23.7 SPARSE vectors which store only the non-zero values physically. See Using SPARSE Vectors.
With Cloud Native Authentication, you can use:
- Node-oracledb’s extensionOci plugin to automatically generate tokens using OCI SDK when authenticating with Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) token-based authentication. See Cloud Native Authentication with the extensionAzure Plugin.
- Node-oracledb’s extensionAzure plugin to automatically generate tokens using Microsoft Authentication Library for Node (msal-node) when authenticating with OAuth 2.0 token-based authentication. See Cloud Native Authentication with the extensionAzure Plugin.
The new oracledb.registerProcessConfigurationHook() method registers extension modules (plugins).
The new connection.maxIdentifierLength property returns the maximum identifier length allowed by the database.
The new dbObject.copy() method creates deep copies of database objects.
With the new oracledb.dbObjectTypeHandler property, you can now specify a user function when using DbObjects to modify its properties before it is returned to the application.
You can now pass BigInt values in DbObjects.
In node-oracledb Thin mode, you can now perform external authentication using Transport Layer Security (TLS) protocol.
In node-oracledb Thin mode, you can now enable Advanced Network Compression support using the new properties networkCompression and networkCompressionThreshold in oracledb.createPool() and oracledb.getConnection().
You can now enable a connection optimization feature which uses Server Name Indication (SNI) extension of the TLS protocol in node-oracledb Thin mode by using the property useSNI in oracledb.createPool() or oracledb.getConnection().
In node-oracledb Thin mode, you can now set the property edition when connecting to Oracle Database.

29.5. Upgrading from node-oracledb 6.6 to 6.7

Review the node-oracledb Release Notes and take advantage of new features.
Using the new oracledb.getNetworkServiceNames() method, you can fetch the list of TNS Aliases from the tnsnames.ora file.
With Centralized Configuration Providers, you can now:
- Connect to Oracle Database using wallets stored in Azure Key Vault and OCI vault.
- Cache the configuration information retrieved from Azure App Configuration and OCI Object Storage centralized configuration providers.
In node-oracledb Thin mode, you can use the attributes oracledb.driverName, oracledb.machine, oracledb.osUser, oracledb.program, and oracledb.terminal to set information about the driver name, machine name, operating system user, program name, and terminal name respectively.
In node-oracledb Thick mode, the new regId property of the message object parameter in the CQN subscription callback function returns a unique identifier during registration.

29.6. Upgrading from node-oracledb 6.5 to 6.6

Review the node-oracledb Release Notes and take advantage of new features.
With the new BINARY vector format, the value of each vector dimension can be represented as a single bit (0 or 1).
You can retrieve configuration information from two Centralized Configuration Providers, Microsoft Azure App Configuration and Oracle Cloud Infrastructure (OCI) Object Storage and connect to Oracle Database.
You can use the new oracledb.DB_TYPE_BFILE constant to represent Oracle Database 26ai data type BFILE.
In node-oracledb Thin mode, you can directly specify the security credentials in the walletContent property of oracledb.createPool() and oracledb.getConnection().
You can now process tnsnames.ora files containing IFILE directives.
You can now use Two-Phase Commits in node-oracledb Thin mode.

29.7. Upgrading from node-oracledb 6.4 to 6.5

Review the node-oracledb Release Notes and take advantage of new features.
The new oracledb.JsonId class represents JSON ID values returned by SODA in Oracle Database 26ai and later in the _id attribute of documents stored in native collections.
You can now pass BigInt values as binds to connection.execute() and connection.executeMany().
With the new oracledb.DB_TYPE_VECTOR constant, you can now represent Oracle Database 26ai data type VECTOR with the vectorDimensions and vectorFormat metadata information attributes.
In node-oracledb Thin mode, a subset of pool creation properties can be changed without restarting the pool or application using the pool.reconfigure() method.
In node-oracledb Thin mode, you can now use Oracle Database 26ai’s Implicit Connection Pooling feature with Database Resident Connection Pooling (DRCP) and Proxy Resident Connection Pooling (PRCP).

29.8. Upgrading from node-oracledb 6.3 to 6.4

Review the node-oracledb Release Notes and take advantage of new features.
By setting the new oracledb.future.oldJsonColumnAsObj property to true, you can fetch the BLOB columns which have the the IS JSON FORMAT OSON constraint enabled in the same way as columns of type JSON. See Using BLOB columns with OSON Storage Format in node-oracledb for more information. In a future version of node-oracledb, the setting of this attribute will no longer be required since this will become the default behavior.
With the new connection.encodeOSON() and connection.decodeOSON() methods, you can fetch and insert into columns which have the IS JSON FORMAT OSON constraint enabled.
The new metadata information attribute isOson indicates whether the fetched column contains binary encoded OSON data.
The lob.getData() now accepts the offset and amount as input parameters.
The connection.execute() now accepts an object as an input parameter. The object is returned from the third-party sql-template-tag module and exposes statement and values properties to retrieve SQL string and bind values.
The new dbObject.toMap() method returns a map object for the collection types indexed by PLS_INTEGER.
Using the new oracledb.poolPingTimeout and pool.poolPingTimeout properties, you can now limit the connection.ping() call time.
Using the new warning property of the result object in connection.executeMany(), your application can manually check for database warnings such as PL/SQL Compilation Warnings.
In node-oracledb Thick mode, the SodaDocumentCursor class now supports asynchronous iteration.

29.9. Upgrading from node-oracledb 6.2 to 6.3

Review the node-oracledb Release Notes and take advantage of new features.
Using the new warning property of the result object in connection.execute(), your application can manually check for database warnings such as PL/SQL Compilation Warnings.
The new connection.warning property can be used to check for warnings that are generated during connection such as the password being in the grace period.
By setting the new oracledb.future.oldJsonColumnAsObj property to true, you can fetch the VARCHAR2 and LOB columns which contain JSON in the same way as columns of type JSON. See Using the Oracle Database 12c JSON Type in node-oracledb for more information. In a future version of node-oracledb, the setting of this attribute will no longer be required since this will become the default behavior.
With the new oracledb.DB_TYPE_XMLTYPE constant, you can now represent data of type SYS.XMLTYPE in the fetchType and dbType metadata information attributes.
node-oracledb now supports using Azure and Oracle Cloud Infrastructure (OCI) Software Development Kits (SDKs) to generate authentication tokens.
With the new connection properties connection.dbDomain, connection.dbName, connection.maxOpenCursors, connection.serviceName and connection.transactionInProgress, you can identify the database domain name, database instance name, maximum number of cursors that can be opened per connection, database service name, and status of any ongoing transactions on the connection respectively.
The new metadata information attribute isJson indicates whether the fetched column contains JSON data.
The new metadata information attributes annotations, domainName, and domainSchema identifies the annotations object, the name of the data use case domain, and the schema name of the data use case domain associated with the fetched column. Annotations and data use case domains are supported from Oracle Database 26ai onwards. For node-oracledb Thick mode, Oracle Client version 23 is also required.
In node-oracledb Thin mode, SYS.XMLTYPE data can now be fetched as strings.

29.10. Upgrading from node-oracledb 6.1 to 6.2

Review the node-oracledb Release Notes and take advantage of new features.
With the new SODA features in node-oracledb Thick mode:
- You can now fetch all the current indexes from a SODA collection using the new sodaCollection.listIndexes() method.
- You can disable modification of SODA documents by other connections using the new sodaOperation.lock() method.
Using the new binaryDir property in node-oracledb Thick mode, you can now specify the directory that is added to the start of the default search path used by initOracleClient() to load the Thick mode binary module.
Using the new packageName property in DbObject class, you can identify the name of the package if the type refers to a PL/SQL type.

29.11. Upgrading from node-oracledb 6.0 to 6.1

Review the node-oracledb Release Notes and take advantage of new features.
With the new Advanced Queuing (AQ) features in node-oracledb Thick mode:
- You can now enqueue and dequeue AQ messages as JSON.
- The queue.enqOne() and queue.enqMany() methods now return a message object with which you can view the unique identifier of each message.
With the new connection.instanceName property, you can identify the Oracle Database instance name associated with a connection.

29.12. Upgrading from node-oracledb 5.5 to 6.0

Review the node-oracledb Release Notes and take advantage of new features.
To use node-oracledb 6.0, you need Node.js 14.6 or later versions. Update your Node.js version, if necessary.
With node-oracledb 6.0, connections to Oracle Database can be established in one of the two modes:
- Thin mode: By default, node-oracledb operates in this mode and connects directly to Oracle Database. This mode does not require Oracle Client libraries.
- Thick mode: When Oracle Client libraries are used, then node-oracledb is in Thick mode. You must call oracledb.initOracleClient() to enable Thick mode. See Enabling node-oracledb Thick Mode.
Review the updated node-oracledb installation instructions and initialization options.
The Oracle Database features supported by the node-oracledb Thin and Thick modes and the notable differences between these two modes are detailed here.
If your application currently uses Thick mode, and you want to use the Thin mode, see Changing Applications to Use node-oracledb Thin Mode.
Note that the Oracle Database Type constants were changed to database type objects in node-oracledb 6.0. When comparing fetch types, ensure that you are using the database type object name instead of the the database type number. For example, use result.metadata[0].fetchType == oracledb.DB_TYPE_VARCHAR instead of result.metadata[0].fetchType == 2001.
Oracle Database DATE and TIMESTAMP types are now returned as JavaScript date types in the application’s timezone. These database types are no longer fetched or bound as TIMESTAMP WITH LOCAL TIME ZONE. The connection session time zone does not impact these database types. There is no change to the handling of TIMESTAMP WITH TIMEZONE. TIMESTAMP WITH LOCAL TIME ZONE columns continue to use the corresponding database type, but their JavaScript Date representation can differ from node-oracledb 5.5 behavior. To return TIMESTAMP WITH LOCAL TIME ZONE values in a time zone representation similar to node-oracledb 5.5, use a fetch type handler with a converter.
The execution option attribute fetchInfo was deprecated. You can use the Using Fetch Type Handlers functionality instead which has introduced a new oracledb.fetchTypeHandler and equivalent execution option which allows you to alter the queried data before it is returned to the application.
The previously deprecated Token-Based Authentication accessTokenCallback attribute has been removed. Use accessToken instead.
Extended metadata is now always returned for queries. The oracledb.extendedMetaData and equivalent execution attribute values are ignored.
The node-oracledb Thin and Thick modes may return different errors in some scenarios. See Error Handling.
The node-oracledb Thick mode uses Oracle Database’s National Language Support (NLS) functionality to assist in globalizing applications. The node-oracledb Thin mode uses Node.js localization functions. See Character Sets and Localization.

29.12.1. Changing Applications to Use node-oracledb Thin Mode

Changing an existing application that currently uses Thick mode to use Thin mode may require a few changes as detailed below.

Review Oracle Database Features Supported by node-oracledb and Differences between node-oracledb Thin and Thick Modes to ensure that all the features required for your application are supported by the Thin mode.

The node-oracledb Thin and Thick modes can both connect to on-premises databases and Oracle Cloud databases. However, the node-oracledb Thin mode does not support some of the advanced Oracle Database features such as Application Continuity (AC), Advanced Queuing (AQ), Continuous Query Notification (CQN), SODA, and Sharding.
If you are upgrading from node-oracledb 5.5, then review Upgrading from node-oracledb 5.5 to 6.0.
Remove all calls to oracledb.initOracleClient() from the application since this enables the node-oracledb Thick mode.
If the configDir parameter of initOracleClient() had been used, then set the configDir attribute of any oracledb.getConnection() or oracledb.createPool() calls.
If the application is connecting using a TNS alias and is looking up that alias in a tnsnames.ora file from a “default” location such as the Instant Client network/admin/ subdirectory, in $ORACLE_HOME/network/admin/, or in $ORACLE_BASE/homes/XYZ/network/admin/ (in a read-only Oracle Database home), then the configuration file directory must now explicitly be set. See Using Optional Oracle Configuration Files.
The node-oracledb Thin mode does not support sqlnet.ora files. Some of these parameters can be set as getConnection() or createPool() attributes, or in an Easy Connect string, or in the tnsnames.ora file connect descriptors.
If you were using node-oracledb in an ORACLE_HOME database installation environment, you will now need to use an explicit connection string since the ORACLE_SID environment variable is not used in node-oracledb Thin mode.
Remove calls to oracledb.oracleclientVersion() and oracledb.oracleclientVersionString which are only available in the node-oracledb Thick mode. Oracle Client libraries are not used in Thin mode.
Ensure that any assumptions about when connections are created in the connection pool are eliminated. The node-oracledb Thin mode creates connections in an async fashion and so oracledb.createPool() will return before any, or all, minimum number of connections are created. The attribute pool.connectionsOpen will change over time and will not be equal to pool.poolMin immediately after the pool is created. In node-oracledb Thick mode and earlier node-oracledb versions, oracledb.createPool() does not return control to the application until all the pool.poolMin connections were created.
Make any additional code changes required for Error Handling differences, or Character Sets and Localization differences.
When you are satisfied, you can optionally remove Oracle Client libraries. For example, by deleting your Oracle Instant Client directory.

You can find the node-oracledb mode by checking node-oracledb attributes or querying the V$SESSION_CONNECT_INFO table, see Finding the node-oracledb Mode.

29.13. Upgrading from node-oracledb 5.4 to 5.5

Review the node-oracledb Release Notes and take advantage of new features.
With the new Oracle Advanced Queuing (AQ) Recipient Lists, you can now specify a list of recipients when enqueuing a message.
Take advantage of the new Open Authorization (OAuth 2.0) token-based authentication which allows users to authenticate to Oracle Database using Microsoft Azure Active Directory OAuth 2.0 tokens.
The connection pool creation attribute accessTokenCallback is deprecated. Use accessToken instead.
The pool.setAccessToken() method is deprecated.

29.14. Upgrading from node-oracledb 5.3 to 5.4

Review the node-oracledb Release Notes and take advantage of new features.
With the connection.isHealthy() function, you can perform a local connection health check.
Take advantage of token-based authentication when establishing pool based connections and standalone connections.
The new stack property in Error object aids in diagnosis of errors.

29.15. Upgrading from node-oracledb 5.2 to 5.3

Review the node-oracledb Release Notes and take advantage of new features.
Using the keepInStmtCache option in execute(), executeMany(), and queryStream(), you can control whether executed statements should be retained in the Statement Cache.
The connection pool statistics is encapsulated in a PoolStatistics Class. The poolstatistics.logStatistics() function is added which is equivalent to the existing pool.logStatistics() function. The exposed pool properties are user, connectString, edition, events, externalAuth, and homogeneous on the Pool and PoolStatistics classes.
Take advantage of the Two-Phase Commit feature.

29.16. Upgrading from node-oracledb 5.1 to 5.2

Review the node-oracledb Release Notes and take advantage of new features.
Review the dead connection detection changes and adjust any application error checks to look for the new error DPI-1080.
Replace obsolete uses of _enableStats and _logStats() with the new functionality enableStatistics, getStatistics(), and logStatistics().

29.17. Upgrading from node-oracledb 4.2 to 5.0

Review the node-oracledb Release Notes and take advantage of new features.
Review the updated installation and initialization options in the node-oracledb installation instructions and Initializing Node-oracledb, particularly around how node-oracledb can locate Oracle Client libraries.
Choose a sensible value for the new Pool queueMax attribute, so that applications get the new error only under abnormal connection load. To allow all pooled connection requests to be queued (the previous behavior), set it to -1.
Take advantage of the new prefetchRows attribute to re-tune SQL queries.
Support for custom Promises was necessarily removed due to a refactoring of the module’s JavaScript layer. Code should be migrated to use the native Node.js Promise implementation.
The function call parameter errors NJS-005: invalid value for parameter and NJS-009: invalid number of parameters are now passed through the callback, if one is used. In earlier versions they were thrown without the ability for them to be caught.

29.18. Upgrading from node-oracledb 4.1 to 4.2

Review the node-oracledb Release Notes and take advantage of new features.
Review the updated Lob stream documentation. The best practice is to use the end event (for readable streams) and finish event (for writeable streams) instead of depending on the close event. Applications should migrate to the Node.js 8 destroy() method instead of the deprecated node-oracledb close() method. Note that unlike close(), the destroy() method does not take a callback parameter. If destroy() is given an error argument, an error event is emitted with this error.

29.19. Upgrading from node-oracledb 4.0 to 4.1

Review the node-oracledb Release Notes and take advantage of new features.
Review your application use of node-oracledb error messages since some have changed.
Note that the default for oracledb.events has reverted to false. If you relied on it being true, then explicitly set it.

29.20. Upgrading from node-oracledb 3.1 to 4.0

Review the node-oracledb Release Notes and take advantage of new features.
Update Node.js, if necessary. Node-oracledb 4.0 requires
- Node.js 8.16 or higher
- Node.js 10.16, or higher
- Node.js 12
Review error handling. Some errors have changed. All exceptions are now passed through the error callback.
Code that relied on numeric values for the node-oracledb Type Constants and Oracle Database Type Constants will need updating. Use the constant names instead of their values.
To view node-oracledb class information, update code to use Object.getPrototypeOf().
Optionally migrate outFormat constants to the new, preferred names OUT_FORMAT_ARRAY and OUT_FORMAT_OBJECT.

29.21. Earlier node-oracledb Versions

Documentation about node-oracledb version 1 is here.

Documentation about node-oracledb version 2 is here.

Documentation about node-oracledb version 3 is here.

Documentation about node-oracledb version 4 is here.