Developing Node.js Applications for Oracle Database

Cloud Account Sign in to Cloud Sign Up for Free Cloud Tier

Oracle Account

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.

Windows

macOS

Linux

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":
Copy

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):
Copy

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:
Copy

Copied to Clipboard
Error: Could not Copy
```
alter user &USERNAME identified by XXXX;
```
Finally, exit SQL*Plus
Copy

Copied to Clipboard
Error: Could not Copy
```
quit
```
3. Install Node.js
4. Install node-oracledb
Using your favorite editor, create a new file package.json in a directory of your choice. It should contain:
Copy

Copied to Clipboard
Error: Could not Copy
```
{
  "name": "Demo",
  "version": "1.0.0",
  "private": true,
  "description": "Demo app",
  "keywords": [
    "myapp"
  ],
  "author": "You",
  "license": "MIT",
  "dependencies": {
    "oracledb": "^6.0"
  }
}
```
Install node-oracledb:
Copy

Copied to Clipboard
Error: Could not Copy
```
npm install
```
For further assistance and options, such as for installing behind an HTTP proxy, see Node-oracledb Installation on Microsoft Windows.

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:

Copy

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:
Copy
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
We recommend using a new database user for this quick start tutorial.

Open a terminal window and add the Instant Client directory to your path:
Copy

Copied to Clipboard
Error: Could not Copy
```
export PATH=$HOME/Downloads/instantclient_19_8:$PATH
```
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":
Copy

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):
Copy

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:
Copy

Copied to Clipboard
Error: Could not Copy
```
alter user &USERNAME identified by XXXX;
```
Finally, exit SQL*Plus
Copy

Copied to Clipboard
Error: Could not Copy
```
quit
```
4. Install Node.js
5. Install node-oracledb
Using your favorite editor, create a new file package.json in a directory of your choice. It should contain:
Copy

Copied to Clipboard
Error: Could not Copy
```
{
  "name": "Demo",
  "version": "1.0.0",
  "private": true,
  "description": "Demo app",
  "keywords": [
    "myapp"
  ],
  "author": "You",
  "license": "MIT",
  "dependencies": {
  "oracledb": "^6.0"
  }
}
```
Install node-oracledb:
Copy

Copied to Clipboard
Error: Could not Copy
```
npm install
```
For further assistance and options, such as for installing behind an HTTP proxy, see Node-oracledb Installation on Apple macOS.

6. Create a Node.js Application

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

Copy

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 and set the appropriate path to your Instant Client directory in the init_oracle_client() call.
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.

7. Run the Node.js Application
In a terminal window, run the app:
Copy
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
- If you do not already have access to an Oracle Database, then download and install Oracle Database XE following these instructions.
  
  More resources: Oracle Database XE Installation Guide for Linux
- 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 following the installation instructions. If you install Instant Client from ZIP files, then make sure to use ldconfig or set LD_LIBRARY_PATH as shown.
  
  More resources: Videos
- 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.

In a terminal window, set the environment for accessing Oracle Database:
Copy

Copied to Clipboard
Error: Could not Copy
```
export ORACLE_SID=XE export ORAENV_ASK=NO source /opt/oracle/product/18c/dbhomeXE/bin/oraenv
```
These commands can be added to application or shell configuration files so the environment is automatically configured in future. You do not need to run them if you installed Instant Client.

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":
Copy

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):
Copy

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:
Copy

Copied to Clipboard
Error: Could not Copy
```
alter user &USERNAME identified by XXXX;
```
Finally, exit SQL*Plus
Copy

Copied to Clipboard
Error: Could not Copy
```
quit
```
3. Install Node.js
Install Node.js. For example, on Oracle Linux 7:
Copy

Copied to Clipboard
Error: Could not Copy
```
sudo yum install oracle-nodejs-release-el7 sudo yum install nodejs
```
For generic installation steps, see Node.js Downloads.
4. Install node-oracledb
Using your favorite editor, create a new file package.json in a directory of your choice. It should contain:
Copy

Copied to Clipboard
Error: Could not Copy
```
{
  "name": "Demo",
  "version": "1.0.0",
  "private": true,
  "description": "Demo app",
  "keywords": [
    "myapp"
  ],
  "author": "You",
  "license": "MIT",
  "dependencies": {
  "oracledb": "^6.0"
  }
}
```
Install node-oracledb:
Copy

Copied to Clipboard
Error: Could not Copy
```
npm install
```
For further assistance and options, such as for installing behind an HTTP proxy, see Node-oracledb Installation on Linux.

5. Create a Node.js Application

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

Copy

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
Open a terminal window. If you didn't globally set the Oracle environment, then set it again. This lets node-oracledb find the necessary Oracle client libraries that are included with the database software. You do not need to execute these if you installed Instant Client:
Copy

Copied to Clipboard
Error: Could not Copy
```
export ORACLE_SID=XE export ORAENV_ASK=NO source /opt/oracle/product/18c/dbhomeXE/bin/oraenv
```
In a terminal window, run the app:
Copy
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.

Quick Start: Developing Node.js Applications for Oracle Database

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

1. Install Oracle Database

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

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