How to get value from MySQL with Node.js

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

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

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

Screenshot from 2014-05-02 17:03:04


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 route
var customers = require('./routes/customers'); 
var app = express();
var connection  = require('express-myconnection'); 
var mysql = require('mysql');
// all environments
app.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 only
if ('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 world
app.get('/', routes.index);//route customer list
app.get('/customers', customers.list);//route add customer, get n post
app.get('/customers/add', customers.add);
app.post('/customers/add', customers.save);//route delete customer
app.get('/customers/delete/:id', customers.delete_customer);//edit customer route , get n post
app.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 %>
        <div class="page-data">
         <div class="data-btn">
           <button onClick="addUser();">+ Add</button>
         </div>
         <div class="data-table">
            <table border="1" cellpadding="7" cellspacing="7">
                <tr>
                    <th width="50px">No</th>
                    <th>Name</th>
                    <th>Address</th>
                    <th>Phone</th>
                    <th>Email</th>
                    <th width="120px">Action</th>
                </tr>                               
                <% if(data.length){ 
                                
                 for(var i = 0;i < data.length;i++) { %>                 
                <tr>
                    <td><%=(i+1)%></td>
                    <td><%=data[i].name%></td>
                    <td><%=data[i].address%></td>
                    <td><%=data[i].phone%></td>
                    <td><%=data[i].email%></td>
                    <td>
                        <a class="a-inside edit" href="../customers/edit/<%=data[i].id%>">Edit</a>                       
                        <a class="a-inside delete" href="../customers/delete/<%=data[i].id%>">Delete</a>                       
                    </td>
                </tr>
            <% }
            
             }else{ %>
                 <tr>
                    <td colspan="3">No user</td>
                 </tr>
            <% } %>
                                                             
            </table>
         </div>
        </div>        
<%- 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

Screenshot from 2014-06-24 11:14:12



Screenshot from 2014-06-24 11:16:26


Notes : The version of express framework could be an issue, also newer express version may have different folder structure with the above, port it to your own version. The Node version of this example is v0.10.25, NPM 1.3.26 and express 3.5.1

Comments

Popular posts from this blog

remove span elements from Contact form 7 but keeping inside elements

Elance HTML5 Test Answers