Introduction

Node.js on Oracle and TimesTen

This blog shows you how to use Node.js with both the Oracle Database and Oracle TimesTen In-Memory Database.

The same Node.js source code will work against either Oracle Database 19c or TimesTen Cache.

 

This blog covers the following topics:

  • Using the Oracle TimesTen Cache LiveLab
  • Installing Node.js 16 in the LiveLab
  • Installing the node-oracledb driver
  • Connecting to either Oracle Database 19c or TimesTen Cache
  • Simple queries returning resultsets
  • Dropping tables via a PLSQL block
  • Creating tables using SQL
  • Inserting, updating and deleting rows using SQL
  • Committing ACID transactions
  • How Node.js programs can work the same with both Oracle Database 19c and TimesTen Cache

 

 

 

 

Using the Oracle TimesTen Cache LiveLab

The TimesTen Cache LiveLab is a VM which runs on Oracle Cloud.  The VM uses Docker containers to run the Oracle Database 19c and TimesTen 22.1 Cache.

Docker constainer in LiveLab VM

The Oracle TimesTen Cache LiveLab is here.

You need to have completed Labs 1 to 13 before you can start using Node.js with TimesTen.

 

 

 

node-oracledb driver and Node.js versions

Node OracleDB and TimesTen versions

 

The node-oracledb driver works with both the Oracle Database and Oracle TimesTen.

node-oracledb versions

The LiveLab for TimesTen Cache uses Oracle Linux 8.6 and has Node.js 10.24 installed by default and I was able to get it working with the node-oracledb 5.4 driver.

This blog will use Node.js 16.16 and the oracle-nodedb 5.5 driver.

 

 

 

Installing Node.js 16 in your LiveLab VM container

node.js 16.16

To install Node.js 16 in your TimesTen Cache LiveLab VM.

First ssh to your LiveLab VM:

ssh oracle@your_livelab_public_ip_address

 

Then ssh to the tthost1 Docker container in the LiveLabs VM:

ssh tthost1

 

Reset the existing module stream for Node.js

sudo dnf module reset nodejs

 

Install Node.js 16 in the container:

sudo dnf module install nodejs:16

 

This installed Node.js 16.16 in my LiveLab container.

 

 

 

Install the node-oracledb driver

Node Oracle DB driver

Install the node-oracledb driver from the npm package manager:

npm install oracledb

 

This installed node-oracledb 5.5 in my LiveLab container.

 

 

A Node.js program that works with Oracle 19c and TimesTen 18c

Create a trivial Node.js program using the node-oracledb driver that works with both Oracle Database 19c and Oracle TimesTen.

The test.js program does the following:

  • Connects as appuser/appuser@$SVC_NAME
  • Determines the version of the node-oracledb driver
  • Determines the version of the Oracle Client
  • Determines the version of the database server
  • Queries a set of rows via a SQL where clause
  • Automatically disconnects

// test.js

const oracledb = require(“oracledb”);


oracledb.initOracleClient();

async function run() {
      let connection;

      // Get the TNS service name from the $SVC_NAME environment variable
      const svcName = process.env.SVC_NAME;

      try {
            connection = await oracledb.getConnection({
                  user: “appuser”,
                  password: “appuser”,
                  connectionString: svcName,
            });

            console.log(“\nnode-oracledb driver version is ” + oracledb.versionString);

            console.log(“\nOracle client version is ” + oracledb.oracleClientVersionString + “\n”);

            result = await connection.execute(
                  “select * from v$version”,
                  [], {
                  resultSet: true,
                  outFormat: oracledb.OUT_FORMAT_OBJECT
                  }
            );

            rs = result.resultSet;

            let row;
            while ((row = await rs.getRow())) {
                  console.log(row);
            }
            await rs.close();

 

            result = await connection.execute(
                  “select * from vpn_users where vpn_id = 0 and vpn_nb < 5 and rownum < 3 order by 2”,
                  [], {
                  resultSet: true,
                  outFormat: oracledb.OUT_FORMAT_OBJECT
                  }
            );

            rs = result.resultSet;


            while ((row = await rs.getRow())) {
                  console.log(row);
            }
            await rs.close();

      } catch (err) {

            console.error(err);

      } finally {

            if (connection) {
                  try {
                  await connection.close();
                  } catch (err) {
                  console.error(err);
                  }
            }

          console.log(“\nBye”);
      }
}

