This node js tutorial helps to create rest API to the listing table, add a record, edit a record and delete a record from the MySQL database. These rest API communicate with MySQL and update data into the MySQL database.
This is the third part of node js tutorial series to create a restful API using the Express node.js module. I have shared node js with MySQL tutorials.
You can also use restify framework instead of express, You can read my updated nodejs tutorial Node JS REST API Example Using Restify and MySQL.
Create node js rest API using Express and MySQL
Let’s create a rest api for all CRUD operations, The CRUD is an acronym for Create, Read, Update and Delete operation. I will create HTTP POST, GET, PUT and DELETE type requests.
I am using the following dependency libraries in this nodejs project:
- Express js: Express is a framework for building web applications on top of Node.js
- MySQL: Use to create a connection with MySQL database and allow operations into table data.
- body-parser: This nodejs module help to reading data from the ‘form’ element and is attached with the request.
Also Checkout other tutorials of nodejs rest api,
- Node js Rest Api to Add, Edit and Delete Record from MySQL Using Express JS
- Node JS REST API Example Using Restify and MySQL
- CRUD Rest API using NodeJS, Express, MongoDB and Mongoose
- Angular File Upload Using Angular 1.x and PHP
Define Routes
Our main motto of this nodejs tutorial is to create Rest API for CRUD operation using node js and express. The Node js Rest API details are as follows:
| Route | Method | Type | Posted JSON | Description |
|---|---|---|---|---|
| /employees | GET | JSON | – | Get all Employee’s data |
| /employees/{id} | GET | JSON | – | Get a single employee data |
| /employees | POST | JSON | {"employee_name":"Adam", "employee_salary":170750, "employee_age":30} |
Insert new employee records into the database |
| /employees | PUT | JSON | {"employee_name":"Adam12", "employee_salary":170, "employee_age":32, "id" : 59} |
Update employee record into the database |
| /employees | DELETE | JSON | {"id" : 59} |
Delete particular employee record from the database |
I am assuming you have read my both previous node.js tutorial, so you have knowledge of package.json file. We will include these nodejs dependency modules in package.json file.
{
"name": "node-restapi",
"version": "1.0.0",
"description": "",
"main": "main.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"author": {
"name": "Adam",
"email": "[email protected]",
"url": "http://js-tutorials.com/"
},
"license": "MIT",
"dependencies": {
"body-parser": "^1.16.1",
"express": "^4.14.1",
"mysql": "^2.13.0"
}
}
save above package.json file and run npm install command:
~/node-restapi$ npm install
MySQL Database & table creation
We will create 'dummy_db' name database in MySQL host. We will create an ’employee’ table in this database using the below SQL query,
CREATE TABLE IF NOT EXISTS `employee` ( `id` int(11) NOT NULL COMMENT 'primary key', `employee_name` varchar(255) NOT NULL COMMENT 'employee name', `employee_salary` double NOT NULL COMMENT 'employee salary', `employee_age` int(11) NOT NULL COMMENT 'employee age' ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='datatable demo table';
GET STARTED To Create CRUD API
Let’s create nodejs rest api to access records from the database, add a record into the table and delete record from the MySQL database. It’s a very common crud operation for any application.
Created main.js file into node js project and created dependency module instances.
var http = require("http");
var express = require('express');
var app = express();
var mysql = require('mysql');
var bodyParser = require('body-parser');
Configure & Connect MySQL database
Created MySQL connection in main.js file.
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '',
database : 'dummy_db'
});
connection.connect(function(err) {
if (err) throw err
console.log('You are now connected...')
})
Step 3: Now we will add the body-parser configuration like below,
app.use( bodyParser.json() ); // to support JSON-encoded bodies
app.use(bodyParser.urlencoded({ // to support URL-encoded bodies
extended: true
}));
Setting up Express.js for our REST API
We will create node.js express server that will listen to our request on a particular port. I am running node server on 3000 port, you can change the port as per your port availability.
var server = app.listen(3000, "127.0.0.1", function () {
var host = server.address().address
var port = server.address().port
console.log("Example app listening at http://%s:%s", host, port)
});
Node js Rest Api to Fetch All Record From MySQL Database Using Express
We will create a new GET type rest request to access all employee records from MySQL database table. We will create a MySQL query to fetch data from the database and send JSON data to the client as a response object. We will test the rest endpoints using the browser or postman.
//rest api to get all results
app.get('/employees', function (req, res) {
console.log(req);
connection.query('select * from employee', function (error, results, fields) {
if (error) throw error;
res.end(JSON.stringify(results));
});
});
res.end() method sends data to the client a JSON string through JSON.stringify() method.
Now you can access this get request from browser or postman,I am accessing GET type request from browser using http://localhost:3000/employees, You should see all employee data in json format which was fetched from the MySQL database.
Node.js Rest Api to Get a Single record from Mysql Database Using Express
We will create a new GET type rest request to access a single employee record from the MySQL database table. We will create a MySQL query to fetch records of a particular employee and send JSON data to the client as a response object. We will test the rest endpoints using postman/browser.
//rest api to get a single employee data
app.get('/employees/:id', function (req, res) {
connection.query('select * from employee where id=?', [req.params.id], function (error, results, fields) {
if (error) throw error;
res.end(JSON.stringify(results));
});
});
Now access http://localhost:3000/employees/1 rest api URL form browser and you will get a single employee record from MySQL database whose id is 2, if you will not get it then something happened wrong, maybe id does not exist in the table, or MySQL is not configured properly.
Node js Rest Api to Create New Record into MySQL Database
We have fetched records from the MySQL database using express, so the next step will create a new entry into the MySQL database table using rest api. This rest api would be a POST type because We will post some JSON data to the server.
//rest api to create a new record into mysql database
app.post('/employees', function (req, res) {
var postData = req.body;
connection.query('INSERT INTO employee SET ?', postData, function (error, results, fields) {
if (error) throw error;
res.end(JSON.stringify(results));
});
});
I am testing the above rest call http://localhost:3000/employees/ using Postman and set json data to body, The JSON data is:
{"employee_name":"Adam","employee_salary":170750,"employee_age":30}
POST type Rest API Testing Using Postman Client
Node js Rest Api to Update Record into MySQL Database
We will create a new Restful API using nodejs and express to update data into MySQL database. We need an employee id for whom we want to update the record. We will create a PUT type request to update record into MySQL database. We will post some JSON data to the server with employee id.
//rest api to update record into mysql database
app.put('/employees', function (req, res) {
connection.query('UPDATE `employee` SET `employee_name`=?,`employee_salary`=?,`employee_age`=? where `id`=?', [req.body.employee_name,req.body.employee_salary, req.body.employee_age, req.body.id], function (error, results, fields) {
if (error) throw error;
res.end(JSON.stringify(results));
});
});
as you can see, I have created an UPDATE MySQL query to update record into the database and send JSON response to the client. You can test the above node js rest call http://localhost:3000/employees/ using postman with PUT type request. We need to post the following JSON data:
{"employee_name":"Adam12","employee_salary":170,"employee_age":32, "id" : 59}
PUT type Rest Request Testing Using Postman Rest Client
Node js Rest Api to Delete Record from MySQL Database Table
Finally, we will create a new node js rest api to create a DELETE Type request to remove a employee record from the MySQL database table. We will pass the employee id as a parameter which we want to delete from the MySQL table.
//rest api to delete record from mysql database
app.delete('/employees', function (req, res) {
console.log(req.body);
connection.query('DELETE FROM `employee` WHERE `id`=?', [req.body.id], function (error, results, fields) {
if (error) throw error;
res.end('Record has been deleted!');
});
});
as you can see, I have created a Delete MySQL query to delete record from the database and send a message response to the client. You can test the above node js rest call http://localhost:3000/employees/ using postman with DELETE type request. We need to post the following JSON data:
{"id" : 59}DELETE type Rest API Testing Using Postman Client
The final main.js file code is as follows:
var http = require("http");
var express = require('express');
var app = express();
var mysql = require('mysql');
var bodyParser = require('body-parser');
//start mysql connection
var connection = mysql.createConnection({
host : 'localhost', //mysql database host name
user : 'root', //mysql database user name
password : '', //mysql database password
database : 'dummy_db' //mysql database name
});
connection.connect(function(err) {
if (err) throw err
console.log('You are now connected...')
})
//end mysql connection
//start body-parser configuration
app.use( bodyParser.json() ); // to support JSON-encoded bodies
app.use(bodyParser.urlencoded({ // to support URL-encoded bodies
extended: true
}));
//end body-parser configuration
//create app server
var server = app.listen(3000, "127.0.0.1", function () {
var host = server.address().address
var port = server.address().port
console.log("Example app listening at http://%s:%s", host, port)
});
//rest api to get all results
app.get('/employees', function (req, res) {
connection.query('select * from employee', function (error, results, fields) {
if (error) throw error;
res.end(JSON.stringify(results));
});
});
//rest api to get a single employee data
app.get('/employees/:id', function (req, res) {
console.log(req);
connection.query('select * from employee where id=?', [req.params.id], function (error, results, fields) {
if (error) throw error;
res.end(JSON.stringify(results));
});
});
//rest api to create a new record into mysql database
app.post('/employees', function (req, res) {
var postData = req.body;
connection.query('INSERT INTO employee SET ?', postData, function (error, results, fields) {
if (error) throw error;
res.end(JSON.stringify(results));
});
});
//rest api to update record into mysql database
app.put('/employees', function (req, res) {
connection.query('UPDATE `employee` SET `employee_name`=?,`employee_salary`=?,`employee_age`=? where `id`=?', [req.body.employee_name,req.body.employee_salary, req.body.employee_age, req.body.id], function (error, results, fields) {
if (error) throw error;
res.end(JSON.stringify(results));
});
});
//rest api to delete record from mysql database
app.delete('/employees', function (req, res) {
console.log(req.body);
connection.query('DELETE FROM `employee` WHERE `id`=?', [req.body.id], function (error, results, fields) {
if (error) throw error;
res.end('Record has been deleted!');
});
});
Conclusion
We have covered MySQL connection, body-parser configuration, and express js configuration with nodejs application. We have also created CRUD rest API example that helps to create a new record into the database using express, update record into MySQL using express, fetch all records from MySQL using rest API and delete record from MySQL using express node js.
You can download the source code from the below link.
http module is not neccessary
read as newbee… missing many things to mention…:(
can you please mentioned, that help me into upcoming tuts.
Good job on this. I was looking for something like this that didn’t over complicate things for a beginner (like me) I do have one question though: Is the SQL syntax correct on the insert route? I can’t get that one to work.
Ok, but how do I attach this code to a form in ejs?
its rest api tutorials, so you need to set and get in json format.