var express = require('express');
var path = require('path');
var http = require('http');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');

var index = require('./routes/index');
var users = require('./routes/users');
var customers = require('./routes/customers');

//load customers route
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');


// uncomment after placing your favicon in /public
//app.use(favicon(path.join(__dirname, 'public', 'favicon.ico')));
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', index);
app.use('/users', users);


// catch 404 and forward to error handler
app.use(function(req, res, next) {
  var err = new Error('Not Found');
  err.status = 404;
  next(err);
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});


/*------------------------------------------
    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'));
});

module.exports = app;

<%- 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 %>

/*
 * 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');

            });

         });
    };