In this tutorial, you will learn how to delete data from mysql database using node js express.
How to Delete Data from MySQL Database using Node Js
Here are steps:
Step 1 – Create Node Express js App
Run the following command on cmd 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
Run 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;
Recommended:-Node Js Google ReCaptcha v3 Example
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
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
Step 4 – Create HTML Markup For List
Navigate to the views directory and create list.ejs file inside it to show a list with a delete button:
<!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>
Recommended:-Angular CRUD + Node.js + Express + MySQL Tutorial
Step 5 – Import Modules in App.js and Create Routes
Open app.js from root directory of project and add import express flash session body-parser mysql modules in app.js:
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.
Recommended:-Create Rest API Using Node.js, Express and MySQL
Step 6 – Test Application
Run the npm start command on cmd to start application server:
//run the below command
npm start
Open browser with the following URL:
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.
Recommended Node JS Tutorials
Recommended:-Node js Express Upload File/Image Example
Recommended:-Node JS Express Rest API File Upload Example
Recommended:-Node JS Express Crud with Mysql Example
Recommended:-Create Registration and Login Form in Node.js MySQL
Recommended:-React JS + Node JS File Upload Tutorial with Example
Recommended:-Node JS Facebook Login Example
Recommended:-Node JS Google Authentication with Passport Example
Recommended:-Node JS Express Socket IO Chat Application Example
Recommended:-Node JS LinkedIn Login using Passport Example
Recommended:-Node.js Web Scraping Example
Recommended:-Node js Express Get Client IP Address Tutorial
Recommended:-VUE JS CRUD + Node.js + Express + MySQL Example
