How to Create a MySQL REST API

Creating a REST API is important for enabling communication between different software systems. MySQL is one of the most popular relational database management systems which serves as the backbone for data storage in web applications.

In this article, we will learn how to create a REST API using MySQL as the database backend. We will cover the step-by-step process, starting from setting up the MySQL database to testing the API endpoints using tools like Postman or Curl.

What do you mean by REST API?

A RESTful API is an architectural style for designing application program interfaces (APIs) that utilize HTTP requests to interact with and manipulate data. Through operations such as GET, PUT, POST and DELETE, this API enables the retrieval, updating, creation and deletion of various data types and allows efficient resource management within applications.

How to Create a MySQL REST API?

Creating a REST API using MySQL as the backend is a fundamental skill for web developers. Let's go through the process of setting up a MySQL database, initializing a Node.js project installing dependencies setting up an Express server establishing a database connection, and implementing CRUD (Create, Read, Update, Delete) operations. By following these steps we will be able to create a robust API that interacts seamlessly with our MySQL database.

Step 1: Setup the MySQL Database

Let's start with creating a MySQL database and defining the necessary tables to store our data. we can use tools like MySQL Workbench or the command-line interface to do this task. For example, let's first create a simple database.

CREATE DATABASE my_db;

This statement will create a Database to store the data in the form of different tables.

Now, Create a table to store our data. For example, let's create a simple users table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Step 2: Initialize Server Project

Now, Create a new folder for our project and navigate into it. Then, initialize a new Node.js project using follow command.

npm init -y

Step 3: Installing Dependencies

Now, install the required dependencies:

npm install express mysql

Step 4: Setup Express Server

Create a new file for e.g server.js (or any preferred name) and set up a basic Express server:

const express = require('express');
const mysql = require('mysql');

const app = express();
const port = 3000;

app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});

Step 5: Establishing Database Connection

Let's connect our Node.js application to the MySQL database by creating a connection pool:

const conn= mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'your_username',
  password: 'your_password',
  database: 'your_database'
});

Step 6: Implementing CRUD Operations

Now, define routes and handlers to perform CRUD (Create, Read, Update, Delete) operations on the users table:

// Create a new user
app.post('/users', (req, res) => {
    const { name, email } = req.body;
    conn.query('INSERT INTO users (name, email) VALUES (?, ?)', [name, email], (err, result) => {
      if (err) throw err;
      res.send('User added successfully');
    });
  });

Explanation: This route allows us to create a new user by providing the user's name and email in the request body. The server inserts the provided data into the users table in the database.

Create a New User

• Method: POST
  
• Endpoint: /users
  
• Description: This route allows us to create a new user by providing the user's name and email in the request body. The server inserts the provided data into the users table in the database.

POST /users
{
  "name": "Geek",
  "email": "[email protected]"
}

Get all users

• Method: GET
  
• Endpoint: /users
  
• Description: This route retrieves all users from the users table in the database and returns them as a JSON array.
  

 // Get all users
app.get('/users', (req, res) => {
  conn.query('SELECT * FROM users', (err, rows) => {
    if (err) throw err;
    res.json(rows);
  });
});

Explanation: This route retrieves all users from the users table in the database and returns them as a JSON array.

Get User by ID

• Method: GET
  
• Endpoint: /users/:id
  
• Description: This route retrieves a specific user by their ID from the users table in the database and returns their information as a JSON object.

// Get user by ID
app.get('/users/:id', (req, res) => {
  const userId = req.params.id;
  conn.query('SELECT * FROM users WHERE id = ?', userId, (err, rows) => {
    if (err) throw err;
    res.json(rows[0]);
  });
});

This route retrieves a specific user by their ID from the users table in the database and returns their information as a JSON object.

Update User by ID

• Method: PUT
  
• Endpoint: /users/:id
  
• Description: This route updates the information of a specific user identified by their ID. Clients provide the updated name and email in the request body, and the server updates the corresponding record in the users table.

 // Update user by ID
app.put('/users/:id', (req, res) => {
  const userId = req.params.id;
  const { name, email } = req.body;
  conn.query('UPDATE users SET name = ?, email = ? WHERE id = ?', [name, email, userId], (err, result) => {
    if (err) throw err;
    res.send('User updated successfully');
  });
});

This route updates the information of a specific user identified by their ID. Clients provide the updated name and email in the request body, and the server updates the corresponding record in the users table.

Example:

PUT /users/123
{
  "name": "Geekina",
  "email": "[email protected]"
}

Delete User by ID

• Method: DELETE
  
• Endpoint: /users/:id
  
• Description: This route deletes a specific user from the users table in the database based on their ID.
  

// Delete user by ID
app.delete('/users/:id', (req, res) => {
  const userId = req.params.id;
  conn.query('DELETE FROM users WHERE id = ?', userId, (err, result) => {
    if (err) throw err;
    res.send('User deleted successfully');
  });
});

Example

DELETE /users/123

This route deletes a specific user from the users table in the database based on their ID.

Step 7: Testing the API

Finally, start our Express server by running follow command:

 node server.js 

Test your API endpoints using tools like Postman or curl. we can send HTTP requests to GET, POST, PUT, and DELETE data from the users table.

Conclusion

Creating a REST API with MySQL backend is a crucial skill for web developers. By following the steps outlined in this guide, you can build a robust API that interacts seamlessly with your MySQL database. Remember to handle errors easily and secure your API endpoints to ensure the integrity and confidentiality of your data. With this knowledge, you can create powerful and efficient web applications that meet the needs of modern software systems.