28. Appendix A: The node-oracledb Thin and Thick Modes

By default, node-oracledb runs in a ‘Thin’ mode which connects directly to Oracle Database. This mode does not need Oracle Client libraries. However, when the driver does use these libraries to communicate to Oracle Database, then node-oracledb is said to be in ‘Thick’ mode and has additional functionality available. See node-oracledb Thick Mode Architecture for the architecture diagram.

This section lists the Oracle Database features that are supported by node-oracledb Thin and Thick modes and the notable differences between the two modes.

28.1. Oracle Database Features Supported by node-oracledb

The following table summarizes the Oracle Database features supported by node-oracledb Thin and Thick modes. For more details see Differences between the node-oracledb Thin and Thick Modes.

Table 28.1 Features Supported in node-oracledb Thin and Thick Modes

Oracle Feature

node-oracledb Thin Mode

node-oracledb Thick Mode

Oracle Client version

Not applicable

Release 11.2 and later

Oracle Database version

Release 12.1 and later

Release 9.2 and later depending on the Oracle Client library version

Oracle Client/Database version interoperability

Not applicable - Connects directly to database version 12.1 and later

Yes

Natively written in JavaScript

Yes

No - Uses a C library called Oracle Database Programming Interface for C (ODPI-C) which internally calls Oracle Call Interface (OCI), the native C interface for Oracle Database

Thin mode

Yes - Direct Oracle Network calls

No - Uses Oracle Client libraries

Standalone connections (see Standalone Connections)

Yes

Yes

Connection pooling - Heterogeneous and Homogeneous (see Connection pooling)

Homogeneous only

Yes

Connection pool draining (see Connection draining)

Yes

Yes

Connection pool session state callback (Node.js Session Callback)

Yes - JavaScript functions but not PL/SQL functions

Yes

Connection pool session tagging (see Connection Tagging and Session State)

No

Yes

Proxy connections (see Connecting Using Proxy Authentication)

Yes

Yes

Set the current schema using an attribute

Yes

Yes

External authentication (see Connecting Using External Authentication)

No

Yes

Connection mode privileges (see Privileged Connection Constants)

Yes

Yes

Preliminary connections

No

Yes

Real Application Clusters (RAC) (see Connecting to Oracle Real Application Clusters (RAC))

Yes

Yes

Oracle Sharded Databases (see Connecting to Sharded Databases)

No

Yes - No TIMESTAMP support

Connection Pool Connection Load Balancing (CLB)

Yes

Yes

Connection Pool Runtime Load Balancing (RLB) (see Runtime Load Balancing (RLB))

No

Yes

Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Tokens (see IAM Token-Based Authentication)

Yes

Yes - In connection string with appropriate Oracle Client

Open Authorization (OAuth 2.0) (see OAuth 2.0 Token-Based Authentication)

Yes

Yes

Kerberos, Radius, and Public Key Infrastructure (PKI) authentication services

No

Yes

Oracle Database Native Network Encryption and Checksumming (see Native Network Encryption)

No

Yes

Connection pinging API (see connection.ping())

Yes

Yes

Oracle Net Services tnsnames.ora file (see Optional Oracle Net Configuration Files)

Yes

Yes

Oracle Net Services sqlnet.ora file (see Optional Oracle Net Configuration Files)

No - A few values can be set in Easy Connect string

Yes

Oracle Client library configuration file oraaccess.xml (see Optional Oracle Client Configuration File)

No

Yes

Easy Connect connection strings (see Easy Connect Syntax for Connection Strings)

Yes - Unknown settings are ignored and not passed to Oracle Database

Yes

Oracle Cloud Database connectivity (see Connecting to Oracle Cloud Autonomous Databases)

Yes

Yes

One-way TLS connections (see One-way TLS Connection to Oracle Autonomous Database)

Yes

Yes

Mutual TLS (mTLS) connections (see Mutual TLS connections to Oracle Cloud Autonomous Database)

Yes - Needs a PEM format wallet

Yes

