Switching to MySQL from MongoDB in Node.js

There are a lot of resources on the web on how to set up a Node.js / Express server with MongoDB for basic CRUD operations. I usually deal with relational data however, and prefer something like MySQL. So I switched this Node Cellar sample app from Mongo to MySQL using the express-myconnection NPM package.

Install and require the mysql and express-myconnection packages in your project using NPM. The MySQL connection can be set up in your Node server along with Express. This assumes you already have an Express server set up.

var app = express();

var connection  = require('express-myconnection'),
    mysql = require('mysql');

app.use(

    connection(mysql,{
        host     : 'example.com',
        user     : 'youruser',
        password : 'YOURPASSWORD',
        port	 : 3306,
        database : 'yourdb'
    },'request')

);

Below are the MySQL CRUD operations, with the MongoDB equivalent commented out for comparison. Following along with the Node Cellar sample app, this replaces most of wines.js. Note the addition of the next parameter to return errors to the Node server.

exports.findById = function(req, res, next) {
    var id = req.params.id;
    console.log('Retrieving wine: ' + id);
    /*
    db.collection('wines', function(err, collection) {
        collection.findOne({'_id':new BSON.ObjectID(id)}, function(err, item) {
            res.send(item);
        });
    });*/
   
    req.getConnection(function (err, conn){
        if (err) return next("Cannot connect to mysql");
        var query = conn.query("SELECT * FROM Wines WHERE _id = ?", id, function(err, rows) {
	    	if(err){
	            console.log(err);
	            return next("Mysql error, check your query");
	       } else {
	       		console.log(rows[0]);
	       }
	        
		  res.send(rows[0]);
        });
     });
};

exports.findAll = function(req, res, next) {
	console.log('Retrieving all wines');
	/*
    db.collection('wines', function(err, collection) {
        collection.find().toArray(function(err, items) {
            res.send(items);
        });
    });*/
   
    req.getConnection(function (err, conn){
        if (err) return next("Cannot connect to mysql");
        var query = conn.query("SELECT * FROM Wines", function(err, rows) {
	    	if(err){
	            console.log(err);
	            return next("Mysql error, check your query");
	         }
			 res.send(rows);
        });
     });
};

exports.addWine = function(req, res, next) {
    var wine = req.body;
    console.log('Adding wine: ' + JSON.stringify(wine));
    /*
    delete wine._id;  
    db.collection('wines', function(err, collection) {
        collection.insert(wine, {safe:true}, function(err, result) {
            if (err) {
                res.send({'error':'An error has occurred'});
            } else {
                console.log('Success: ' + JSON.stringify(result[0]));
                res.send(result[0]);
            }
        });
    });*/
     
    req.getConnection(function (err, conn){
        if (err) return next("Cannot connect to mysql");
        var query = conn.query("INSERT INTO Wines SET ? ", wine, function(err, rows) {
	    	if(err){
	            console.log(err);
	            return next("Mysql error, check your query");
	        } else {
	       		console.log("Data inserted with Id: " + rows.insertId);
	        }
          wine._id = rows.insertId;
		  res.send(wine);
        });
     });
}

exports.updateWine = function(req, res, next) {
    var id = req.params.id;
    var wine = req.body;
    delete wine._id;
    console.log('Updating wine: ' + id);
    console.log(JSON.stringify(wine));
    /*
    db.collection('wines', function(err, collection) {
        collection.update({'_id':new BSON.ObjectID(id)}, wine, {safe:true}, function(err, result) {
            if (err) {
                console.log('Error updating wine: ' + err);
                res.send({'error':'An error has occurred'});
            } else {
                console.log('' + result + ' document(s) updated');
                res.send(wine);
            }
        });
    });*/
   
    req.getConnection(function (err, conn){
        if (err) return next("Cannot connect to mysql");
        var query = conn.query("UPDATE Wines SET ? WHERE _id = ?", [wine,id], function(err, rows) {
	    	if(err){
	            console.log(err);
	            return next("Mysql error, check your query");
	        } else {
	       		console.log("Data updated, rows affected: " + rows.affectedRows);
	        }
		  res.send(wine);
        });
     });
}

exports.deleteWine = function(req, res, next) {
    var id = req.params.id;
    console.log('Deleting wine: ' + id);
    /*
    db.collection('wines', function(err, collection) {
        collection.remove({'_id':new BSON.ObjectID(id)}, {safe:true}, function(err, result) {
            if (err) {
                res.send({'error':'An error has occurred - ' + err});
            } else {
                console.log('' + result + ' document(s) deleted');
                res.send(req.body);
            }
        });
    });*/
   
    req.getConnection(function (err, conn){
        if (err) return next("Cannot connect to mysql");
        var query = conn.query("DELETE FROM Wines WHERE _id = ?", id, function(err, rows) {
	    	if(err){
	            console.log(err);
	            return next("Mysql error, check your query");
	        } else {
	       		console.log("Data deleted, rows affected: " + rows.affectedRows);
	        }
		  res.send(req.body);
        });
     });
}