How to Fetch / Show Data from MySQL Database in Node Js

Last Updated on by in Node JS

Node Get and Show data from MySQL tutorial; When it comes to managing data, MySQL is the first choice of the developers. Obviously, certain requirements are always considered when choosing a tech stack. However, this MySQL always has the upper hand for storing a large number of records.

Similarly, Node js is the popular open-source server environment that allows you to create a robust backend not only but also allow you to build a powerful frontend application.

In this tutorial, we will show you the confluence of Node js and MySQL. We will create a simple node app, this app will Get records or data from the MySQL database and display the MySQL data in an HTML template in a Node js app.

To make the request to the MySQL database, we will take the help of Express js, and the express allows us to write code for routes that will request and communicate with the MySQL database through the Node platform.

Node js MySQL Render and Display Records from Database in HTML Example

  • Step 1: Build Node Project
  • Step 2: Install NPM Dependencies
  • Step 3: Create SQL Table
  • Step 4: Make MySQL Database Connection
  • Step 5: Display Records in Html
  • Step 6: Build Express Route
  • Step 7: Build Server File
  • Step 8: Serve Node Application

Build Node Project

On the terminal’s command-prompt enter the given command, then execute the command to build a new folder for new node project.

mkdir node-blog-demo

Next, enter inside the app folder.

cd node-blog-demo

In order to install additional packages in the node, we need to have a specific package.json file located in our app; hence run the given below command to generate the package file modules.

npm init

Install NPM Dependencies

Add the given set of commands and run the commands respectively to install packages from npm registry altogether.

npx express --view=ejs
npm install -g express-generator

npm install

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

Create SQL Table

In this step, we will create a table in the MySQL database; creating a table is super easy with SQL query; hence run the given command to complete the task.

CREATE TABLE `users ` (
  `id` int(20) NOT NULL,
  `name` varchar(155) NOT NULL,
  `email` varchar(155) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Make MySQL Database Connection

To connect to MySWL database, create a database.js file, then in this file you have to add your hostname, username, password and database name as given in the given code example.

var mysql = require('mysql')

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

connection.connect((err) => {
  if (err) {
    console.log(err)
    return
  }
  console.log('Database connected')
})

module.exports = connection

Display Records in HTML

To display data in the HTML view file, we need to create the profile.ejs file in the views/ folder, also insert the given code in the view file.

<!DOCTYPE html>
<html lang="en">
  <head>
    <title>
      Node Js MySQL Fetch and Show Records from MySQL Database 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-4">
      <% if (messages.success) { %>
      <p class="alert alert-success mt-4"><%- messages.success %></p>
      <% } %>
      <br />
      <table class="table">
        <thead>
          <tr>
            <th>#Id</th>
            <th>Name</th>
            <th>Email</th>
            <th>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 data ever existed.</td>
          </tr>
          <% } %>
        </tbody>
      </table>
    </div>
  </body>
</html>

Build Express Route

Head over to the users.js file; you can find this file inside the routes directory; here, we will use the express js instance to formulate the route that will get the result from the database.

var express = require('express')
var connection = require('../database.js')
var router = express.Router()


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

module.exports = router

Build Server File

To set up the node server, you have to create the app.js file. In this file, we will write the code to run the node app.

var createError = require('http-errors')
var express = require('express')

var path = require('path')
var logger = require('morgan')
var bodyParser = require('body-parser')
var flash = require('express-flash')
var cookieParser = require('cookie-parser')

var expressValidator = require('express-validator')
var session = require('express-session')

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

var nodeRoutes = require('./routes/index')
var userRoute = 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: '123@abcd',
    resave: false,
    saveUninitialized: true,
    cookie: { maxAge: 60000 },
  }),
)

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


app.use('/', nodeRoutes)
app.use('/users', userRoute)

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

app.listen(5555, function () {
  console.log('Node server running on port : 5555')
})

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

Serve Node Application

We can execute the node script using the given command, make sure to run the given command.

nodemon

You have to type the given url on the browser to run the app.

http://localhost:5555/users

How to Fetch / Show Data from MySQL Database in Node Js

Conclusion

Throughout this guide, we learned in detail how to connect the node js app to the MySQL database simultaneously, render the data from the MySQL database, and show the data into the HTML table within a Node js application.

To accomplish this small functionality, we installed and used various NPM modules, and we shared the idea utterly with you to build such a feature.