Oracle Database Dedicated Servers, Shared Servers, and Database Resident Connection Pooling (DRCP) (see Database Resident Connection Pooling (DRCP))

Yes

Yes

Oracle Database 23ai Implicit connection pooling for DRCP and PRCP (see Implicit Connection Pooling)

Yes

Yes

Multitenant Databases

Yes

Yes

CMAN and CMAN-TDM connectivity

Yes

Yes

Bequeath connections

No

Yes

Lightweight Directory Access Protocol (LDAP) connections

No

Yes

Socket Secure (SOCKS) Proxy connections

No

No

Password changing (see connection.changePassword())

Yes

Yes

Statement break/reset (see connection.break())

Yes - Out-of-Band (OOB) Connection Breaks not supported

Yes

Edition Based Redefinition (EBR) (see Edition-Based Redefinition)

No

Yes

SQL execution (see Executing SQL)

Yes - Bind and fetch all types except BFILE

Yes

PL/SQL execution (see Executing PL/SQL)

Yes - For scalar types and collection types using array interface

Yes

Bind variables for data binding (see Using Bind Variables)

Yes

Yes

Array DML binding for bulk DML and PL/SQL (also called executeMany()) (see Executing Batch Statements and Bulk Loading)

Yes

Yes

SQL and PL/SQL type and collections (see Fetching Oracle Database Objects and Collections)

Yes

Yes

Query column metadata (see Query Column Metadata)

Yes

Yes

Client character set support (see Character Sets)

UTF-8

UTF-8

Oracle Globalization support (see Character Sets and Localization)

No - All Oracle NLS environment variables are ignored

Yes - Oracle NLS environment variables are respected except the character set in NLS_LANG

Statement caching (see Statement Caching)

Yes

Yes

Row prefetching on first query execute (see oracledb.prefetchRows)

Yes

Yes

Array fetching for queries (see oracledb.fetchArraySize)

Yes

Yes

Client Result Caching (CRC) (see Client Result Caching (CRC))

No

Yes

In-band notifications

Yes

Yes

Continuous Query Notification (CQN) (see Working with Continuous Query Notification (CQN))

No

Yes

Advanced Queuing (AQ) (see Using Oracle Advanced Queuing (AQ))

No

Yes

AQ: Transactional Event Queue (TxEventQ)

No

Yes

Call timeouts (see connection.callTimeout)

Yes

Yes

Oracle Database startup and shutdown (see Starting and Stopping Oracle Database)

No

Yes

Transaction management (see Managing Transactions)

Yes - Property autoCommit is false

Yes

Automatic Diagnostic Repository (ADR)

No

Yes

Events mode for notifications

No

Yes

Fast Application Notification (FAN) (see Fast Application Notification (FAN))

No

Yes

Transparent Application Failover (TAF)

No

Yes - No callback

Transaction Guard (TG)

No

Yes

Data Guard (DG) and Active Data Guard (ADG)

Yes

Yes

Application Continuity (AC) and Transparent Application Continuity (TAC) (see Application Continuity)

No

Yes

End-to-end monitoring and tracing attributes (see Tracing Executed Statements)

Yes

Yes

Java Debug Wire Protocol for debugging PL/SQL (see Debugging PL/SQL with the Java Debug Wire Protocol)

Yes - Using the connection parameter debugJdwp or the ORA_DEBUG_JDWP environment variable

Yes - Using the the ORA_DEBUG_JDWP environment variable

Feature tracking

No

Yes

Two-phase Commit (TPC) (see Using Two-Phase Commits (TPC))

No

Yes

REF CURSORs (see REF CURSOR Bind Parameters)

Yes

Yes

Nested Cursors (see Fetching Nested Cursors)

Yes

Yes

Pipelined table functions (see pipelined table functions)

Yes

Yes

Implicit Result Sets (see Implicit Results)

Yes

Yes

Application Contexts

No

No

Persistent and Temporary LOBs

Yes

Yes

LOB prefetching

No

No - Does have LOB length prefetch

LOB locator operations such as trim (see API: LOB Class)

Yes - Only read and write operations supported

Yes - Only read operations supported

