In this tutorial, you will learn how to fetch and display data in HTML view from MySQL db in node js express js application.
How to Fetch Data from mysql Database in Node js and Display in Html
Set up routes in node js application and create HTML view file and fetch data from mysql database using sql query SELECT * FROM table and display it in html table:
Step 1 – Create Node Express js App
To create project node js express project directory, simply use mkdir my-app && cd my-app command on cmd or terminal window:
mkdir my-app cd my-app
To intialize package.json in node express js project, simply run npm init -y command:
npm init -y
Step 2 – Connect to the MySQL database
To create database.js file in node express js project root directory 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: 'test' // // Replace with your database Name
});
conn.connect(function(err) {
if (err) throw err;
console.log('Server started on http://127.0.0.1:4000/');
});
module.exports = conn;
Once connected to mysql database via node js app; Need to create table into your database by using the following sql query:
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;
Step 3 – Install express flash ejs body-parser mysql Modules
To run the following command on the terminal or cmd install express flash ejs body-parser mysql dependencies on your node express project:
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 Views
To create a HTML list; Navigate to views directory, and create list.ejs file and then add the following code into it for displaying data from mysql database:
<!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
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 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('/', 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;
Step 6 – Create Route For Fetch and Display Data From Database
To create routes for fetch and display data in html table from database; Simply navigate routes directory open index.js file and add the following route 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 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 7 – Start App Server
To start application server, Simply run the following command on cmd or terminal window:
//run the below command npm start after run this command open your browser and hit http://127.0.0.1:4000/
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
