Node JS CRUD operation with express and MySql database

Hello developers, Today we will discuss about node js crud (create, update, read, delete) operation. We will use express node js framework and mysql for database. Express is the most popular framework for node js and other side mysql is also popular database. So let’s get started with the node js crud operation with express and mysql database tutorial

We will use ejs templating engine. It is very simple and easy to understand for everyone.

Let’s start create CRUD with Node.Js , Express, MySQL

Create Express js Project

Use the below command and create your express project with name expressfirst

express --view=ejs expressfirst

After successfully created expressfirst folder in your system. Next follow the below commands and install node js in your project :

cd expressfirst

npm install

Next we need to install some required pacakges, go to terminal and use the below commands :

 npm install express-flash --save
npm install express-session --save
npm install express-validator --save
npm install method-override --save
npm install mysql --save
express-flash

Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.
In this node js mysql crud tutorial express flash is used to display a warning, error and information message

express-session

Express-session is used to made a session as like in PHP. In this node js mysql crud tutorial, session is needed as the express requirement of express-flash.

express-validator

Express validator is used to validate form data it is easy to use. express-validator highly effective and efficient way to accelerate the creation of applications.

method-override

NPM is used to run a DELETE and PUT method from an HTML form. In several web browsers only support GET and POST methods.

MySQL

Driver to connect node.js with MySQL

Database Connection with Mysql

Next we need to create one folder name lib and create a new file name db.js inside this folder. We will connect node js to mysql using this file

lib/db.js
 var mysql=require('mysql');
var connection=mysql.createConnection({
host:'localhost',
user:'your username',
password:'your password',
database:'your database name'
});
connection.connect(function(error){
if(!!error){
console.log(error);
}else{
console.log('Connected!:)');
}
});
module.exports = connection;

Changes in app.js

We need to some changes in app.js file. go to app.js file and put some code here :

 var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var expressValidator = require('express-validator');
var flash = require('express-flash');
var session = require('express-session');
var bodyParser = require('body-parser');

var mysql = require('mysql');
var connection = require('./lib/db');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
var customersRouter = require('./routes/customers');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use(session({
secret: '123456cat',
resave: false,
saveUninitialized: true,
cookie: { maxAge: 60000 }
}))

app.use(flash());
app.use(expressValidator());

app.use('/', indexRouter);
app.use('/users', usersRouter);
app.use('/customers', customersRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
// set locals, only providing error in development
res.locals.message = err.message;
res.locals.error = req.app.get('env') === 'development' ? err : {};
// render the error page
res.status(err.status || 500);
res.render('error');
});
module.exports = app;

Create Route

Next We need to create one route file name customers.js inside routes folder. After created this file, We will implement crud logic in this file. Go to routes/customers.js and use the below code :

var express = require('express');
var router = express.Router();
var connection  = require('../lib/db');


/* GET home page. */
router.get('/', function(req, res, next) {
	 
 connection.query('SELECT * FROM customers ORDER BY id desc',function(err,rows)     {

        if(err){
         req.flash('error', err); 
         res.render('customers',{page_title:"Customers - Node.js",data:''});   
        }else{
           
            res.render('customers',{page_title:"Customers - Node.js",data:rows});
        }
                           
         });
       
    });


// SHOW ADD USER FORM
router.get('/add', function(req, res, next){    
    // render to views/user/add.ejs
    res.render('customers/add', {
        title: 'Add New Customers',
        name: '',
        email: ''        
    })
})

// ADD NEW USER POST ACTION
router.post('/add', function(req, res, next){    
    req.assert('name', 'Name is required').notEmpty()           //Validate name
    req.assert('email', 'A valid email is required').isEmail()  //Validate email
 
    var errors = req.validationErrors()
    
    if( !errors ) {   //No errors were found.  Passed Validation!
        
    
        var user = {
            name: req.sanitize('name').escape().trim(),
            email: req.sanitize('email').escape().trim()
        }
        
     connection.query('INSERT INTO customers SET ?', user, function(err, result) {
                //if(err) throw err
                if (err) {
                    req.flash('error', err)
                    
                    // render to views/user/add.ejs
                    res.render('customers/add', {
                        title: 'Add New Customer',
                        name: user.name,
                        email: user.email                    
                    })
                } else {                
                    req.flash('success', 'Data added successfully!');
                    res.redirect('/customers');
                }
            })
    }
    else {   //Display errors to user
        var error_msg = ''
        errors.forEach(function(error) {
            error_msg += error.msg + '<br>'
        })                
        req.flash('error', error_msg)        
        
        /**
         * Using req.body.name 
         * because req.param('name') is deprecated
         */ 
        res.render('customers/add', { 
            title: 'Add New Customer',
            name: req.body.name,
            email: req.body.email
        })
    }
})

