Most Popular Tutorials
Most Popular Tutorials :-

Simply Easy Learning at Your Fingertips. Click Tutorials Menu to view More Tutorial List





How to fetch Data from MySQL database table in Node.js?

create node.js connection to mySql Database

First of all, you have to create a connection node.js app to the MySQL database.

File Name : database.js

var mysql = require('mysql');
var conn = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'nodeapp'
});
conn.connect(function(err) {
if (err) throw err;
console.log('Database is connected successfully !');
});
module.exports = conn;

Create a Route for Fetch Data

Include the database.js file for connection.
Create a route /user-list to fetch data from the users table
Write a SQL query to fetch data from the database.
Pass the fetched data into the view file in the form of userData
finally export router.

File Name : users.js

var express = require('express');
var router = express.Router();
var db=require('../database');
router.get('/user-list', function(req, res, next) {
var sql='SELECT * FROM users';
db.query(sql, function (err, data, fields) {
if (err) throw err;
res.render('user-list', { title: 'User List', userData: data});
});
});
module.exports = router;

Load Route into the root file

You have to include the created route in the app.js.

File Name : 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 indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');
;
var app = express();

// view engine setup
app.use('/', indexRouter);
app.use('/users', usersRouter);



app.set('views', path.join(__dirname, 'views'));

app.set('view engine', 'ejs');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

// 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;

Create Html view

Create HTML view to Display data

File Name : user-list.ejs

<!DOCTYPE html>
<html lang="en">
<head>
<title>how to Fetch data using MySQL and Node.js</title>
</head>
<body>
<div class="table-data">
<h2>Display Data in html table using Node.js & MySQL</h2>
<table border="1">
<tr>
<th>S.N</th>
<th>Full Name</th>
<th>Email Address</th>
<th>City</th>
<th>Country</th>
<th>Edit</th>
<th>Delete</th>
</tr>

<%
if(userData.length!=0){
var i=1;
userData.forEach(function(data){
%>
<tr>
<td><%=i; %></td>
<td><%=data.fullName %></td>
<td><%=data.emailAddress %></td>
<td><%=data.city %></td>
<td><%=data.country %></td>
<td><a href="/users/edit/<%=data.id%>">Edit</a></td>
<td><a href="/users/delete/<%=data.id%>">Delete</a></td>
</tr>
<% i++; }) %>
<% } else{ %>
<tr>
<td colspan="7">No Data Found</td>
</tr>
<% } %>
</table>
</div>
</body>
</html>

Run Application

File Name :

Start your Node.js server using npm start.

Test On Browser

File Name :

http://localhost:3000/users/user-list





Previous Next


Trending Tutorials


PHP

PHP

100+ Tutorials
oops

PHP OOPs

80+ Tutorial
Codeigniter

Codeigniter

100+ Tutorial
Laravel

Laravel

100+ Tutorials
Node.js

NODE.JS

100+ Tutorials
Javascript

Javascript

100+ Tutorials


Review & Rating

0.0 / 5

0 Review

5
(0)

4
(0)

3
(0)

2
(0)

1
(0)

Write Review Here


Most Popular Tutorials

 Important Link