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
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
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
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
Post a Comment