// SHOW EDIT USER FORM
router.get('/edit/(:id)', function(req, res, next){
  
connection.query('SELECT * FROM customers WHERE id = ' + req.params.id, function(err, rows, fields) {
            if(err) throw err
            
            // if user not found
            if (rows.length <= 0) {
                req.flash('error', 'Customers not found with id = ' + req.params.id)
                res.redirect('/customers')
            }
            else { // if user found
                // render to views/user/edit.ejs template file
                res.render('customers/edit', {
                    title: 'Edit Customer', 
                    //data: rows[0],
                    id: rows[0].id,
                    name: rows[0].name,
                    email: rows[0].email                    
                })
            }            
        })
 
})

// EDIT USER POST ACTION
router.post('/update/:id', function(req, res, next) {
    req.assert('name', 'Name is required').notEmpty()           //Validate nam           //Validate age
    req.assert('email', 'A valid email is required').isEmail()  //Validate email
 
    var errors = req.validationErrors()
    
    if( !errors ) {   

        var user = {
            name: req.sanitize('name').escape().trim(),
            email: req.sanitize('email').escape().trim()
        }
        
connection.query('UPDATE customers SET ? WHERE id = ' + req.params.id, user, function(err, result) {
                //if(err) throw err
                if (err) {
                    req.flash('error', err)
                    
                    // render to views/user/add.ejs
                    res.render('customers/edit', {
                        title: 'Edit Customer',
                        id: req.params.id,
                        name: req.body.name,
                        email: req.body.email
                    })
                } else {
                    req.flash('success', 'Data updated successfully!');
                    res.redirect('/customers');
                }
            })
        
    }
    else {   //Display errors to user
        var error_msg = ''
        errors.forEach(function(error) {
            error_msg += error.msg + '<br>'
        })
        req.flash('error', error_msg)
        
        /**
         * Using req.body.name 
         * because req.param('name') is deprecated
         */ 
        res.render('customers/edit', { 
            title: 'Edit Customer',            
            id: req.params.id, 
            name: req.body.name,
            email: req.body.email
        })
    }
})
	  
// DELETE USER
router.get('/delete/(:id)', function(req, res, next) {
    var user = { id: req.params.id }
    
connection.query('DELETE FROM customers WHERE id = ' + req.params.id, user, function(err, result) {
            //if(err) throw err
            if (err) {
                req.flash('error', err)
                // redirect to users list page
                res.redirect('/customers')
            } else {
                req.flash('success', 'Customer deleted successfully! id = ' + req.params.id)
                // redirect to users list page
                res.redirect('/customers')
            }
        })
   })


module.exports = router;

Create views

First we will create one foleder name customers inside the views folder.
Next we need to create three views file name add.ejs, edit.ejs and index.ejs. We will create three view files inside the views/customers folder.

Create first file index.ejs

Index.ejs file, we will display the list of customers.

<!DOCTYPE html>
<html>
<head>
  <title>Customers</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
  <div>
    <a href="/" class="btn btn-primary ml-3">Home</a>  
    <a href="/customers/add" class="btn btn-secondary ml-3">New Customer</a> 
    <a href="/customers" class="btn btn-info ml-3">Customer List</a>
</div>    
<!--   <% if (messages.error) { %>
  <p style="color:red"><%- messages.error %></p>
<% } %> -->
 
<% if (messages.success) { %>
    <p class="alert alert-success mt-4"><%- messages.success %></p>
<% } %>  
<br>
  <table class="table">
<thead>
  <tr>
    <th scope="col">#</th>
    <th scope="col">Name</th>
    <th scope="col">Email</th>
    <th width="200px">Action</th>

  </tr>
</thead>
<tbody>
  <% if(data.length){

  for(var i = 0; i< data.length; i++) {%>  
  <tr>
    <th scope="row"><%= (i+1) %></th>
    <td><%= data[i].name%></td>
    <td><%= data[i].email%></td>
    <td>
    <a class="btn btn-success edit" href="../customers/edit/<%=data[i].id%>">Edit</a>                       
    <a class="btn btn-danger delete" onclick="return alert('Are You sure?')" href="../customers/delete/<%=data[i].id%>">Delete</a>                       
   </td>
  </tr>
  <% }
          
   }else{ %>
       <tr>
          <td colspan="3">No user</td>
       </tr>
    <% } %>    
 
</tbody>
</table>
</body>
</html>

Create second file name add.ejs

Add.ejs file, we will create form for sending to data in database.

<!DOCTYPE html>
<html>
<head>
  <title>Customers</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
  <% if (messages.error) { %>
  <p style="color:red"><%- messages.error %></p>
<% } %>
 
<% if (messages.success) { %>
    <p style="color:green"><%- messages.success %></p>
<% } %>
<form action="/customers/add" method="post" name="form1">
<div class="form-group">
  <label for="exampleInputPassword1">Name</label>
  <input type="text" class="form-control" name="name" id="name" value="" placeholder="Name">
