sqlHelper.js

var _ = require("underscore");

exports.requestHas = function(params,tests) {
  return _.isEmpty(_.difference(_.keys(params),tests));
}

exports.insertQuery = function(table,obj){
  var query = "INSERT INTO `" + table + "` (",
    valStr = "";
  _.each(_.keys(obj),function(key,i) {
    if(i !== 0){
      query += ",";
      valStr += ",";
    }
    query += "`" + key + "`";
    valStr += '?';
  });
  query += ") VALUES ( " + valStr + ") ;";
  return query;
};

exports.insert = function(mysql,table,obj,cb) {
  var query = exports.insertQuery(table,obj);
  mysql.query(query,_.values(obj),function(err,result) {
    if(err){
      console.log("error runing insert query",query,err);
      cb(err);
    }
    obj[table + "ID"] = result.insertId;  
    cb(null,obj)
  })
};

exports.find = function(mysql,table,where,cb) {
  var query  = "SELECT * FROM `" + table +"` "
  query += whereClause(where);
  mysql.query(query,_.values(where),function(err,result) {
    if(err) {
      console.log(err);
      cb(err);
    } else {
      cb(null,result);
    } 
  });
}

function whereClause(where) {
  var query = " WHERE ";
  if(!_.isEmpty(where)) {
    var i = 0;
    _.each(where,function(val,key) {
      if(i !== 0)
        query += ",";
      query += "`" + key + "`=?" ;
      i++;
    });
  } else {
    query += "1";
  }
  return query; 
}

exports.update = function(mysql,table,updates,where,params,cb) {
  var query = "UPDATE `" + table + "` SET",
    i = 0;
  _.each(updates,function(val,key) {
    if(i !== 0)
      query += ","; 
    query += " `" + key + "` = ? ";
    i++;
  });
  query += whereClause(where);
  mysql.query(query,_.flatten([_.values(updates),_.values(where)]),function(err,results) {
    if(err){
      console.log(err);
      if(cb)
        cb(err);
    }
    if(params['showNewRecord']) {
      exports.find(table,where,function(err,selectResults) {
        console.log(err);
        cb(null,selectResults);
      });
    } else {
      if(cb)
        cb(null,results);
    }
  });
}

exports.delete = function(mysql, table, where, cb) {
  if(where){
    var query = "DELETE FROM `" + table + "` ";
    query += whereClause(where);
    mysql.query(query,_.values(where),function(err,result) {
      if (err) {
        console.log(err);
        cb(err);
      } else {
        cb(null,result);
      }
    });
  } else
    console.log("scary delete trying to run");
}