run();

The TimesTen Cache LiveLab already has the appuser schema and the VPN_USERS tables with data in it.

TimesTen needs to use the Node.js thick client via calling oracledb.initOracleClient().

 

 

Use Oracle Net Service names to connect

There are multiple ways to connect with the node-oracledb driver.  This example uses Oracle Net service names via tnsnames.ora:

ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB1)
    )
  )

SAMPLEDB =
  (DESCRIPTION =
    (CONNECT_DATA =
      (SERVER = timesten_direct)
      (SERVICE_NAME = sampledb)
    )
  )

SAMPLEDBCS =
  (DESCRIPTION =
    (CONNECT_DATA =
      (SERVER = timesten_client)
      (SERVICE_NAME = sampledbcs)
    )
  )

 

This tnsnames.ora file provides three services to connect to:

TNS service names

  • TimesTen client/server connections use TCP/IP sockets [and optionally TLS]. The TimesTen client can be on a different machine than the TimesTen server
  • TimesTen direct linked connections use shared memory
    • Direct linked connections are about 10x faster than TCP sockets
    • The application and TimesTen MUST to be on the same machine.
  • The TimesTen service name [direct linked or client/server] in tnsnames.or MUST point to a working TimesTen DSN in sys.odbc.ini

 

The TimesTen Cache LiveLab configures and uses $TNS_ADMIN/tnsnames.ora to enable connections to either the Oracle Database 19c or Oracle TimesTen.

 

 

 

Choose which service name to connect to

The test.js Node.js program uses an environment variable [$SVC_NAME] to define the service name to connect to.

You need to define the value of $SVC_NAME to choose which of the three services to connect to.

 

To set the SVC_NAME in the Linux bash shell to point ot Oracle Database 19c:

export SVC_NAME=ORCLPDB1

 

To set the SVC_NAME in the Linux bash shell to point to TimesTen with a direct linked connection:

export SVC_NAME=sampledb

 

To set the SVC_NAME in the Linux bash shell to point to a TimesTen client/server connection:

export SVC_NAME=sampledbcs

 

 

 

Use Node.js to query Oracle Database 19c or TimesTen

Run test.js against Oracle Database 19c:

export SVC_NAME=ORCLPDB1
node test.js

 

The output from test.js when connected to Oracle Database 19c:

[oracle@tthost1 js]$ export SVC_NAME=ORCLPDB1
[oracle@tthost1 js]$ node test.js

node-oracledb driver version is 5.5.0

Oracle client version is 19.14.0.0.0

Database Server version is
{
  BANNER: ‘Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production’,
  BANNER_FULL: ‘Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production\n’ +
    ‘Version 19.3.0.0.0’,
  BANNER_LEGACY: ‘Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production’,
  CON_ID: 0
}

Some data from APPUSER.VPN_TABLES is
{
  VPN_ID: 0,
  VPN_NB: 0,
  DIRECTORY_NB: ‘5500      ‘,
  LAST_CALLING_PARTY: ‘000000000 ‘,
  DESCR: ‘<placeholderfordescriptionofVPN0extension0>                                                         ‘
}
{
  VPN_ID: 0,
  VPN_NB: 1,
  DIRECTORY_NB: ‘5501      ‘,
  LAST_CALLING_PARTY: ‘000000000 ‘,
  DESCR: ‘<placeholderfordescriptionofVPN0extension1>                                                         ‘
}

Bye
[oracle@tthost1 js]$

 

The TimesTen Cache LiveLab uses:

  • Oracle Instant Client 19c which ships with TimesTen 22.1.  This is why the Client version output is 19.14
  • Oracle Database 19c which is why the Server version output is 19.3
  • Pre-loaded data which exists in the APPUSER.VPN_USERS table

 

Run test.js against TimesTen Cache:

export SVC_NAME=sampledb
node test.js

 

The output of test.js when connected to Oracle TimesTen Cache:

[oracle@tthost1 js]$ export SVC_NAME=sampledb
[oracle@tthost1 js]$ node test.js

node-oracledb driver version is 5.5.0

Oracle client version is 19.14.0.0.0

Database Server version is
{
  MAJOR: 22,
  MINOR: 1,
  PATCHSET: 1,
  PATCH: 3,
  RESERVED: 0,
  ELEMENTID: 1
}

