Settings required for MySQL and node.js Web API

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;

Setup Controller File:

• Open “employeecontroller.js” file from “controller” folder.
• Call your database connection file inside this “employeecontroller.js”.
• Create “employees” for writing CRUD(Create, Retrieve, Update, Delete) query inside this object.
• Pass that object “employees” using “module.exports”.

Employeecontroller.js
var db = require('../config/connection'); //reference of connection.js
var employees = {
}
module.exports=employees;

Setup Route File:

• Open “employeeroute.js” file from “route” folder.
• Install express in your project. First, run “npm install express” in your node.js project terminal.
• Call your controller file inside this “employeeroute.js”.
• Call express and Router() method for routing.
• Here we will perform get, post, put, delete methods through the router.
• Pass that object “router” using “module.exports”.

employeeroute.js:

var express = require('express');
var router = express.Router();
var employees = require('../controller/employeecontroller');
router.get('/getall', function (req, res, next) {
//call controller method
})
router.post('/create', function (req, res, next) {
//call controller method
})
router.put('/update/:id’, function (req, res, next) {
//call controller method
})
router.delete('/delete/:id', function (req, res, next) {
//call controller method
})
module.exports = router;

Set up 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:

Check all the CRUD API following below link:

• Get All Records
• Get Record By Id
• Insert Record
• Update Record
• Delete Record