Api for Retrieving records using MySql and Node.js.

Here I will explain to you how to show Mysql table records using node.js server. Before starting this process you have to complete the following things:

• Download Node.js, XAMPP and Visual Studio Code
• Create a New Project
• Connect MySQL database with your Node.js Project

The above things I already discussed in my blog. If you want then check this link connect MySQL with node js.

Create Database:

First, we will create one database “db_employee” in MySql using phpMyAdmin. Then create a new table “tbl_profile”. Here’s a dump of the database, so that you can get up and running quickly if you wish to follow along:

Create Table:

CREATE TABLE tbl_profile(

id int(11) NOT NULL AUTO_INCREMENT,

name varchar(200) NULL,

phone varchar(50) NULL,

email varchar(343) NULL,

PRIMARY KEY (id) 

);  

Insert record:

INSERT INTO tbl_profile(name,phone,email) VALUES

('Ram','6767567875','ram@gmail.com'),

('Hari','6555555875','hari@gmail.com'),

('Sita','6777777775','sita@gmail.com');

Create Node.js Project:

• First Create a folder “EMPLOYEE”. 
• Open this folder in Visual Studio Code.
• Open “New Terminal” and Run “npm init”.
• “Package.json” file will create.
• Create an “app.js” file in that folder.
• Then create three folders. The first one is “controller”, the second one in “config”, and the Last one is “route”.
• In the “config” folder create a “connection.js” file.
• In “controller” folder create “employeecontroller.js” file.
• In the “route” folder create an “employeeroute.js” file.

The solution will look like this:

Connect Database with Node.js:

• Open the “connection.js” file from the “config” folder. 
• Open the XAMPP server and start MySQL and Apache.

• Go to the PHPMyAdmin page and get your host IP address, user name, password, and database name.
• Run “npm install mysql” in visual studio code terminal.
• Write the following connection query for connecting Mysql with Node.js.

Connection.js

var mysql=require("mysql"); var db = mysql.createPool({     connectionLimit: 1000,     acquireTimeout: 3000000, //30 secs     host: '127.0.0.1',     port:'3307',     user: 'root',     password: '',     database: 'db_employee' }); db.getConnection(function (err, connection) {     if (err) {         console.log(err);         throw err;     } else {         console.log('Connected to database');         connection.release();     } }) module.exports = db;

Write Select Query in Controller:

For getting all record from MySQL table write query in “employeecontroller.js” file in “controller” folder.

In the Phpmyadmin page, the table structure looks like this:

MySQL query for getting all record is :

Select * from tbl_profile

Now use that query in the “employeecontroller.js” file in the “controller” folder. 

Employeecontroller.js

var db = require('../config/connection'); //reference of connection.js var employees = {     getAll: function (callback) {         return db.query("SELECT * FROM tbl_profile", callback);     } } module.exports=employees;

Route Configuration for get employee records:

For configuring route for getting all records from employee profile table. Write route configuration code in “employeeroute.js” file in “route” folder.

Install express in your project. First, run “npm install express” in your node.js project terminal.

Set Get method to get all employee’s profile details. Now use this following code in your “employeeroute.js” file in the “route” folder.

employeeroute.js:

var express = require('express'); var router = express.Router();  var employees = require('../controller/employeecontroller'); //Call your employee controller... router.get('/getall', function (req, res, next) {     employees.getAll(function (err, rows) {         if (err) {             res.send(err);         }         else {             res.send(rows);         }     }) }) module.exports = router;

Set app.js File:

• Install body-parser in your project. First, run “npm install body-parser” in your node.js project terminal.
• Write the following code in your app.js file.

const express = require('express'); const app = express(); const bodyParser = require('body-parser'); var http = require('http'); // -----------------Routes-------------------------// var employee=require('./route/employeeroute');  //call route file app.use(bodyParser.json()); app.use(bodyParser.urlencoded({     extended: true })); app.listen(8080, () =>   console.log('Example app listening on port '+8080), );  //localhost port setting // default route app.get('/', function (req, res) {     return res.send({ error: true, message: 'hello' }) }); app.use('/employee',employee); module.exports = app;

Run the application:

Start your Apache and MySQL in XAMPP server.
First, build your application using “npm rebuild” in your project terminal.

The output will be:

Then Run “node app.js”. 

The output will be:

Now open postman for checking that node.js application. Write your URL and set the method type is GET.

Click on the Send button then the output will be shown in the below window.

Like this, we will write our get API for getting all records from MySQL database table. 

Happy Coding