ExampleofCRUDwithNode. js & amp; MySQL_MySQL

ExampleofCRUDwithNode. jsamp; amp; MySQL NodeJS

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.

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 abve

Install Node. js, NPM and Express in Ubuntu

After you installation's completed, 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 you 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. careate a MySQL Database: nodejs and create a tablecustomer (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 locatedroutes/MERS mers. 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

});
};
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 customerWHERE 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 %>

 

 + Add
 

 






























 
  <% if(data.length){ for(var i = 0;i < data.length;i++) { %> 
   <% } }else{ %> 
   <% } %> 
  

  
  
 
   
 
    
No
 
    
Name
 
    
Address
 
    
Phone
 
    
Email
 
    
Action
 
   
 
    
<%=(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 herenodecrud and put a questions or issue on the Comment bellow.

Run the source code:

ubuntu@AcerXtimeline:~/hello_world$ node app.js

http://localhost:4300/customers

The source will produce things like these:

Happy coding