In this tutorial, you’ll learn how to retrieve data from a MySQL database table and display it in an HTML table using Node.js. We’ll guide you through each step with a simple example, making it easy to follow along.
Prerequisites
Before you get started, Make sure you have done the following
- Node.js installed on your system.
- MySQL database server running.
Steps to Display MySQL Data in HTML Table with Node js
Let’s start coding with the following simple steps that are very easy to understand.
1. Set up Basic Configuration
First of all, You must set up the following basic configuration –
- Install Express Application.
- Install MySQL Dependency
- Create a MySQL database named ‘nodeapp’
- Create a MySQL table named ‘users’ with columns ‘id’, ‘fullName’, ’emailAddress’, ‘city’, ‘country’
- Insert Data into the MySQL Table ‘users’
After doing the above, You will get the following directory structure of Express Application. But Here I have mentioned only the required folders & files
nodeapp/ |__bin |__node_modules |__public |__routes/ | |__index.js | |__users.js |__views/ | |__index.ejs | |__users.ejs | |__user-list.ejs |__app.js |__database.js |__package-lock.json |__package.json
2. Create MySQL Connection
Create a connection to your MySQL database using the mysql package. You’ll need to provide your database credentials in the connection settings.
File Name – database.js
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: 'nodeapp' // // Replace with your database Name
});
conn.connect(function(err) {
if (err) throw err;
console.log('Database is connected successfully !');
});
module.exports = conn;
Steps to write code:
- Create a file named ‘database.js’ in the root directory of ‘nodeapp’
- Import the ‘mysql’ package.
- Create a connection object (conn) with database configuration settings (host, user, password, database).
- Attempt to connect to the database using the connect function with an error handling callback.
- If the connection fails, throw an error; if successful, log a connection success message.
- Export the connection object (conn) to be used in other parts of the application.
3. Fetch Data From MySQL Database
Set up an Express route that fetches data from your MySQL table. You can use a simple SQL query to retrieve the data you need.
File Name – users.js
const express = require('express');
const router = express.Router();
const db = require('../database');
router.get('/user-list', (req, res) => {
const sql = 'SELECT * FROM users';
db.query(sql, (err, data) => {
if (err) throw err;
res.render('user-list', { title: 'User List', userData: data });
});
});
module.exports = router;
Steps to write code:
- Create a file named ‘routes.js’ in the ‘routes’ folder.
- Import the express framework and create an Express router.
- Import the database connection module (db) from a file.
- Define an Express route for handling GET requests to ‘/user-list’.
- Construct an SQL query to retrieve all data from the ‘users’ table.
- Execute the SQL query using the database connection’s query method, and handle results.
- If an error occurs during the query, throw the error.
- Render a view named ‘user-list’, passing the fetched user data and a title.
- Close the route handling function.
- Export the router to make this route available to other parts of the application
4. Display Data in HTML Table
Now, Display data in the HTML table with the help of passing variable userData from the route ‘user-list’
File Name – user-list.ejs
<!DOCTYPE html>
<html lang="en">
<head>
<title>Fetch using MySQL and Node.js</title>
</head>
<body>
<div class="table-data">
<h2>Display Data 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>
</tr>
<% if (userData.length !== 0) { %>
<% userData.forEach(function(data, index) { %>
<tr>
<td><%= index + 1 %></td>
<td><%= data.fullName %></td>
<td><%= data.emailAddress %></td>
<td><%= data.city %></td>
<td><%= data.country %></td>
</tr>
<% }); %>
<% } else { %>
<tr>
<td colspan="7">No Data Found</td>
</tr>
<% } %>
</table>
</div>
</body>
</html>
Explanation:
- created a file ‘user-list.ejs’ in the ‘views’ folder
- The code checks if there’s data in the userData array using <% if (userData.length !== 0) { %>.
- It starts a loop to iterate through each user object in the userData array using <% userData.forEach(function(data, index) { %>.
- The current row number is displayed by <%= index + 1 %>.
- The user’s full name is displayed dynamically using <%= data.fullName %>.
- Similarly, the user’s email address is displayed with <%= data.emailAddress %>.
- The user’s city and country are inserted into the corresponding cells using <%= data.city %> and <%= data.country %>.
5. Load Routes into the root file
You have to include the created route in the app.js. If you forget to include this file, your app will not work. You will get an error when you try to execute your script.
app.js and its code is created by the express application. in this file, you have to include the ‘index’ and ‘users’ routes
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.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')));
app.use('/', indexRouter);
app.use('/users', 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;
6. Start Node.js Server:
Open your terminal or command prompt and navigate to your project directory. Run the following command to start your Node.js server:
npm start
This will initiate your server and make it ready to handle requests.
7. View HTML Table Data in Browser
After accessing the URL, your Node.js server will respond by displaying the HTML page with the data presented in a table format. This data could be dynamically generated by your Node.js server or fetched from a database or other data source
http://localhost:3000/users/user-list
Suggestion
Remember, this is a simplified explanation, and the actual implementation might involve more details based on your specific project and the technologies you are using.
If you’re new to this, follow the tutorial to display basic table data in HTML. Once you’re comfortable, challenge yourself by applying it to larger datasets. If you’re experienced and need a quick solution, feel free to directly use the provided query in your project.
I hope that you’ve grasped the script above, enabling you to exhibit data from a MySQL database table in Node.js. You can even extend this to exhibit data from different tables by following the same procedure.
Should you have any inquiries or ideas regarding Node.js, don’t hesitate to reach out via the comment box below.