DOCSEARCHbyAlgolia
Submit Docs

node-oracledb

repository·main·Indexed 25 days ago

https://github.com/oracle/node-oracledb

A high-performance Node.js add-on for Oracle Database access from JavaScript and TypeScript. Version 7.0.1 supports both a lightweight 'Thin' mode, which connects directly to the database, and a feature-rich 'Thick' mode that utilizes Oracle Client libraries. The module provides the Connection class for executing SQL statements via execute() and executeMany(), managing transactions with commit() and rollback(), and handling large result sets using queryStream().

Tokens
178.8K
Snippets
354
Records
809
Agent score
81%

What's inside node-oracledb

  1. Key features of node-oracledb

    main

    The node-oracledb driver provides several high-performance features for Oracle Database applications:

    • SQL & PL/SQL: Execution of standard SQL and PL/SQL statements.
    • SODA: Support for the Oracle Database API for Document Access (SODA) for document-style access.
    • Data Types: Extensive support for Oracle data types, including JSON, VECTOR, large objects (CLOB and BLOB), and binding of complex types like Oracle Database objects and collections.
    • Connection Management: Built-in support for connection pooling.
    • High Availability: Support for Oracle Database High Availability features.
    • Security: Full use of Oracle Network Service infrastructure, including encrypted network traffic.

  2. Understand Connection Handling in node-oracledb

    main

    Connection handling in node-oracledb encompasses several critical areas for managing database interactions:

    • Connection Strings: Methods for specifying database locations, including Easy Connect syntax, Embedded Connect Descriptor strings, and Net Service Names.
    • Concurrency and Parallelism: Managing connections in relation to Node.js Worker Threads and achieving parallelism on individual connections.
    • Connection Pooling: Using pools to manage multiple connections efficiently, including sizing, draining, caching, queuing, monitoring (via Pool Statistics), and pinging.
    • Authentication: Supporting External Authentication, Token-Based Authentication (OAuth 2.0 and IAM), and handling expired passwords.
    • High Availability and Scalability: Connecting to Oracle Real Application Clusters (RAC), using Database Resident Connection Pooling (DRCP), and implementing High Availability features like Fast Application Notification (FAN), Runtime Load Balancing (RLB), and Application Continuity.
    • Cloud and Specialized Environments: Connecting to Oracle Cloud Autonomous Databases (via TLS or Mutual TLS) and Sharded Databases.

  3. Access node-oracledb documentation

    main

    The official documentation for node-oracledb has moved to Read the Docs. For the most up-to-date information regarding installation, architecture, getting started, and the full API manual, visit the following links:

  4. What is Client Result Caching (CRC) and how to use it

    main

    Client Result Caching (CRC) enables client-side caching of SQL query results in client memory. This is highly effective for small, mostly static lookup tables, as it reduces network round-trips and database CPU usage.

    Requirements and Constraints:

    • Mode: Currently supported in Thick mode only.
    • Dependency: Requires statement caching to be enabled (which is the default).
    • Scope: Managed at the application process level by Oracle Client libraries. Pooled connections use it; however, sequences of standalone connections (connect -> execute -> close) will not benefit from it.

    How to enable it in your SQL:

    1. Table Level: Create or alter tables with RESULT_CACHE (MODE FORCE).
    2. Query Level: Use the /*+ result_cache */ hint in your SELECT statements.

  5. What is Pipelining and when to use it

    main

    Pipelining allows an application to send multiple, independent database statements to Oracle Database in a single call. This reduces network latency and client wait time by allowing the database to process a queue of operations sequentially without waiting for the application to receive results for each one before sending the next.

    Key Benefits:

    • Increases application responsiveness and system throughput.
    • Most beneficial when performing many small operations in rapid succession or when network latency to the database is high.

    Important Constraints:

    • Sequential Execution: Pipelined operations are executed sequentially by the database, not concurrently.
    • True Pipelining Requirements: True pipelining (where requests are queued on the server) only occurs when using node-oracledb Thin mode connected to Oracle AI Database 26ai (or later).
    • Thick Mode/Older Versions: In Thick mode or with older database versions, operations are executed sequentially by the client (one finishes before the next is sent), so you do not get the true server-side queuing benefits.
    • Data Isolation: Query results or OUT binds from one operation cannot be passed to subsequent operations within the same pipeline.
    • Unsupported Features: Oracle Database Object types and OpenTelemetry tracing are not supported in pipeline mode.

  6. Understand Thin mode vs Thick mode

    main

    Starting from version 6.0, node-oracledb supports two modes of operation:

    • Thin mode: The default mode. It connects directly to the Oracle Database without requiring any Oracle Client libraries installed on the system. It requires Oracle Database 12.1 or later.
    • Thick mode: Required for certain advanced Oracle Database and Oracle Client features. This mode requires loading optional Oracle Client libraries (such as Oracle Instant Client). It can connect to Oracle Database 11.2 or later, depending on the version of the Oracle Client libraries used (which should be version 19 or later).

  7. Use the DbObject class for Database Objects

    main

    The DbObject class represents an Oracle database object (such as a User-Defined Type).

    Properties:

    • name: The name of the object.
    • schema: The schema of the object.
    • fqn: The Fully Qualified Name of the object.
    • attributes: Access to the object's attributes.
    • isCollection: Boolean indicating if the object is a collection.
    • length: The length of the object.
    • elementType, elementTypeClass, elementTypeName: Metadata regarding the type of elements if the object is a collection.

    Methods:

    • DbObject provides specific methods for handling Collections to manipulate array-like database structures.

  8. Switch between Thin and Thick modes

    main

    node-oracledb can operate in two modes: Thin mode (default) and Thick mode.

    • Thin mode: Connects directly to Oracle Database without requiring Oracle Client libraries. It is the default behavior.
    • Thick mode: Uses Oracle Client libraries to provide additional functionality. To enable Thick mode, you must call oracledb.initOracleClient() in your application before creating any standalone connections or pools.

    Note that only one mode can be active in a single Node.js process.

  9. Configure CQN Quality of Service (QoS) levels

    main

    You can control the granularity of notifications using the qos option in the subscription object:

    1. Object-level (Default): The callback is invoked whenever a transaction is committed that changes an object (table) referenced by the query, regardless of whether the actual result set of the query changes.
    2. Query-level (oracledb.SUBSCR_QOS_QUERY): The database only notifies the application if the transaction changes the actual result set of the registered query. For example, if a query selects rows where key > 100, an insertion of a row with key = 10 will not trigger a notification.
    const options = {
    sql      : `SELECT * FROM mytable WHERE key > 100`,
    callback : myCallback,
    qos      : oracledb.SUBSCR_QOS_QUERY
};

  10. Implement the TraceHandlerBase class for OpenTelemetry tracing

    main

    To implement custom tracing and monitoring using OpenTelemetry, you must extend the TraceHandlerBase class. This class provides abstract methods that are invoked at various stages of the connection and query lifecycle.

    Lifecycle Methods:

    • onEnterFn(traceContext): Invoked before a public method passes the traceContext.
    • onExitFn(traceContext): Invoked after a public method completes.
    • onBeginRoundTrip(traceContext): Called when a round trip begins (starts a new OpenTelemetry span).
    • onEndRoundTrip(traceContext): Called when a round trip ends (ends the span).

    All these methods are synchronous and receive a traceContext object containing connection configuration, call-level details, and additional attributes.

    Note: All methods in TraceHandlerBase are synchronous.

  11. Use token-based authentication with accessToken

    main

    For Microsoft Azure Active Directory OAuth 2.0 or Oracle Cloud Infrastructure (OCI) IAM authentication, use the accessToken property in poolAttrs.

    Requirements for Token-Based Authentication:

    • Set externalAuth to true.
    • Set homogeneous to true.
    • Do not set the password attribute.
    • The user (or username) attribute can optionally be set to a proxy user (e.g., "[proxyuser]").

    accessToken formats:

    1. String: The token itself.
    2. Object: An object with a token attribute (for OAuth 2.0) or token and privateKey attributes (for OCI IAM).
    3. Callback Function: function accessToken(boolean refresh, object accessTokenConfig).
      • When refresh is false, provide a token from cache or generate a new one.
      • When refresh is true, you must externally acquire a new token.

    Note on Thick Mode:

    • For IAM: Oracle Client libraries 19.14+ or 21.5+ are required.
    • For OAuth 2.0: Oracle Client libraries 19.15+ or 21.7+ are required.

  12. Use bind variables for secure and scalable SQL

    main

    Bind variables are placeholders in SQL or PL/SQL statements (prefixed with a colon, e.g., :country_id) used to pass data to and from the Oracle Database.

    Why use them?

    • Security: They mitigate SQL injection because data is never treated as part of the executable statement text.
    • Performance: They allow the database to reuse execution plans and context, reducing parsing costs when executing the same statement multiple times with different values.

    Important Restrictions:

    • No Concatenation: Never use string interpolation or concatenation to insert user data into SQL strings.
    • No DDL: Bind variables cannot be used in DDL statements (e.g., CREATE TABLE, ALTER).
    • No Identifiers: They cannot be used to substitute column or table names.
    • No Direct Substitution: They can only substitute data values.
    const oracledb = require('oracledb');

const result = await connection.execute(
 `INSERT INTO countries VALUES (:country_id, :country_name)`,
 {country_id: 90, country_name: "Tonga"}
);