17-Jan-2023
.
Admin
Hello Friends,
Here you will learn how to Fetch or Select Data from MySQL Database Using Node JS. To Insert Fetch or Select Data from MySQL Database, we will use the mysql2 module.
In this tutorial, I will show you how to display data from the MySQL database table using Node.js. Even you will learn this tutorial with a simple example. In this example, All the data will display in the HTML table in the proper format.
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;
Then 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 requests.
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, So 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 Display Data From MySQL Database in table 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/edit/<%=data[i].id%>">Edit</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('/database');
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 the following routes into 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 - Node.js",data:''});
}else{
res.render('list',{page_title:"Users - Node.js",data:rows});
}
});
});
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 image 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/users
I hope it can help you...
#Node JS
