I have a script that I want to be able to schedule to run periodically, and query a DB, open a connection, execute a query, then close the connection.

Trying to make the following code work:

const Query = require('./query_db')
Query("select * from table").then( result => doSomethingWith(result) );
Query("select * from table2").then( result => doSomethingWith(result) );

Because these can resolve in any order (which is ok) I can't tell this script when to close the connection, so I want to have the query_db module responsible for opening and closing the connection per request.

The original module code was as follows:

const sql = require("mssql")
const config = require("./config").db

query_db = async function (sql_query) {
    try {
        pool = await sql.connect(config);
        result = await pool.request().query(sql_query);
        await sql.close()
        return result.recordset;
    } catch (err) {
        new Error(err)
    }
};

module.exports = query_db

This worked unless I had to execute 2 queries at one time. To remedy this, I used the ConnectionPool as follows:

const sql = require("mssql")
const config = require("./config").db

query_db = function (sql_query) {
    return new Promise( (resolve,reject) => {
        new sql.ConnectionPool(config).connect().then(pool =>{
            return pool.request().query(sql_query);
        }).then(result=> {
            sql.close();
            resolve(result.recordset);
        }).catch(err => {
            sql.close();
            reject(err)
        })
    })
};

module.exports = query_db

This works, but the pool stays open and the application never exits. I'd like it to exit when the active connections are closed. What's the best way to do this? Is it to put the connection in the main app and pass it to the function in the module instead of trying to create/close it with in the function?

UPDATE Resolved

Got it working as such. Much simpler as well.

module.exports = async function (sql_query) {
    const pool = new sql.ConnectionPool(config);
    try {
        await pool.connect();
        let result = await pool.request().query(sql_query)
        return result.recordset;
    }
    catch (err) {
        console.log("ERROR:", err)
    }
    finally {
        pool.close();
    }
}