Some data from APPUSER.VPN_TABLES is
{
  VPN_ID: 0,
  VPN_NB: 0,
  DIRECTORY_NB: ‘5500      ‘,
  LAST_CALLING_PARTY: ‘000000000 ‘,
  DESCR: ‘<placeholderfordescriptionofVPN0extension0>                                                         ‘
}
{
  VPN_ID: 0,
  VPN_NB: 1,
  DIRECTORY_NB: ‘5501      ‘,
  LAST_CALLING_PARTY: ‘000000000 ‘,
  DESCR: ‘<placeholderfordescriptionofVPN0extension1>                                                         ‘
}

Bye
[oracle@tthost1 js]$

The TimesTen Cache LiveLab uses:

  • Oracle Instant Client 19c which ships with TimesTen 22.1.  This is why the Client version output is 19.14
  • TimesTen 22.1 which is why the Server version output is 22.1.1.3
  • Pre-loaded data which exists in the APPUSER.VPN_USERS table

 

Apart from the Database server version, the output is the same for both Oracle Database 19c and TimesTen Cache:

  • The Server Version output is supposed to be different for Oracle and TimesTen
  • The output is the same for both TimesTen Direct Linked and Client/Server connections

 

 

 

A Node.js application to modify Oracle Database 19c or TimesTen

The test2.js program does the following:

  • Connects as appuser/appuser@$SVC_NAME
  • Determines the version of the database server
  • Drops table T via a PLSQL block
  • Creates table T via SQL
  • Does a batch insert of three rows into table T
  • Updates all of the rows in table T
  • Deletes a row by primary key in table T
  • Commits the transaction
  • Queries the rows in table T after the inserts, updates and deletes
  • Automatically disconnects

 

// test2.js

const oracledb = require(“oracledb”);

oracledb.initOracleClient();

async function run() {
      let connection;

      // Get the TNS service name from the $SVC_NAME environment variable
      const svcName = process.env.SVC_NAME;

      try {
            connection = await oracledb.getConnection({
                  user: “appuser”,
                  password: “appuser”,
                  connectionString: svcName,
            });

            console.log(“\nDatabase Server version is “);
            result = await connection.execute(
                  “select * from v$version”,
                  [], {
                  resultSet: true,
                  outFormat: oracledb.OUT_FORMAT_OBJECT
                  }
            );

            rs = result.resultSet;

            let row;
            while ((row = await rs.getRow())) {
                  console.log(row);
            }
            await rs.close();

            // Use a PLSQL block to drop a table
            result = await connection.execute(
            `begin
               execute immediate ‘drop table t’;
             exception when others
               then null;
             end;`
            );

            // Create a table to use for insert, update, delete

            result = await connection.execute(
            `create table t (
             id     number not null primary key,
             addr   varchar2(64),
             price  number(6,2)
            )`
            );

            // Do a batch insert
            const sql = `insert into t values (:id, :addr, :price)`;

            const binds = [
              { id: 1, addr: “addr 1”, price: 10.12 },
              { id: 2, addr: “addr 2”, price: 42.69 },
              { id: 3, addr: “addr 3”, price: 1078.00 }
            ];

            const options = {
              autoCommit: true,
              bindDefs: {
              id: { type: oracledb.NUMBER },
              addr: { type: oracledb.STRING, maxSize: 64 },
              price: { type: oracledb.NUMBER }
              }
            };

            const result2 = await connection.executeMany(sql, binds, options);
            console.log(“Rows inserted ” + result2.rowsAffected);

            console.log(“\nRows inserted into table t”);

            result = await connection.execute(
                  “select * from t”,
                  [], {
                  resultSet: true,
                  outFormat: oracledb.OUT_FORMAT_OBJECT
                  }
            );

            rs = result.resultSet;

            while ((row = await rs.getRow())) {
                  console.log(row);
            }
            await rs.close();

            // Update some rows
            result = await connection.execute(
            `update t set addr = ‘Unknown’`
            );

            console.log(“\nRows updated ” + result.rowsAffected);
            result = await connection.execute(
                  “select * from t”,
                  [], {
                  resultSet: true,
                  outFormat: oracledb.OUT_FORMAT_OBJECT
                  }
            );

            rs = result.resultSet;

            while ((row = await rs.getRow())) {
                  console.log(row);
            }
            await rs.close();

            // Delete some rows

            result = await connection.execute(
            `delete from t where id = 1`
            );

            console.log(“\nRows Deleted ” + result.rowsAffected);
            result = await connection.execute(
                  “select * from t”,
                  [], {
                  resultSet: true,
                  outFormat: oracledb.OUT_FORMAT_OBJECT
                  }
            );

            rs = result.resultSet;

            while ((row = await rs.getRow())) {
                  console.log(row);
            }
            await rs.close();

            // Commit the changes
            connection.commit();

      } catch (err) {

            console.error(err);

      } finally {

            if (connection) {
                  try {
                  await connection.close();
                  } catch (err) {
                  console.error(err);
                  }
            }

            console.log(“\nBye”);
      }
}

