Implementing cursor pagination in Golang: Go Fiber, MySQL, GORM from scratch

Introduction

Pagination is the process of dividing a large number of records or items into smaller chunks, or pages. Pagination is useful as it allows users to view a large amount of data
in a more manageable way.

There are several different approaches to pagination, including numbered pagination, which displays page numbers that the user can click to move between pages, and infinite scrolling, which automatically loads new pages of data as the user scrolls down the page. Cursor pagination is another approach, which uses a cursor, or pointer, to track the user's position in the record set and allows them to move between pages using the cursor.

In this tutorial we will create a new REST API using Go, Fiber, GORM and MySQL from scratch.

Prerequisite

Please make sure to have following dependencies installed on your system

• Go (1.16 / higher is required)
• MySQL 8

Implementation:

• Let's start by creating a new folder, mkdir go-bookie.
• Now let's init a new go mod: Go mod init“github.com/sadhakbj/go-bookie”
• Now we need to install go fiber as our dependency. Pleae check official documentation for all the detailed information about go fiber
  • go get github.com/gofiber/fiber/v2
• We also need few other dependencies so install them as well:

go get gorm.io/gorm
go get gorm.io/driver/mysql
go get github.com/google/uuid

• Now let's create new files and folders:

touch main.go && mkdir models database helpers

• As our app's name is bookie, we want to list the books data, so let's create a model book.go inside the folder models.

package models

import (
    "time"

    "github.com/google/uuid"
    "gorm.io/gorm"
)

type Book struct {
    ID          string `gorm:"primaryKey"`
    Title       string `json:"title" gorm:"type:varchar(191);not null"`
    Description string `json:"description" gorm:"type:text;not null"`
    Author      string `json:"author" gorm:"type:varchar(191);not null"`
    Price       uint   `json:"price" gorm:"not null"`
    CreatedAt   time.Time
    UpdatedAt   time.Time
}

func (book *Book) BeforeCreate(tx *gorm.DB) (err error) {
    book.ID = uuid.NewString()
    return
}

• We are keeping the fields very simple, and we are using uuid as our primary key, using GORM, we will set the id to new uuid using the BeforeCreate hook
• Now let's create a new file database.go inside the folder database and write a method to connect to the database:

package database

import (
    "log"

    "github.com/sadhakbj/bookie-go/models"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

// DB is a global variable that represents the database connection
var DB *gorm.DB

// InitDB initializes the database connection
func InitDB() {
    // Connect to the database
    var err error
    dsn := "root:root@tcp(127.0.0.1:3306)/bookies?charset=utf8mb4&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        log.Fatal(err)
    }
    log.Println("Connected Successfully to Database")
    db.Logger = logger.Default.LogMode(logger.Info)
    log.Println("Running Migrations")
    db.AutoMigrate(&models.Book{})
    DB = db
}

• Now let's update our main file which is main.go to connect to the database, instantiate a new fiber app and add routes to it:

package main

import (
    "fmt"
    "log"
    "math/rand"
    "time"

    "github.com/gofiber/fiber/v2"
    "github.com/sadhakbj/bookie-go/controllers"
    "github.com/sadhakbj/bookie-go/database"
    "github.com/sadhakbj/bookie-go/models"
)

func main() {
    app := fiber.New()
    database.InitDB()

    app.Get("/books/seed", func(c *fiber.Ctx) error {
        var book models.Book
        if err := database.DB.Exec("delete from books where 1").Error; err != nil {
            return c.SendStatus(500)
        }
        for i := 1; i <= 20; i++ {
            book.Title = fmt.Sprintf("Book %d", i)
            book.Description = fmt.Sprintf("This is a description for a book %d", i)
            book.Price = uint(rand.Intn(500))
            book.Author = fmt.Sprintf("Book author %d", i)
            book.CreatedAt = time.Now().Add(-time.Duration(21-i) * time.Hour)

            database.DB.Create(&book)
        }

        return c.SendStatus(fiber.StatusOK)
    })

    app.Get("/books", controllers.GetPaginatedBooks)

    log.Fatal(app.Listen(":3000"))
}

We are keeping the things very simple over here, we are seeding the initial 20 data using a new HTTP endpoint: /books/seed and we have another endpoint: '/books' which gets the paginated data

• Now, that we have 20 data in our database, let's move on to implementing the actual feature in the controllers folder, so let's create a new bookscontroller.go inside the folder controllers

package controllers

import (
    "fmt"
    "strconv"

    "github.com/gofiber/fiber/v2"
    "github.com/sadhakbj/bookie-go/common"
    "github.com/sadhakbj/bookie-go/database"
    "github.com/sadhakbj/bookie-go/helpers"
    "github.com/sadhakbj/bookie-go/models"
)

