Pagination in Go language MYSQL REST API!

Pagination is a common technique used to handle large datasets in web applications. In a typical scenario, we need to display only a few results per page, and allow the user to navigate through the pages to see more results. This can be easily achieved in a MySQL database using the LIMIT and OFFSET clauses.

In this article, we'll go over how to implement pagination in a MySQL database using Go language.

Prerequisites

Before we start, make sure you have the following installed:

• Go (at least version 1.16)

• MySQL

We'll be using the github.com/go-sql-driver/mysql package to connect to the MySQL database from Go.

Setting up the Database

Let's create a simple users table in our MySQL database to store user information:

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

We'll add some sample data to this table:

INSERT INTO users (name, email) VALUES
    ('John Doe', 'john.doe@example.com'),
    ('Jane Doe', 'jane.doe@example.com'),
    ('Bob Smith', 'bob.smith@example.com'),
    ('Alice Jones', 'alice.jones@example.com'),
    ('Tom Brown', 'tom.brown@example.com'),
    ('Emily Davis', 'emily.davis@example.com'),
    ('Michael Johnson', 'michael.johnson@example.com'),
    ('Amy Lee', 'amy.lee@example.com'),
    ('Chris Brown', 'chris.brown@example.com'),
    ('Jennifer Lee', 'jennifer.lee@example.com');

Implementing Pagination

Now, let's write a Go function to fetch a list of users from the database with pagination support. We'll use the LIMIT and OFFSET clauses to retrieve only a subset of the results.

func getUsers(page, pageSize int) ([]User, error) {
    // Open a connection to the MySQL database
    db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/database")
    if err != nil {
        return nil, err
    }
    defer db.Close()

    // Calculate the offset based on the page and pageSize
    offset := (page - 1) * pageSize

    // Query the database with LIMIT and OFFSET clauses
    rows, err := db.Query("SELECT id, name, email FROM users LIMIT ? OFFSET ?", pageSize, offset)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    // Loop through the results and build a list of users
    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Email); err != nil {
            return nil, err
        }
        users = append(users, user)
    }

    return users, nil
}

In this function, we first open a connection to the MySQL database using the sql.Open function. We then calculate the offset value based on the requested page and pageSize. We use these values in the SQL query to retrieve only the specified subset of results.

We then loop through the results returned by the query and build a list of User objects. Finally, we return the list of users.

Testing the Function

Let's test our getUsers function by printing the list of users for each page:

In this file, we'll import the necessary packages (testing, database/sql, and fmt), and create a new TestGetUsers function.

package main

import (
    "database/sql"
    "fmt"
    "testing"
)

func TestGetUsers(t *testing.T) {
    db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/database_name")
    if err != nil {
        t.Errorf("Error opening database: %v", err)
    }
    defer db.Close()
    // Fetch 3 users per page
    users, err := GetUsers(db, 0, 3)
    if err != nil {
        t.Errorf("Error getting users: %v", err)
    }

    for _, user := range users {
        fmt.Println(user)
    }
}

In this test function, we first open a connection to our MySQL database using the sql.Open function. We then defer closing the connection using the defer keyword. Next, we call our GetUsers function with a limit of 10 and an offset of 0. Finally, we loop through the returned users and print each one to the console.

To run the test, simply navigate to the directory containing the test file and run the following command:

$ go test

If everything is working correctly, you should see the list of users printed on the console.

Conclusion

In this article, we've shown how to use Go and MySQL to retrieve a list of users from a database with pagination. We started by setting up a MySQL database and creating a users table. We then created a GetUsers function that takes a sql.DB connection, a limit, and an offset, and returns a slice of User structs.

We also showed how to create a test function to ensure that our code is working as expected. With this knowledge, you should be able to implement pagination in your own Go and MySQL projects.

I hope this helps, you!!

More such articles:

https://medium.com/techwasti

https://www.youtube.com/channel/UCiTaHm1AYqMS4F4L9zyO7qA

https://www.techwasti.com/

\==========================**=========================

If this article adds any value to you then please clap and comment.

Let’s connect on Stackoverflow, LinkedIn, & Twitter.