REST is an architectural style that provides standards and a set of rules that systems on the web follow. This standards make the communication between systems and different applications much easier and more flexible. To be RESTful, a REST API must satisfy some principles. To learn more about REST and RESTful API, you can go here What is RESTful API?.
This tutorial is walk through on how to build a complete RESTful API with node.js and Express and connect it to a MySQL database. To follow along this process, you should already have some familiarity with node.js, Express and MySQL server. If it’s not the case, the following links may be helpful for you.
- How to install MySQL Server on Windows .
- How to install MySQL Server on mac OS.
- How to install node.js.
Related Articles:
- How to Build a Complete API for User Login and Authentication using MySQL and Node.js.
- Node.js + MySQL : Add Forgot/Reset Password to Login-Authentication System.
- How to store Session in MySQL Database using express-mysql-session.
- How to add Custom Error Handler Middleware to your Node.js/Express API.
- Complete JWT Authentication and Authorization System for MySQL/Node.js API.
- Automating Your MySQL Database with Triggers.
- How to use ON DELETE CASCADE in MySQL.
- MySQL + Event Scheduler: How to delete data from MYSQL table using Event Scheduler.
- Using MySQL Event Scheduler: A Complete Guide.
- How to get Request Origin in Express.js?
- How to get the Request Host in express.js?
- How to Get Current Date and Time in JavaScript.
- Nodemailer + Gmail: How to Send Emails from Node.js API using Gmail.
- Node.js + Nodemailer : How to send Emails via SMTP with Nodemailer
Create a RESTful API
The first step in this tutorial is to set up our server boilerplate code. For that you need to have node.js installed and running. Then in your projects folder, create a new folder for your API, let’s name it: myRESTfulAPI. You can do that manually or by running the command line: mkdir myRESTfulAPI. Then open the blank folder myRESTfulAPI in your code editor (mine is Visual Studio code) then execute the following command in the terminal:
npm init
Now we are ready to setup our environment and install everything we need.
For creating and publishing our API we will use the node.js framework Express. This framework is an amazing tool that allows you to quickly launch a functional API and you can combine it with many other packages. So first, we need to install Express by running this Command:
npm install --save express
Inside your myRESTfulAPI directory, create a new file: server.js and open it.
Then install body parser package and setup body-parsing middleware in order to read the “body” of an incoming JSON object in any response. But if you are using Express version 4.16+, the body-parser implementation is now included in the default Express package so there is no need for you to download and install another dependency, the only thing you have to do is adding this to your code.
app.use(express.json()); //use express bodyparser to parse JSON bodies
but, in our code we will install and use the usual body parser package.
npm install --save body-parser
After that, install CORS middleware with the cors package. For now you can use the default settings. CORS package will help you to configure the Allow-Control-Allow-Origin header inside the application code. This is much better than setting your headers with plain text in your application code.
npm install --save cors
Next, install Morgan to output request details on the console, store your HTTP requests and give you concise insight into how your app is being used, and where there could be potential errors or other issues that you haven’t yet explored. Morgan is useful when you have many middleware and you want to know the sequence of the request flow through various middleware in your application. It will help you understand every operation you perform during building or testing your API.
Now, import all the packages you have just installed and setup the corresponding middlewares in your server.js file.
server.js
const express =require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const morgan = require('morgan');
const app = express();
app.use(bodyParser.json());
app.use(cors());
app.use(morgan('dev'));// we use morgan with the pre-defined format "dev" for developer.
module.exports = app;
To save all your environment variables and not to use hard-coded number, in your project directory myRESTfulAPI create a new file .env then open it and add your API port: PORT variable to it. we use here the port 3000 but you can change it if you want.
.env
PORT = 3000
To loads environment variables from .env file, first install dotenv package in you application
npm install --save dotenv
then add this line of code to your server.js.
require("dotenv").config();
Then define the constant PORT
const PORT= process.env.PORT;
Now, you can start the server listening on the provided PORT.
server.js
require("dotenv").config();
const express =require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const morgan = require('morgan');
const app = express();
const PORT= process.env.APP_PORT;
app.use(bodyParser.json());
app.use(cors());
app.use(morgan('dev'));
app.listen(PORT, ()=>{
console.log(`server is listening on ${PORT}`);
});
module.exports = app;
To watch your server you need to install nodemon, so you wont need to restart you server every time you make changes in your code.
npm install --save nodemon
Add the following code line to your package.json:
"start": "nodemon server.js "
So your Package.json will look like this:
//package.json
{
"name": "myRESTfulAPI",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1",
"start": "nodemon server.js "
},
"author": "",
"license": "ISC",
"dependencies": {
"body-parser": "^1.19.0",
"cors": "^2.8.5",
"dotenv": "^8.2.0",
"express": "^4.17.1",
"morgan": "^1.10.0",
"nodemon": "^2.0.4",
"save": "^2.4.0"
}
}
The next step is to create a new file named apiRouter.js inside myRESTfulAPI directory.This apiRouter will serve as the starting point for all your API routes..
apiRouter.j
//apiRouter.js
const express =require('express');
const apiRouter = express.Router();
module.exports = apiRouter;
Mount the existing apiRouter into server.js.
require("dotenv").config();
const express =require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const morgan = require('morgan');
const apiRouter = require('./apiRouter');
const app = express();
const PORT= process.env.PORT;
app.use(bodyParser.json());
app.use(cors());
app.use(morgan('dev'));
app.use('/apiRouter',apiRouter)
app.listen(PORT, ()=>{
console.log(`server is listening on ${PORT}`);
});
module.exports = app;
Connect RESTful API to Database
In this tutorial we have our data stored in a MySQL database named “RESTdatabase” with one table named “Employee“. We used the following query to create Employee table :
If you want to know how to create a database using node.js click here: How to create MySQL database using node.js.
CREATE TABLE Employee (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
position varchar(255) NOT NULL,
wage INT NOT NULL,
PRIMARY KEY (id))
After creating your database and your table , you have to save all the environment variables related to your database in your .env file, so you will be able to load and use them later.
.env
PORT = 3000
DB_PORT = “yourDBport”
DB_HOST = localhost
DB_USER = “yourusername”
DB_PASS = “yourDBpassword”
MYSQL_DB = “yourDBname”
CONNECTION_LIMIT = 10
Connect node.js to MySQL database
To connect and access MySQL database with node.js you need to follow this steps:
- first, install mysql module using this command line:
npm install --save mysql
- Then, inside myRESTfulAPI folder create a new file db.js where you will setup your connection to the database and write your queries after importing mysql module by adding this line of code to db.js:
const mysql =require(‘mysql’)
- In this tutorial, We are using connection pool to connect our database. If you want to now more about connection pooling and how it works you can check this article: Why is Connection Pooling better than Single Connection?.
//db.js
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: process.env.CONNECTION_LIMIT, // the number of connections node.js will hold open to our database
password: process.env.DB_PASS,
user: process.env.DB_USER,
database: process.env.MYSQL_DB,
host: process.env.DB_HOST,
port: process.env.DB_PORT
});
let db = {}; //create an empty object you will use later to write and export your queries.
module.exports = db
- Now we are ready to write our queries (INSERT, SELECT, UPDATE, DELETE) necessary to operate all CRUD (Create, Read, Update, Delete) operations. For that you will write all this queries as methods of the db object in your db.js:
// db.js
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: process.env.CONNECTION_LIMIT, // the number of connections node.js will hold open to our database
password: process.env.DB_PASS,
user: process.env.DB_USER,
database: process.env.MYSQL_DB,
host: process.env.DB_HOST,
port: process.env.DB_PORT
});
let db = {};
db.getAllEmployees = () =>{
return new Promise((resolve, reject)=>{
pool.query('SELECT * FROM Employee ', (error, employees)=>{
if(error){
return reject(error);
}
return resolve(employees);
});
});
};
db.getOneEmployee = (id) =>{
return new Promise((resolve, reject)=>{
pool.query('SELECT * FROM Employee WHERE id= ?', [id], (error, employee)=>{
if(error){
return reject(error);
}
return resolve(employee);
});
});
};
db.insertEmployee = (name, position, wage) =>{
return new Promise((resolve, reject)=>{
pool.query('INSERT INTO Employee (name, position, wage) VALUES (?, ?, ?)', [name, position, wage], (error, result)=>{
if(error){
return reject(error);
}
return resolve(result.insertId);
});
});
};
db.updateEmployee = (name, position, wage, id) =>{
return new Promise((resolve, reject)=>{
pool.query('UPDATE Employee SET name = ?, position= ?, wage= ? WHERE id = ?', [name, position, wage, id], (error)=>{
if(error){
return reject(error);
}
return resolve();
});
});
};
db.deleteEmployee = (id) =>{
return new Promise((resolve, reject)=>{
pool.query('DELETE Employee WHERE id= ?', [id], (error)=>{
if(error){
return reject(error);
}
return resolve();
});
});
};
module.exports = db;
We used promises instead of callback functions in our queries because it makes coding easier when we are using async/await to write our routes. For more details about async/await functions and promises go here: How to interact with MySQL database using async/await promises in node.js ?

