Fetch data from MySQL database in node js express and show in html; In this tutorial, you will learn how to fetch data from MySQL database in node js and display in html table.
This tutorial will create a simple HTML list table using bootstrap 4 library and then create routes and import in app.js file for fetch and display data into MySQL database in node js express app.
How to Fetch Data from mysql Database in Node js and Display in Html
Let’s follow the following steps to fetch and display data from Mysql database in node js:
- Step 1 – Create Node Express js App
- Step 2 – Create Table in MySQL Database and Connect App to DB
- Step 3 – Install express flash ejs body-parser mysql Modules
- Step 4 – Create HTML Markup Form
- Step 5 – Import Modules in App.js and Create Routes
- Step 6 – Start App Server
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, `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 database.js file and add the following code into it to connect your app to 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 make it available under req.body property. In other words, it simplifies the 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 a HTML list; So visit views directory and create list.ejs file inside it. 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="../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 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 above given Node.js code that uses the Express framework to create a web server and handle HTTP GET requests. The code also connects to a MySQL database using the database.js file.
The first line of the code imports the Express framework using the require function and assigns it to a variable named express.
var express = require('express');
The second line creates a new Router instance using the express.Router() method, which is used to create modular, mountable route handlers.
var router = express.Router();
The third line imports the database connection from the database.js file.
var connection = require('../database.js');
The router object then handles HTTP GET requests to the root path ('/') by defining a callback function that retrieves data from the users table in the MySQL database using the query method of the connection object. If an error occurs during the query, an error message is passed to the req.flash() method and the list view is rendered with an empty data object. Otherwise, the list view is rendered with the retrieved data.
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});
}
});
});
The last line of the code exports the router object as a module, which can be imported in another module to handle HTTP requests.
module.exports = router;
This module can then be mounted on a path in the main application using the app.use() method.
Step 6 – Start App Server
Now execute the following command to start how to fetch data from database in node js and display in html table app server:
//run the below command npm start after run this command open your browser and hit http://127.0.0.1:3000/users
Conclusion
Fetch data from MySQL database in node js express and display in html; In this tutorial, you have learned how to fetch data from MySQL database in node js and display in html