INTERVAL DAY TO SECOND data type (see Oracle Database Type Objects)

No

No

INTERVAL YEAR TO MONTH data type (see Oracle Database Type Objects)

No

No

Simple Oracle Document Access (SODA) (see SODA)

No

Yes

Oracle Database 12c JSON (as BLOB) (see Using the Oracle Database 12c JSON Type in node-oracledb)

Yes

Yes

Oracle Database 21c JSON data type (see Using the Oracle Database 21c JSON Type in node-oracledb)

Yes

Yes

Oracle Database 23ai JSON duality view

Yes

Yes

Oracle Database 23ai BOOLEAN data type (see Oracle Database Type Objects)

Yes

Yes

ROWID, UROWID data types (see Oracle Database Type Objects)

Yes

Yes

XMLType data type (see Using XMLType Data)

Yes

Yes - May need to fetch as CLOB

BFILE data type

No

No

TIMESTAMP WITH TIME ZONE data type (see Oracle Database Type Objects)

Yes

Yes

NCHAR, NVARCHAR2, NCLOB data types (see Oracle Database Type Objects)

Yes

Yes

Oracle Database 23c VECTOR data type (see Oracle Database Type Objects)

Yes

Yes

Bind PL/SQL Boolean

Yes

Yes

Parallel Queries

No

Yes

Async/Await, Promises, Callbacks and Streams

Yes

Yes

OS Authentication

No

Yes

Batch Errors

Yes

Yes

Database objects (see Using Oracle Database Objects and Collections)

Yes

Yes

Restricted Rowid

No

Yes

28.2. Differences between the node-oracledb Thin and Thick Modes

This section details the differences between the node-oracledb Thin and Thick modes. Also, see the summary feature comparison table in Oracle Database Features Supported by node-oracledb.

28.2.1. Connection Handling Differences between Thin and Thick Modes

Node-oracledb can create connections in either the Thin mode or the Thick mode. However, only one of these modes can be used in each Node.js process.

28.2.1.1. Oracle Client Library Loading

28.2.1.2. Unclosed Standalone Connections

In node-oracedb Thin mode, an unclosed standalone connection takes several seconds to terminate after the query results are printed. This does not occur if connection.close() is explicitly called. The node-oracledb Thick mode does not use event handlers to manage its connections and does not run into this issue. However, the Thick mode does not close connections until garbage collection occurs or it may not close the connection if the process terminates before garbage collection occurs. It is recommended to explicitly close standalone connections using connection.close() in both the Thin and Thick modes.

28.2.1.3. Connections to a Local Database

In node-oracledb Thin mode, there is no concept of a local database. Bequeath connections cannot be made since Oracle Client libraries are not used. The Thin mode does not de-reference environment variables such as ORACLE_SID, TWO_TASK, or LOCAL (the latter is specific to Windows). A connection string, or equivalent, must always be used.

28.2.1.4. Oracle Net Services and Client Configuration Files

In the node-oracledb Thin mode:

  • The tnsnames.ora file will be read. The directory can be set using:

    The default file locations such as Instant Client network/admin/ subdirectory, $ORACLE_HOME/network/admin/, or $ORACLE_BASE/homes/XYZ/network/admin/ (in a read-only Oracle Database home) will not be used automatically by the Thin mode.

  • Any sqlnet.ora file will not be read. Instead, pass equivalent settings when connecting.

  • There is no support for oraaccess.xml since there are no Oracle Client libraries.

See Optional Oracle Net Configuration Files and Optional Oracle Client Configuration File for more information.

28.2.1.5. Connection Strings

The node-oracledb Thin mode accepts connection strings in the same formats as the Oracle Client libraries used by Thick mode does, but not all Oracle Net keywords will be supported.

The following table lists the parameters that are recognized in Thin mode either in Easy Connect Strings or in Full Connect Descriptor Strings that are either explicitly passed or referred to by a tnsnames.ora alias. All unrecognized parameters are ignored.

Table 28.2 Oracle Net Keywords Supported in the node-oracledb Thin Mode

