How to Delete Data from MySQL Database using Node Js

Last Updated on March 4, 2024 by Digamber in Node JS

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.

For removing data from the database, only such things are not enough; instead, we will need a couple of more packages, including mysql, express-generator, express-flash express-session, and body-parser and nodemon.

Modifying the data on the database is a process that involves nuances. We need a client from which we request the server to perform any action that manages the data based on the request.

To trigger a request to the server or database, we will be using Express js; with the help of Express, we will create a route that will delete the data in the database and update the SQL tables.

Node Js Remove Data from MySQL Database Example

• Step 1: Create Node App
• Step 2: Install NPM Modules
• Step 3: Create Table in Database
• Step 4: Database Connection
• Step 5: Create Route File
• Step 6: Delete Data from Database
• Step 7: Create Server File
• Step 8: Run Node Project

Create Node App

The mkdir command offers you to generate the folder or directory, run command to create the folder for project.

mkdir node-bin

Move into empty folder:

cd node-bin

A package.json file is very much needed to begin the node js development, hence run command to create the file.

npm init

Further, we have to make the app.js file, this file will be the main script which invokes the node app. Therefore, it should be added in scripts section in package.json file.

{
  "main": "app.js",
}

Install NPM Modules

We have to set up the EJS templates for creating Node js delete data functionality, hence run all the given commands one-by-one.

npx express --view=ejs

npm install -g express-generator

npm install express-validator@5.3.0

npm install

npm install express-flash mysql express-session nodemon body-parser

Create Table in Database

Head over to the PHPMyAdmin section; here, look for the SQL tab; within here, you have to run the given command to create the table in the database.

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

Database Connection

Make the database.js file, in this file we will add the database credentials. Ensure that you add the suggested code into the database.js file.

var mysql = require('mysql')

var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'test'
})

connection.connect((err) => {
  if (err) {
    console.log(err)
    return
  }
  console.log('Database connected')
})

module.exports = connection

Create Route File

Move inside the routes/ folder, in this directory you have to open the users.js file. Now, in this file you have to define the code for making the routes.

var express = require('express')

var connection = require('../database.js')

var router = express.Router()


router.get('/', function (req, res, next) {
  connection.query('SELECT * FROM users ORDER BY id desc', function (
    err,
    rows,
  ) {
    if (err) {
      req.flash(err)
      res.render('user-list', { data: '' })
    } else {
      res.render('user-list', { data: rows })
    }
  })
})

// DELETE Record
router.get('/remove/(: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) {
        req.flash('error', err)
        res.redirect('/')
      } else {
        req.flash('success', 'Data removed :' + req.params.id)
        res.redirect('/')
      }
    },
  )
})

module.exports = router

Delete Data from Database

To delete the data, go inside the views/ directory, here you have to make the user-list.ejs file. In this file you have to place the suggested code as given below.

<!DOCTYPE html>
<html>
  <head>
    <title><%= title %></title>
    <link
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
      rel="stylesheet"
    />
  </head>
  <body>
    <table class="table">
      <thead>
        <tr>
          <th scope="col">#Id</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><%= (i+1) %></th>
          <td><%= data[i].name%></td>
          <td><%= data[i].email%></td>
          <td>
            <a class="btn btn-dark" href="../remove/<%=data[i].id%>"
              >Delete</a
            >
          </td>
        </tr>
        <% } }else{ %>
        <tr>
          <td colspan="3">Data does not exist</td>
        </tr>
        <% } %>
      </tbody>
    </table>
  </body>
</html>

Create Server File

In the last segment, we will set up the main node js script, copy the given code into the app.js file.

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('./database')


var appRoutes = require('./routes/index')
var userRoutes = 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: 'ginni@123',
    resave: false,
    saveUninitialized: true,
    cookie: { maxAge: 60000 },
  }),
)

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

app.use('/', appRoutes)
app.use('/users-list', userRoutes)


app.use(function (req, res, next) {
  next(createError(404))
})

// error handler
app.use(function (err, req, res, next) {
  res.locals.message = err.message
  res.locals.error = req.app.get('env') === 'development' ? err : {}
  res.status(err.status || 500)
  res.render('error')
})

module.exports = app

Run Node Project

Lastly, you have to again run the given command to run the node application.

nodemon

Open the browser and access the app using the given url.

http://localhost:3000

Conclusion

In this thorough guide, we have found out how to quickly connect the not app to the MySQL database and remove the record from the MySQL database using Node js and a couple of other external dependencies.