Skip to content

All topics  /  Node.js

How to Delete Data from MySQL Database using Node Js?

Node.js ·

Teams applying “How to Delete Data from MySQL Database using Node Js?” in a production project can use the product resource to record implementation, review and debugging time, then compare that effort with tests and shipped functionality instead of treating activity as performance by itself.

Before copying the example into a live application, confirm version-specific behaviour with the official MySQL website and test it against the project’s actual database and runtime.

Hello Friends,

Node js delete data from MySQL database tutorial; Throughout this guide, you will ascertain how to remove records from MySQL database from Node js platform.

To connect MySQL to Node js, we will install and use the MySQL package in the Node js app. Furthermore, we will show you how to set up a Node js application from scratch eloquently.

Step 1: Create Node Express js App


Execute the following command on the terminal to create the node js app:

mkdir my-app
cd my-app
npm init -y

Step 2: Create Table in MySQL Database and Connect App to DB

Execute the following SQL query to create a table in your database:

CREATE TABLE `users` (
    `id` int(11) NOT NULL,
    `name` varchar(100) NOT NULL,
    `email` varchar(100) NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Create a database.js file and add the following code into it to connect your app to the database:

var mysql = require('mysql');
var conn = mysql.createConnection({
    host: 'localhost', // Replace with your host name
    user: 'root',      // Replace with your database username
    password: '',      // Replace with your database password
    database: 'my-node' // // Replace with your database Name
}); 

 
conn.connect(function(err) {
    if (err) throw err;
    console.log('Database is connected successfully !');
});
module.exports = conn;

Step 3: Install express flash ejs body-parser mysql Modules

Execute the following command on the terminal to express flash ejs body-parser MySQL dependencies :

npm install -g express-generator
npx express --view=ejs
npm install
npm install express-flash --save
npm install express-session --save
npm install body-parser --save
npm install mysql --save

body-parser – Node.js request body parsing middleware which parses the incoming request body before your handlers, and makes it available under req.body property. In other words, it simplifies incoming request.

Express-Flash – Flash Messages for your Express Application. Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.

Express-Session – Express-session – an HTTP server-side framework used to create and manage a session middleware.

Express-EJS – EJS is a simple templating language which is used to generate HTML markup with plain JavaScript. It also helps to embed JavaScript to HTML pages

Mysql – an open-source relational database management system (RDBMS).

Step 4: Create HTML Markup For List

Create an HTML list; visit the views directory and create a list.ejs file inside it. Then add the following code into it:

<!DOCTYPE html>
<html>
    <head>
        <title>How to Delete Data from MySQL Database using Node Js? - Nicesnippets.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>  
        <!--   <% 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="../users/delete/<%=data[i].id%>">Delete</a>                                            
                    </td>
                </tr>
                <% }
                }else{ %>
                <tr>
                    <td colspan="3">No user</td>
                </tr>
                <% } %>
            </tbody>
        </table>
    </body>
</html>

Step 5: Import Modules in App.js and Create Routes

Visit your app root directory and import express flash session body-parser MySQL dependencies in app.js; as shown below:

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 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('/list', usersRouter);
// 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;

Then visit routes/ directory and open the users.js file and add a list and delete routes into it; as shown below:

var express = require('express');
var router = express.Router();
var connection  = require('../database.js');

 
/* GET home page. */
router.get('/', function(req, res, next) {
 connection.query('SELECT * FROM users ORDER BY id desc',function(err,rows)     {
        if(err){
            req.flash('error', err); 
            res.render('list',{page_title:"Users List - Node.js",data:''});   
        }else{
            res.render('list',{page_title:"Users List - Node.js",data:rows});
        }
    });
});

 
// DELETE USER
router.get('/delete/(:id)', function(req, res, next) {
    var user = { id: req.params.id }
    connection.query('DELETE FROM users 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('/')
        } else {
            req.flash('success', 'User has been deleted successfully! id = ' + req.params.id)
            // redirect to users list page
            res.redirect('/')
        }
    });
});

 
module.exports = router;

The following routes will fetch data into the MySQL database and render with the list. ejs file.

Step 6: Start App Server

You can use the following command to start uploading images in MySQL using node js express app server:

//run the below command
npm start

After running this command open your browser and hit 

http://127.0.0.1:3000/

More on this topic