How to write a common MySQL query which will return result when called from API function in NodeJS?

Question

Right now whenever I want to execute a mysql query in my api functions I create a DB connection , then executing the query and releasing the connection for each api function. The code gets messier when nested DB query should be executed and Even I thought about performance issues since connections are continuously created and released . This is my current implementation

dbServer.js

const db = mysql.createPool({
   connectionLimit: 100,
   host: DB_HOST,
   user: DB_USER,
   password: DB_PASSWORD,
   database: DB_DATABASE,
   port: DB_PORT
})

module.exports = db

app.js (main file)

const express = require("express")
const app = express()
app.use(express.json())
const db = require('./dbServer')

require('./Backend/Routes/ApifileName')(app , db);

const port = process.env.PORT

app.listen(port)

ApiFileName.js

const mysql = require("mysql")
const auth = require("../Middlewares/VerifyMerchantAuth")

module.exports = function(app , db){
    app.post("/v1/route" ,auth, async(req , res) => {

        let someParam= req.body.someParam;

        let sql_command = "SELECT * from table_Name where column_name = ?"
        let sql_query   = mysql.format(sql_command,[someParam])

        db.getConnection ( async (err, connection)=> {
            if (err) throw err;

            await connection.query (sql_query, async (err, result) => {
                connection.release();

                if (err) throw err;

                if(result.length == 0){
                    return res.json({error : "some error"})
                }

                return res.json({result : "some result"});
            })
        })
    })
} 

My Files Hierarchy

node_modules
package.json
app.js
dbServer.js
Backend -> 1. Routes -> ApiFileName.js
           2. Middlewares -> Here I want to write this sql function

My requirement now is I need to write a common function which takes db and sql_query as parameters and should return response if success or error.

I tried it myself but the mysql response comes after the api returns it's response. I tried using await , .then() but nothing worked.

Or Is this current implemented method is efficient already? Give your views.

Please Help me and Thanks in advance

Answer 1

A new connection for each SQL query is terrible for performance. Have a single connection for the entire invocation of the app. That may involve having a "global" or "static" place to hold the "handle" to the connection.

If you are in a web environment, you cannot keep the connection from one "web page" to the next. Simply let it go and connect again on the next page.

Don't worry about "pools"; they done buy enough performance to worry about.

There is a rare case where you need more than one "connection" in an app -- if you are talking to different servers. Within a single server, one connection can interact with all the databases and tables (that it has permission to touch).