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.
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.
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.
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":
sqlplus -l system/XXXX@localhost/xepdb1
At the SQL> prompt, enter the following statements (you can choose a different username, if you like):
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:
alter user &USERNAME identified by XXXX;
Finally, exit SQL*Plus
quit
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.
Use your editor to create a new Node.js file example.js in the same directory as package.json:
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:
In a terminal window, run the app:
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.
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.
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.
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.
More information and resources on using node-oracledb are available here.