Managing requests from Front-End with node-oracledb in backend

Question

I am writing a new method that connects to the database, runs SQL queries and closes that connection every time a request is made from front-end. Connection to database is established and closed again and again for every query that I want to run. Does this impact the performance? Is this the right way or there is a better way to do this?

app.post('/register', (req, res) =>{
  registerDb().then(resp => {
    res.json({"message" : resp})})
    .catch(err => {
      console.log(err);
    })
})

app.post('/signin', (req, res) => {
  checkAuth(req.body).then(response => {
    res.send(response);
  })
  .catch(err => {
    console.log(err);
  });
})

app.listen(4000);

async function registerDb() {
  let conn;
  try {
    conn = await oracledb.getConnection(config)
    let result = await conn.execute(
      `INSERT INTO "User" VALUES (name, email, id, password, age)`,
    );
    console.log("Rows inserted: " + result.rowsAffected);  // 1
    console.log("ROWID of new row: " + result.lastRowid);
    return result.rowsAffected;
  } catch (err) {
    console.log('Ouch!', err)
    return err.message;
  } finally {
    if (conn) { // conn assignment worked, need to close
      await conn.close()
    }
  }
}

async function checkAuth(data) {
  let conn;

  try {
  
    conn = await oracledb.getConnection(config)

    let result = await conn.execute(
      `Select name 
      from "User"
      where email = :email and password = :password`,
      {
        email : {val: data.email},
        password: {val: data.password}
      }
    );

    return result.rows;

  } catch (err) {
    console.log('Ouch!', err)
    return err.message;
  } finally {
    if (conn) { // conn assignment worked, need to close
      await conn.close()
    }
  }
}

Answer 1

Opening and closing connections to the DB impacts performance. A process needs to be started on the DB host, memory has to be allocated and initialized. And the reverse at connection close. Since these are new connections, they can't reuse some cached data for statement execution.

From the node-oracledb pooling documentation:

When applications use a lot of connections for short periods, Oracle recommends using a connection pool for efficiency.

Review that manual, and look at examples like webapp.js.

Make sure you increase UV_THREADPOOL_MAX for apps that have multiple connections open.