Oracle Net Keyword

Equivalent Connection Parameter

Description

SSL_SERVER_CERT_DN

sslServerCertDN

The distinguished name (DN) that should be matched with the server.

Note: If specified, this value is used for any verification. Otherwise, the hostname will be used.

SSL_SERVER_DN_MATCH

sslServerDNMatch

Determines whether the server certificate DN should be matched in addition to the regular certificate verification that is performed.

Note: In Thin mode, parsing the parameter supports case insensitive on/yes/true values similar to the Thick mode. Any other value is treated as disabling it.

WALLET_LOCATION

walletLocation

The directory where the wallet can be found.

Note: Used in Easy Connect Strings. It is same as MY_WALLET_DIRECTORY in a connect descriptor.

MY_WALLET_DIRECTORY

walletLocation

The directory where the wallet can be found.

Note: Used by connect descriptors in the oracledb.getConnection() or oracledb.createPool() functions.

EXPIRE_TIME

expireTime

The number of minutes between the sending of keepalive probes.

HTTPS_PROXY

httpsProxy

The name or IP address of a proxy host to use for tunneling secure connections.

HTTPS_PROXY_PORT

httpsProxyPort

The port to be used to communicate with the proxy host.

RETRY_COUNT

retryCount

The number of times that a connection attempt should be retried before the attempt is terminated.

RETRY_DELAY

retryDelay

The number of seconds to wait before making a new connection attempt.

TRANSPORT_CONNECT_TIMEOUT

transportConnectTimeout

The maximum number of seconds to wait to establish a connection to the database host.

POOL_CONNECTION_CLASS

cclass

Defines a logical name for connections.

In node-oracledb Thick mode, the above values only work when connected to Oracle Database 21c or later.

The ENABLE=BROKEN connect descriptor option is not supported in node-oracledb Thin mode. Use expireTime instead.

The Session Data Unit (SDU) connect descriptor option that is used to tune network transfers is supported in node-oracledb Thin mode and has a default value of 8 KB. In node-oracledb Thick mode, the SDU connect descriptor option and equivalent sqlnet.ora setting are used.

If a bare name is given as a connect string, then the node-oracledb Thin mode will consider it as a Net Service Name and not as the minimal Easy Connect string of a hostname. The given connect string will be looked up in a tnsnames.ora file. This is different from the node-oracledb Thick mode. If supporting a bare name as a hostname is important to you in the node-oracledb Thin mode, then you can alter the connection string to include a port number such as hostname:1521 or a protocol such as tcp://hostname.

For multiple hosts or IP addresses, node-oracledb connects to the host name used in the parameters of the Connection object, if available. If the Connection object does not have the parameters, then the parameters in the connect string are considered.

28.2.1.6. Database Resident Connection Pooling (DRCP)

When using DRCP, the oracledb.connectionClass should be set in the node-oracledb application. If not, then node-oracledb generates a unique connection class for each pool. The prefix of the generated connection class varies in the node-oracledb Thin and Thick modes. In node-oracledb Thin mode, the prefix of the generated connection class is “NJS”. For node-oracledb Thick mode, the prefix is “OCI”. See Database Resident Connection Pooling (DRCP) for more information.

28.2.1.7. Transport Layer Security (TLS) Support

When connecting with mutual TLS (mTLS) also known as two-way TLS, for example to Oracle Autonomous Database in Oracle Cloud using a wallet, the certificate must be in the correct format.

For the node-oracledb Thin mode, the certificate must be in a Privacy Enhanced Mail (PEM) ewallet.pem file. In node-oracledb Thick mode the certificate must be in a cwallet.sso file. See Connecting to Oracle Cloud Autonomous Databases for more information.

28.2.1.8. Native Network Encryption and Checksumming

The node-oracledb Thin mode does not support connections using Oracle Database native network encryption or checksumming. You can enable TLS instead of using native network encryption. If native network encryption or checksumming are required, then use node-oracledb in the Thick mode. See Native Network Encryption.

28.2.1.9. Password Verifier Support

