Skip to content

Instantly share code, notes, and snippets.

@isidroamv
Created November 23, 2015 06:40
Show Gist options
  • Save isidroamv/2b2de308e4eb3fe995e9 to your computer and use it in GitHub Desktop.
Save isidroamv/2b2de308e4eb3fe995e9 to your computer and use it in GitHub Desktop.
CRUD - MySQL - NodeJS - Express
'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);
});
};
'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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment