Building a REST API with Node.js, MySQL, and Express

Tags: mysql
Published: 2021-12-21 22:58:07

Node.js is commonly used in conjunction with NoSQL databases such as MongoDB, although it also works well with relational databases such as MySQL, PostgreSQL, and others. MySQL has been around for decades and is one of the widely used relational database management systems (RDBMS). The world's most open-source database is an ideal RDBMS for both small and large applications.

This tutorial will explore how to build a REST API with Express and MySQL by creating a simple database for adding, updating, and removing todos. You can follow along by cloning the [GitHub repository](https://github.com/icode247/Serverside-rendering-FastAPI) for this project. Let's get started!

## Prerequisites

This tutorial is a hands-on demonstration. Be sure you have the following in place before getting started:

1. [MySQL](https://docs.arctype.com/connect/mysql) server installed on your computer.
2. [Node.js](https://nodejs.org/en/download/) installed on your computer.

All the demonstrations will use Ubuntu 20.04 with Node 14 LTS. It also works for other operating systems and other versions of Linux.

## What is REST?

REST (Representational State Transfer) is a software architectural style that developers established to assist in creating and developing the World Wide Web's architecture. REST specifies a set of criteria on how the architecture of an Internet-scale distributed hypermedia system, such as the Web, should operate. It is one of the various ways applications, servers, and websites may communicate data and services. It generally provides the rules for how developers working with data and services represent elements through the API. Other programs may appropriately request and receive the data and services that an API makes accessible.

## Why use REST?

REST has several unique characteristics that make it one of the top software architectural styles extensively employed by software engineers.

1. **It is lightweight**: REST APIs use the HTTP standard. Thus you can use XML, JSON, HTML, and other data formats. REST APIs are, therefore, quick and lightweight, making them an excellent choice for developing mobile applications, internet of things devices, and other applications.
2. **It is independent:**  The separation of client and server in a REST API makes it independent. The REST protocol separates the data storage and the user interface from the server, making it easy for developers to collaborate in building a project.
3. **It is scalable and flexible**: REST APIs are scalable and flexible to use due to the separation of the client and the server, allowing developers to integrate REST APIs without any extra effort simply.
4. **It is stateless**: Calls are made to REST independently, and each of these calls contains all the data to complete the request.
5. **It has a uniform interface** that allows independent evolution of the application without having the application's services or models and actions tightly coupled to the API layer itself.
6. **It is a layered architectural system** that builds a hierarchy that helps create a more scalable and modular application.

## Overview of MySQL databases

[MySQL](https://www.geeksforgeeks.org/structured-query-language/) is an open-source relational database management system (RDBMS). It is the most popular database system used with PHP. MySQL is a fully-managed database service used to deploy cloud-native applications. HeatWave, an integrated, high-performance query accelerator, boosts MySQL performance by 5400x.

MySQL is developed, distributed, and supported by Oracle Corporation with the following features.

- The data in a MySQL database lives in tables that consist of columns and rows.
- MySQL is a database system that runs on a server.
- MySQL is ideal for both small and large applications.
- MySQL is a very fast, reliable, and easy-to-use database system. It uses standard SQL.
- MySQL compiles on several platforms.

## Creating a Node.js application

Now that we have explored what REST is all about, and had a quick overview of the MySQL database, let's dive into creating our RESTFul application. First, we need to create a folder for our project with the command below:

    mkdir rest-with-mysql && cd rest-with-mysql

The above command will create a *rest-with-MySQL* folder and change the directory to the folder. Next, we'll initialize a new node.js project with the command below:

    npm init -y

The above command will initialize a new Node.js project, skipping all the prompts for the project details. The command will also create a package.json file in our project's root directory, which will record all the essential metadata about our project. You can decide to go through the prompts to enter the project details by removing the flag from the command.

Next, we'll install the dependencies we require for our project.

    npm install express MySQL cors
    
The above command will take a little while to complete, but we'll have [Express](https://www.npmjs.com/package/express), MySQL, and [Cors](https://www.npmjs.com/package/cors) installed at the end. Finally, let's look at our project structure. At the end of this tutorial, our project structure will look like this:
![The structure of our application.](https://arctype.com/blog/content/images/2021/12/Screen-Shot-2021-12-21-at-3.30.15-PM.png)

## Setting up the Express server

Now that we have our dependencies installed let's put them to work by first setting up our express server.

Create an *app.js* file and add the following code snippet below to it. We'll import the following:

- **Express:** To create our server.
- **Cors:** To allow and redirect request resources.
- **Router:** This is where our API routes will be defined later in the sections.
- **AppError and errorHandler:** These are our global error handler functions. We will create them later.

```
const express = require("express");
const cors = require("cors");
const router = require("./routes");
const AppError = require("./utils/appError");
const errorHandler = require("./utils/errorHandler");
….
```
    
Next, we create an **app** instance from express, using the **express.json()** middleware in our app to parse the URL encoded body. Finally, we make our API router middleware listen to incoming requests to the URL specified.

Then, we check for URLs missing from our endpoints and throw a [404 ](https://www.ionos.com/digitalguide/websites/website-creation/what-does-the-404-not-found-error-mean/)error to the user if they're accessed. The global error handler will handle this (we will create it in subsequent sections).

    app.use(api, router);
    
    app.all("*", (req, res, next) => {
     next(new AppError(`The URL ${req.originalUrl} does not exists`, 404));
    });
    app.use(errorHandler);
    ...

Finally, we will configure our application to listen to port **3000.**

    const PORT = 3000;
    app.listen(PORT, () => {
     console.log(`server running on port ${PORT}`);
    });
    
    module.exports = app;

## Setup and connect to MySQL

Now, we have our Express server set up. Let's go ahead and set up our MySQL Database. First, we'll open our MySQL shell with the command below:

    //On Windows
    MySQL
    
    //Ubuntu
    mysql -u root -p

The above command will prompt for your root password.

![Screenshot of a password prompt.](https://arctype.com/blog/content/images/2021/12/Screen-Shot-2021-12-21-at-3.34.05-PM.png)
Enter the password and press the Enter key to continue. If everything goes well, you should see a similar output to the screenshot below on your terminal.
![Shell screenshot.](https://arctype.com/blog/content/images/2021/12/Screen-Shot-2021-12-21-at-3.34.36-PM.png)

Next, execute the SQL statements below on your MySQL shell to create our **todos** database.

    CREATE DATABASE todos
    
Then, execute the command below to create our **todolist** table. The table will have an **id, name, status, date_created** fields. The id filed will be the [primary key](https://searchsqlserver.techtarget.com/definition/primary-key#:~:text=A%20primary%20key%2C%20also%20called,and%20only%20one%20primary%20key.) of our table.

    CREATE TABLE todolist(id int NOT NULL AUTO_INCREMENT,
    name varchar(50) NOT NULL, 
    status varchar(50), 
    date_created DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (id));

Next, create ***services*** folder in our project root directory. In the **services** folder, create a **db.js** file and add the following code snippets below to it.

    const mysql = require('mysql');
    const conn = mysql.createConnection({
     host: "localhost",
     user: "root",
     password: "1234",
     database: "todos",
    });
    
    conn.connect();
    
    module.exports = conn;

The above code will connect to our MySQL database using the MySQL **createConnection** method. The createConnection method takes the **host**, **username**, **password**, and **database** name as required parameters.

## Creating application controllers

We have successfully connected our MySQL database. Let's proceed to create the routes for our application.

In our project root directory, create a **controllers** folder, then create an **index.js** file in the **controllers** folder.

First, we'll import our global error handler and our MySQL database connection.

    const AppError = require("../utils/appError");
    const conn = require("../services/db");
    
Next, we'll create our **getAllTodos** handler to fetch all the todos in our database. This handler will use the MySQL query method, which takes an SQL query and a callback function as parameters. If an error occurs during the operation, we'll return the error to the user using the **AppError** class. Then, we return the data to the user when the operation runs successfully with a **200 status** code.

    exports.getAllTodos = (req, res, next) => {
     conn.query("SELECT * FROM todolist", function (err, data, fields) {
       if(err) return next(new AppError(err))
       res.status(200).json({
         status: "success",
         length: data?.length,
         data: data,
       });
     });
    };

Next, we'll create our **createTodo** handler to add new todos to our database. Then we check if the client is sending an empty form and return a **404 error** message.

Then, we get the todo name from the request body and set the status of each todo created to **pending** by default. Using the query mysql method, we create an insert query to add the todo to our database.

    exports.createTodo = (req, res, next) => {
     if (!req.body) return next(new AppError("No form data found", 404));
     const values = [req.body.name, "pending"];
     conn.query(
       "INSERT INTO todolist (name, status) VALUES(?)",
       [values],
       function (err, data, fields) {
         if (err) return next(new AppError(err, 500));
         res.status(201).json({
           status: "success",
           message: "todo created!",
         });
       }
     );
    };

Next, we create a **getTodo** handler to get our todos by IDs. First, we'll check if the id is specified in the request parameter, then return an error to the client if none was found.

We’ll execute an SQL statement to get todo whose id is on the request parameter and return it to the client.

    exports.getTodo = (req, res, next) => {
     if (!req.params.id) {
       return next(new AppError("No todo id found", 404));
     }
     conn.query(
       "SELECT * FROM todolist WHERE id = ?",
       [req.params.id],
       function (err, data, fields) {
         if (err) return next(new AppError(err, 500));
         res.status(200).json({
           status: "success",
           length: data?.length,
           data: data,
         });
       }
     );
    };

Next, we’ll create our **updateTodo** handler to update our todos, this time will modify the todo whose **id** is in the request parameter to be completed.

    exports.updateTodo = (req, res, next) => {
     if (!req.params.id) {
       return next(new AppError("No todo id found", 404));
     }
     conn.query(
       "UPDATE todolist SET status='completed' WHERE id=?",
       [req.params.id],
       function (err, data, fields) {
         if (err) return next(new AppError(err, 500));
         res.status(201).json({
           status: "success",
           message: "todo updated!",
         });
       }
     );
    };
    
Finally, we’ll create a** deleteTodo** handler to delete a todo from our database. We’ll execute a delete statement to delete a todo whose **id** is the request parameter.

    exports.deleteTodo = (req, res, next) => {
     if (!req.params.id) {
       return next(new AppError("No todo id found", 404));
     }
     conn.query(
       "DELETE FROM todolist WHERE id=?",
       [req.params.id],
       function (err, fields) {
         if (err) return next(new AppError(err, 500));
         res.status(201).json({
           status: "success",
           message: "todo deleted!",
         });
       }
     );
    }

## Creating global error handlers

Now, let's quickly create our global error handlers. To get started, create a **utils** folder in our project root directory. Then, create an **appError.js** and **errorHandler.js** files. Add the following code snippet below to the **appError.js** file.

    class AppError extends Error {
     constructor(msg, statusCode) {
       super(msg);
    
       this.statusCode = statusCode;
       this.error = `${statusCode}`.startsWith('4') ? 'fail' : 'error';
       this.isOperational = true;
    
       Error.captureStackTrace(this, this.constructor);
     }
    }
    module.exports = AppError;
    
The above code creates an **AppError** class that extends the built-in **Error** class. We'll then pass the error **message** and **status** to the Error class constructor. Then we'll check what type of error occurred in our application with the start of the status code, and add the error to the error stack trace.

Next, open the **errorHandler.js** file and add the code snippet below:

    module.exports = (err, req, res, next) => {
     err.statusCode = err.statusCode || 500;
     err.status = err.status || "error";
     res.status(err.statusCode).json({
       status: err.status,
       message: err.message,
     });
    };

The above code will check for possible errors in our application and send the corresponding error and status code to the client without breaking our application.

## Creating application routes

In our project root directory, create a **routes** folder, then create an **index.js** file, and add the following code snippet below to it.

    const express = require("express");
    const controllers = require("../controllers");
    const router = express.Router();
    
    router.route("/").get(controllers.getAllTodos).post(controllers.createTodo);
    router
     .route("/:id")
     .get(controllers.getTodo)
     .put(controllers.updateTodo)
     .delete(controllers.deleteTodo);
    module.exports = router;

The above code creates a **router** object from the express router class. Then we make the following routes in our application.

- Get Route: to get all the todos in our database.
- Post Route: to add a new todo to our database
- Get Route: to get a todo by its id
- Put Route: to update a todo by the id
- Delete Route: to delete a todo by the id.

Then we export the router object.

## View Data with Arctype

Now we've created all our routes. Let's now connect to Arctype to visualize the data in our database. To do that, we need to download [Arctype](https://arctype.com/) from the official website. Get Arctype downloaded and installed, and then connect your database to it. When you launch the Arctype, click on MySQL to create a connection - [follow this guide](https://docs.arctype.com/connect/mysql) if you need help.
![Image of Arctype on launch.](https://arctype.com/blog/content/images/2021/12/Screen-Shot-2021-12-21-at-3.55.15-PM.png)

Then, we’ll add our connection credentials and press the **save** button.

![Screenshot of adding credentials in Arctype.](https://arctype.com/blog/content/images/2021/12/Screen-Shot-2021-12-21-at-3.56.12-PM.png)

We have successfully connected to our database. You should see our database tables on the right-hand side of the Arctype dashboard. Click on the **todolist** table to run some queries on our database on Arctype.

We can add a new record to our database by clicking on the **Insert row** tab.

![Screenshot of insert rows function in Arctype.](https://arctype.com/blog/content/images/2021/12/Screen-Shot-2021-12-21-at-3.57.13-PM.png)

We can also **edit** and query our database table using Arctype... and more.

## Conclusion

You now understand how to build a REST API using the Express framework—install the Express Node.js package, create a MySQL database, and create a simple to-do application. That's all it takes!

Perhaps you can learn more on how to use MySQL with Node.js [here](https://arctype.com/mysql/drivers/node-mysql). Feel free to reach out on [Twitter](https://twitter.com/EkekentaZion) if you have any questions!