To delete data from mysql database using node js express; Simply connect to node js app to mysql db and create a delete data route and use DELETE FROM table WHERE id = ?; sql query to delete it.
This tutorial will create a simple HTML list table using bootstrap 4 library and add delete button on list. Then create routes for fetching, displaying, and deleting data by id,name,etc into MySQL database in node js express app and as well and import installed modules in app.js file.
How to Delete Data from MySQL Database using Node Js
Steps to delete data from a MySQL database using Node.js and the Express.js framework:
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 AUTO_INCREMENT, `name` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
To connect node js express app to database; Simply create database.js file and add the following code into it:
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
To install some required modules; Simply run the following command on the cmd or terminal to install express flash ejs body-parser mysql modules:
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.
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.npx express --view=ejs: This command creates a new Express application with EJS as the default view engine. Thenpxcommand is used to run theexpresscommand from the locally installedexpress-generatorpackage. The--view=ejsoption specifies the view engine to be used.npm install: This command installs all the required dependencies specified in thepackage.jsonfile of the Express application.npm install express-flash --save: This command installs theexpress-flashpackage and saves it as a dependency in thepackage.jsonfile. Theexpress-flashpackage is used to display flash messages in the Express application.npm install express-session --save: This command installs theexpress-sessionpackage and saves it as a dependency in thepackage.jsonfile. Theexpress-sessionpackage is used to manage user sessions in the Express application.npm install body-parser --save: This command installs thebody-parserpackage and saves it as a dependency in thepackage.jsonfile. Thebody-parserpackage is used to parse incoming request bodies in the Express application.npm install mysql --save: This command installs themysqlpackage and saves it as a dependency in thepackage.jsonfile. Themysqlpackage is used to connect to a MySQL database from the Express application.
Step 4 – Create HTML Markup For List
To create html list view with delete buttion; Simply navigate to views directory and create list.ejs file inside it and 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="list/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
To import express flash session body-parser mysql modules in app.js; Just open app.js from root directory of project and add import modules like following:
var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var flash = require('express-flash');
var session = require('express-session');
var bodyParser = require('body-parser');
var mysql = require('mysql');
var connection = require('./database.js');
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('/', 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');
});
// port must be set to 4000 because incoming http requests are routed from port 80 to port 8080
app.listen(4000, function () {
console.log('Node app is running on port 4000');
});
module.exports = app;
Creating route to show list with button Delete data by id, name etc from MySQL database in nodejs; Simply navigate to the root directory of project and open the users.js file and then add the following routes to it:
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('/list')
} else {
req.flash('success', 'User has been deleted successfully! id = ' + req.params.id)
// redirect to users list page
res.redirect('/list')
}
})
})
module.exports = router;
The following routes will fetch data into mysql database and render with list.ejs file.
Step 6 – Start App Server
Run the npm start command on cmd or terminal to start node js mysql delete data application:
//run the below command npm start after run this command open your browser and hit http://127.0.0.1:3000/list
Conclusion
That’s it; In this tutorial, you have learned how to delete data from MySQL database using node js express app.
