A simple function to query MSSQL then close the connection for use in quick scripts

Question

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();
    }
}
    

Answer 1

If your goal is to explicitly close the pool, I would expect to see pool.close(); somewhere. e.g.:

try { await pool.connect(); ... }
catch { ... }
finally { pool.close(); }

The whole point of connection pooling is exactly the behavior you're seeing: since establishing a connection is expensive, they don't go away immediately or automatically, they hang around waiting for efficient reuse. If you close the pool directly, you're kind of defeating the purpose of having the feature enabled in the first place.