a simple mysql transaction wrapper for node.js using node-mysql.

mysql.js

var mysql      = require('mysql');

var mysqlConfig = {
    connectionLimit : 100,
    host            : 'hostname',
    user            : 'username',
    password        : 'password',
    database        : 'database',
    acquireTimeout  : 30000
};

var pool;

/*
 * TODO: consider to add a button to retrieve mysql connection on error or timeout
 */
function handleError (err) {
    if (err) {
        if (err.code === 'PROTOCOL_CONNECTION_LOST') {
            connect();
        } else if (err.code.toLowerCase().indexOf('timeout') != -1) {
            connect();
        } else {
            console.error(err.stack || err);
        }
    }
}

function connect () {
    //connection = mysql.createConnection(mysqlConfig);
    //connection.connect(handleError);
    //connection.on('error', handleError);
    console.log('connecting to msyql...')
    pool  = mysql.createPool(mysqlConfig);
}

connect();

function process(sql, cb) {
    pool.getConnection(function(err, connection) {
        if (err) {
            handleError(err);
        }

        connection.query( sql, function(err, rows) {
            // And done with the connection.
            connection.release();

            cb(err, rows);
        });
    });
}

function execute(sql_gen, callback) {
    pool.getConnection(function(err, connection) {
        if (err) {
            handleError(err);
        }

        if (connection && connection.query) {
            connection.query(sql_gen(connection), function(err, rows) {
                connection.release();

                callback(err, rows);
            });
        } else {
            callback(new Error('Connection to database lost'));
        }
    });
}

module.exports.query = process;

module.exports.execute = execute;

sqlc.js

var T = require('./tables');
var H = require('../libs/utils');

var FieldTypes = {
    "Number": 1,
    "String": 2,
    "Datetime": 3,
    "CHARS": 4
};

String.prototype.replaceAll = function(s1,s2){
    return this.replace(new RegExp(s1,"gm"),s2);
};

/**
 * You first need to create a formatting function to pad numbers to two digits…
 **/
function twoDigits(d) {
    if(0 <= d && d < 10) return "0" + d.toString();
    if(-10 < d && d < 0) return "-0" + (-1*d).toString();
    return d.toString();
}

/**
 * …and then create the method to output the date string as desired.
 * Some people hate using prototypes this way, but if you are going
 * to apply this to more than one Date object, having it as a prototype
 * makes sense.
 **/
Date.prototype.toMysqlFormat = function() {
    return this.getUTCFullYear() + "-" + twoDigits(1 + this.getUTCMonth()) + "-" + twoDigits(this.getUTCDate()) + " " + twoDigits(this.getUTCHours()) + ":" + twoDigits(this.getUTCMinutes()) + ":" + twoDigits(this.getUTCSeconds());
};

/**
 *
 * SQL Insert Statement Generator
 *
 * fields
 *  1. in `object`
 *  2. in `table.fields`
 *  3. in `fieldNames` if specified
 *  will be inserted in a proper way specified in table descriptors.
 *
 * @param connection: connection to use
 * @param table: table descriptor from 'functions/tables'
 * @param object: object to insert
 * @param [fieldNames]: optional, allowed fields
 */
function insert(connection, table, object, fieldNames) {
    var h = "insert into `" + table.table_name + "` (";

    var keysMatched = [];

    H.each(object, function(v, k) {
        if (table.fields[k] && k != "id") {
            if (fieldNames && fieldNames.indexOf(k) == -1) {
                return;
            }
            keysMatched.push(k);
        }
    });

    var fields = "`" + keysMatched.join("`, `") + "`) values (";

    var values = [];

    H.each(keysMatched, function(k) {
        if (table.fields[k] == FieldTypes.Number) {
            values.push(connection.escape(object[k]) + "");
        } else if (table.fields[k] == FieldTypes.String) {
            values.push(connection.escape(object[k]));
        } else if (table.fields[k] == FieldTypes.Datetime) {
            values.push(connection.escape((new Date(object[k])).toMysqlFormat()));
        } else if (table.fields[k] == FieldTypes.CHARS) {
            values.push("CHAR(" + object[k] + ")");
        }
    });

    var valuesF = values.join(", ") + ");";

    return h + fields + valuesF;
}

