TypeError: Cannot read property 'execute' of undefined . node.js how to export oracle db connection

Question

Hi I am new to node and oracle.I have created a app and made a successfull connection to db. I need to use connection object across the application how can i do that?

Below is my index.js file

const express = require("express");
const app = express();

const authRoute = require("./routes/auth");
app.use(express.json());
app.use("/api",authRoute) ;

app.listen(3000,function(){
    console.log("Node Server : Running on port 3000...");
})

database connection file => connect.js

const oracledb = require('oracledb');
const dotenv = require('dotenv');
dotenv.config();

const connection = oracledb.getConnection(
    {
      user          : process.env.USER,
      password      : process.env.PASS,
      connectString : process.env.ConnectString
    },
    function(err, connection)
    {
        if (err) {
        console.error(err.message);
        return;
    }
    console.log('Connection was successful!');
    connection.close(function(err){
        if (err) {
        console.error(err.message);
        return;
        }
    });
});
module.exports = connection;

I want to use this db connection in my auth.js file

const router = require('express').Router();
const db = require('../database/connect');


router.post("/authenticate",function(req,res){

    //console.log(req);
    const user = req.body.username;
    const username = {"name" : user};
    const pass = req.body.key;
    const password  = {"pass" : pass};
    //const result = db.execute('select * from usertable');// this doesn't work
    //console.log(result.rows);
    res.send('success');
});

module.exports = router;

when i run const result = db.execute('select * from usertable'); I get the error below.

TypeError: Cannot read property 'execute' of undefined

What am i doing wrong.Can anyone please help.Thanks in advance

Answer 1

I had faced this problem. You must install Oracle install client v 19 in your machine. You have to go to web install oracle instant client base on your machine.

Answer 2

(Update: there is a multi-part series with code showing what you want at https://github.com/oracle/oracle-db-examples/tree/main/javascript/rest-api)

Use a connection pool that is opened at app start. Then the pool cache can be used to get the pool (and then connections) in other modules.

For a web app like yours you definitely want to use a connection pool for performance.

There's a big section on connection pooling in the documentation. E.g see Connection Pool Cache which says:

When pools are created, they can be given a named alias. The alias can later be used to retrieve the related pool object for use. This facilitates sharing pools across modules and simplifies getting connections.

The examples are worth reviewing.