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

In this tutorial, I will show you how to display data from the MySQL database table using Node.js. Even you will learn this tutorial with a simple example. In this example, All the data will display in the HTML table in the proper format. So, read all the following steps and use them in your project.

display data using Node.js

Display MySQL Data in HTML Table Using Node.js

Before getting started, You must insert data into the MySQL database using node.js. Even make sure that Database Name is ‘nodeapp’ and table name is ‘users’. If you want to use your own database & table name then you can.

1. Install Express Application

You have to Install Basic Express Application like the following project folder structure

myapp/
  |__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. Connect Node.js App to MySQL Database

First of all, you have to connect the node.js app to the MySQL database with the connection credentials like lost, username, password & database name

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;

3. Create a Route to Fetch Data

You have to configure the following steps to create a route for fetching data using MySQL in Node.js –

  • Include the database connection file database.js
  • 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
  • At last. export router.

File Name – users.js

var express = require('express');
var router = express.Router();
var db=require('../database');
// another routes also appear here
// this script to fetch data from MySQL databse table
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;

If you want to create your own route URL according to your project requirement then you can. But make sure that all the scripts must be correct. otherwise, your app will not work properly.

4. Load Route 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.

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;

 

5. Create HTML Table to Display data

Configure the following steps to create an HTML table for displaying data –

  • Create a file user-list.ejs in the views folder.
  • Display data with the help of passing variable userData from the route.

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

6. Run Node.js Code to display data in the HTML Table

To Run Node.js code for displaying data in the HTML table, you will have to do the following things –

  • Start your Node.js server using npm start the command
  • Enter the following URL into your web browser
http://localhost:3000/users/user-list

In this tutorial, I have taught you to display data in an HTML table with simple records of a table. If are a beginner then you should execute this script as it is given. After that, try to implement it with another kind of largest data/records. If you are experienced and you need only its query then you can use it directly in your project.

My Suggestion

Dear developers, I hope you have understood the above script, Now you are able to display data from the MySQL database table in Node.js. Even you can display another table of data by using the above steps.

If you have any questions or suggestions regarding Node.js. You can directly ask me through the below comment box.

Categories Node.js

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

  1. 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

    Reply
    • 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

      Reply
  2. 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.

    Reply
    • 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..

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

    Reply
    • 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

      Reply
    • 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.

      Reply
      • 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……

        Reply
        • 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.

          Reply
  4. 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???

    Reply
    • 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
      Reply
  5. i cannot open http://localhost:3000/users/user-list in browser
    terminate show
    GET /user/user-list 404 1.788 ms – 1033

    Reply
    • 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.

      Reply
  6. 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??

    Reply
    • 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

      Reply
  7. 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”

    Reply
    • 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});
      });

      });

      });

      Reply
  8. 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

    Reply
  9. 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

    Reply
    • 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

      Reply
  10. 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 ?

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

      Reply

Leave a Comment