run();

 

Run test2.js against Oracle Database 19c:

export SVC_NAME=ORCLPDB1
node test2.js

 

The output of test2.js when connected to Oracle Database 19c:

[oracle@tthost1 js]$ export SVC_NAME=ORCLPDB1
[oracle@tthost1 js]$ node test2.js

Database Server version is
{
  BANNER: ‘Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production’,
  BANNER_FULL: ‘Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production\n’ +
    ‘Version 19.3.0.0.0’,
  BANNER_LEGACY: ‘Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production’,
  CON_ID: 0
}
Rows inserted 3

Rows inserted into table t
{ ID: 1, ADDR: ‘addr 1’, PRICE: 10.120000000000001 }
{ ID: 2, ADDR: ‘addr 2’, PRICE: 42.69 }
{ ID: 3, ADDR: ‘addr 3’, PRICE: 1078 }

Rows updated 3
{ ID: 1, ADDR: ‘Unknown’, PRICE: 10.120000000000001 }
{ ID: 2, ADDR: ‘Unknown’, PRICE: 42.69 }
{ ID: 3, ADDR: ‘Unknown’, PRICE: 1078 }

Rows Deleted 1
{ ID: 2, ADDR: ‘Unknown’, PRICE: 42.69 }
{ ID: 3, ADDR: ‘Unknown’, PRICE: 1078 }

Bye
[oracle@tthost1 js]$

 

Run test2.js against TimesTen Cache:

export SVC_NAME=SAMPLEDB
node test2.js

 

The output of test2.js when connected to TimesTen Cache:

[oracle@tthost1 js]$ export SVC_NAME=sampledb
[oracle@tthost1 js]$ node test2.js

Database Server version is
{
  MAJOR: 22,
  MINOR: 1,
  PATCHSET: 1,
  PATCH: 3,
  RESERVED: 0,
  ELEMENTID: 1
}
Rows inserted 3

Rows inserted into table t
{ ID: 1, ADDR: ‘addr 1’, PRICE: 10.120000000000001 }
{ ID: 2, ADDR: ‘addr 2’, PRICE: 42.69 }
{ ID: 3, ADDR: ‘addr 3’, PRICE: 1078 }

Rows updated 3
{ ID: 1, ADDR: ‘Unknown’, PRICE: 10.120000000000001 }
{ ID: 2, ADDR: ‘Unknown’, PRICE: 42.69 }
{ ID: 3, ADDR: ‘Unknown’, PRICE: 1078 }

Rows Deleted 1
{ ID: 2, ADDR: ‘Unknown’, PRICE: 42.69 }
{ ID: 3, ADDR: ‘Unknown’, PRICE: 1078 }

Bye
[oracle@tthost1 js]$

 

Apart from the Database server version, the output is the same for both Oracle Database 19c and TimesTen Cache:

  • The Database server version output is supposed to be different for Oracle and TimesTen
  • The output is the same for both TimesTen Direct Linked and Client/Server connections

 

 

 

Summary

  • The LiveLab for Oracle TimesTen Cache runs on Oracle Cloud
  • You use the node-oracledb driver with the Oracle Instant Client from TimesTen
  • The same Node.js program can be used with Oracle 19c or TimesTen Cache
  • Connect to the database via a service name in tnsnames.ora
  • Part 2 will cover more details

 

 

Learn more SQL Language APIs

 

 

Learn more about TimesTen XE:

 

 

More TimesTen XE Blogs

 

 

Disclaimer: These are my personal thoughts and do not represent Oracle’s official viewpoint in any way, shape, or form.