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 Appl本文来源gaodai$ma#com搞$代*码*网ication.
- 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
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<br />
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.
/**<br /> * Module dependencies.<br /> */<br />var express = require('express');<br />var routes = require('./routes');<br />var http = require('http');<br />var path = require('path');<br />//load customers route<br />var customers = require('./routes/customers'); <br />var app = express();<br />var connection= require('express-myconnection'); <br />var mysql = require('mysql');<br />// all environments<br />app.set('port', process.env.PORT || 4300);<br />app.set('views', path.join(__dirname, 'views'));<br />app.set('view engine', 'ejs');<br />//app.use(express.favicon());<br />app.use(express.logger('dev'));<br />app.use(express.json());<br />app.use(express.urlencoded());<br />app.use(express.methodOverride());<br />app.use(express.static(path.join(__dirname, 'public')));<br />// development only<br />if ('development' == app.get('env')) {<br />app.use(express.errorHandler());<br />}<br />/*------------------------------------------<br />connection peer, register as middleware<br />type koneksi : single,pool and request <br />-------------------------------------------*/<br />app.use(<br /><br />connection(mysql,{<br /><br />host: 'localhost',<br />user: 'root',<br />password : '',<br />port : 3306, //port mysql<br />database:'nodejs'<br />},'request')<br />);//route index, hello world<br />app.get('/', routes.index);//route customer list<br />app.get('/customers', customers.list);//route add customer, get n post<br />app.get('/customers/add', customers.add);<br />app.post('/customers/add', customers.save);//route delete customer<br />app.get('/customers/delete/:id', customers.delete_customer);//edit customer route , get n post<br />app.get('/customers/edit/:id', customers.edit); <br />app.post('/customers/edit/:id',customers.save_edit);<br />app.use(app.router);<br />http.createServer(app).listen(app.get('port'), function(){<br />console.log('Express server listening on port ' + app.get('port'));<br />});
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
/*<br /> * GET customers listing.<br /> */<br />exports.list = function(req, res){<br />req.getConnection(function(err,connection){<br /> <br /> connection.query('SELECT * FROM customer',function(err,rows) {<br /><br />if(err)<br /> console.log("Error Selecting : %s ",err );<br /> <br />res.render('customers',{page_title:"Customers - Node.js",data:rows});<br /> <br /> });<br /> <br />});<br /><br />};<br />exports.add = function(req, res){<br />res.render('add_customer',{page_title:"Add Customers-Node.js"});<br />};<br />exports.edit = function(req, res){<br /><br />var id = req.params.id;<br /><br />req.getConnection(function(err,connection){<br /> <br /> connection.query('SELECT * FROM customer WHERE id = ?',[id],function(err,rows)<br />{<br /><br />if(err)<br />console.log("Error Selecting : %s ",err );<br /> <br />res.render('edit_customer',{page_title:"Edit Customers - Node.js",data:rows});<br /> <br /> });<br /> <br />}); <br />};<br />/*Save the customer*/<br />exports.save = function(req,res){<br /><br />var input = JSON.parse(JSON.stringify(req.body));<br /><br />req.getConnection(function (err, connection) {<br /><br />var data = {<br /><br />name: input.name,<br />address : input.address,<br />email : input.email,<br />phone : input.phone <br /><br />};<br /><br />var query = connection.query("INSERT INTO customer set ? ",data, function(err, rows)<br />{<br /><br />if (err)<br />console.log("Error inserting : %s ",err );<br /> <br />res.redirect('/customers');<br /><br />});<br /><br /> // console.log(query.sql); get raw query<br /><br />});<br />};<br />exports.save_edit = function(req,res){<br /><br />var input = JSON.parse(JSON.stringify(req.body));<br />var id = req.params.id;<br /><br />req.getConnection(function (err, connection) {<br /><br />var data = {<br /><br />name: input.name,<br />address : input.address,<br />email : input.email,<br />phone : input.phone <br /><br />};<br /><br />connection.query("UPDATE customer set ? WHERE id = ? ",[data,id], function(err, rows)<br />{<br /><br />if (err)<br />console.log("Error Updating : %s ",err );<br /> <br />res.redirect('/customers');<br /><br />});<br /><br />});<br />};<br /><br />exports.delete_customer = function(req,res){<br /><br /> var id = req.params.id;<br /><br /> req.getConnection(function (err, connection) {<br /><br />connection.query("DELETE FROM customerWHERE id = ? ",[id], function(err, rows)<br />{<br /><br /> if(err)<br /> console.log("Error deleting : %s ",err );<br /><br /> res.redirect('/customers');<br /> <br />});<br /><br /> });<br />};
here’s html code (ejs template) for listing the customer
<%- include layouts/header.ejs %><br /><div class="page-data"><br /> <div class="data-btn"><br /> <button>+ Add</button><br /> </div><br /> <div class="data-table"><br /><table border="1" cellpadding="7" cellspacing="7"><br /><tr><br /><th width="50px">No</th><br /><th>Name</th><br /><th>Address</th><br /><th>Phone</th><br /><th>Email</th><br /><th width="120px">Action</th><br /></tr> <br /><% if(data.length){ <br /><br /> for(var i = 0;i <br /><tr><br /><td><%=(i+1)%></td><br /><td><%=data[i].name%></td><br /><td><%=data[i].address%></td><br /><td><%=data[i].phone%></td><br /><td><%=data[i].email%></td><br /><td><br />">Edit <br />">Delete <br /></td><br /></tr><br /><% }<br /><br /> }else{ %><br /> <tr><br /><td colspan="3">No user</td><br /> </tr><br /><% } %><br /> <br /></table><br /> </div><br /></div><br /><%- 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 the source code :
ubuntu@AcerXtimeline:~/hello_world$ node app.js
http://localhost:4300/customers<br />
The source will produce things like these:
Happy coding
转载请注明原文链接：Example of CRUD with Node.js & MySQL_MySQL