Password verifiers help in authenticating the passwords of your user account when you are using username and password authentication to connect your application to Oracle Database. Password verifiers are also called password versions. The password verifier can be 10G (case-insensitive Oracle password verifier), 11G (SHA-1-based password verifier), and 12C (SHA-2-based SHA-512 password verifier).

The node-oracledb Thin mode supports password verifiers 11G and later. The node-oracledb Thick mode supports password verifiers 10G and later. To view all the password verifiers configured for the user accounts, use the following query:

SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

The PASSWORD_VERSIONS column lists all the password verifiers that exist for the user.

If you try to connect to any supported Oracle Database with node-oracledb Thin mode, but the user account is created only with the 10G password verifier, then the connection will fail with the NJS-116 error.

28.2.2. Connection Pooling Differences between Thin and Thick Modes

The createPool() method in the node-oracledb Thin mode differs from the node-oracledb Thick mode in the following ways:

  • Not all the parameters of the oracledb.createPool() method are applicable to both node-oracledb modes. Each mode ignores unrecognized parameters. The parameters that are supported in Thin mode include accessToken, connectString, connectionString, enableStatistics, password, poolAlias, poolIncrement, poolMax, poolMin, poolPingInterval, poolTimeout, queueMax, queueRequests, queueTimeout, stmtCacheSize, user, and username parameters.

  • The node-oracledb Thin mode only supports homogeneous pools.

  • The node-oracledb Thin mode creates connections in an async fashion and so oracledb.createPool() returns before any or all minimum number of connections are created. As soon as the pool is created, the pool.connectionsOpen attribute will not be equal to pool.poolMin. The connectionsOpen attribute will increase to the minimum value over a short time as the connections are established. Note that this behavior may also be true of recent versions of the Oracle Call Interface (OCI) Session Pool used in the Thick mode.

    This improves the application start up time when compared to the node-oracledb Thick mode, where oracledb.createPool() will not return control to the application until all pool.poolMin connections have been created.

    If the old default Thick mode behaviour is required, then the application could check if pool.connectionsOpen has reached pool.poolMin and then continue with application start up.

  • In node-oracledb Thin mode, the cclass parameter value is not used to tag connections in the application connection pool. It is only used for Database Resident Connection Pooling (DRCP).

  • In node-oracledb Thin mode, the connection pool supports all the connection mode privileges.

    The node-oracledb Thick mode does not support all the connection mode privileges.

  • In node-oracledb Thin mode, privileged connections can be created with homogeneous pools.

    The node-oracledb Thick mode can only create privileged connections with heterogeneous pools.

  • In node-oracledb Thick mode, the worker threads can be increased by setting the environment variable UV_THREADPOOL_SIZE before starting Node.js. This is not applicable to the Thin mode since it does not use threads.

28.2.3. Query Metadata in Thin and Thick Modes

In node-oracledb Thin mode, resultset.metaData can distinguish the ROWID and UROWID database types. The UROWID database type shows the new value DB_TYPE_UROWID and the database type ROWID uses the existing value DB_TYPE_ROWID.

In node-oracledb Thick mode, the value DB_TYPE_ROWID is shown for both ROWID and UROWID database types. In node-oracledb Thick and Thin modes, comparison with the type oracledb.ROWID will match both ROWID and UROWID database types.

28.2.4. Error Handling in Thin and Thick Modes

The node-oracledb Thin and Thick modes handle some errors differently. See Errors in Thin and Thick Modes.

28.2.5. Globalization in Thin and Thick Modes

All Oracle NLS environment variables, and the ORA_TZFILE environment variable, are ignored by the node-oracledb Thin mode.

The node-oracledb Thin mode can only use NCHAR, NVARCHAR2, and NCLOB data when Oracle Database’s secondary character set is AL16UTF16.

See Character Sets and Localization.

28.2.6. Tracing in Thin and Thick Modes

In the node-oracledb Thin mode, low level tracing is different because there are no Oracle Client libraries. See Tracing with node-oracledb.

28.2.7. Data Type Conversion in Thin and Thick Modes

