CRUD - MySQL - NodeJS - Express

mysql.js

'use strict';
var mysql = require('mysql');
var config = require('../config');
var pool = mysql.createPool(config.mysql);
exports.exec = function(query, params, callback) {
  if (!query) {
    callback(true);
  }
  pool.getConnection(function(err, connection) {
    if(err) { console.log(err); callback(true); return; }
    var q = connection.query(query, params, function(err, results) {
      connection.release();
      if(err) { console.log(err); callback(true); return; }
      callback(false, results);
    });
    console.log("Query: ",q.sql);
  });
};

user.controller.js

'use strict';

var _        = require('lodash');
var User     = require('./user.model');
var https    = require('https');
var mysql    = require('../../components/mysql');
var utility  = require('../../components/utility');



// Get list of Users
exports.index = function(req, res) {

  // Define number to paginate
  var from   = parseInt(req.query.from) || 0;
  var to     = parseInt(req.query.to)   || 1;

  // Create query and parameters
  var query      = "SELECT * FROM users LIMIT ?";
  var pagination = [from,to];

  // Return user
  mysql.exec(query, [pagination], function (err, user) {
    if (err) { handleError(res, err, 500) };
    res.json(user);
  });
};



// Get one User
exports.show = function (req, res) {

  // Define Query and parameters
  var query   = "SELECT * FROM users WHERE id = ?";
  var id      = req.params.id;

  // Return One User
  mysql.exec(query, [id], function (err, users) {
    if (err) { handleError(res, err, 500) };
    res.json(users);
  });
}



// Create a User
exports.create = function (req, res) {
  var user = req.body;
  var now = utility.dateNow();

  // Validate input fields
  User.validate( user, 'post' , function (err) {
    if (err) { handleError(res,err,400); return; };

    // Define values to insert
    var values = {
      name:               user.name,
      last_name:          user.last_name,
      username:           user.username,
      password:           user.password,
      email:              user.email,
      birthdate:          user.birthdate,
      social_account_id:  user.social_account_id,
      customers_id:       user.customers_id,
      plan_id:            user.plan_id,
      controllers_id:     user.controllers_id,
      updated_at:         now,
      created_at:         now
    };

    // Define Query
    var query   = "INSERT INTO users SET ? ";

    // Return One User
    mysql.exec(query, values, function (err, data) {
    if (err) { handleError(res, err, 500); return; };

    if (data.affectedRows < 1) {
      let error = { errors: [{
        title: 'Can not insert User'
      }]};
      handleError(res, error, 400 ); return;
    }

    res.json({
      id: data.insertId,
      username: values.username,
      password: values.password
    });
    });
  });
}



// Update a User
exports.update = function (req, res) {
  var user_new   = req.body;
  var now        = utility.dateNow();
  var error      = { errors: [] };

  // User Id comes from URL
  user_new.id = req.params.id;

  User.validate( user_new ,'put',  function (err) {
    if (err) { handleError(res,err,400); return; };

    User.findById(user_new.id, function (err, user) {
      if (err) { handleError(res,err,400); return; };

      if (!user){
        err = {
          source: { parameter: 'id'},
          title: 'User no found'
        };
        error.errors.push(err);
        handleError(res,err,404); return;
      }

      // Define values to insert
      var values = {
        name:               user_new.name              || user.name,
        last_name:          user_new.last_name         || user.last_name,
        username:           user_new.username          || user.username,
        password:           user_new.password          || user.password,
        email:              user_new.email             || user.email,
        birthdate:          user_new.birthdate         || user.birthdate ,
        social_account_id:  user_new.social_account_id || user.social_account_id,
        plan_id:            user_new.plan_id           || user.plan_id,
        controllers_id:     user_new.controllers_id    || user.controllers_id,
        updated_at:         now,
        created_at:         now
      }

      // Define Query
      var query   = "UPDATE users SET ? WHERE id = ? ";

      // Return Query Status
      mysql.exec(query, [values, user.id], function (err, data) {
        if (err) { handleError(res, err, 500); };
        if (data.affectedRows < 1) {
          error.errors.push({ title: 'Can not insert Users' });
          handleError(res, error, 400 ); return;
        }
        res.json( {success: true });
      });
    });

  });
}



// Delete a User
exports.delete = function (req, res) {
  var error = { errors: [] };

  // SQL Query
  var query = "DELETE FROM users WHERE id = ?";

  // Return One User
  mysql.exec(query, req.params.id , function (err, data) {
    if (err) { handleError(res, err, 500); return; };
    if (data.affectedRows < 1) {
      error.errors.push({ title: 'Can not delete User' });
      handleError(res, error, 400 ); return;
    }
    res.json( {success: true });
  });
}



// Reponse with error and code
function handleError(res, err, code) {
  if (code===500) {
    err = "Internar Error";
  }
  res.status(code).send(err);
}