16-Jan-2023
.
Admin
Hello Friends,
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.
Step 1: Create Node Express js App
Execute the following command on the terminal to create the 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 in 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 a database.js file and add the following code into it to connect your app to the 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
body-parser – Node.js request body parsing middleware which parses the incoming request body before your handlers, and makes it available under req.body property. In other words, it simplifies incoming request.
Express-Flash – Flash Messages for your Express Application. Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.
Express-Session – Express-session – an HTTP server-side framework used to create and manage a session middleware.
Express-EJS – EJS is a simple templating language which is used to generate HTML markup with plain JavaScript. It also helps to embed JavaScript to HTML pages
Mysql – an open-source relational database management system (RDBMS).
Step 4: Create HTML Markup For List
Create an HTML list; visit the views directory and create a list.ejs file inside it. Then add the following code into it:
<!DOCTYPE html>
<html>
<head>
<title>How to Delete Data from MySQL Database using Node Js? - Nicesnippets.com</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 the users.js file and add a 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 the MySQL database and render with the list. ejs file.
Step 6: Start App Server
You can use the following command to start uploading images in MySQL using node js express app server:
//run the below command
npm start
After running this command open your browser and hit
http://127.0.0.1:3000/
I hope it can help you...
#Node JS