</div>
<div class="form-group">
  <label for="exampleInputEmail1">Email address</label>
  <input type="email" name="email" class="form-control" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="">
</div>

<input type="submit" class="btn btn-primary" value="Add">
</form>
</body>
</html>

Create third file name edit.ejs

Next create last file edit.ejs, we will to edit data in this form.

<!DOCTYPE html>
<html>
<head>
  <title>Customers</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
<form action="/customers/update/<%= id %>" method="post" name="form1">
<div class="form-group">
  <label for="exampleInputPassword1">Name</label>
  <input type="text" class="form-control" name="name" id="name" value="<%= name %>" placeholder="Name">
</div>
<div class="form-group">
  <label for="exampleInputEmail1">Email address</label>
  <input type="email" class="form-control" name="email" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="<%= email %>">
 
</div>

<button type="submit" class="btn btn-info">Update</button>
</form>
</body>
</html>

Test Node js Crud app

run the below command

npm start

after run this command open your browser and hit

http://127.0.0.1:3000/customers

Conclusion

In this node express js crud tutorial – We have created node js crud (create, read, update, delete) application with mysql database. We have also successfully install node js framework express with use ejs templating engine.

node js mysql crud
node js mysql add crud
node js crud

34 Comments

  1. Martin said:

    I simply could not leave your website prior to suggesting that I really enjoyed the standard info a
    person provide in your guests? Is going to be back regularly
    to inspect new posts

    my web page :: cheap flights

    January 31, 2021
    Reply
  2. Jerome said:

    I was wondering if you ever considered changing
    the structure of your site? Its very well written; I love what youve got to
    say. But maybe you could a little more in the way of content so people could connect with it better.

    Youve got an awful lot of text for only having 1 or
    2 images. Maybe you could space it out better?

    Feel free to visit my webpage: cheap flights

    January 31, 2021
    Reply
  3. Carrol said:

    Good day! Do you use Twitter? I’d like to follow you
    if that would be okay. I’m undoubtedly enjoying your blog and
    look forward to new updates.

    Here is my web site: cheap flights

    January 31, 2021
    Reply
  4. Maryann said:

    Hi! I know this is somewhat off topic but I was wondering which blog platform are you
    using for this website? I’m getting tired of WordPress because I’ve had issues with hackers and I’m looking at
    alternatives for another platform. I would be great if you could point me in the direction of
    a good platform.

    Feel free to visit my web-site :: cheap flights

    February 1, 2021
    Reply
  5. Deanne said:

    Hey there this is kind of of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually
    code with HTML. I’m starting a blog soon but have no coding
    skills so I wanted to get advice from someone with experience.
    Any help would be greatly appreciated!

    Feel free to surf to my page – cheap flights

    February 1, 2021
    Reply
  6. Bernd said:

    I think the admin of this web site is really working hard in favor of his web site, since here every information is quality based stuff.

    My website – cheap flights [tinyurl.com]

    February 2, 2021
    Reply
  7. Shantell said:

    We absolutely love your blog and find a lot of your post’s to
    be what precisely I’m looking for. Would you offer
    guest writers to write content for you? I wouldn’t mind creating a post or
    elaborating on most of the subjects you write about here.
    Again, awesome site!

    my site :: cheap flights

    February 2, 2021
    Reply
  8. Theron said:

    Howdy! Someone in my Myspace group shared this website with us so
    I came to look it over. I’m definitely loving the information.
    I’m book-marking and will be tweeting this to my followers!
    Exceptional blog and brilliant style and design.

    Here is my web site – cheap flights (tinyurl.com)

    February 3, 2021
    Reply
  9. Jbnbbluch said:

    shoppers pharmacy cholesterol Cialis Oral Jelly (Orange)

    February 4, 2021
    Reply
  10. Labxguany said:

    30 tablet free trial cialis 2.5mg of cialis cialis free trials

    February 10, 2021
    Reply
  11. NncsGeste said:

    dapoxetine used with viagra viagra a 100mg price for viagra

    February 11, 2021
    Reply
  12. Sandra said:

    It’s amazing for me to have a site, which is good in support of my experience.
    thanks admin

    My web page … cheap flights (tinyurl.com)

    February 12, 2021
    Reply
  13. Ahbzguany said:

    payday advance laws in michigan fca payday loan cap cash advance places kitchener

    February 13, 2021
    Reply
  14. Patsy said:

    Sarkari Result Up Board 2019 Bsc 1st Year A Short History Of Nearly Everything Chapter 4 Summary

    Review my web blog books

    February 16, 2021
    Reply
  15. Donaldcep said:

    Very good written text.

    free robux

    February 17, 2021
    Reply
  16. Earl said:

    Maurice Sendak Foundation Beowulf Epic Poem Original

    Look at my web blog; book epub

    February 17, 2021
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *