Today, I’ll go over the nodejs rest API and how to use it with Express. Nodejs is a very powerful js framework for working on both front-end and back-end functionality. I’ll show you how to use node.js and express to add, edit, and delete records from the MySQL database.
I’ll make a simple customer management module that allows you to add a customer, edit a customer, view all customers’ records, view a single customer’s record, and delete a customer record from the MySQL database.
I’m using MySQL as a database to Create, Modify, and Delete records. Because node.js and MySQL are both open-source, you can create rest calls for mobile and web applications for free. You do not need to purchase a licence.
This node.js rest API tutorial demonstrates how to use rest endpoints to retrieve all records from a MySQL database, add a record, edit a record, and delete a record. These REST APIs communicate with MySQL and update database data. I’ll use HTTP POST, GET, PUT, and DELETE requests for various rest API calls.
Table of Contents
How To Create Node.js Application
We will use following dependency libraries in this nodejs project:
- Express js : Express is a framework for building web applications on top of Node.js
- MySQL : This nodejs module help to create connection with MySQL database and allow SQL queries with table
- body-parser : This nodejs module help to reading data from the
formelement and attached with request.
The Rest End Point Details
The Node js Rest API details are as follows:
| Route | Method | Type | Posted JSON | Description |
|---|---|---|---|---|
| /customer | GET | JSON | – | Get all customers data |
| /customer/{id} | GET | JSON | – | Get a single customer data |
| /customer | POST | JSON | {"Name": "Rachel", "Address": "120 st park aevnue NY", "Country" : "America", "Phone" : "1234567451"} | Insert new customer record into database |
| /customer | PUT | JSON | {"Name": "Rachel1", "Address": "110 st park aevnue NY", "Country" : "America", "Phone" : "1234567451", "Id":1} | Update customer record into database |
| /customer | DELETE | JSON | {"Id" : 59} | Delete particular customer record from database |
Create Node.js Project and Package.json File
We will create a folder nodejs-restapi-using-express-mysql into d: directive. The location of the nodejs project would be d:\nodejs-restapi-using-express-mysql.
In this folder, create a new package.json file. All nodejs module dependencies will be listed in the package.json file.
{
"name": "nodejs-restapi",
"version": "1.0.0",
"description": "Simple Example of nodejs to create rest call using MySQL",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"author": {
"name": "Rachel",
"email": "restapi2example@gmail.com",
"url": "http://www.resapiexample.com/"
},
"license": "MIT",
"dependencies": {
"body-parser": "^1.16.1",
"express": "^4.14.1",
"mysql": "^2.13.0"
}
}
After making the changes in the 'package.json' file, run the npm install command as shown below.
~/nodejs-restapi-using-express-mysql$ npm installMySQL database & table creation
We will create a ‘test’ name database in the MySQL host server and insert a 'customer' table into it using the SQL query below.
CREATE TABLE IF NOT EXISTS `customer` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(255) NOT NULL, `Address` varchar(255) NOT NULL, `Country` varchar(100) NOT NULL, `Phone` int(10) NOT NULL, `Created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `Updated_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Create Node.js Rest Api using ExpressJS and MySQL
We’ve created the structure of the nodejs rest api example, and now we’ll create an index.js file in the root of the nodejs project. The index.js file will be located at d:\nodejs-restapi-using-express-mysql\index.js.
Step 1: Import Node.js application dependencies
In the node js project, I added an index.js file and created dependency module instances.
Step 2:Create MySQL Connection in Nodejs
The MySQL connection has been created in the index.js file, and a console message will be displayed when it connects to the database.
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '',
database : 'test'
});
connection.connect(function(err) {
if (err) throw err
console.log('You are now connected with mysql database...')
})
Step 3: Body Parser Configuration
We will add the body-parser configuration shown below.
app.use( bodyParser.json() ); // to support JSON-encoded bodies
app.use(bodyParser.urlencoded({ // to support URL-encoded bodies
extended: true
}));
Create node.js Server For Rest API
We will build a node.js express server that will listen on a specific port for our request. I’m running node server on port 3000, but you can change it to whatever port is available to you.
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’ll make a GET Rest Request to get access to all customer records in the MySQL database table. We will use a MySQL query to retrieve data from the customer table and send JSON data as a response object to the client.
//rest api to get all customers
app.get('/customer', function (req, res) {
connection.query('select * from customer', function (error, results, fields) {
if (error) throw error;
res.end(JSON.stringify(results));
});
});
res.end() method send data to client a json string through JSON.stringify() method.
Now navigate to http://localhost:3000/customer rest API URL in your browser to retrieve all customer records from the MySQL database.
Get A Single record from MySQL Database Using Node.js
To access a single customer record from a MySQL database table, we will create a GET type rest request. We will use a MySQL query to retrieve a specific customer’s record and send JSON data to the client as a response object.
//rest api to get a single customer data
app.get('/customer/:id', function (req, res) {
connection.query('select * from customer where Id=?', [req.params.id], function (error, results, fields) {
if (error) throw error;
res.end(JSON.stringify(results));
});
});
Create A New Record into MySQL Using Node.js and Express
Using node.js, we will create a new Rest API to add a new entry to a MySQL database table. Because we will be sending JSON data to the node server, I will create a POST type Rest request.
//rest api to create a new customer record into mysql database
app.post('/customer', function (req, res) {
var params = req.body;
console.log(params);
connection.query('INSERT INTO customer SET ?', params, function (error, results, fields) {
if (error) throw error;
res.end(JSON.stringify(results));
});
});
Update A Record into MySQL Database Using Node JS
To update data in the MySQL database, we will create a new PUT type Restful API request with nodejs and express. We must provide the customer id, which will be used to update the record in the table.
//rest api to update record into mysql database
app.put('/customer', function (req, res) {
connection.query('UPDATE `customer` SET `Name`=?,`Address`=?,`Country`=?,`Phone`=? where `Id`=?', [req.body.Name,req.body.Address, req.body.Country, req.body.Phone, req.body.Id], function (error, results, fields) {
if (error) throw error;
res.end(JSON.stringify(results));
});
});
Delete A Record from MySQL Database
We will create a new DELETE Type rest Api request using node js to remove customer records from the MySQL database table. We will pass the customer id as a parameter that we want to delete from the MySQL table.
//rest api to delete record from mysql database
app.delete('/customer', function (req, res) {
console.log(req.body);
connection.query('DELETE FROM `customer` WHERE `Id`=?', [req.body.Id], function (error, results, fields) {
if (error) throw error;
res.end('Record has been deleted!');
});
});
Conclusion
We created a simple nodejs application and used an npm installer to install nodejs module dependencies. We also used node js to connect to MySQL. Rest API was developed to retrieve all customers, retrieve a single customer record, create a new customer entry, update customer data, and delete customer data from the MySQL database.
You can download the source code from the below link.