node.jsnode-oracledb

Is there any way to verify oracle SQL database connection and error handling?


Recently I'm configuring Oracle SQL Databse with node.js.
I'm able to established DB connection successfully and able to execute query.

My db connection sample query is:

import * as oracledb from 'oracledb';

export class OracleSQL {
    private static conn;

    static async connect() {
        this.conn = await oracledb.getConnection({
            connectString: "host:port/schema",
            user     :  "username",
            password :  "********"
        });
        // TODO: add some condition to verify database connected successfully
        // if (connection is established) {
            console.log('Database connected successfully');
            return true;
        // }
        
        // TODO: if connection not established for any reason and throwing error
        //       How to handle this error ? 
        //       is that a simple "Try...Catch" method or some other way ?
    }

    static async query(queryStr) {
        // TODO: add some condition to verify connection is still alive or not
        if (!this.conn) {
            let res = await this.connect()
            if (res) {
                return await this.conn.execute(queryStr);
            }
        } else {
            return await this.conn.execute(queryStr);
        }
    }

    static async close() {
        // TODO: add some condition to verify connection already established or not
        // if (connection is established) {
                await this.conn.close()
                .then(() => {
                    console.log('Database disconnected successfully');
                    this.conn = null;
                    return true;
                });
        // }
    }
}

I want add two objective here:

  1. How to check my connection is established(alive) or not ?
  2. How handle error ? Is there any db event handler ?

I have cheeked the Node.js Oracle Connection, npm oracledb documents. But did not found any valuable information.


After lots of research I found,
Oracle DB connection object have a property, conn._impl.nscon.connected, which contains boolean value of connection establishment.

import * as oracledb from 'oracledb';

const conn = await oracledb.getConnection({
                               connectString: "host:port/schema",
                               user     :  "username",
                               password :  "********"
                          });
if ( conn._impl.nscon.connected ) {
    console.log('Database connected successfully');
}

Is that a valid way to verify connection is established or alive status ?
I'm not sure. Have you any idea ?


Solution

  • I would recommend to use try/catch method to verify the connection establishment and for error handling

    import * as oracledb from 'oracledb';
    
    export class OracleSQL {
        private static conn;
    
        static async connect() {
            try{
                this.conn = await oracledb.getConnection({
                    connectString: "host:port/schema",
                    user     :  "username",
                    password :  "********"
                });
                console.log('Database connected successfully');
                return true;
            }catch(e){
                console.log('Database connection failed, Reason:',e.message);
                return false;
            }
        }
    
        static async query(queryStr) {
            if (!this.conn) {
                let res = await this.connect()
                if (res) {
                    return await this.conn.execute(queryStr);
                }
            } else {
                return await this.conn.execute(queryStr);
            }
        }
    
        static async close() {
            if (this.conn) {
                    try{
                        await this.conn.close()
                        console.log('Database disconnected successfully');
                        this.conn = null;
                        return true;
                    }catch(e){
                        console.log("Database disconnection failed, Reason", e.message);
                    }
            }
        }
    }