using mssql node library with promise support

create-connection.js

const sql = require('mssql');
const Promise = require('bluebird');

Promise.promisifyAll([
    sql.Connection,
]);

const createConnection = connectionOptions => {
    const connection = new sql.Connection(connectionOptions);
    return connection.connectAsync().then(() => {
        return connection;
    }).disposer(() => {
        return connection.closeAsync();
    });
};

module.exports = createConnection;

execute.js

const sql = require('mssql');
const Promise = require('bluebird');
const createConnection = require('./create-connection');

/**
 * Run the specified stored procedure with the supplied parameters, if any.
 * @param connectionOptions {Object} - the detail needed to connect to the database.
 * @param procedure {string} - the name of the stored procedure to run.
 * @param params {Object} - the parameters, if any, to run the stored procedure with.
 */
function execute(connectionOptions, procedure, params = {}) {
    return Promise.using(createConnection(connectionOptions), (connection) => {
        let request = new sql.Request(connection);
        Object.keys(params).forEach(key => {
            request.input(key, params[key]);
        });
        return request.execute(procedure);
    });
}

module.exports = execute;

index.js

const express = require('express');
const app = express();
const http = require('http').Server(app);
const Promise = require('bluebird');
const api = express.Router();

const PORT = process.env.PORT || 3000;
const CONNECTION_INFO = {
    server: '',
    port: 1433,
    database: '',
    user: '',
    password: '',
    pool: {
        idleTimeoutMillis: 1000
    }
};

const query = require('./query');
const execute = require('./execute');

// a stub for the function to get connection info (from redis, eventually...)
const getInfo = companyId => Promise.resolve(CONNECTION_INFO);

api.get('/', (req, res) => {
    getInfo('100128')
    // .then(info => query(info, 'SELECT 1 AS result'))
    .then(info => execute(info, 'sp_GetSBIInfo', {}))
    .then(res.json)
    .catch(err => res.status(500).json(err));
});


/* boring server setup ------------------------------------------- */

app
.use('*/api/', api)
.use((req, res) => {
    res.send('hello. Request path: ' + req.url);
});

http.listen(PORT, () => {
	var d = new Date();
	console.log(`server started at ${d.toString()}`);
	console.log(`listening on *:${PORT}`);
});

query.js

const sql = require('mssql');
const Promise = require('bluebird');
const createConnection = require('./create-connection');

/**
 * run the specified query with supplied paramseters (if any)
 * @param connectionOptions {Object} - the detail needed to connect to the database.
 * @param query {string} - the sql query to run.
 * @param params {Object} - a dictionary of keys and values to run the query with.
 */
function query(connectionOptions, query, params = {}) {
    return Promise.using(createConnection(connectionOptions), (connection) => {
        let request = new sql.Request(connection);
        Object.keys(params).forEach(key => {
            request.input(key, params[key]);
        });
        return request.query(query)
    });
}

module.exports = query;