Node Js Delete Record from MySQL Database Tutorial

In this tutorial, we are going to learn how to delete data records from the MySQL database through the Node js application.

We will show you how to use MySQL query and Express js API to delete and retrieve the records from the MySQL database.

To delete the record from MySQL, we need to set up a complete node app from scratch. We will also take the help of Express-EJS, MySQL, Express Session, Express Flash, and Body Parser modules.

MySQL is a popularly known relational database management system (RDBMS). Writing structured query language (SQL) with MySQL is easy, and it helps you store structured records, mainly used for building apps.

How to Delete Data Record from MySQL Database in Node Js

  • Step 1: Set Up Node App
  • Step 2: Install Essentials Packages
  • Step 3: Create MySQL Table
  • Step 4: Connect Node to MySQL Database
  • Step 5: Build Express Routes
  • Step 6: Remove Record from MySQL Db
  • Step 7: Configure Node Server
  • Step 8: Start Node Application

Set Up Node App

Type the provided command and run the command to generate the project directory.

mkdir node-astral

Enter inside the project directory:

cd node-astral

On the command prompt, paste the given command and create the package.json file.

npm init

Next, create app.js file, then register the file name into the package.json file.

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

Install Essentials Packages

Add the express js templating using the given command.

Ejs is a templating language/engine allows create HTML with plain javascript.

npx express --view=ejs

You also need to install the following packages:

npm install -g express-generator

npm install express-validator@5.3.0

npm install

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

Create MySQL Table

Start your MySQL server, and we are using MAMP; you may use any PHP server of your choice.

Then, head over to phpMyAdmin, open the SQL and run the given query to create the ’employees’ table.

CREATE TABLE `employees` (
  `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;

Connect Node to MySQL Database

To create MySQL database connection in Node, you need a db connection file, hence, create the db.js file.

Next, add the following code in the db.js file.

var mysql = require('mysql')

var connection = mysql.createConnection({
  host: 'localhost', // Define host name
  user: 'root', // Define database username
  password: '', // Define database password
  database: 'demo' // Define database name
})

connection.connect((err) => {
  if (err) {
    console.log('Database connection error :' + err)
    return
  }
  console.log('Connected to database')
})

module.exports = connection

Build Express Routes

Get into the routes/ folder, and look for the users.js; within this file, you have to create two routes.

One will fetch the data, and another will let you delete the MySQL data or record from the database via node js.

var express = require('express')

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

var router = express.Router()

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

// DELETE Record
router.get('/delete/(:id)', function (req, res, next) {
  var user = { id: req.params.id }

  connection.query(
    'DELETE FROM employees WHERE id = ' + req.params.id,
    user,
    function (err, result) {
      if (err) {
        req.flash('error', err)
        res.redirect('/')
      } else {
        req.flash('success', 'Record successfully deleted :' + req.params.id)
        res.redirect('/')
      }
    },
  )
})

module.exports = router

Remove Record from MySQL Db

Let’s move on to the views/ folder, and create the new file, name it emp-list.ejs.

Open this file, and ensure that you are adding the given code along with Bootstrap 5 UI to create the table for getting the data and removing from the MySQL database.

<!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"
    />
    <link rel="stylesheet" href="/stylesheets/style.css" />
  </head>
  <body>
    <table class="table mt-5">
      <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-danger" href="../delete/<%=data[i].id%>"
              >Delete</a
            >
          </td>
        </tr>
        <% } }else{ %>
        <tr>
          <td colspan="3">No record found</td>
        </tr>
        <% } %>
      </tbody>
    </table>
  </body>
</html>

Configure Node Server

To define the node server, make sure to update the app.js file with the provided code.

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('./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: 'abcd123456',
    resave: false,
    saveUninitialized: true,
    cookie: { maxAge: 60000 },
  }),
)

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

app.use('/', indexRouter)
app.use('/employees-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

Start Node Application

We will show you how to start the node server and fetch the data from database in node app.

Type the suggested command and press enter to run the app.

nodemon

You have to type this url to view the app:

http://localhost:3000

Node Js Delete Record from MySQL Database Tutorial

Summary

In this comprehensive tutorial, we have ascertained how to delete data from MySQL database in Node js application using third-party modules.

We described how to create express routes to fetch and delete the data from the MySQL database; we hope you liked this guide.