Quick Start: Developing Node.js Applications for Oracle Database

This tutorial shows you how to connect Node.js applications to Oracle Database using the node-oracledb module. This module lets you quickly develop applications that execute SQL or PL/SQL statements. Your applications can also use Oracle's document storage SODA calls. Node-oracledb can be used with TypeScript or directly with Node.js.

The steps below show connecting to an on-premise database, but if you would like to use an Oracle Autonomous Database in Oracle Cloud instead (for example from the Always Free service), then see the Developing Node.js Applications for Oracle Autonomous Database tutorial.

    Node.js node-oracledb on Windows

    Open all Close all

  • 1. Install Oracle Database

    • If you do not already have access to an Oracle Database, then download and install Oracle Database XE following these instructions.

      More resources:

      Video showing how to install Oracle Database XE on Windows and conduct basic administration.

      Oracle Database XE Installation Guide for Windows

    • Alternatively, if you already have a database but it is on a remote computer, then install the Oracle Instant Client "Basic" and "SQL*Plus" packages from here. Remember to install the VS Redistributable and add the directory to your PATH environment variable, as instructed. You will need to know the connect string for the database, and substitute it in the instructions below.

    • Alternatively, if you would like to use an Oracle Autonomous Database in Oracle Cloud instead (for example from the Always Free service), then see the Developing Node.js Applications for Oracle Autonomous Database tutorial.

  • 2. Create a Database User

    We recommend using a new database user for this quick start tutorial.

    Open a command terminal and run the SQL*Plus command line tool, replacing XXXX with the privileged user password that you chose during database installation. If you are not using Oracle Database XE, then also substitute your database's hostname and database service name for "localhost/xepdb1":

    Copied to Clipboard
    Error: Could not Copy
    sqlplus -l system/XXXX@localhost/xepdb1

    At the SQL> prompt, enter the following statements (you can choose a different username, if you like):

    Copied to Clipboard
    Error: Could not Copy
    define USERNAME = demonode create user &USERNAME;
    alter user &USERNAME default tablespace users temporary tablespace temp quota unlimited on users;
    grant create session, create view, create sequence, create procedure, create table, create trigger, create type, create materialized view to &USERNAME;

    Still in SQL*Plus, set a password for your new user. Replace the XXXX with a valid password and run:

    Copied to Clipboard
    Error: Could not Copy
    alter user &USERNAME identified by XXXX;

    Finally, exit SQL*Plus

    Copied to Clipboard
    Error: Could not Copy
    quit
  • 3. Install Node.js

    Install Node.js by downloading the 64-bit MSI package, clicking it, and following the prompts.

    Restart terminal windows, if necessary, so that the new Node.js binary is found.

  • 4. Install node-oracledb

  • 5. Create a Node.js Application

    • Use your editor to create a new Node.js file example.js in the same directory as package.json:

      Copied to Clipboard
      Error: Could not Copy
      const oracledb = require('oracledb');
      async function runApp()
      {
        let connection;
        try {
          connection = await oracledb.getConnection({ user: "demonode", password: "XXXX", connectionString: "localhost/xepdb1" });
          console.log("Successfully connected to Oracle Database");
        
          // Create a table
          await connection.execute(`begin execute immediate 'drop table todoitem'; exception when others then if sqlcode <> -942 then raise; end if; end;`);
          await connection.execute(`create table todoitem ( id number generated always as identity, description varchar2(4000), creation_ts timestamp with time zone default current_timestamp, done number(1,0), primary key (id))`);
        
          // Insert some data
          const sql = `insert into todoitem (description, done) values(:1, :2)`;
          const rows = [ ["Task 1", 0 ], ["Task 2", 0 ], ["Task 3", 1 ], ["Task 4", 0 ], ["Task 5", 1 ] ];
          let result = await connection.executeMany(sql, rows);
          console.log(result.rowsAffected, "Rows Inserted");
          connection.commit();
        
          // Now query the rows back
          result = await connection.execute( `select description, done from todoitem`, [], { resultSet: true, outFormat: oracledb.OUT_FORMAT_OBJECT });
          const rs = result.resultSet; let row;
          while ((row = await rs.getRow())) {
            if (row.DONE)
              console.log(row.DESCRIPTION, "is done");
            else
              console.log(row.DESCRIPTION, "is NOT done");
          }
          await rs.close();
        } catch (err) {
          console.error(err);
        } finally {
          if (connection)
          {
            try {
              await connection.close();
            } catch (err) {
              console.error(err);
            }
          }
        }
      }
      runApp();
    • Modify example.js to use your database connection information in the oracledb.getConnection() call:

      • User: Use the username created by the SQL*Plus script.
      • Password: Use the user's password.
    • Save the changes to example.js.
  • 6. Run the Node.js Application

    In a terminal window, run the app:

    Copied to Clipboard
    Error: Could not Copy
    node example.js

    You should see a message that you connected to the database, five rows were inserted, and the task list with each task's completion status returned to the terminal window. Congratulations! You have successfully queried the Oracle Database.

More information and resources on using node-oracledb are available here.

    Node.js node-oracledb on macOS (Intel x86)

    Open all Close all

  • 1. Install Oracle Database

    If you do not already have access to an Oracle Database, you can easily install one using VirtualBox. (There is no native macOS port of Oracle Database). If you would like to use an Oracle Autonomous Database in Oracle Cloud instead (for example from the Always Free service), then see the Developing Node.js Applications for Oracle Autonomous Database tutorial.

    To install Oracle Database XE, follow the instructions here. In summary:

    • Install Oracle VM VirtualBox for "OS X hosts"

    • Install Vagrant

    • Download and unzip the Oracle Database 18c XE Vagrant configuration ZIP file, or use git to clone the GitHub repository. Open a terminal window and change to the OracleDatabase/18.4.0-XE directory, then run vagrant up

      At the conclusion, the database will be running. The confirmation message will display the password for the privileged accounts.

  • 2. Install the Oracle Instant Client Basic and SQL*Plus Packages

  • 3. Create a Database User

  • 4. Install Node.js

  • 5. Install node-oracledb

  • 6. Create a Node.js Application

  • 7. Run the Node.js Application

    In a terminal window, run the app:
    Copied to Clipboard
    Error: Could not Copy
    node example.js

    You should see a message that you connected to the database, five rows were inserted, and the task list with each task's completion status returned to the terminal window. Congratulations! You have successfully queried the Oracle Database.

More information and resources on using node-oracledb are available here.

    Node.js node-oracledb on Linux

    Open all Close all

  • 1. Install Oracle Database

  • 2. Create a Database User

  • 3. Install Node.js

  • 4. Install node-oracledb

  • 5. Create a Node.js Application

  • 6. Run the Node.js Application

More information and resources on using node-oracledb are available here.

Oracle Chatbot
Disconnected