func GetPaginatedBooks(c *fiber.Ctx) error {
    books := []models.Book{}
    perPage := c.Query("per_page", "10")
    sortOrder := c.Query("sort_order", "desc")
    cursor := c.Query("cursor", "")
    limit, err := strconv.ParseInt(perPage, 10, 64)
    if limit < 1 || limit > 100 {
        limit = 10
    }
    if err != nil {
        return c.Status(500).JSON("Invalid per_page option")
    }

    isFirstPage := cursor == ""
    pointsNext := false

    query := database.DB
    if cursor != "" {
        decodedCursor, err := helpers.DecodeCursor(cursor)
        if err != nil {
            fmt.Println(err)
            return c.SendStatus(500)
        }
        pointsNext = decodedCursor["points_next"] == true

        operator, order := getPaginationOperator(pointsNext, sortOrder)
        whereStr := fmt.Sprintf("(created_at %s ? OR (created_at = ? AND id %s ?))", operator, operator)
        query = query.Where(whereStr, decodedCursor["created_at"], decodedCursor["created_at"], decodedCursor["id"])
        if order != "" {
            sortOrder = order
        }
    }
    query.Order("created_at " + sortOrder).Limit(int(limit) + 1).Find(&books)
    hasPagination := len(books) > int(limit)

    if hasPagination {
        books = books[:limit]
    }

    if !isFirstPage && !pointsNext {
        books = helpers.Reverse(books)
    }

    pageInfo := calculatePagination(isFirstPage, hasPagination, int(limit), books, pointsNext)

    response := common.ResponseDTO{
        Success:    true,
        Data:       books,
        Pagination: pageInfo,
    }

    return c.Status(fiber.StatusOK).JSON(response)
}

func calculatePagination(isFirstPage bool, hasPagination bool, limit int, books []models.Book, pointsNext bool) helpers.PaginationInfo {
    pagination := helpers.PaginationInfo{}
    nextCur := helpers.Cursor{}
    prevCur := helpers.Cursor{}
    if isFirstPage {
        if hasPagination {
            nextCur := helpers.CreateCursor(books[limit-1].ID, books[limit-1].CreatedAt, true)
            pagination = helpers.GeneratePager(nextCur, nil)
        }
    } else {
        if pointsNext {
            // if pointing next, it always has prev but it might not have next
            if hasPagination {
                nextCur = helpers.CreateCursor(books[limit-1].ID, books[limit-1].CreatedAt, true)
            }
            prevCur = helpers.CreateCursor(books[0].ID, books[0].CreatedAt, false)
            pagination = helpers.GeneratePager(nextCur, prevCur)
        } else {
            // this is case of prev, there will always be nest, but prev needs to be calculated
            nextCur = helpers.CreateCursor(books[limit-1].ID, books[limit-1].CreatedAt, true)
            if hasPagination {
                prevCur = helpers.CreateCursor(books[0].ID, books[0].CreatedAt, false)
            }
            pagination = helpers.GeneratePager(nextCur, prevCur)
        }
    }
    return pagination
}

func getPaginationOperator(pointsNext bool, sortOrder string) (string, string) {
    if pointsNext && sortOrder == "asc" {
        return ">", ""
    }
    if pointsNext && sortOrder == "desc" {
        return "<", ""
    }
    if !pointsNext && sortOrder == "asc" {
        return "<", "desc"
    }
    if !pointsNext && sortOrder == "desc" {
        return ">", "asc"
    }

    return "", ""
}

Here the main method: GetPaginatedBooks performs the database query to get the data and method: calculatePagination calculates the pagination information. The logic to calculate the pagination is really simple, we perform the query in the database with the limit of (provided limit+1), so if the count of the results > provided limit it means there is next / previous page.

• As you can see, we are missing two helper files: common.go and pagination.go, so let's write the contents for them as well. (The first file is intended to include the common helper functions which can be used across the app while second one includes functionalities related to pagination):

//common.go
package helpers

func Reverse[T any](s []T) []T {
    for i, j := 0, len(s)-1; i < j; i, j = i+1, j-1 {
        s[i], s[j] = s[j], s[i]
    }
    return s
}

package helpers

import (
    "encoding/base64"
    "encoding/json"
    "time"
)

type PaginationInfo struct {
    NextCursor string `json:"next_cursor"`
    PrevCursor string `json:"prev_cursor"`
}

type Cursor map[string]interface{}

func CreateCursor(id string, createdAt time.Time, pointsNext bool) Cursor {
    return Cursor{
        "id":          id,
        "created_at":  createdAt,
        "points_next": pointsNext,
    }
}

func GeneratePager(next Cursor, prev Cursor) PaginationInfo {
    return PaginationInfo{
        NextCursor: encodeCursor(next),
        PrevCursor: encodeCursor(prev),
    }
}

func encodeCursor(cursor Cursor) string {
    if len(cursor) == 0 {
        return ""
    }
    serializedCursor, err := json.Marshal(cursor)
    if err != nil {
        return ""
    }
    encodedCursor := base64.StdEncoding.EncodeToString(serializedCursor)
    return encodedCursor
}

