Simple CRUD Node.js & MySQL

By Budyks    NodeJs

Please Share this to your social media if this is useful for you

Recommended : New Node.js Example project with Hapi.js. 


This time I'd like to share a basic and simple example of CRUD Operation in Node.js and MySQL. Its a lil bit hard to find tutorial Node.js n MySQL as poeple tend to use Mongoose instead of MySQL.

LIVE DEMO HERE

"note that there's no input validation on the Demo, just a crud example" 
** UPDATE **
I've created RESTFul Crud example with newest version of express and other dependencies here 

Before we start, Please mind the environment of this Application.

  • I'm using Ubuntu
  • NPM, Express 
  • MySQL for Node

I haven't tested it yet on Windows. but i bet this will work too.

Installing all those things above

Install Node.js, NPM and Express in Ubuntu 

After your installation's complete, lets start creating your project folder:

ubuntu@AcerXtimeline:~$ express hello_world

once your hello_world folder is ready, Install MySQL. Go inside hello_world

ubuntu@AcerXtimeline:~/hello_world$ npm install mysql
We need a connection manager in Express. install it 
ubuntu@AcerXtimeline:~/hello_world$ npm install express-myconnection
Now take a look at this Folder structure
See your folder structure, compare it to the picture above. make new folder/files that you dont have yet in folder just like on the pic.
Are we ready yet ? 
1. Create a MySQL Database : nodejs  and create a table customer (id,name,address,email,phone).  or you can import the SQL in source code (see the end of this tuts)
2. Open app.js . by default some codes are already given for you. we'll just need to add a lil more codes.
/** * Module dependencies. */var express = require('express');var routes = require('./routes');var http = require('http');var path = require('path');//load customers routevar customers = require('./routes/customers'); var app = express();var connection  = require('express-myconnection'); var mysql = require('mysql');// all environmentsapp.set('port', process.env.PORT || 4300);app.set('views', path.join(__dirname, 'views'));app.set('view engine', 'ejs');//app.use(express.favicon());app.use(express.logger('dev'));app.use(express.json());app.use(express.urlencoded());app.use(express.methodOverride());app.use(express.static(path.join(__dirname, 'public')));// development onlyif ('development' == app.get('env')) {  app.use(express.errorHandler());}/*------------------------------------------    connection peer, register as middleware    type koneksi : single,pool and request -------------------------------------------*/app.use(        connection(mysql,{                host: 'localhost',        user: 'root',        password : '',        port : 3306, //port mysql        database:'nodejs'    },'request'));//route index, hello worldapp.get('/', routes.index);//route customer listapp.get('/customers', customers.list);//route add customer, get n postapp.get('/customers/add', customers.add);app.post('/customers/add', customers.save);//route delete customerapp.get('/customers/delete/:id', customers.delete_customer);//edit customer route , get n postapp.get('/customers/edit/:id', customers.edit); app.post('/customers/edit/:id',customers.save_edit);app.use(app.router);http.createServer(app).listen(app.get('port'), function(){  console.log('Express server listening on port ' + app.get('port'));});
remember to make new files/folder like shown on the above pic. Now, wee need codes to DO THE CRUD. the file's located routes/customers.js
/* * GET customers listing. */exports.list = function(req, res){  req.getConnection(function(err,connection){            connection.query('SELECT * FROM customer',function(err,rows)     {                    if(err)           console.log("Error Selecting : %s ",err );                 res.render('customers',{page_title:"Customers - Node.js",data:rows});                                    });           });  };exports.add = function(req, res){  res.render('add_customer',{page_title:"Add Customers-Node.js"});};exports.edit = function(req, res){      var id = req.params.id;      req.getConnection(function(err,connection){            connection.query('SELECT * FROM customer WHERE id = ?',[id],function(err,rows)        {                        if(err)                console.log("Error Selecting : %s ",err );                 res.render('edit_customer',{page_title:"Edit Customers - Node.js",data:rows});                                    });                     }); };/*Save the customer*/exports.save = function(req,res){        var input = JSON.parse(JSON.stringify(req.body));        req.getConnection(function (err, connection) {                var data = {                        name    : input.name,            address : input.address,            email   : input.email,            phone   : input.phone                 };                var query = connection.query("INSERT INTO customer set ? ",data, function(err, rows)        {            if (err)              console.log("Error inserting : %s ",err );                   res.redirect('/customers');                  });               // console.log(query.sql); get raw query        });};/*Save edited customer*/exports.save_edit = function(req,res){        var input = JSON.parse(JSON.stringify(req.body));    var id = req.params.id;        req.getConnection(function (err, connection) {                var data = {                        name    : input.name,            address : input.address,            email   : input.email,            phone   : input.phone                 };                connection.query("UPDATE customer set ? WHERE id = ? ",[data,id], function(err, rows)        {            if (err)              console.log("Error Updating : %s ",err );                   res.redirect('/customers');                  });        });};exports.delete_customer = function(req,res){               var id = req.params.id;         req.getConnection(function (err, connection) {                connection.query("DELETE FROM customer  WHERE id = ? ",[id], function(err, rows)        {                         if(err)                 console.log("Error deleting : %s ",err );                         res.redirect('/customers');                     });             });};
here's html code (ejs template) for listing the customer
<%- include layouts/header.ejs %>        
         
                    
         
                                                                                                                                                                                                                   <% if(data.length){                                                  for(var i = 0;i < data.length;i++) { %>                                                                                                                                                                                     <% }                         }else{ %>                                                                  <% } %>                                                                        
NoNameAddressPhoneEmailAction
<%=(i+1)%><%=data[i].name%><%=data[i].address%><%=data[i].phone%><%=data[i].email%>                        Edit                                               Delete                                          
No user
         
       
        <%- include layouts/footer.ejs %>
Well, actually 'm too lazy to put it all here...its gonna be a long scroll :(. pardon me for that. I think you can just download the Source here nodecrud and put a questions or issue on the Comment bellow. 
run the the source code :
ubuntu@AcerXtimeline:~/hello_world$ node app.js
http://localhost:4300/customers
The source will produce things like

Comments



    Follow Us