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": "geek@example.com"
}

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": "geekina@example.com"
}

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.