How to connect an Oracle Cloud database to a NodeJS backend on Oracle Linux 8 using wallet

Databases are essential for many practical software applications as they allow for the persistence, analysis, and manipulation of data. Connecting applications to databases is a common requirement for developers, and this functionality is typically supported by various programming languages.

One such language is NodeJS, which provides the oracledb Node package for establishing connections between Node-based applications and an Oracle database.

To begin connecting to a cloud database, you will need a deployed Oracle database, which you can access its cloud wallet from. See Oracle’s Autonomous Databases.

Software Versions

This tutorial assumes Oracle Linux 8 and Oracle Autonomous Database 21c. We will use Node v18 and oracledb 5.1.0. This tutorial is accurate as of March 2023.

TL;DR



sudo dnf update -y
sudo dnf install -y zip oracle-instantclient-release-el8
sudo dnf install -y oracle-instantclient-basic
sudo cp Wallet*.zip /usr/lib/oracle/21/client64/lib/network/admin/
sudo rm /usr/lib/oracle/21/client64/lib/network/admin/README
sudo unzip -o /usr/lib/oracle/21/client64/lib/network/admin/Wallet*.zip -d /usr/lib/oracle/21/client64/lib/network/admin

How to Get Started

We recommend having all your packages updated first:



sudo yum update -y && sudo yum upgrade -y

2. Make sure to have these Linux packages installed:



sudo yum install nodejs
sudo yum install wget
sudo yum install glibc

3. Download and install the Oracle Instant Client for Linux x86–64 Version 21.9 Basic Package (OL8 RPM) using the following Shell code. Detailed installation instructions can also be found at the bottom of the linked webpage:



wget https://download.oracle.com/otn_software/linux/instantclient/219000/oracle-instantclient-basic-21.9.0.0.0-1.el8.x86_64.rpm
sudo yum install oracle-instantclient-basic-21.9.0.0.0-1.el8.x86_64.rpm

Alternatively, you can install an available dnf package using:



sudo dnf install -y zip oracle-instantclient-release-el8
sudo dnf install -y oracle-instantclient-basic

4. Update your PATH environment:



export PATH=/usr/lib/oracle/21/client64/bin:$PATH

5. Provision an Autonomous Database instance on Oracle Cloud and download the Wallet

Screenshot of Oracle Cloud console leading to download of the database wallet

6. Unzip the wallet:



sudo cp Wallet_*.zip /usr/lib/oracle/21/client64/lib/network/admin/
sudo sh -c 'cd /usr/lib/oracle/21/client64/lib/network/admin/ && unzip -B Wallet_*.zip'

Wallet files including /usr/lib/oracle/21/client64/lib/network/admin/tnsnames.ora should exist.

7. Create these 2 files in the same directory:

package.json:



{
  "name": "Demo",
  "version": "1.0.0",
  "private": true,
  "description": "Demo app",
  "keywords": [
    "myapp"
  ],
  "dependencies": {
    "oracledb": "^5.1.0"
  },
  "author": "You",
  "license": "MIT"
}

example.js:



const oracledb = require("oracledb");

async function run() {
  let connection;

  try {
    connection = await oracledb.getConnection({
      user: "myuser",   // TODO CHANGE THIS 
      password: "mypassword",    // TODO CHANGE THIS 
      connectionString: "db_high",    // TODO CHANGE THIS 
    });

    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);
      }
    }
  }
}

run();

Update the user and password accordingly. And change the connectionString to the equivalent value found in /usr/lib/oracle/21/client64/lib/network/admin/tnsnames.ora.

8. Install and run the application:



npm install
node example.js

And there you go! If you see some logs and don’t see any errors, that means the connection was made!

References

Safe harbor statement
The information provided on this channel/article/story is solely intended for informational purposes and cannot be used as a part of any contractual agreement. The content does not guarantee the delivery of any material, code, or functionality, and should not be the sole basis for making purchasing decisions. The postings on this site are my own and do not necessarily reflect the views or work of Oracle or Mythics, LLC.

This work is licensed under a Creative Commons Attribution 4.0 International License.

Connecting OracleDB with NodeJS on Oracle Linux 8

Software Versions

TL;DR

How to Get Started

References