Node.js Express CRUD Example with MySQL Example

CRUD operations are a basic fundamental need of any application. In this post, you will learn how to create CRUD (Create, Read, Update Delete) operations application with MySQL databases in Node Express JS.

Node.js Express CRUD Example with MySQL

Here are steps:

Step 1 – Set up Node Express JS App

Start your cmd or terminal window and run express --view=ejs expressfirst to create node js express application:

express --view=ejs expressfirst

To install npm into your node express application by using npm install:

cd expressfirst

npm install

Step 2 – Install Required Modules

To install flash, validator, session, override and MySQL module in node js express crud application by running the following command on cmd or terminal window:

 npm install express-flash --save
 npm install express-session --save
 npm install [email protected]
 npm install method-override --save
 npm install mysql --save

Step 3 – Connect to Application to Database

To create a directory or folder name lib and inside it create new file db.js, which will use to connect mysql database to your node express application, and you can use this code for that:

 var mysql=require('mysql');
 var connection=mysql.createConnection({
   host:'localhost',
   user:'root',
   password:'',
   database:'crud'
 });
connection.connect(function(error){
   if(!!error){
     console.log(error);
   }else{
     console.log('Connected!:)');
   }
 });
module.exports = connection; 

And create the table in the database using the following query, if you have already done this, then proceed to the next step:

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Step 4 – Import Modules in App.js

Navigate to your app root directory and open app.js, and then import the following modules 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');
 });
// port must be set to 3000 because incoming http requests are routed from port 80 to port 8080
app.listen(3000, function () {
    console.log('Node app is running on port 3000');
});
 module.exports = app;

Step 5 – Create CRUD Routes

Navigate to your application routes directory and create the customers.js routes file inside this directory, and then create the crud operation routes in it; You can create routes like the following:

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 Customer 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 Customer 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 Customer 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 Customer 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 Customer
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

Navigate to the Views directory and create a directory named Customers, and then create CRUD views inside Customers directory named add.ejs, edit.ejs and Index.ejs.

The index.ejs file will display the list of customers with add, delete and edit buttons:

<!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>

The add.ejs file will display add customers 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>
  <% 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>

The edit.js file will update customer information:

<!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>

Step 7 – Test This Application

Run the following command to start application server:

run the below command

npm start

Open your browser test this application using the following route URL:

http://127.0.0.1:3000/customers

Node js express crud example with MySQL will look like:

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

Conclusion

That’s it; you have learned how to create crud (create, read, update, delete) application with mysql database in node express js.

AuthorDevendra Dode

Greetings, I'm Devendra Dode, a full-stack developer, entrepreneur, and the proud owner of Tutsmake.com. My passion lies in crafting informative tutorials and offering valuable tips to assist fellow developers on their coding journey. Within my content, I cover a spectrum of technologies, including PHP, Python, JavaScript, jQuery, Laravel, Livewire, CodeIgniter, Node.js, Express.js, Vue.js, Angular.js, React.js, MySQL, MongoDB, REST APIs, Windows, XAMPP, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL, and Bootstrap. Whether you're starting out or looking for advanced examples, I provide step-by-step guides and practical demonstrations to make your learning experience seamless. Let's explore the diverse realms of coding together.

6 replies to Node.js Express CRUD Example with MySQL Example

  1. 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!

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

    Thanks

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

  4. This node js express crud example with mysql will very helpful for creating a simple crud app in node js express using mysql with validation.

    Thanks

  5. This helped me allot in solving my queries ..

    • It’s my pleasure! 🙂

Leave a Reply

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