Adding Routes to the API
Before to add routes to your apiRouter.js using HTTP methods, first you need to pull in the db object from db.js by adding this line of code to your apiRouter.js:
const db = require(‘./db.js’);
Then make requests to the database using the queries you wrote already in db.js, for that, we use async function so it doesn’t block the processing of the code below it.
//apiRouter.js
const express =require('express');
Const apiRouter = express.Router();
Const db = require(‘./db.js’);
apiRouter.get('/', async (req, res, next)=>{
try {
const employees = await db.getAllEmployees();
res.status(200).json({employees: employees});
} catch(e) {
console.log(e);
res.sendStatus(500);
}
});
apiRouter.param('employeeId', async (req, res, next, employeeId)=> {
try{
const employee = await db.getOneEmployee(employeeId);
req.employee = employee;
next(); // go to apiRouter.get('/:employeeId')
} catch(e) {
console.log(e);
res.sendStatus(404);
}
});
apiRouter.get('/:employeeId', (req, res, next)=>{
res.status(200).json({employee: req.employee});
});
apiRouter.post('/', async (req, res, next)=>{
try{
const name = req.body.employee.name;
const position = req.body.employee.position;
const wage = req.body.employee.wage;
if (!name || !position || !wage) {
return res.sendStatus(400);
}
const employee = await db.insertEmployee(name, position, wage).then(insertId=>{return db.getOneEmployee(insertId);});
res.json({employee: employee});
} catch(e){
console.log(e);
res.sendStatus(400);
}
});
apiRouter.put('/:employeeId', async (req, res, next)=>{
try{
const name = req.body.employee.name;
const position = req.body.employee.position;
const wage = req.body.employee.wage;
const employeeId= req.params.employeeId;
if (!name || !position || !wage) {
return res.sendStatus(400);
}
const employee = await db.updateEmployee(name, position, wage, employeeId).then(()=>{return db.getOneEmployee(employeeId);});
res.json({employee: employee});
} catch(e){
console.log(e);
res.sendStatus(400);
}
});
apiRouter.delete('/:employeeId', async (req, res, next)=>{
try{
const employeeId = req.params.employeeId;
const response = await db.deleteEmployee(employeeId);
return res.sendStatus(204);
} catch(e){
console.log(e);
}
})
module.exports = apiRouter;

Summary
You have noticed that our back-end API implementation is separated from any front end parts. Separating the server implementation from the client side is one of RESTful API principles. So, this separation gives you the possibility of using your API with any front-end application of your choice.
You might also like:
Automating Your MySQL Database with Triggers.
How to use ON DELETE CASCADE in MySQL.
MySQL + Event Scheduler: How to delete data from MYSQL table using Event Scheduler.
Using MySQL Event Scheduler: A Complete Guide.
ONE-TO-MANY Association in MYSQL Database using Sequelize async/await with Node.js.
ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js.
Example How to use initialize() Function in Node.js/Express API .
How to use Sequelize async/await to interact with MySQL database in Node.js.
How to add Routes to insert data into MySQL database-related tables in Node.js API?
How to interact with MySQL database using async/await promises in node.js ?
How to Build a Complete API for User Login and Authentication using MySQL and Node.js.
Why is Connection Pooling better than Single Connection?.
How to create MySQL database using node.js.
How to store Session in MySQL Database using express-mysql-session.
Complete Guide to Build a RESTful API with Node.js and Express.
Complete JWT Authentication and Authorization System for MySQL/Node.js API.