How to display Data from MySQL database table in Node.js

by

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.

display data using Node.js

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 –

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.

 

35 thoughts on “How to display Data from MySQL database table in Node.js”

  2. Hi,

    Thank you for excellent article.

    How do I format the date from mysql:

    “Sun Feb 02 2020 00:00:00 GMT+0200 (South Africa Standard Time)” into 20200202

    Thank you in advance

    • Hello Naven, Thanks for asking the question.
      You can use the following javascript code to solve your problem
      var dateFormate=”Sun Feb 02 2020 00:00:00 GMT+0200 (South Africa Standard Time)”;
      var convertDate= convert(dateFormate);
      console.log(convertDate);
      function convert(str) {
      var date = new Date(str),
      mnth = (“0” + (date.getMonth() + 1)).slice(-2),
      day = (“0” + date.getDate()).slice(-2);
      return [date.getFullYear(), mnth, day].join(“-“);
      }

      I hope the above script will be helpful

  3. hi,thanks for an excellent article post. I tried to follow all your instructions, but somehow when I run node app.js, then go to browser: http://localhost:3000/users/user-list, it says” this stie can’t be reached localhost refused to connect. cOULD you please advice if there is error I can fix? As i don’t see any in my terminal console.

    • Hi joyous, Thanks for asking..
      First of all, you have to run command npm start to start your local server
      then http://localhost:3000/users/user-list in your browser, It will work well..

  4. Hi
    when i perform this query ..
    i got this error—>
    PS C:\Users\Dhruvit\Desktop\experiment> npm start
    npm ERR! missing script: start

    • Hi,
      Generally, this error occurs whenever we run npm start command in the wrong project folder So, You must run the npm start command in your right node project.
      first, click here and read complete article then try again yourself. It will definitely work

    • Hi, Mamta… Thanks for showing your errors.
      Check your HTML tag. write down in proper format. Don’t forget the closing tag or angle bracket. It will definitely work well.

      • I write down only simple html open and closing tag on first line….. no other tag….. but still gave same error after HTML open tag……

        • I have checked this script on my system. Here it works well. ..
          Your Browser may contain cache. So, You can do one of the following steps –

          • Clear your browser cache
          • Run Your node app in an incognito window

          By doing one of the above steps, I hope it will not generate any errors.

  7. Following Error comes on UI , when i run !!!

    Failed to lookup view “user-list” in views directory “D:\node js\expressfile\myapp\views”
    Error: Failed to lookup view “user-list” in views directory “D:\node js\expressfile\myapp\views”

    Can you have any solution for this???

    • Hi, Thanks for Asking your error.
      To solve this error, you have to configure the following steps-

      • First of all, Install ejs template engine using npm install ejs
      • create user-list.ejs file in the views folder

  8. i cannot open http://localhost:3000/users/user-list in browser
    terminate show
    GET /user/user-list 404 1.788 ms – 1033

    • You have to create a route for /users/user-list.
      configure the following steps to create route-

      • create a route router.get('/users/user-list', function(req, res, next) in routes/user.js
      • Write the following line of code in app.js
        • var usersRouter = require('./routes/users');
        • app.use('/users', usersRouter);

        • Once you configure the above steps, your URL will definitely work.

  9. Hi, thanks for the tutorial. I follow the step correctly but in the end, I get this error:

    Error: Cannot find module ‘mysql’ Require stack: – C:\Users\User\myapp\database.js – C:\Users\User\myapp\routes\users.js – C:\Users\User\myapp\app.js –

    Am I missing some configuration??

    • Hi, Thanks for asking for your issue.
      You need to configure the following steps to solve your issue

      • Install MySQL module using npm install mysql command
      • Import MySQL using var mysql = require('mysql'); in database connection file

      I hope that the above steps will solve your issue

  10. tried to repeat this example, got the same error as “insert example”… I am sure i have installed ejs and created user-list.ejs file in the folder of views

    rror: Failed to lookup view “error” in views directory “D:\0_Bo\nodejs_02\myapp\views”

    • Hi Jay, You have asked a good question.
      You can use another query in the same route by writing like the following script-

      router.get('/users/user-list', function(req, res, next) {

      // first query
      var query1=function(callback)
      {
      var sql='SELECT * FROM users';
      db.query(sql, function (err, data, fields) {
      if (err) throw err;
      return callback(data);
      });

      }
      // query2
      var query2=function(callback)
      {
      var sql='SELECT * FROM crud';
      db.query(sql, function (err, data, fields) {
      if (err) throw err;
      return callback(data);
      });

      }
      // render to same page
      query1(function(data){
      query2(function(data2){
      res.render('user-list', { title: 'User List', userData: data, userData2: data2});
      });

      });

      });

  13. Hi Noor, how can I display a sql query but without the loop?
    I wrote this on routes:
    router.get(‘/user-edit’, function(req, res, next) {
    var adr = url.parse(req.url,true).query;
    var txt = adr.id;
    var sql2 = ‘SELECT * FROM order_new LEFT JOIN orders_items_qty ON order_new.id = orders_items_qty.id WHERE order_new.id= ‘ + txt;
    db.query(sql2, function (err, results, fields) {
    if (err) throw err;
    console.log(results)
    res.render(‘user-edit’, { adr: adr, results: results});
    });
    });

    I am not getting the date and whenever I have more than one item in the order the page get Name, Record, User ID, etc again and again for each item.

    Thank you so much,

    Gabriel

  15. Hi Noor,
    Thank you for your article
    I have connected to to the Database successfully in node.js
    I have used an external database in address 127.0.0.1:3006
    When trying to get the data in the browser (http://127.0.0.1:3006/) there is the error:
    This site can’t be reached
    127.0.0.1 refused to connect.

    Thank you

    David

    • Hi David,
      You need to start node.js server. If your project is replaced in xampp/wampp server then you will have to start it.
      One more thing that you should run your code on this address 127.0.0.1:3000

  16. Is there any way in which the file “user-list.ejs” can be directly accessed using http://localhost:3000/user-list instead of http://localhost:3000/users/user-list just like we are able to access “user.ejs” using http://localhost:3000/users ?

    • Hi Billu, Yes you can do it,
      you have to replace app.use('/users',usersRouter)with app.use('/',usersRouter) in the app.js file

  17. How can i change this code in handlebar (.hbs) file

    <a href="/users/edit/”>Edit
    <a href="/users/delete/”>Delete

    No Data Found

Comments are closed.