27. 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.

27.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 27.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 23c Implicit connection pooling for DRCP and PRCP (see Implicit Connection Pooling with DRCP and PRCP)	Yes	Yes
Multitenant Databases	Yes	Yes
CMAN and CMAN-TDM connectivity	Yes	Yes
Bequeath connections	No	Yes
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 23c JSON duality view	Yes	Yes
Oracle Database 23c 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
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

27.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.

27.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.

27.2.1.1. Oracle Client Library Loading

By default, node-oracledb runs in a ‘Thin’ mode which connects directly to Oracle Database. This mode does not need Oracle Client libraries. See node-oracledb Thin Mode Architecture.
If oracledb.initOracleClient() is called in your application before any standalone connections or pool is created, then the node-oracledb mode changes to Thick mode. Calling the initOracleClient() method immediately loads Oracle Client libraries. Some additional functionality is available when node-oracledb uses the Oracle Client libraries. See node-oracledb Thick Mode Architecture.

27.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.

27.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.

27.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 TNS_ADMIN environment variable
- The configDir property of the getConnection() or createPool() functions
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.

27.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 27.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.

27.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.

27.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.

27.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.

27.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.

27.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 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.

27.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.

27.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.

27.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.

27.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.

27.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.

27.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 27.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

27.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.

27.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().