The node-oracledb Thick mode uses Oracle NLS conversion routines to convert the data found in the database to the desired data type. The node-oracledb Thin mode uses fixed JavaScript routines such as toString(). You can use a converter function to modify the behavior. See fetch type handlers.

28.2.8. Supported Database Data Types in Thin and Thick Modes

The node-oracledb Thin and Thick modes support different Oracle Database data types. The following table lists the types that are supported in the node-oracledb driver. See Oracle Database Types and PL/SQL Types. The node-oracledb database type shown is the common one. In some node-oracledb APIs you may use other types, for example when binding numeric values.

Table 28.3 Oracle Database Data Types Supported

Oracle Database Type

node-oracledb Database Type

Supported in node-oracledb

CHAR

DB_TYPE_CHAR

Yes

NCHAR

DB_TYPE_NCHAR

Yes

VARCHAR2

DB_TYPE_VARCHAR

Yes

NVARCHAR2

DB_TYPE_NVARCHAR

Yes

NUMBER, FLOAT

DB_TYPE_NUMBER

Yes

BINARY_FLOAT

DB_TYPE_BINARY_FLOAT

Yes

BINARY_DOUBLE

DB_TYPE_BINARY_DOUBLE

Yes

LONG

DB_TYPE_LONG

Yes

DATE

DB_TYPE_DATE

Yes

TIMESTAMP

DB_TYPE_TIMESTAMP

Yes

TIMESTAMP WITH TIME ZONE

DB_TYPE_TIMESTAMP_TZ

Yes

TIMESTAMP WITH LOCAL TIME ZONE

DB_TYPE_TIMESTAMP_LTZ

Yes

INTERVAL YEAR TO MONTH

Not supported

No

INTERVAL DAY TO SECOND

Not supported

No

RAW

DB_TYPE_RAW

Yes

LONG RAW

DB_TYPE_LONG_RAW

Yes

BFILE

DB_TYPE_BFILE

No

BLOB

DB_TYPE_BLOB

Yes

CLOB

DB_TYPE_CLOB

Yes

NCLOB

DB_TYPE_NCLOB

Yes

JSON

DB_TYPE_JSON

Yes

ROWID

DB_TYPE_ROWID

Yes

UROWID

DB_TYPE_ROWID, DB_TYPE_UROWID

Yes. May show DB_TYPE_UROWID in metadata. See Query Metadata Differences.

BOOLEAN (PL/SQL and SQL)

DB_TYPE_BOOLEAN

Yes

PLS_INTEGER (PL/SQL)

DB_TYPE_BINARY_INTEGER

Yes

BINARY_INTEGER (PL/SQL)

DB_TYPE_BINARY_INTEGER

Yes

REF CURSOR (PL/SQL or nested cursor)

DB_TYPE_CURSOR

Yes

REF

DB_TYPE_OBJECT

No

User-defined types (object type, VARRAY, records, collections, SDO_* types)

DB_TYPE_OBJECT

Thick mode only

ANYTYPE

DB_TYPE_OBJECT

Thick mode only

ANYDATA

DB_TYPE_OBJECT

Thick mode only

ANYDATASET

DB_TYPE_OBJECT

Thick mode only

XMLType

DB_TYPE_XMLTYPE

Yes

28.3. Testing Which Mode Is in Use

To know whether the driver is in Thin or Thick Mode, you can use oracledb.thin. The boolean attributes connection.thin and pool.thin can be used to show the current mode of a node-oracledb connection or pool, respectively.

Another method that can be used to check which mode is in use is to query V$SESSION_CONNECT_INFO. See Finding the node-oracledb Mode.

28.4. Frameworks, SQL Generators, and ORMs

The features of node-oracledb Thin mode cover the needs of common frameworks that depend upon the Node.js API. For example, the node-oracledb Thin mode can be used in Sequelize. To run the node-oracledb Thin mode through Sequelize, you must not set the libPath in the dialectOptions object and must not initOracleClient(). For node-oracledb Thick mode, set the libPath in the dialectOptions object or call initOracleClient().