PDO with MS-SQL (Azure) and Node.js

executesql.js

// -------------------------------------------------------------------------
// USING MSSQL NPM AND PREPARED STATEMENTS IN NODE.JS
// ENABLING ? AS PLACEHOLDERS IN SQL AND AUTOMATIC TYPECASTING OF PARAMETERS
// THIS IS WORKING ON AZURE WITH NODE.JS AND MSSQL NODE MODULE - YAII :) 
// -------------------------------------------------------------------------
// MIT LICENSE 2015 @ Anders Holm-Jensen
// -------------------------------------------------------------------------
var sql = require('mssql'); 

var config = {
    user: 'INSERT-USERNAME',
    password: 'INSERT-PASSWORD',
    server: 'INSERT-SERVER',
    port: 1433,
    database: 'INSERT-DATABASE-NAME',
    stream: false,
    options: {
        useUTC: true,
        encrypt: true
    }
}


function defineSQLType(input) {
    returnVal = null;

    switch(typeof(input)) {
        case 'string': returnVal = sql.NVarChar; break;
        case 'number': returnVal = sql.Int; break;
        case 'boolean': returnVal = sql.Bit; break;
        case 'buffer': returnVal = sql.VarBinary; break;
        case 'object':
            returnVal = sql.NVarChar;
            if (input.getMonth) returnVal = sql.DateTime;
        break;
        default: returnVal = sql.NVarChar; break;
    }

    return returnVal;
}


function prepareQueryAndParams(sqlQuery, sqlParams) {

    // Prepare query and params (based in ? and typeof of params)
    var sqlSplit = sqlQuery.split('?');
    var valueObject = {};
    var paramObject = [];

    if (sqlSplit.length - 1 != sqlParams.length) {
        returnVal = {
            'error': 'Params and query does not match.'
        };
    } else {

        for (sqlLoop = 0; sqlLoop < sqlParams.length; sqlLoop++) {
            var value = sqlParams[sqlLoop];

            // set param object array
            paramArray = ['param' + sqlLoop, defineSQLType(value)];
            paramObject.push(paramArray);

            // append paramValue array
            valueObject['param' + sqlLoop] = value;

            // update query with param name
            sqlSplit[sqlLoop] = sqlSplit[sqlLoop] + '@param' + sqlLoop;
        }

        returnVal = {
            'error': null,
            'query': sqlSplit.join(''),
            'params': paramObject,
            'values': valueObject
        };
    }

    return returnVal;
}


function executeSQL(query, params, callback) {
    if(sql == null || config == null || query == null) {
        error = 'Error in executeSQL - sql/config/query is null.';
        console.log(error);
        callback(error, null);
        return false;
    }

    // perform sql and return data
    sql.connect(config, function (err) {

        if (err != null) {
            console.log('Error with sql.connect:');
            console.log(err);
            callback(err, null);
            return false;
        }

        var ps = new sql.PreparedStatement();

        sqlPrepared = prepareQueryAndParams(query, params);
        if (sqlPrepared.error != null) {
            console.log('Error with prepared sql:');
            console.log(sqlPrepared.error);
            callback(sqlPrepared.error, null);
            return false;
        }

        // set any params
        if (sqlPrepared.params.length > 0) {
            for (sqlLoop = 0; sqlLoop < sqlPrepared.params.length; sqlLoop++) {
                ps.input(sqlPrepared.params[sqlLoop][0], sqlPrepared.params[sqlLoop][1]);
            }
        }

        // prepare (SQL) the prepared (DIY-fix) statement
        ps.prepare(sqlPrepared.query, function (err) {
            if (err != null) {
                console.log('Error performing query:');
                console.log(sqlPrepared.query);
                callback('Error performing query', null);
                return false;
            }

            // execute the SQL (with params)
            ps.execute(sqlPrepared.values, function (err, recordset) {
                // important to unprepare (release the connection)
                ps.unprepare(function (err) { 
                    if (err != null) {
                        console.log('Error unpreparing statement:');
                        console.log(err);
                    }
                });

                callback(err, recordset);
                return false;
            });
        });
    });    
}


module.exports.executeSQL = executeSQL;

server.js

var http = require('http');
var execSQL = require('./executesql');

var sqlQuery = 'SELECT foo FROM dbo.table WHERE firstname = ? AND age > ? AND lastlogin < ?';
var sqlParams = ['Anders',25, new Date()];

execSQL.executeSQL(sqlQuery, sqlParams, function (error, result) {
    if (error != null) {
        console.log(error);
    } else {
        console.log(result);
    }
});