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 ubuntunpm, express mysql for nodei haven't tested it yet on windows. but i bet this will work too.
installing all those things aboveinstall 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/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
});
};
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
+ add
no
name
address
phone
email
action
for(var i = 0;i
edit
delete
}else{ %>
no user
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 the source code :
ubuntu@acerxtimeline:~/hello_world$ node app.js
http://localhost:4300/customers
the source will produce things like these:
happy coding