/**
 *
 * SQL Update Statement Generator
 *
 * fields
 *  1. in `object`
 *  2. in `table.fields`
 *  3. in `fieldNames` if specified
 *  will be inserted using proper way specified in table descriptors.
 *
 * @param connection: connection to use
 * @param table: table descriptor from 'functions/tables'
 * @param object: object to insert
 * @param condition: condition string after `WHERE`, no pending `;`
 * @param [fieldNames]: optional, allowed fields
 */
function update(connection, table, object, condition, fieldNames) {
    var h = "update " + table.table_name + " set ";

    var keysMatched = [];

    H.each(object, function(v, k) {
        if (table.fields[k] && k != "id") {
            if (fieldNames && fieldNames.indexOf(k) == -1) {
                return;
            }
            keysMatched.push(k);
        }
    });

    var setters = [];

    H.each(keysMatched, function(k) {
        var pre = "`" + k + "`=";
        var post = "";
        if (table.fields[k] == FieldTypes.Number) {
            post = connection.escape(object[k]) + "";
        } else if (table.fields[k] == FieldTypes.String) {
            post = connection.escape(object[k]);
        } else if (table.fields[k] == FieldTypes.Datetime) {
            post = connection.escape((new Date(object[k])).toMysqlFormat());
        } else if (table.fields[k] == FieldTypes.CHARS) {
            post = "CHAR(" + object[k] + ")";
        }
        if (post != "") {
            setters.push(pre + post);
        }
    });

    var valueF = setters.join(", ");

    var where = "";

    if (condition) {
        where = " where " + condition;
    } else if (table.condition) {
        where = " where " + table.condition;
    }

    return h + valueF + where + ";";
}

/**
 * SQL Select Statement Generator
 *
 * @param table
 * @param fieldNames
 * @param condition
 * @param limit: no prepending `limit`
 * @returns {string}
 */
function select(table, fieldNames, condition, limit) {
    var f = "";
    if (fieldNames == "*") {
        f = fieldNames;
    } else if (fieldNames.length) {
        f = fieldNames.join(", ");
    } else {
        f = "*";
    }

    //filter fields from table.fields
    var filteredFields = [];

    if (f != "*" && table.fields) {
        H.each(fieldNames, function(f) {
            if (table.fields[f]) {
                filteredFields.push(f);
            }
        });
        f = filteredFields.join(", ");
    }

    var pre = "select " + f + " from " + table.table_name;

    var where = "";

    if (table.condition) {
        if (condition) {
            if (condition.indexOf(table.condition) != -1) {
                where = " where " + condition;
            } else {
                where = " where " + table.condition + " and " + condition;
            }
        } else {
            where = " where " + table.condition;
        }
    } else {
        if (condition) {
            where = " where " + condition;
        }  else {
            //where = "";
        }
    }

    var filter = "";

    if (limit) {
        filter = " limit " + limit;
    }

    return pre + where + filter + ";";
}

/**
 * Custom Delete Statement: update valid -> 0
 * @param table
 * @param condition
 * @returns {string}
 */
function cdelete(table, condition) {
    return "update " + table.table_name + " set valid=0 where " + condition + ";";
}

/**
 * SQL Remove/Delete Statement Generator
 * @param table
 * @param condition
 * @returns {string}
 */
function remove(table, condition) {
    return "delete from " + table.table_name + " where " + condition + ";";
}

module.exports = {
    insert: insert,
    select: select,
    update: update,
    cdelete: cdelete,
    "delete": remove
};

tables.js

var Tables = {};

Tables.FileTable = {
    table_name: "file",
    fields: {
        id: 1,
        hash: 2,
        size: 1,
        id3: 2,
        name: 2,
        picture: 4,
        picture_url: 2,
        valid: 1
    },
    condition: "valid=1"
};

module.exports = Tables;

transaction.js

var SQLC = require('./sqlc');

var MYSQL = require('./mysql');

var TBS = require('./tables');

var TSC = {};

TSC.BasicTransacc = TSC.BTranscc = function(sql_gen, callback) {
    MYSQL.execute(sql_gen, callback);
};

TSC.listFileInfo = function(callback) {
    TSC.BTranscc(function() {
        return SQLC.select(TBS.FileTable, ['id', 'name', 'hash', 'size', 'id3', 'picture_url']);
    }, callback);
};

module.exports = TSC;