5 Steps Create a RESTful API using Node.js and MySQL

5 Steps Create a RESTful API using Node.js and MySQL

Have you ever asked:

How to build an application or website with good performance?

How to build a website that is easily developed on multiple platforms?

How to integrate a website with different programming languages application?

The answer is you need to create an API.

To create an API, there is the most popular architecture that RESTful API.  

In this tutorial, I will share with you how to create a RESTful API using node.js, express, and MySQL.

(Step-by-Step)

Let’s get started.

 

What is RESTful API?

RESTful API is an implementation of API (Application Programming Interface).

REST (REpresentational State Transfer) is a communication method architecture that uses the HTTP protocol to exchange data and this method is often applied in application development.

It may sound complicated but it really isn't.

The purpose is to make the system with good performance, fast, and scalable, especially in the exchange and data communication.

 

Why do we need to create a RESTful API?

If you look at the traditional architecture of data communication between client and server, it looks like the following picture:

traditional HTTP

The client sends a request to the server via HTTP Request, and the server provides a response via HTTP Response.

The response was given by the server, usually in HTML format.

Well, imagine if you have to develop your website into a Mobile application like Android or iOS.

You already know that Android and iOS applications don't need HTML as a response from the server.

Because Android and iOS use different programming languages and don't recognize HTML.

That's why we need to create a RESTful API.

RESTful API will become a bridge of data communication between the client and the server.

So, the server no longer sends HTML as a response, but only data.

Yes, just data.

This is thing can save server bandwidth.

This data response can be used for multiple platforms from different application programming languages.

This data response, usually formatted JSON or XML.

But, the most commonly used is JSON.

In this tutorial, we will create a RESTful API with the response of data in JSON format.

Here's the architecture of the RESTful API:

Restful Api

The client can be from various platforms, such as web, desktop or mobile app.

Awesome right?

Ok, Let’s dive right in.

 

Step #1. Define Your RESTful API

This is important!

Before creating a RESTful API, the first thing you need to do is define EndPoint of the RESTful API that you will create.

RESTful API uses HTTP verbs.

HTTP verbs that are commonly used are GET, POST, PUT, and DELETE.

GET to get data from a server or better known as READ, POST to CREATE new data, PUT to UPDATE data, and DELETE to delete data.

Or better known as CRUD (Create-Read-Update-Delete).

In this tutorial, I will share with you how to create a simple RESTful API to retrieve data from the server (GET), create new data to the server (POST), update data to the server (PUT), and delete data to the server (DELETE) from a table in the database namely product.

Here's the design of the RESTful API that we will create:

End Point

 

Step #2. Create a Database and Table

Create a new database with MySQL. You can use tools like SQLyog, PHPMyAdmin or similar tools.

Here I create a database named restful_db.

If you create a database with the same name it's better.

To create a database with MySQL, it can be done by executing the query below:

CREATE DATABASE restful_db;

The SQL command above will create a database named restful_db.

After that, create a table in the restful_db database.

Here I create a table named: product.

If you create a table with the same name it's better. 

To create a product table, it can be done by executing the SQL command below:

CREATE TABLE product(
product_id INT(11) PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(200),
product_price INT(11) 
)ENGINE=INNODB;

After that, insert some data into the product table by executing the query below:

INSERT INTO product(product_name,product_price) VALUES 
('Product 1','2000'),
('Product 2','5000'),
('Product 3','4000'),
('Product 4','6000'),
('Product 5','7000');

The SQL command above will input 5 data into the product table.

 

Step #3. Install Dependencies

Before installing dependencies, please create a new folder, here I create a folder named restful-api.

Consider the following picture for more details:

Project Structure

This restful-api folder will be our project folder in this tutorial.

Alright, Let's continue, 

In this tutorial, we need 3 dependencies below:

1. Express (node.js framework)

2. MySQL (MySQL driver for node.js)

3. Body-parser (middleware to handle post body request)

To install dependencies on node.js it can be done easily by using NPM (Node Package Manager).

You can run NPM in Terminal or Command Prompt.

However, in this tutorial I don't use Command Prompt, instead, I use Git Bash Terminal.

I highly recommend that you also use Git Bash.

You can download Git Bash in the following URL:

https://git-scm.com/downloads

Download it according to your platform, then install it on your computer.

If you have installed Git Bash, please open the restful-api folder that was created before.

Then right-click and select Git Bash Here, like the picture below:

git bash terminal

Then the terminal will appear as shown below:

gitbash terminal

In the terminal above, you can install all the dependencies that you need for your project.

Before installing dependencies, the first thing you need to do is create a package.json file.

To create package.json, you can run the following command on the terminal.

npm init

Like the picture below:

NPM Init

The above command will automatically create a file called package.json on your project.

After that,

Install all the dependencies needed by typing the following command in the terminal:

npm install --save express mysql body-parser

Like the picture below:

install dependencies

The above command will install all the dependencies that we need: express, mysql, and body-parser.

If you open the package.json file, it will look like this:

{
  "name": "restful-api",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo "Error: no test specified" && exit 1"
  },
  "author": "M Fikri",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.18.3",
    "express": "^4.16.4",
    "mysql": "^2.16.0"
  }
}

 

Step #4. Create Index.js File

Create a file named index.js. Like the following picture:

index-js

Then open index.js and type the following code:

