Node.js node-oracledb on Windows

Open all Close all

This tutorial shows you how use the Node.js node-oracledb interface on Windows to connect applications to Oracle Autonomous Database (ADB). Follow the steps below.

• 1. Provision an ADB Instance

  Click on the links below to walk through these steps if you have not yet provisioned an ADB instance. Remember the ADMIN password. You will use this to connect Node.js to ADB.

  • Create an Oracle cloud account.
  • Provision an ADB instance.

  You may select the Always Free option to use the Always Free Oracle ADB. Choose the Shared Infrastructure deployment type.

• 2. Obtain Client Credentials

  Below are the instructions to download the client credentials from the Oracle Cloud Console. The credentials give mutual TLS, providing enhanced security for authentication and encryption.

  • From the Oracle Cloud Console, go to the Autonomous Database Details page of your Oracle Autonomous Database instance.
  • Click the DB Connection button.
  

  • A new window will appear. Click the Download Wallet button.

  

  • Enter a wallet password in the Password field and confirm the password in the Confirm password field. Then, click the Download button. The password must be at least 8 characters long and include at least 1 letter and either 1 numeric character or 1 special character. Although not required for Node.js, this password protects the downloaded client credentials wallet.

  • Save the credentials zip file to a secure location.

• 3. Install the Oracle Instant Client Basic Package

  Download the free Oracle Instant Client Basic zip file from Oracle Instant Client for Microsoft Windows (x64) 64-bit. (If your Node.js is 32-bit, then you will need to download the 32-bit Basic package from here instead). Remember to install the matching VS Redistributable, as shown on the download page.

  Extract the libraries to an accessible directory, for example the libraries could be in C:\oracle\instantclient_19_11

• 4. Unzip the Wallet

  Make a network\admin sub-directory in your Instant Client directory, for example C:\oracle\instantclient_19_11\network\admin.

  Unzip the previously obtained credentials zip file and move the extracted files to the new network\admin sub-directory. Wallet files including network\admin\tnsnames.ora should exist.

• 5. Install Node.js

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

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

• 6. Install node-oracledb

  Using your favorite editor, create a new file package.json in a directory of your choice. It should contain:

  Copy

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

  Install node-oracledb:

  Copy

  npm install

  For further assistance and options, such as for installing behind an HTTP proxy, see Node-oracledb Installation on Microsoft Windows.

• 7. 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

    const oracledb = require('oracledb');
    
    
    oracledb.initOracleClient({ libDir: 'C:\\oracle\\instantclient_19_11' });
    
    async function run() {
    
      let connection;
    
      try {
    
        connection = await oracledb.getConnection({ user: "admin", password: "XXXX", connectionString: "XXX_high" });
    
        // Create a table
    
        await connection.execute(`begin
                                    execute immediate 'drop table nodetab';
                                    exception when others then if sqlcode <> -942 then raise; end if;
                                  end;`);
    
        await connection.execute(`create table nodetab (id number, data varchar2(20))`);
    
        // Insert some rows
    
        const sql = `INSERT INTO nodetab VALUES (:1, :2)`;
    
        const binds =
          [ [1, "First" ],
            [2, "Second" ],
            [3, "Third" ],
            [4, "Fourth" ],
            [5, "Fifth" ],
            [6, "Sixth" ],
            [7, "Seventh" ] ];
    
        await connection.executeMany(sql, binds);
    
        // connection.commit();     // uncomment to make data persistent
    
        // Now query the rows back
    
        const result = await connection.execute(`SELECT * FROM nodetab`);
    
        console.dir(result.rows, { depth: null });
    
      } catch (err) {
        console.error(err);
      } finally {
        if (connection) {
          try {
            await connection.close();
          } catch (err) {
            console.error(err);
          }
        }
      }
    }
    
    run();

  • Modify example.js and set the appropriate path to your Instant Client directory in the initOracleClient() call.
  • Modify example.js to use your ADB connection information in the oracledb.getConnection() call:
    • User: Use admin which is the user created by default during the creation of Autonomous Database. (If you created another ADB user, you can use that user instead.)
    • Password: Use the database user's password. If connecting as the admin user, set this to the password you chose during the Create Autonomous Database step while provisioning Autonomous Database.
    • Connection String: Use a net service name such as DBName_high. DBName is the Database Name entered during the Create Autonomous Database step while provisioning Autonomous Database. The available net service names can be seen in the wallet tnsnames.ora file.
  • Save the changes to example.js.

• 8. Run the Node.js Application

  Run the app:

  Copy

  node example.js

  You will see the queried rows returned from the database. Congratulations! You have successfully used Oracle Autonomous Database.

  Note: If you connect to ADB from behind a firewall, you will likely encounter a connection timeout error. Update the tnsnames.ora file to use an HTTP proxy. Learn how to do this in this ADB documentation section. Scroll down to the "Connections with an HTTP Proxy" section on the doc page.

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