How to Implement CRUD in Node.js With MySQL
In this post, we are going to create a simple CRUD application in Node.js with MySQL as the database. We are using EJS as the template engine.
Before get started with this tutorial: You need to have Node installed. Read my previous post for Node.js installation.
Step 1: Create index.js as main file and package.json file
package.json
{
"name": "curdnode",
"version": "1.0.0",
"description": "Create simple curd example in nodejs",
"main": "index.js",
"scripts": {
"start": "node index.js",
"test": "echo \"Error: no test specified\" && exit 1"
},
"author": "techsudhir",
"license": "ISC"
}
index.js
console.log('Welcome You');
Now open up your command line and run :
npm start
Output:
Welcome You
Stop the current server by hitting CTRL + C in the command line.
Step 2: Now install required dependency for CRUD application
a) Express dependency
npm install express --save
b) Path dependency
npm install path --save
c) Body Parser dependency
npm install body-parser --save
d) MySQL dependency
npm install mysql --save
e) EJS template
npm install ejs --save
Step 3: We are using http method to handling GET/POST request
var http = require("http");
Now use all dependency
var express = require('express');
var path = require('path');
var app = express();
var mysql = require('mysql');
var bodyParser = require('body-parser');
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.set('view engine', 'ejs');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "nodejs"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
Step 4: Now create port and run from browser
app.listen(3015, function () {
console.log('Example app listening on port 3015!');
});
Now Run in browser localhost:3015
CRUD - Implementation begain from here
Note: For view handling create folder views at root directory
Use GET method for GET type request handling
app.get('/', function (req, res) {
res.render('pages/index', {
title : 'Node Curd'
});
});
Inside views folder create pages folder. Inside pages folder create index.ejs file
views/pages/index.ejs
We are using EJS template engine
<!DOCTYPE html>
<html lang="en">
<head>
<!-- include elements head -->
<% include ../partials/head %>
</head>
<body class="container">
<header>
<!-- include elements head -->
<% include ../partials/header %>
</header>
<main>
<div class="jumbotron">
<h1>This is great</h1>
<p>Welcome to templating using EJS</p>
<p><%= tagline %></p>
<ul>
<% drinks.forEach(function(drink) { %>
<li><%= drink.name %> - <%= drink.drunkness %></li>
<% }); %>
</ul>
</div>
</main>
<footer>
<% include ../partials/footer %>
</footer>
</body>
</html>
views/partials/head.ejs
Partial view include in index file
<meta charset="UTF-8">
<title><%= title %></title>
<!-- CSS (load bootstrap from a CDN) -->
<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
<style>
body { padding-top:50px; }
</style>
views/partials/header.ejs
Partial view include in index file
<nav class="navbar navbar-default" role="navigation">
<div class="container-fluid">
<div class="navbar-header">
<a class="navbar-brand" href="#">
<span class="glyphicon glyphicon glyphicon-tree-deciduous"></span>
EJS Is Fun
</a>
<ul class="nav navbar-nav">
<li><a href="/">Home</a></li>
<li><a href="/about">About</a></li>
<li><a href="/customer">Customer</a></li>
<li><a href="/add-customer">Add New Customer</a></li>
</ul>
</div>
</div>
</nav>
views/partials/header.ejs
Partial view include in index file
<p class="text-center text-muted">© Copyright 2017 Appt Medical</p>
Node Create add method inside index.js file
app.get('/add-customer', function(req, res) {
res.render('pages/add-customer',{title : 'Add New Customer'});
});
Create its views
views/pages/add-customer.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<% include ../partials/head %>
</head>
<body class="container">
<header>
<% include ../partials/header %>
</header>
<main>
<div class="row">
<div class="col-sm-8">
<div class="well">
<form class="form-horizontal" method="POST">
<div class="form-group">
<label class="control-label col-sm-2" for="inputName">Name</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputName" placeholder="Name" name="name">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputEmail">Email</label>
<div class="col-sm-10">
<input type="email" class="form-control" id="inputEmail" placeholder="Email" name="email">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputPassword">Password</label>
<div class="col-sm-10">
<input type="password" class="form-control" id="inputPassword" placeholder="Password" name="password">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputAddress">Address</label>
<div class="col-sm-10">
<textarea class="form-control" rows="5" id="inputAddress" placeholder="Address" name="address"></textarea>
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputPhone">Phone</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputPhone" placeholder="Phone" name="phone">
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<button type="submit" class="btn btn-primary">Submit</button>
</div>
</div>
</form>
</div>
</div>
<div class="col-sm-4">
<div class="well">
<h3>Look I'm A Sidebar!</h3>
</div>
</div>
</div>
</main>
<footer>
<% include ../partials/footer %>
</footer>
</body>
</html>
Create POST method handle request and insert into database
app.post('/add-customer', function(request, response){
response.setHeader('Content-Type', 'application/json');
var users={
"name":request.body.name,
"email":request.body.email,
"password":request.body.password,
"address":request.body.address,
"phone":request.body.phone,
}
con.query('INSERT INTO customer SET ?',users, function (error, results, fields) {
if (error) {
console.log("error ocurred",error);
response.send({
"code":400,
"failed":"error ocurred"
})
}else{
console.log('The solution is: ', results);
return response.redirect('/customer');
}
});
});
Node Create edit method inside index.js file
app.get('/customer/:id',function(req,res) {
var customerId = req.params.id;
con.query('select * from customer where id = ?', customerId, function(err,result) {
res.render('pages/edit-customer',{title : 'Customer',customer:result});
});
});
Create its views
views/pages/edit-customer.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<% include ../partials/head %>
</head>
<body class="container">
<header>
<% include ../partials/header %>
</header>
<main>
<div class="row">
<div class="col-sm-8">
<div class="well">
<form class="form-horizontal" method="POST">
<div class="form-group">
<label class="control-label col-sm-2" for="inputName">Name</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputName" placeholder="Name" name="name" value="<%= customer[0].name %>">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputEmail">Email</label>
<div class="col-sm-10">
<input type="email" class="form-control" id="inputEmail" placeholder="Email" name="email" value="<%= customer[0].email %>">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputAddress">Address</label>
<div class="col-sm-10">
<textarea class="form-control" rows="5" id="inputAddress" placeholder="Address" name="address"><%= customer[0].address %></textarea>
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputPhone">Phone</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputPhone" placeholder="Phone" name="phone" value="<%= customer[0].phone %>">
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<button type="submit" class="btn btn-primary">Submit</button>
</div>
</div>
</form>
</div>
</div>
<div class="col-sm-4">
<div class="well">
<h3>Look I'm A Sidebar!</h3>
</div>
</div>
</div>
</main>
<footer>
<% include ../partials/footer %>
</footer>
</body>
</html>
Create POST method handle request and update into database
app.post('/customer/:id',function(req,res) {
var customerId = req.params.id;
res.setHeader('Content-Type', 'application/json');
var users={
"name":req.body.name,
"email":req.body.email,
"address":req.body.address,
"phone":req.body.phone,
}
console.log(users);
con.query(' UPDATE customer SET ? where id = ? ',[users,customerId], function (error, results, fields) {
if (error) {
console.log("error ocurred",error);
res.send({
"code":400,
"failed":"error ocurred"
})
}else{
console.log('The solution is: ', results);
return res.redirect('/customer');
}
});
});
Create method list all rows index.js file
app.get('/customer', function(req, res) {
con.query("SELECT * FROM customer", function(err, result) {
if(err){
throw err;
}
req.flash('success', 'Registration successfully');
res.locals.message = req.flash();
res.render('pages/customer',{title : 'Customer',customer:result});
});
});
Create its views
views/pages/customer.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<% include ../partials/head %>
</head>
<body class="container">
<header>
<% include ../partials/header %>
</header>
<main>
<div class="row">
<div class="col-sm-8">
<% if(locals.message){ %>
<div class="alert alert-success" role="alert">
<strong>Well done!</strong> <%=message.success%>
</div>
<% } %>
<div class="well">
<h3>Teacher Listing</h3>
<table>
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Address</th>
<th>Phone</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<% for(i=0;i<customer.length;i++){ %>
<tr>
<td><%= customer[i].name %> </td>
<td><%= customer[i].email %> </td>
<td><%= customer[i].address %> </td>
<td><%= customer[i].phone %> </td>
<td><a href="/customer/<%= customer[i].id %>">Edit</a>
<a href="/delete/<%= customer[i].id %>">Delete</a>
</td>
</tr>
<% } %>
</tbody>
</table>
</div>
</div>
</div>
</main>
<footer>
<% include ../partials/footer %>
</footer>
</body>
</html>
Create delete method inside index.js file
app.get('/delete/:id',function(req,res) {
var customerId = req.params.id;
con.query('DELETE FROM customer WHERE id = ?', customerId, function (err, result) {
if (err) throw err;
console.log('Deleted ' + result.affectedRows + ' rows');
}
);
return res.redirect('/customer');
});
In this post, we are going to create a simple CRUD application in Node.js with MySQL as the database. We are using EJS as the template engine.
Before get started with this tutorial: You need to have Node installed. Read my previous post for Node.js installation.
Step 1: Create index.js as main file and package.json file
package.json
{
"name": "curdnode",
"version": "1.0.0",
"description": "Create simple curd example in nodejs",
"main": "index.js",
"scripts": {
"start": "node index.js",
"test": "echo \"Error: no test specified\" && exit 1"
},
"author": "techsudhir",
"license": "ISC"
}
index.js
console.log('Welcome You');
Now open up your command line and run :
npm start
Output:
Welcome You
Stop the current server by hitting CTRL + C in the command line.
Step 2: Now install required dependency for CRUD application
a) Express dependency
npm install express --save
b) Path dependency
npm install path --save
c) Body Parser dependency
npm install body-parser --save
d) MySQL dependency
npm install mysql --save
e) EJS template
npm install ejs --save
Step 3: We are using http method to handling GET/POST request
var http = require("http");
Now use all dependency
var express = require('express');
var path = require('path');
var app = express();
var mysql = require('mysql');
var bodyParser = require('body-parser');
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.set('view engine', 'ejs');
var con = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "nodejs"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
Step 4: Now create port and run from browser
app.listen(3015, function () {
console.log('Example app listening on port 3015!');
});
Now Run in browser localhost:3015
CRUD - Implementation begain from here
Note: For view handling create folder views at root directory
Use GET method for GET type request handling
app.get('/', function (req, res) {
res.render('pages/index', {
title : 'Node Curd'
});
});
Inside views folder create pages folder. Inside pages folder create index.ejs file
views/pages/index.ejs
We are using EJS template engine
<!DOCTYPE html>
<html lang="en">
<head>
<!-- include elements head -->
<% include ../partials/head %>
</head>
<body class="container">
<header>
<!-- include elements head -->
<% include ../partials/header %>
</header>
<main>
<div class="jumbotron">
<h1>This is great</h1>
<p>Welcome to templating using EJS</p>
<p><%= tagline %></p>
<ul>
<% drinks.forEach(function(drink) { %>
<li><%= drink.name %> - <%= drink.drunkness %></li>
<% }); %>
</ul>
</div>
</main>
<footer>
<% include ../partials/footer %>
</footer>
</body>
</html>
views/partials/head.ejs
Partial view include in index file
<meta charset="UTF-8">
<title><%= title %></title>
<!-- CSS (load bootstrap from a CDN) -->
<link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
<style>
body { padding-top:50px; }
</style>
views/partials/header.ejs
Partial view include in index file
<nav class="navbar navbar-default" role="navigation">
<div class="container-fluid">
<div class="navbar-header">
<a class="navbar-brand" href="#">
<span class="glyphicon glyphicon glyphicon-tree-deciduous"></span>
EJS Is Fun
</a>
<ul class="nav navbar-nav">
<li><a href="/">Home</a></li>
<li><a href="/about">About</a></li>
<li><a href="/customer">Customer</a></li>
<li><a href="/add-customer">Add New Customer</a></li>
</ul>
</div>
</div>
</nav>
views/partials/header.ejs
Partial view include in index file
<p class="text-center text-muted">© Copyright 2017 Appt Medical</p>
Node Create add method inside index.js file
app.get('/add-customer', function(req, res) {
res.render('pages/add-customer',{title : 'Add New Customer'});
});
Create its views
views/pages/add-customer.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<% include ../partials/head %>
</head>
<body class="container">
<header>
<% include ../partials/header %>
</header>
<main>
<div class="row">
<div class="col-sm-8">
<div class="well">
<form class="form-horizontal" method="POST">
<div class="form-group">
<label class="control-label col-sm-2" for="inputName">Name</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputName" placeholder="Name" name="name">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputEmail">Email</label>
<div class="col-sm-10">
<input type="email" class="form-control" id="inputEmail" placeholder="Email" name="email">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputPassword">Password</label>
<div class="col-sm-10">
<input type="password" class="form-control" id="inputPassword" placeholder="Password" name="password">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputAddress">Address</label>
<div class="col-sm-10">
<textarea class="form-control" rows="5" id="inputAddress" placeholder="Address" name="address"></textarea>
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputPhone">Phone</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputPhone" placeholder="Phone" name="phone">
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<button type="submit" class="btn btn-primary">Submit</button>
</div>
</div>
</form>
</div>
</div>
<div class="col-sm-4">
<div class="well">
<h3>Look I'm A Sidebar!</h3>
</div>
</div>
</div>
</main>
<footer>
<% include ../partials/footer %>
</footer>
</body>
</html>
Create POST method handle request and insert into database
app.post('/add-customer', function(request, response){
response.setHeader('Content-Type', 'application/json');
var users={
"name":request.body.name,
"email":request.body.email,
"password":request.body.password,
"address":request.body.address,
"phone":request.body.phone,
}
con.query('INSERT INTO customer SET ?',users, function (error, results, fields) {
if (error) {
console.log("error ocurred",error);
response.send({
"code":400,
"failed":"error ocurred"
})
}else{
console.log('The solution is: ', results);
return response.redirect('/customer');
}
});
});
Node Create edit method inside index.js file
app.get('/customer/:id',function(req,res) {
var customerId = req.params.id;
con.query('select * from customer where id = ?', customerId, function(err,result) {
res.render('pages/edit-customer',{title : 'Customer',customer:result});
});
});
Create its views
views/pages/edit-customer.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<% include ../partials/head %>
</head>
<body class="container">
<header>
<% include ../partials/header %>
</header>
<main>
<div class="row">
<div class="col-sm-8">
<div class="well">
<form class="form-horizontal" method="POST">
<div class="form-group">
<label class="control-label col-sm-2" for="inputName">Name</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputName" placeholder="Name" name="name" value="<%= customer[0].name %>">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputEmail">Email</label>
<div class="col-sm-10">
<input type="email" class="form-control" id="inputEmail" placeholder="Email" name="email" value="<%= customer[0].email %>">
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputAddress">Address</label>
<div class="col-sm-10">
<textarea class="form-control" rows="5" id="inputAddress" placeholder="Address" name="address"><%= customer[0].address %></textarea>
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-2" for="inputPhone">Phone</label>
<div class="col-sm-10">
<input type="text" class="form-control" id="inputPhone" placeholder="Phone" name="phone" value="<%= customer[0].phone %>">
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-2 col-sm-10">
<button type="submit" class="btn btn-primary">Submit</button>
</div>
</div>
</form>
</div>
</div>
<div class="col-sm-4">
<div class="well">
<h3>Look I'm A Sidebar!</h3>
</div>
</div>
</div>
</main>
<footer>
<% include ../partials/footer %>
</footer>
</body>
</html>
Create POST method handle request and update into database
app.post('/customer/:id',function(req,res) {
var customerId = req.params.id;
res.setHeader('Content-Type', 'application/json');
var users={
"name":req.body.name,
"email":req.body.email,
"address":req.body.address,
"phone":req.body.phone,
}
console.log(users);
con.query(' UPDATE customer SET ? where id = ? ',[users,customerId], function (error, results, fields) {
if (error) {
console.log("error ocurred",error);
res.send({
"code":400,
"failed":"error ocurred"
})
}else{
console.log('The solution is: ', results);
return res.redirect('/customer');
}
});
});
Create method list all rows index.js file
app.get('/customer', function(req, res) {
con.query("SELECT * FROM customer", function(err, result) {
if(err){
throw err;
}
req.flash('success', 'Registration successfully');
res.locals.message = req.flash();
res.render('pages/customer',{title : 'Customer',customer:result});
});
});
Create its views
views/pages/customer.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<% include ../partials/head %>
</head>
<body class="container">
<header>
<% include ../partials/header %>
</header>
<main>
<div class="row">
<div class="col-sm-8">
<% if(locals.message){ %>
<div class="alert alert-success" role="alert">
<strong>Well done!</strong> <%=message.success%>
</div>
<% } %>
<div class="well">
<h3>Teacher Listing</h3>
<table>
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Address</th>
<th>Phone</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<% for(i=0;i<customer.length;i++){ %>
<tr>
<td><%= customer[i].name %> </td>
<td><%= customer[i].email %> </td>
<td><%= customer[i].address %> </td>
<td><%= customer[i].phone %> </td>
<td><a href="/customer/<%= customer[i].id %>">Edit</a>
<a href="/delete/<%= customer[i].id %>">Delete</a>
</td>
</tr>
<% } %>
</tbody>
</table>
</div>
</div>
</div>
</main>
<footer>
<% include ../partials/footer %>
</footer>
</body>
</html>
Create delete method inside index.js file
app.get('/delete/:id',function(req,res) {
var customerId = req.params.id;
con.query('DELETE FROM customer WHERE id = ?', customerId, function (err, result) {
if (err) throw err;
console.log('Deleted ' + result.affectedRows + ' rows');
}
);
return res.redirect('/customer');
});
No comments:
Post a Comment