const express = require('express');
const bodyParser = require('body-parser');
const app = express();
const mysql = require('mysql');

// parse application/json
app.use(bodyParser.json());

//create database connection
const conn = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'restful_db'
});

//connect to database
conn.connect((err) =>{
  if(err) throw err;
  console.log('Mysql Connected...');
});

//show all products
app.get('/api/products',(req, res) => {
  let sql = "SELECT * FROM product";
  let query = conn.query(sql, (err, results) => {
    if(err) throw err;
    res.send(JSON.stringify({"status": 200, "error": null, "response": results}));
  });
});

//show single product
app.get('/api/products/:id',(req, res) => {
  let sql = "SELECT * FROM product WHERE product_id="+req.params.id;
  let query = conn.query(sql, (err, results) => {
    if(err) throw err;
    res.send(JSON.stringify({"status": 200, "error": null, "response": results}));
  });
});

//add new product
app.post('/api/products',(req, res) => {
  let data = {product_name: req.body.product_name, product_price: req.body.product_price};
  let sql = "INSERT INTO product SET ?";
  let query = conn.query(sql, data,(err, results) => {
    if(err) throw err;
    res.send(JSON.stringify({"status": 200, "error": null, "response": results}));
  });
});

//update product
app.put('/api/products/:id',(req, res) => {
  let sql = "UPDATE product SET product_name='"+req.body.product_name+"', product_price='"+req.body.product_price+"' WHERE product_id="+req.params.id;
  let query = conn.query(sql, (err, results) => {
    if(err) throw err;
    res.send(JSON.stringify({"status": 200, "error": null, "response": results}));
  });
});

//Delete product
app.delete('/api/products/:id',(req, res) => {
  let sql = "DELETE FROM product WHERE product_id="+req.params.id+"";
  let query = conn.query(sql, (err, results) => {
    if(err) throw err;
      res.send(JSON.stringify({"status": 200, "error": null, "response": results}));
  });
});

//Server listening
app.listen(3000,() =>{
  console.log('Server started on port 3000...');
});

 

Step #5.  Testing

Test the API that we have made to make sure that the RESTful API is running well.

To test the API, there are many tools that you can use.

In this tutorial, I use POSTMAN to test the API that we have created.

If you also use POSTMAN it's better.

You can download POSTMAN on the official website:

https://www.getpostman.com/

Download and install POSTMAN on your computer then open.

If POSTMAN has been opened, it will look like the picture below:

postman

Alright, it's time for testing:

Running the project by typing the command:

node index

In the terminal as shown below:

node server running

If it looks like the picture above, it means running well.

Let's test the EndPoint one by one.

 

#1. Get All Products (GET)

Open the POSTMAN, and type the following URL in the Postman URL column:

http://localhost:3000/api/products

Select the "GET" method, then click the Send button, and then the results will appear as shown below:

get all products

In the picture above, it can be seen that EndPoint to get all the product data is running well.

 

#2. Get Single Product (GET)

Type the following URL in the URL column to get a single product:

http://localhost:3000/api/products/2

Select the GET method, then click the Send button, and then the results will appear like the picture below:

get single product

In the picture above, it can be seen that only one product data is displayed, that's the product with product_id='2' according to the parameters in the URL.

 

#3. Create a New Product (POST)

Type the following URL in the URL column to create a new product:

http://localhost:3000/api/products

Select the POST method, then enter the following data in the JSON column (application / json):

{
	"product_name": "Product 6 Added",
	"product_price": 6000
}

Then click the Send button, the results will appear as shown below:

Post a new Product

If you notice in the response section, there are "affectedRows": 1, and "insertId": 6.

Itu berarti terdapat satu data yang diinsert ke database dengan product_id=’6’.

 

#4. Update Product (PUT)

Type the following URL in the URL column to update the product:

http://localhost:3000/api/products/2

Select the PUT method, then enter the following data in the JSON column (application / json):

{
	"product_name": "Product 2 Update",
	"product_price": 7000
}

Then click the Send button, the results will appear as shown below:

Update product

If you notice in the response section, there are "affectedRows": 1, and "changedRows": 1.

That means there is one data that is updated to the database with product_id = '2' according to the parameters in the URL.

 

#5 Delete Product (DELETE)

Type the following URL in the URL column to delete the product:

http://localhost:3000/api/products/6

Select the DELETE method, then click the Send button, the results will appear as shown below:

Delete Product

If you notice in the response section, there are "affectedRows": 1, "insertId": 0, and "changedRows": 0.

That means there is one data that is deleted to the database with product_id = '6' according to the parameters in the URL.

 

Conclusion

The discussion today is about how to create a simple RESTful API with node.js, express, and MySQL.

REST (REpresentational State Transfer) is a communication method that uses the HTTP protocol to exchange data and this method is often applied in application development.

The purpose is to make the system with good performance, fast, and easy to scale, especially in the exchange and data communication.

In this tutorial, you have learned how to create a simple RESTful API with node.js, express, and MySQL.

Now you can create more complex APIs.

So what are you waiting for, let's coding!

Download Source Code

Share:



Sponsorship:


Comments (2)

FX Bayu Anggara, 16 April 2019 10:15 -

Very nice and easy to understand tutorial, thank you.

M Fikri, 19 April 2019 08:24 -

You are welcome

Leave a Comment