Skip to main content

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');

});
Labels:

Comments

Post a Comment

Popular posts from this blog

A Guide to UTF-8 for PHP and MySQL

Data Encoding: A Guide to UTF-8 for PHP and MySQL As a MySQL or PHP developer, once you step beyond the comfortable confines of English-only character sets, you quickly find yourself entangled in the wonderfully wacky world of UTF-8. On a previous job, we began running into data encoding issues when displaying bios of artists from all over the world. It soon became apparent that there were problems with the stored data, as sometimes the data was correctly encoded and sometimes it was not. This led programmers to implement a hodge-podge of patches, sometimes with JavaScript, sometimes with HTML charset meta tags, sometimes with PHP, and soon. Soon, we ended up with a list of 600,000 artist bios with double- or triple encoded information, with data being stored in different ways depending on who programmed the feature or implemented the patch. A classical technical rat’s nest.Indeed, navigating through UTF-8 related data encoding issues can be a frustrating and hair-pul...
Post a Comment
Read more

How To Create Shortcodes In WordPress

We can create own shortcode by using its predified hooks add_shortcode( 'hello-world', 'techsudhir_hello_world_shortcode' ); 1. Write the Shortcode Function Write a function with a unique name, which will execute the code you’d like the shortcode to trigger: function techsudhir_hello_world_shortcode() {    return 'Hello world!'; } Example: [hello-world] If we were to use this function normally, it would return Hello world! as a string 2. Shortcode function with parameters function techsudhir_hello_world_shortcode( $atts ) {    $a = shortcode_atts( array(       'name' => 'world'    ), $atts );    return 'Hello ' . $a['name'] . !'; } Example: [hello-world name="Sudhir"] You can also call shortcode function in PHP using do_shortcode function Example: do_shortcode('[hello-world]');
Post a Comment
Read more

How to replace plain URLs with links

Here we will explain how to replace Urls with links from string Using PHP $string ='Rajiv Uttamchandani is an astrophysicist, human rights activist, and entrepreneur. Academy, a nonprofit organization dedicated to providing a robust technology-centered education program for refugee and displaced youth around the world.  CNN Interview - https://www.youtube.com/watch?v=EtTwGke6Jtg   CNN Interview - https://www.youtube.com/watch?v=g7pRTAppsCc&feature=youtu.be'; $string = preg_replace('@(https?://([-\w\.]+)+(:\d+)?(/([\w/_\.%-=#]*(\?\S+)?)?)?)@', '<a href="$1">$1</a>', $string); Using Javascript <script> function linkify(inputText) {     var replacedText, replacePattern1, replacePattern2, replacePattern3;     //URLs starting with http://, https://, or ftp://     replacePattern1 = /(\b(https?|ftp):\/\/[-A-Z0-9+&@#\/%?=~_|!:,.;]*[-A-Z0-9+&@#\/%=~_|])/gim;     replacedText = inputT...
1 comment
Read more