Is there any way to verify oracle SQL database connection is still alive or not?

[artbindu]

1. Oracle SQL Version: 23.1.1.345

2. Describe the problem:

Recently I'm configuring Oracle SQL Databse with node.js.
I'm trying to verify alive db connection.

This is my db connection sample query:

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

    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 to add objective, my connection is already established(alive) or not.

There should be some this.conn object's key or function which should return a Boolean value for connection alive validation

---

• Related StackOverflow Question: https://stackoverflow.com/questions/78670590/is-there-any-way-to-verify-oracle-sql-database-connection-and-error-handling

[sharadraju]

@artbindu You can use connection.isHealthy() to check the health status of a connection.
You can use connection.ping to see if a connection is currently usable and the network to the database is valid. Note that ping() function requires a round trip to the database.

[sosoba]

Meybe oracledb should allow to enable KeepAlive on network socket underlay to database connection?

[anthony-tuininga]

It does, if you use the expire_time parameter, a reference to which can be found in the documentation.

[artbindu]

Thanks @sharadraju connection.isHealthy() is working and my final code is:

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 :  "********"
            });
            if (this.conn.isHealthy()) {
                console.info("Database connected successfully");
                return true;
            }
        } catch(err) {
            console.error('Database connectivity error: ' + err.message);
            return false;
        }
    }

    static async query(queryStr) {
        console.debug(`Query: ${queryStr}`);
        if (!this.conn || (this.conn && !this.conn.isHealthy())) {
            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.isHealthy()) {
            await this.conn.close().then(() => {
                this.conn = null;
                console.info("Database disconnected successfully");
            });
        }
    }
}

is there any way to handle database error event ?

I don't want to use try..catch method. It's really silly

[artbindu]

expire_time thanks @anthony-tuininga

It's nice configuration, both expire_time & connect_timeout for oracle sql db. I definitely used it later.

I need to understand to use oracle pool configuration?

When I use Mysql server, I used configuration like:

import * as sql from 'mssql';

function createConnection(sqlDbConfig) {
      const conn = new sql.ConnectionPool(sqlDbConfig);
      console.info('DB Connected.');
      await conn.connect();
      
      const pool = await this.conn.request();
      console.debug('Connection pool initialized.');
      
      // Events to handle Errors
       pool.on('error', (err) => {
              logger.debug(`Error connecting DB: ${err}`);
              conn.close()
         });
}

createConnection({
     user: 'user',
    password: '******',
    server: '192.xxx.xxx.xxx',
    database: 'dbName',
    pool: {
      max: 10,
      min: 0,
      idleTimeoutMillis: 30000,
    }
});

Is there any way to configure oracle db this way ?

Actually I want to use oracle db connection pool configuration and need a event to handle error proper way . . .

this is my oracle sql db connection config:

const oracleConfig = {
          connectString: "host:port/schema",
          user     :  "username",
          password :  "********"
          poolMax: 10,
          poolMin: 0,
          poolTimeout: 60
}

[sudarshan12s]

The errors are thrown from the async functions and application needs to catch them. There are no events emitted for error/result. The exceptions are normal in JS else a old callback style might help .

These pool examples might help you to get started for your usecase ?:

https://github.com/oracle/node-oracledb/blob/main/examples/connectionpool.js

https://github.com/oracle/node-oracledb/blob/main/test/pool.js

[sharadraju]

@artbindu There are no events emitted for pool or connection objects as pointed out by @sudarshan12s .

But we will look into the possibility of adding them for a future release.

[artbindu]

Hi, @sudarshan12s
Thank you for sharing valuable information.

I understand how to use connection pool and then connection. Also this is very informative for all configuration of connection pool.

Thank you again

[artbindu]

Hi, @sharadraju
Thank your for your helping information. I hope we will see some error handling events in future . . .

I am closing this ticket as I give all valuable information from yours . . .
Big thank you all !!!