CRUD Operation using Node js Express MySQL

Simple crud application using node js express and mysql; In this tutorial, i am going to show you to create crud operation app using node js express with MySQL and Bootstrap.

CRUD is an acronym for Create, Read, Update, and Delete. CRUD operations are basic data manipulation for database. Though this tutorial; you can create a simple Node js MySQL CRUD application to perform all these operations on a MySQL database table at one place in node js express app.

Crud operation in node js using express MySQL example; i will create a simple add, edit and list pages into node js express app and then fetch and display data from MySQL database into this node js express app pages.

Node.js Express + MySQL + CRUD + Example

  • Step 1 – Create Node js Express App
  • Step 2 – Install Required Node Modules
  • Step 3 – Connect App to Database
  • Step 4 – Import Install Node Modules
  • Step 5 – Create CRUD Routes
  • Step 6 – Create CRUD views
  • Step 7 – Test Crud Operation in Node js with MySQL

Step 1 – Create Node js Express App

Execute the following command on cmd to create node js express app directory:

express --view=ejs expressfirst

Execute the following command on cmd to install node js express:

cd expressfirst

npm install

Step 2 – Install Required Node Modules

Install some required node modules; so execute the following command on terminal to install into your app:

 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

Step 3 – Connect App to Database

Visit your node js app root directory and create new file name database.js and add the following code into it:

 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; 

Step 4 – Import Install Node Modules

Import above installed node modules into app.js file; so visit your app root directory and open app.js. Then add the following code into it;

 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;

Step 5 – Create CRUD Routes

Create CRUD routes; so visit your app root/routes directory and create one route file name customers.js. Then add the following routes into it; as shown below:

var express = require('express');
var router = express.Router();
var connection  = require('../database');
/* 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;

Step 6 – Create CRUD views

Create CRUD views; so visit your app root/views/ directory and create customers directory into it. Then create the following views file into customers directory:

  • Index.ejs
  • Add.ejs
  • Edit.ejs

Visit views/customers/ directory and open Index.ejs file. And add the following code into it:

<!DOCTYPE html>
<html>
<head>
  <title>node.js express mysql crud operation example - laratutorials.com</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>

Visit views/customers/ directory and open add.ejs file. And add the following code into it:

<!DOCTYPE html>
<html>
<head>
  <title>crud operation using nodejs expressjs mysql</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>

Visit views/customers/ directory and open edit.ejs file. And add the following code into it:

<!DOCTYPE html>
<html>
<head>
  <title>crud operation using nodejs expressjs mysql</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>

Step 7 – Test Crud Operation in Node js with MySQL

Execute the following command on terminal to start crud operation in node js with mysql app and the hit following url on browser to test this 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

Recommended Node JS Tutorials

3 Comments

  1. Vinicius said:

    Hello, this example helped me clarify some doubts I have.
    I will try to implement authentication using passaport.
    Do you have any tips for using passaport?
    Congratulations!

    April 23, 2019
    Reply
  2. Paejan said:

    Hi..
    Nice tutorial…
    Now i wonder how to integrate this application with frontend framework like vue, Nuxt etc…

    Thanks

    September 11, 2019
    Reply
  3. ADELEKE Megtes said:

    This is the best tutorial I have ever read through. It helps a lot. Now I know how to implement pooling

    August 8, 2021
    Reply

Leave a Reply

Your email address will not be published.