How to Delete Data from MySQL Database using Node Js

Node js express remove data MySQL database; In this tutorial, you will learn how to delete data from MySQL database using node js express app.

Note that :- This tutorial will create a simple HTML list table using bootstrap 4 library and add delete button on list. Then create routes for fetch, display, and delete data into MySQL database in node js express app and as well as and import installed modules in app.js file.

How to Delete Data from MySQL Database using Node Js

Deleting data from a MySQL database is a common operation in web applications. Here, you will explore step by step on how to delete data from a MySQL database using Node.js and the Express.js framework:

• Step 1 – Create Node Express js App
• Step 2 – Create Table in MySQL Database and Connect App to DB
• Step 3 – Install express flash ejs body-parser mysql Modules
• Step 4 – Create HTML Markup Form
• Step 5 – Import Modules in App.js and Create Routes
  • Import Installed Module
  • Create List Routes
  • Create Delete Route
• Step 6 – Start App Server

Step 1 – Create Node Express js App

Execute the following command on terminal to create 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 into 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 database.js file and add the following code into it to connect your app to 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

These are Node.js commands that install various packages and libraries required to build a web application using the Express framework and the EJS view engine.

1. npm install -g express-generator: This command installs the Express application generator globally on your system. The generator is used to create a new Express application with a basic directory structure, pre-configured middleware, and a set of default routes.
2. npx express --view=ejs: This command creates a new Express application with EJS as the default view engine. The npx command is used to run the express command from the locally installed express-generator package. The --view=ejs option specifies the view engine to be used.
3. npm install: This command installs all the required dependencies specified in the package.json file of the Express application.
4. npm install express-flash --save: This command installs the express-flash package and saves it as a dependency in the package.json file. The express-flash package is used to display flash messages in the Express application.
5. npm install express-session --save: This command installs the express-session package and saves it as a dependency in the package.json file. The express-session package is used to manage user sessions in the Express application.
6. npm install body-parser --save: This command installs the body-parser package and saves it as a dependency in the package.json file. The body-parser package is used to parse incoming request bodies in the Express application.
7. npm install mysql --save: This command installs the mysql package and saves it as a dependency in the package.json file. The mysql package is used to connect to a MySQL database from the Express application.

Step 4 – Create HTML Markup For List

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

<!DOCTYPE html>
<html>
<head>
  <title>how to fetch data from database in node js and display in html</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 users.js file and add 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 mysql database and render with list.ejs file.

Step 6 – Start App Server

You can use the following command to start upload image in mysql using node js express app server:

//run the below command

npm start

after run this command open your browser and hit 

http://127.0.0.1:3000/

Conclusion

Node js express delete data MySQL database; In this tutorial, you have learned how to delete data from MySQL database using node js express app.

Recommended Node JS Tutorials