Easiest Way To Create An RESTfulAPI Using Node.js And Mysql

Abi-Tech Pupil
6 min readAug 24, 2020

--

Hello there, In this article, we are going to look for the best practice and simple steps to create a Restful API using Node.js and MySQL. The stuff we are going to use in this Restful API is Node.js, Express js, MySQL server, MySQL workbench.

What Is Restful API?

Before getting into a Restful API. Let us know what is an API?

Application Program Interface (API) is a code used as an intermediator between two Software programs to communicate with each other. Rest API is the architectural style, that uses HTTP request to GET, PUT, POST and DELETE data in Database

Installations

Download and install the node.js from https://nodejs.org/en/

MySQL server, MySQL workbench from https://dev.mysql.com/downloads/ After the installation follows the below step to execute.

1. The First Step to Install npm Packages

Create a folder where you going to work on API creation. Then open a command terminal of that folder.

After that, enter the npm init and press enter. It will ask for certain details about the project if you needed to mention anything you can mention it otherwise keep it default and after it will create a package.json file.

package.json

It is a JSON file used for managing the project dependencies, scripts, version, and a lot more.

Then install npm packages express, body-parser, Mysql by entering the following command in a command terminal

npm install — save express@4.17.1 mysql@2.18.1 body-parser@1.19.0

After the installation checks, the packages are installed in package.json file it holds the data of installed packages.

2. Create MySql Database(use Mysql Workbench)

To create a database right-click on the schema panel and click on creating a schema. And provide a database and click apply. After that action database is created.

And create a table click on the SQL query tab above the schema panel. And pass the below query to create an employee table.

Use employeedb;

CREATE TABLE `employee` (

`EmpId` int(11) NOT NULL AUTO_INCREMENT,

`Name` varchar(45) DEFAULT NULL,

`EmpCode` varchar(45) DEFAULT NULL,

PRIMARY KEY (`EmpId`)

) ENGINE = InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_0900_ai_ci;

Before getting into an database connection let us add an dummy data in database to get an structure. For that execute the following query

Use employeedb;

INSERT INTO `employee` VALUES (1, ‘Jhon Doe’, ‘EMP001’),(2, ‘Peter Parker’, ‘EMP002’),(3, ‘Kevin’, ‘EMP003’);

After that check whether a data is added in a table using the query

SELECT * FROM employeedb.employee

3. Database Connection

Create a root and main javascript file index.js. After that, to connect with the database we needed to import the MySQL package by calling require function and save it in a variable to access it anywhere in the file.

And give a login database details. After that execute a connect function to connect with the database. Use a CallBack function to throw an error if the connection does not execute.

Important note: Do Reconfigure Authentication Method, there you select legacy type password support for 5.1(MySQL installer).

The below image shows the above-mentioned coding practice.

4. API CREATION

Before getting into an API let us know about the express js. It is a well-known node js framework. It will provide more flexibility for the coder to develop a code in node js. Express js is a third party module.

To use the express js, initially import the express js and store it in a variable as you decide. We have now only imported the express js but we need to initialize the express. For that, we need to execute the express function as mentioned below.

Then import a body-parser third-party module in index.js file and it is also called as a middleware. It means body-parser is work in between the post and get method of HTTP API. It will watch the CRUD function. And all the API requests are passed through a middleware. By using a body-parser(middleware) we can achieve which method is calling, whether GET or POST method is requested by a user. For that purpose only we are using body-parser.

To use body-parser in API we need to call a middleware inside the express function. As we have mentioned below.

Now we need to start a server for API to use. For that, we give a port number and we have the callback function to find the server is existing or not.

To fetch the data inside the database we needed to call the get function. we are going to pass the route value and callback function as a parameter. Inside the callback function, we are going to pass two parameters, they are request and response. This callback function invoked when the user requests the route. As we have mentioned below.

Inside the callback function, we are going to pass a query to fetch the data inside the database. Inside that, we are going to pass the SELECT query to fetch data. To save data from the database we are using another callback function. Inside the callback function, we are going to pass three parameters they are, err, row, fields.

err throws the error and row shows the data inside the database. To print, the data(row) from the database we have to call the res.send() method. Inside the res.send method, we are going to pass the parameter in which one has data from the database. After that, it will show data(JSON) in the URL. To get that response please follow the code as mentioned below.

cmd node index.js

After entering the coding in a command terminal then run the command by pressing the button Enter. Then we get a console message server is running in port number 3000 and DB is connected. It represents the database and the port is working properly.

Then pass the URL http://localhost:3000/employees in the browser. As a response, we get the array of an object which holds the data in the database. In other words, the data will be in JSON format. By using the JSON file we can get all data from a database to display.

I hope you enjoyed the small article of API Creations. Thank you.

By Keerthi, Software Developer @ ABI-Tech Solution

--

--