Node AJAX Retrieve Records from MySQL Database Tutorial

Last Updated on March 4, 2024 by Digamber in Node JS

MySQL is a relational database management system based on SQL – Structured Query Language, and managing data in MySQL is not that difficult.

In this detailed guide, we will find out how to retrieve data from the MySQL database using AJAX in the Node js app. We will build an HTML dropdown element in which we will get the records from the database.

We will install some packages from the NPM registry, and these packages will help us connect Node to the MySQL database and build the API routes that will interact with the database.

Furthermore, we will create the node script, which will help us run the node server.

Let us find out how to work with MySQL database through a node environment.

How to Get Data from MySQL Database using Node Js and jQuery Ajax

• Step 1: Create Node App
• Step 2: Add NPM Modules
• Step 3: Create Table in Database
• Step 4: Database Connection
• Step 5: Create Server File
• Step 6: Render and Show Data in HTML
• Step 7: Serve Node Project

Create Node App

Type the mkdir command followed by your project name and hit enter to create a new folder.

mkdir node-vlog

Then, move into application folder.

cd node-vlog

Use the npm init command, this command creates the package.json file, where your project’s meta information stays.

npm init

In the project’s root create an app.js file, register the file name in scripts section so that this node script can be invoked using the command-line tool.

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

Add NPM Modules

We are going to install the given below packages from the npm registry, execute the command and install the modules simultaneously.

npm install ejs express cors mysql body-parser nodemon

Create Table in Database

In your database, you need to have a table with some records, so that you can get the data from the database.

If you don’t have table created, run the given sql command from the sql tab.

CREATE DATABASE nodedb;


CREATE TABLE Country (
  id int not null,
  name varchar(150) not null);

INSERT INTO country VALUES(1,'Colombia');
INSERT INTO country VALUES(2,'Estonia');
INSERT INTO country VALUES(3,'Belgium');
INSERT INTO country VALUES(4,'Austria');
INSERT INTO country VALUES(5,'Denmark');</code></pre>

Database Connection

In your node project, make the database.js file and then inside the given code, add your database credentials in order to connect the database to the node app.

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

Create Server File

Open the app.js script file, in this file put the given code.

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


var dbMySQLNode = require('./database')

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


app.use(cors())

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

app.get('/fetch-countries', function (req, res) {
  dbMySQLNode.query('SELECT * FROM Country ORDER BY id desc', function (
    error,
    response,
  ) {
    if (error) {
      res.json({
        msg: error,
      })
    } else {
      res.json({
        msg: 'Data successfully fetched',
        country: response,
      })
    }
  })
})

app.listen(5555, function () {
  console.log('Node app is being served on port: 5555')
})

module.exports = app

The following script is responsible to set up the ejs view engine, define the cors setting, declare the route that will communicate with database and define the application port.

Render and Show Data in HTML

At the root of your application, make the index.ejs file, this file will handle the view of your node app. It will display the records that you get from the database.

<!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">
      <h2 class="mb-4">Node js Ajax Get Data from MySQL Example</h2>
      <div class="form-group">
        <label><strong>Countries</strong></label>
        <select class="form-control" id="dynamicDropdown"></select>
      </div>
    </div>
    <script>
      $(document).ready(function () {
        function showCountryCollection() {
          var count_id = this.value;
          $("#dynamicDropdown").html("");
          $.ajax({
            url: "http://localhost:5555/fetch-countries",
            type: "GET",
            dataType: "json",
            success: function (res) {
              console.log(res);
              $("#dynamicDropdown").html('<option value="">Select</option>');
              $.each(res.Country, function (key, value) {
                $("#dynamicDropdown").append(
                  '<option value="' + value.id + '">' + value.name + "</option>"
                );
              });
            },
          });
        }
        showCountryCollection();
      });
    </script>
  </body>
</html>

Serve Node Project

Ultimately, you need to evoke the suggested command to start the node application.

nodemon

You require to use this url to test the app:

http://localhost/:5555

Conclusion

In this guide, we looked at the process of getting the results from the MySQL database into the Node js app.

We covered how to fetch the data from the MySQL database and display the records in the HTML select dropdown in a Node js app using the external dependencies.

We hope you liked this guide and share it with others.