Building Masite: A Portfolio Website Builder — Part 5: APIs

This 8 part series focuses on building Masite: A Portfolio Website Builder. Link to the website: https://masite-portfolio-website-builder.vercel.app/

Hi there, I am Mrinal Prakash. I am a Software Developer with a passion for building efficient, scalable, and user-friendly web applications.

Why APIs?

APIs (Application Programming Interfaces) allow different parts of a web application to communicate with each other. In our case, we will use APIs to send data from our front-end to the back-end database, and vice versa. This enables dynamic features like adding new portfolio items, retrieving user information, and updating content based on user interaction.

APIs are essential for building a scalable and maintainable web application. They separate the front-end logic from the back-end, ensuring flexibility and ease of maintenance.

Using Dbeaver for Creating Database

Make sure that your MySQL server is running locally with the credentials used in the code:

• Host: localhost
• User: root
• Password: root
• Database: sys

If the table user doesn't exist in the sys database, create it with a basic structure:

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    jobTitle VARCHAR(255) NOT NULL,
    description TEXT,
    profileImg VARCHAR(255),
    articleFeedLink VARCHAR(1000)
);

Using Insomnia for API Testing

To test our APIs during development, we can use Insomnia, a REST client that simplifies the process of sending HTTP requests. Here’s how you can use it to test your API:

When you will install and open insomnia you will get to see the following screen:

1. Add a New Request in Insomnia

First, click on the ‘+’ icon located on the right side of the filter label in Insomnia. This will allow you to create a new request for testing your API.

2. Set Up the Request

Now, you can create a new request depending on the API you want to test:

• POST Request: If you’re testing data insertion, choose the POST method.
• GET Request: If you’re testing data retrieval, choose the GET method.

For this example, we’ll focus on creating a POST request.

3. Define the API Endpoint

• Endpoint: Enter the API endpoint that you want to test. If you’re running your server locally (for example, with Next.js), the endpoint could look something like this:

http://localhost:3000/api/users/add

4. Choose the HTTP Method

• Method: Set the method to POST.

5. Add Data to the Request Body

For POST requests, you need to send data in JSON format:

• Body: Select JSON as the body type in Insomnia.
• Sample Data: Enter the following sample data to test the API’s functionality for inserting a new user:

{
  "name": "Mrinal Prakash",
  "profileImg": "https://mrinal.jpeg",
  "title": "Software Engineer",
  "description": "Experienced full-stack developer.",
  "articleFeedLink": "https://medium.com/@mrinalprakash4577/feed"
}

Your Insomnia request should look something like this after you perform all the above steps:

6. Send the Request

• Once your endpoint, method, and data are set up, click on the “Send” button to send the request.

7. Analyze the Response

After sending the request, you’ll see the response and status codes directly in Insomnia. This helps you understand whether the API call was successful or if there were any issues.

• Successful Insertion: If the user is successfully added to the database, Insomnia will display a 201 Created status and a response like this:

{
  "created": true
}

• Error Handling: If there’s an issue (like a problem connecting to the database), you’ll receive a 500 Internal Server Error status and an error response:

{
  "error": "Failed to insert user"
}

By following these steps, you can easily test your API functionality and debug any issues within Insomnia.

Setting up the API for Data Insertion

In this section, we will create an API that allows users to insert data (such as name, job title, and profile image) into a MySQL database.

import mysql from 'mysql2/promise';

const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'sys'
});

export default async function handler(req, res) {
    const { name, profileImg, title, description, articleFeedLink } = req.body;
    try {
        const [results, _] = await connection.execute(
            'INSERT INTO users (name, profile_img, title, description, article_feed_link) VALUES (?, ?, ?, ?, ?)',
            [name, profileImg, title, description, articleFeedLink]
        );
        if (results.affectedRows === 1) {
            res.status(201).json({ created: true });
        } else {
            res.status(500).json({ error: 'Failed to insert user' });
        }
    } catch (error) {
        console.error('Error inserting user:', error);
        res.status(500).json({ error: 'Failed to insert user' });
    }
}

Explanation:

• MySQL Connection: We use the mysql2/promise library to establish a connection to the MySQL database.
• API Handler: The handler function processes POST requests. It takes user input (name, jobTitle, description, and profileImg) from the request body and inserts the data into the database.
• Error Handling: If the insertion fails, the error is caught, logged, and a 500 Internal Server Error is returned to the client.

Now we will push some data into the database:

Now check the database:

Retrieving Data from the Database

Now in the next API, we will retrieve all user data from the database and return it as a JSON response.

We will create a file ‘list.js’ and write the following code:

// pages/api/users/list.js

import mysql from 'mysql2/promise';

let connection;

async function connectToDatabase() {
    if (!connection) {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'root',
            database: 'sys'
        });
    }
}

export default async function handler(req, res) {
    await connectToDatabase();
    
    if (req.method === 'GET') {
        try {
            const [rows, fields] = await connection.execute('SELECT * FROM users');
            res.status(200).json(rows);
        } catch (error) {
            console.error('Error retrieving users:', error);
            res.status(500).json({ error: 'Failed to retrieve users' });
        }
    } else {
        res.setHeader('Allow', ['GET']);
        res.status(405).end(`Method ${req.method} Not Allowed`);
    }
}

Explanation:

• createConnection: A helper function to establish a MySQL connection, reused across different API routes.
• Fetching Data: The handler function fetches all user data from the user table using the SELECT * FROM user SQL query.
• Error Handling: Errors are caught, and if the database connection fails, a 500 error is returned.

Now we will list some data from the database:

Removing Data from the Database

In this section, we’ll focus on how to implement an API for removing a user from the database. This is essential when a user wants to delete their profile or when an admin needs to remove outdated or incorrect data.

