Node Ajax Get Data from MySQL Database Tutorial

In this tutorial, we are going to show you how to fetch data from the MySQL database in the Node js application using the jQuery Ajax HTTP Get request. To render the data from the MySQL database, we will install and configure express js and MySQL modules.

MySQL is a relational database management system (RDBMS) created by Oracle, and It is based on structured query language (SQL). A database retains data in a structured form, generally used to store the data for web and mobile apps.

We will create a select dropdown using the Bootstrap 5 framework and make the simple API using the Express js. Create the MySQL database, insert the data into the database, and build the database connection with Node js.

Retrieve the data from MySQL using the jQuery Ajax call and Display the data in the select dropdown.

Let us understand how to use jQuery AJAX for making HTTP Get Request to render the data from the MySQL in Node js environment.

How to Fetch Data from MySQL Database using jQuery Ajax in Node Js

  • Step 1: Build Node App
  • Step 2: Install Express, MySQL, Body Parser Packages
  • Step 3: Insert Data Records in MySQL
  • Step 4: Define MySQL Database Connection
  • Step 5: Build Node Server
  • Step 6: Fetch and Display Data
  • Step 7: Run Node Application

Build Node App

First, you have to create the empty folder, this empty folder will contain project files and packages.

mkdir node-andromeda

Next, move into the folder:

cd node-andromeda

In this step, you have to run the following command. This command will generate the package.json file.

npm init

Now, create the app.js file in your project’s root. Also, add this file name in the package.json file as given below.

{
  "main": "app.js",
}

Install Express, MySQL, Body Parser Packages

In order to retrieve the data from the MySQL database in the Node application.

We need certain modules which will make our job facile; therefore, type and execute the given command.

npm install mysql express body-parser ejs nodemon cors

Insert Data Records in MySQL

Open the phpMyAdmin, head over to the SQL tab, paste the given sql query and execute this query.

This SQL query will create a country names table similarly insert country names.

CREATE DATABASE demo;


CREATE TABLE Country (
  id int not null,
  name varchar(50) not null);
INSERT INTO country VALUES(1,'Austria');
INSERT INTO country VALUES(2,'Barbados');
INSERT INTO country VALUES(3,'Belgium');
INSERT INTO country VALUES(4,'Colombia');
INSERT INTO country VALUES(5,'Denmark');
INSERT INTO country VALUES(6,'Estonia');

Define MySQL Database Connection

To establish a MySQL database connection in Node, you have to create the db.js file at the root of your project.

Then, insert the given code into the db.js file.

var mysql = require('mysql')

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

connection.connect((err) => {
  if (err) {
    console.log('Error occurred while connecting to database :' + err)
    return
  }
  console.log('Connected to MySQL database')
})

module.exports = connection

Create Node Server

We will create node server, at the same time we will also create route to handle AJAX HTTP Get request.

Also, set up the view and write the code for making a get request using the express get() method.

To set up the node server, you have to import the database module into the app.js file.

var express = require('express')
var createError = require('http-errors')
var path = require('path')
var bodyParser = require('body-parser')
var cors = require('cors')

var database = require('./db')

var app = express()

// view engine setup
app.set('views', path.join(__dirname, '/'))
app.set('view engine', 'ejs')
app.use(bodyParser.json())
app.use(
  bodyParser.urlencoded({
    extended: true,
  }),
)

// allow cross-origin requests
app.use(cors())

app.get('/', (req, res) => {
  res.render('index')
})

app.get('/show-list', function (req, res) {
  database.query('SELECT * FROM Country ORDER BY id desc', function (
    error,
    results,
  ) {
    if (error) {
      res.json({
        msg: 'Error occured',
      })
    } else {
      res.json({
        msg: 'Request successful',
        country: results,
      })
    }
  })
})

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

module.exports = app

Fetch and Display Data

In this step, we will create index.ejs file and insert the given code into the file.

The following code example make the request to MySQL database, retrieve data from MySQL and display in the Node application.

<!DOCTYPE html>
<html>
  <head>
    <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"
    />
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  </head>
  <body>
    <div class="container mt-5">
      <h2 class="mb-3">Node js Fetch Data from Ajax Request Example</h2>
      <div class="form-group">
        <label><strong>Country List</strong></label>
        <select class="form-control" id="country-dd"></select>
      </div>
    </div>
    <script>
      $(document).ready(function () {
        function showCountryData() {
          var count_id = this.value;
          $("#country-dd").html("");
          $.ajax({
            url: "http://localhost:8080/show-list",
            type: "GET",
            dataType: "json",
            success: function (res) {
              console.log(res);
              $("#country-dd").html('<option value="">Choose Country</option>');
              $.each(res.Country, function (key, value) {
                $("#country-dd").append(
                  '<option value="' + value.id + '">' + value.name + "</option>"
                );
              });
            },
          });
        }
        showCountryData();
      });
    </script>
  </body>
</html>

Run Node Application

We will show you how to start the node server and fetch the data from database in node app.

Type the suggested command and press enter to run the app.

nodemon

You have to type this url to view the app:

http://localhost/:8080

Node Ajax Get Data from MySQL Database Tutorial

Summary

This tutorial taught us how to set up a node server, create a MySQL database connection, and make the HTTP Get request through the Node js application.

Fetch THE data from THE database, convert the response in JSON format and display it on the browser through the Node mechanism.