Site icon RemoteStack

Node js Retrieve and Show Data from MySQL Database Tutorial

remotestack

This eloquent step-by-step tutorial will show you how to fetch data or record MySQL database in the Node js application.

Not just that, we will also ascertain how to render the MySQL data in the HTML table component using the Bootstrap 5 UI framework.

To retrieve the data from the MySQL database, we will create a GET route using the express js to display the records in the Node application.

We will also need express-generator, mysql, body-parser, express-session, and EJS (embedded JavaScript templates) modules.

Most of us already know how robust MySQL is; it is generally a relational database management system that works on Structured Query Language (SQL). It helps build a scalable web application where MySQL allows you to store the data in the database.

Let us find out how to get and display the records from the MySQL database to the Node js application.

How to Get and Display Records from MySQL Database in Node Js

Set Up Node App

Create the utterly new project folder for keeping the project files.

mkdir node-dune

Move inside the new directory:

cd node-dune

Head over to command prompt, here you have to type the given command.

npm init

After you invoked the command, the package.json file will be generated in your project’s root.

Add Essential Modules

Install EJS module, it will allow you generate HTML with plain javascript.

npx express --view=ejs

Immediately after run the given set of commands to install the mysql, express-flash, express-session, body-parser, and nodemon packages.

npm install -g express-generator

npm install

npm install mysql express-flash express-session body-parser nodemon

Generate Database Table with SQL Query

In order to communicate with a database, we need a particular table in the MySQL database with certain values.

Let us create the table into the database using the given SQL query.

CREATE TABLE `employee ` (
  `id` int(20) NOT NULL,
  `name` varchar(55) NOT NULL,
  `email` varchar(55) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Connect Node App to MySQL Database

Inside the project root, make a db.js file; within this file we will add the credentials to create the connection of Node js to MySQL database.

Make sure to add your database details such as host, user, password, and database name.

var mysql = require('mysql')

var connection = mysql.createConnection({
  host: 'localhost', // Define host name
  user: 'root', // Define database username
  password: '', // Define database password
  database: 'demo', // Define database name
})

connection.connect((err) => {
  if (err) {
    console.log('Database connection error :' + err)
    return
  }
  console.log('Connected to database')
})

module.exports = connection

Show Records in HTML Table

Next, you have to create users-list.ejs inside the views/ folder, make sure to add the given code inside the newly created file.

<!DOCTYPE html>
<html lang="en">
  <head>
    <title>Node Js Get Data from MySQL and Display HTML Table Example</title>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
      rel="stylesheet"
    />
  </head>
  <body>
    <div class="container mt-5">
      <% if (messages.success) { %>
      <p class="alert alert-success mt-4"><%- messages.success %></p>
      <% } %>
      <br />
      <table class="table">
        <thead>
          <tr>
            <th scope="col">#Id</th>
            <th scope="col">Name</th>
            <th scope="col">Email</th>
            <th width="155px">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>
          </tr>
          <% } }else{ %>
          <tr>
            <td>No record found</td>
          </tr>
          <% } %>
        </tbody>
      </table>
    </div>
  </body>
</html>

Create Route in Node

Inside the routes/ folder, you need to update the given code into the users.js file for building the route, which performs the sql query to manage the data in the database.

var express = require('express')
var connection = require('../db.js')

var router = express.Router()


router.get('/', function (req, res, next) {
  connection.query('SELECT * FROM employee ORDER BY id desc', function (err, rows) {
    if (err) {
      req.flash('error', err)
      res.render('users-list', { data: '' })
    } else {
      res.render('users-list', { data: rows })
    }
  })
})

module.exports = router

Configure Node Server

In this final step, you have to update the suggested code inside the app.js file. Import the route, and set the db connection to make the app work.

var createError = require('http-errors')
var express = require('express')
var path = require('path')
var cookieParser = require('cookie-parser')
var logger = require('morgan')
var bodyParser = require('body-parser')
var flash = require('express-flash')
var expressValidator = require('express-validator')
var session = require('express-session')

var mysql = require('mysql')
var connection = require('./db')

var appRouter = require('./routes/index')
var sqlRoute = require('./routes/users')

var app = express()

app.set('views', path.join(__dirname, 'views'))
app.set('view engine', 'ejs')

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

app.use(
  session({
    secret: 'abcd123456',
    resave: false,
    saveUninitialized: true,
    cookie: { maxAge: 60000 },
  }),
)

app.use(expressValidator())
app.use(flash())


app.use('/', appRouter)
app.use('/users', sqlRoute)

// 404
app.use(function (req, res, next) {
  next(createError(404))
})

app.listen(4000, function () {
  console.log('App connected on port : 4000')
})

// error
app.use(function (err, req, res, next) {
  res.locals.message = err.message
  res.locals.error = req.app.get('env') === 'development' ? err : {}
  res.status(err.status || 500)
  res.render('error')
})

module.exports = app

Invoke Node Application

Let’s evoke the node application server using the given command.

nodemon

Provided url will show the users list on the browser:

http://localhost:4000/users

Summary

In this quick tutorial, we looked in detail at how to fetch the records from the MySQL database and how to show MySQL database results in a simple HTML table in a Node js application.

We utterly believe this short tutorial will surely help in your web development journey.

Exit mobile version