Close MSSQL connection in Node

Question

I'm connecting with SQL Server using Node mssql package in my Electron app.
I can't create REST API.

It works fine although I have concerns:

• it doesn't close SQL connection after query
• it makes a new DB connection for each query

Is that ok?

How it works:
app.js makes 2 queries and logs results
sql.js connects with DB

// app.js
const { getUser, getUsers } = require('./sql');

getUser(10).then((result) => {
    console.dir(result);
});

getUsers.then((result) => {
    console.dir(result);
})

// sql.js
const sql = require("mssql");

// DB credentials
const config = {
    user: 'myuser',
    password: '123',
    server: 'myserver',
    database: 'mydb',
    options: {
        encrypt: true
    }
}

// Creates new connection pool for each query
function connectDB() {
    const pool = new sql.ConnectionPool(config);

    return pool.connect()
        .then(pool => {
            console.log('Connected to database');

            // returns Promise
            return pool;
        })
        .catch(err => console.log('Database connection failed!', err));
}

// 1st query
function getUser(id) {
    const connection = connectDB();

    return connection
        .then(pool => {
            return pool.request()
                .input('PK_ID', sql.Int, parseInt(id))
                .execute('[uspGetUser]');
        })
        .then(result => {
            return result.recordset[0];
        })
        .catch(err => {
            console.log('Query failed!', err);
        });
}

// 2nd query
function getUsers() {
    const DB = connectDB();

    return DB
        .then(pool => {
            return pool.request()
                .execute('[uspGetUsers]');
        })
        .then(result => {
            return result.recordset[0];
        })
        .catch(err => {
            console.log('Query failed!', err);
        });
}

module.exports = {
    getUser,
    getUsers
}

Answer 1

No, you don't need to close a.k.a. release a connection back to the connection pool after every query. The library already does that for you.

The pool.close() method will close all the connection in the pool. Technically, you should only do that when you're terminating your application, not after every query, since creating a new pool every time create quite an overhead on your application.

I had the same question myself, so I looked up the project's source code.

Answer 2

Solved!

To close DB connection and return results, we can use finally statement and asnyc/await functions.

The finally statement lets you execute code, after try and catch, regardless of the result.

// sql.js
const sql = require("mssql");

// DB credentials
const config = {
    user: 'myuser',
    password: '123',
    server: 'myserver',
    database: 'mydb',
    options: {
        encrypt: true
    }
}

async function connectDB() {
    const pool = new sql.ConnectionPool(config);

    try {
        await pool.connect();
        console.log('Connected to database');

        return pool;
    }
    catch(err) {
        console.log('Database connection failed!', err);

        return err;
    }
}

async function getAll() {
    const DB = await connectDB();

    try {
        const result = await DB.request()
            .query('select * from [your_table]');

        return result.recordset;
    }
    catch (err) {
        console.log('Error querying database', err);

        return err;
    }
    finally {
        DB.close();
    }
}

async function execute() {
    let result = await getAll();
    console.dir(JSON.stringify(result));

    return result;
}

execute();