func DecodeCursor(cursor string) (Cursor, error) {
    decodedCursor, err := base64.StdEncoding.DecodeString(cursor)
    if err != nil {
        return nil, err
    }

    var cur Cursor
    if err := json.Unmarshal(decodedCursor, &cur); err != nil {
        return nil, err
    }
    return cur, nil
}

• We are using base64 encoding mechanism to encode a cursor value if there is pagination. It includes the id, created_at timestamp & the information about if the current cursor points to next or not. The cursor is generated as a string and is returned back to the user.
• The user need to send cursor as a query parameter, which is decoded and the database query is performed accordingly.
• Finally, we now need to create a file inside a folder common named response.go which includes a struct for the response dto:

package common

import "github.com/sadhakbj/bookie-go/helpers"

type ResponseDTO struct {
    Success    bool                   `json:"success"`
    Data       any                    `json:"data"`
    Pagination helpers.PaginationInfo `json:"pagination"`
}

• So, yeah we are now ready with our implementation. Now, lets run the server: go run main.go
• Firstly let's seed our data: http://localhost:3000/books/seed
• Now access our HTTP endpoint: http://localhost:3000/books?per_page=2&sort_order=asc, which should return the result:

{
  "success": true,
  "data": [
    {
      "ID": "c55a8347-0b78-42f1-9b48-936c9c65361e",
      "title": "Book 1",
      "description": "This is a description for a book 1",
      "author": "Book author 1",
      "price": 81,
      "CreatedAt": "2022-12-17T16:14:47.952+09:00",
      "UpdatedAt": "2022-12-18T12:14:47.954+09:00"
    },
    {
      "ID": "6e4d9295-6b26-439c-928f-980b7361b662",
      "title": "Book 2",
      "description": "This is a description for a book 2",
      "author": "Book author 2",
      "price": 387,
      "CreatedAt": "2022-12-17T17:14:47.956+09:00",
      "UpdatedAt": "2022-12-18T12:14:47.954+09:00"
    }
  ],
  "pagination": {
    "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyMi0xMi0xN1QxNzoxNDo0Ny45NTYrMDk6MDAiLCJpZCI6IjZlNGQ5Mjk1LTZiMjYtNDM5Yy05MjhmLTk4MGI3MzYxYjY2MiIsInBvaW50c19uZXh0Ijp0cnVlfQ==",
    "prev_cursor": ""
  }
}

• As we can see we have pagination info and encoded next_cursor value, now let's try to get the next 2 items using the cursor: http://localhost:3000/books?per_page=2&sort_order=asc&cursor=eyJjcmVhdGVkX2F0IjoiMjAyMi0xMi0xN1QxNzoxNDo0Ny45NTYrMDk6MDAiLCJpZCI6IjZlNGQ5Mjk1LTZiMjYtNDM5Yy05MjhmLTk4MGI3MzYxYjY2MiIsInBvaW50c19uZXh0Ijp0cnVlfQ== the response is:

{
  "success": true,
  "data": [
    {
      "ID": "62d843d7-889c-46c8-9956-12527b2b1a0d",
      "title": "Book 3",
      "description": "This is a description for a book 3",
      "author": "Book author 3",
      "price": 347,
      "CreatedAt": "2022-12-17T18:14:47.957+09:00",
      "UpdatedAt": "2022-12-18T12:14:47.954+09:00"
    },
    {
      "ID": "a0746960-a9da-4f62-be04-98e49ace03ef",
      "title": "Book 4",
      "description": "This is a description for a book 4",
      "author": "Book author 4",
      "price": 59,
      "CreatedAt": "2022-12-17T19:14:47.958+09:00",
      "UpdatedAt": "2022-12-18T12:14:47.954+09:00"
    }
  ],
  "pagination": {
    "next_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyMi0xMi0xN1QxOToxNDo0Ny45NTgrMDk6MDAiLCJpZCI6ImEwNzQ2OTYwLWE5ZGEtNGY2Mi1iZTA0LTk4ZTQ5YWNlMDNlZiIsInBvaW50c19uZXh0Ijp0cnVlfQ==",
    "prev_cursor": "eyJjcmVhdGVkX2F0IjoiMjAyMi0xMi0xN1QxODoxNDo0Ny45NTcrMDk6MDAiLCJpZCI6IjYyZDg0M2Q3LTg4OWMtNDZjOC05OTU2LTEyNTI3YjJiMWEwZCIsInBvaW50c19uZXh0IjpmYWxzZX0="
  }
}

As we can see in this case we have both next & prev cursor.

Conclusion

We implemented a cursor paginator using Go, Go Fiber, Go ORM and MySQL pretty easily.

Comments

[Alit Darma Putra]

I think it's better to implement Cursor as struct rather than map as this line
query = query.Where(whereStr, decodedCursor["created_at"], decodedCursor["created_at"], decodedCursor["id"])
could result a wrong query to database for the date condition where it doesn't match mysql date format. Thanks for the tutorial

[Bijaya Prasad Kuikel]

Thank you for the comment Alit.

[Madhu Sudhan Subedi]

Good Read