# 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.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1680350172150/35465daf-0c4d-4cbd-afa9-f2ac575e91b9.png align="center")

## **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`](http://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:

```sql
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:

```sql
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.

```go
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`](http://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.

```go
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`](http://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
$ 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](Link)

[https://www.youtube.com/channel/UCiTaHm1AYqMS4F4L9zyO7qA](Link)

[https://www.techwasti.com/](Link)

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

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

**Let’s connect on** [**Stackoverflow**](http://stackoverflow.com/users/3187349/maheshwar-ligade)**,** [**LinkedIn**](https://in.linkedin.com/in/maheshwar-ligade-14447841)**, &** [**Twitter**](https://twitter.com/MaheshwarLigade)**.**