Our goal is to provide a clean and reliable way to delete data from the MySQL database using a DELETE request.

Step 1: Setting Up MySQL Connection

We’ll use the mysql2/promise library to handle database operations asynchronously. This is important for ensuring that the application can handle multiple requests efficiently.

We start by creating a reusable connection function that establishes a connection to the MySQL database only once. This ensures that we don’t create a new connection for every request, improving performance.

import mysql from 'mysql2/promise';

let connection;

async function connectToDatabase() {
    if (!connection) {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'root',
            database: 'sys'
        });
    }
}

• connectToDatabase(): This function checks whether a connection to the MySQL database already exists. If not, it creates a new connection using the provided credentials (host, user, password, and database).
• Connection Reuse: By using a global connection variable, we reuse the same connection throughout different API calls, reducing overhead.

Step 2: Creating the Delete API

Next, we will build the API route that handles DELETE requests. This API will expect an id in the request body, which corresponds to the id of the user we want to delete.

import mysql from 'mysql2/promise';

let connection;

async function connectToDatabase() {
    if (!connection) {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'root',
            database: 'sys'
        });
    }
}

export default async function handler(req, res) {
    await connectToDatabase();

    if (req.method === 'DELETE') {
        const { id } = req.body;
        if (!id) {
            res.status(400).json({ error: 'User ID is required' });
            return;
        }
        
        try {
            const [results] = await connection.execute(
                'DELETE FROM users WHERE id = ?',
                [id]
            );

            if (results.affectedRows === 1) {
                res.status(200).json({ deleted: true });
            } else {
                res.status(404).json({ error: 'User not found' });
            }
        } catch (error) {
            console.error('Error deleting user:', error);
            res.status(500).json({ error: 'Failed to delete user' });
        }
    } else {
        res.setHeader('Allow', ['DELETE']);
        res.status(405).end(`Method ${req.method} Not Allowed`);
    }
}

Explanation of the Code:

1. Input Validation: Before processing the request, the API checks whether the id of the user to be deleted is provided in the request body. If the id is missing, the API responds with a 400 Bad Request status, indicating that the user ID is required.
2. Executing the DELETE Operation: If the id is provided, the API executes a DELETE SQL query. The query uses a placeholder (?) to securely insert the id parameter, preventing SQL injection attacks.
3. Checking the Result: After executing the query, the API checks if any rows were affected (i.e., if a user was deleted). If results.affectedRows equals 1, it means the deletion was successful, and the API returns a 200 OK status with a deleted: true message
4. Error Handling: Any errors during the database interaction (e.g., connection issues, SQL errors) are caught and logged. The API responds with a 500 Internal Server Error status and an appropriate error message.
5. Handling Unsupported Methods: The API explicitly allows only the DELETE method. If a request is made with any other HTTP method (like GET, POST, etc.), the API responds with a 405 Method Not Allowed status and sets the allowed methods in the response header

Now we will delete some data from the database:

Updating the Data from the Database

In this section, we will create an API that allows users to update their profile information in the MySQL database.

This API will handle PUT requests and enable updating fields such as name, profile image, job title, description, and article feed link.

We create the PUT API endpoint. This API will expect an id in the request body along with the other user data fields to be updated.

The id is essential as it identifies the specific user whose data should be updated in the database.

import mysql from 'mysql2/promise';

let connection;

async function connectToDatabase() {
    if (!connection) {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'root',
            database: 'sys'
        });
    }
}

export default async function handler(req, res) {
    await connectToDatabase();

    if (req.method === 'PUT') {
        const { id, name, profileImg, title, description, articleFeedLink } = req.body;
        if (!id) {
            res.status(400).json({ error: 'User ID is required' });
            return;
        }
        
        try {
            const [results] = await connection.execute(
                'UPDATE users SET name = ?, profile_img = ?, title = ?, description = ?, article_feed_link = ? WHERE id = ?',
                [name, profileImg, title, description, articleFeedLink, id]
            );

            if (results.affectedRows === 1) {
                res.status(200).json({ updated: true });
            } else {
                res.status(404).json({ error: 'User not found' });
            }
        } catch (error) {
            console.error('Error updating user:', error);
            res.status(500).json({ error: 'Failed to update user' });
        }
    } else {
        res.setHeader('Allow', ['PUT']);
        res.status(405).end(`Method ${req.method} Not Allowed`);
    }
}

Now heading over to Insomnia.

Dynamic Folders

Now, having created the APIs for the user, we will create the APIs for adding the details in the similar manner. In order to add, fetch, update or delete data for a particular user, we will name the folder as [user-id].

When you name a folder using a placeholder like [user-id], it is commonly referred to as a dynamic folder or parameterized folder name.

In this case, the [user-id] represents a variable or placeholder that will be dynamically replaced with an actual user identifier (or some unique value) at runtime.

This pattern is often used in scenarios where:

• Each user or entity needs its own folder for storage.
• The folder name is generated dynamically based on some context, such as a user’s ID or other identifying information.

This approach is widely used in web applications, cloud storage systems, and file management systems to organize data and ensure unique folder paths for each user or entity.

Further Steps

Now create APIs for projects and presentations like I have created for the users, in the [user-id] folder.

When you create all the files and write all the codes, you should be able to see the following file structure:

Conclusion

In this part, we focused on creating APIs to manage user data in our portfolio application.

Next, we’ll take a significant step forward by implementing authentication with NextAuth.js, a flexible authentication solution for Next.js applications. This will allow us to add user login and session management, enabling personalized experiences for different users.

We will also integrate Prisma to standardize our database interactions. By combining NextAuth.js with Prisma, we’ll ensure secure and efficient authentication and database handling, setting a strong foundation for further growth.

If you like my work, connect with me on LinkedIn, follow me on X (formerly twitter) as well as on Medium