Thursday, 28 December 2017

How to Implement CRUD in Node js With MySQL

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 %>&nbsp;</td>  
<td><%=  customer[i].email %>&nbsp;</td>
<td><%=  customer[i].address %>&nbsp;</td>
<td><%=  customer[i].phone %>&nbsp;</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');

});

0 comments:

Post a Comment