Golang Backend Data Pagination

When there are too many data to request in the frontend, pagination can be considered to display the data. It can be controlled by two parameters pageSize (number of items per page) and pageNumber (page number).

Parameters for Frontend Data Request#

When sending an ajax request, pass the parameters psize and pnum. psize is set to 10 to return 10 data items per page, and pnum is set to 1 to return data from the first page.

http://localhost:8848/api/datalist?psize=10&pnum=1

Handling Parameters in Backend#

HandleFunc as follows#

Read the values of psize and pnum from the requested URL. Since the values obtained from the URL are of string type, they need to be converted to integer type before passing them to the database for processing.

func DataListApi(w http.ResponseWriter, r *http.Request) {
	params := r.URL.Query()
	pageSize, _ := strconv.Atoi(params.Get("psize"))
	pageNum, _ := strconv.Atoi(params.Get("pnum"))
	dataList := GetDataFromDbByPages(pageSize, pageNum)
	if dataList != nil {
		jsonData, _ := json.Marshal(dataList)
		_, err := w.Write(jsonData)
		if err != nil {
			log.Println(err)
		}
	}
}

Gorm Query Operation as follows#

Query the data with specified conditions from the database. If the query is successful, return an array of structures. If no data is found, return nil.

The offset is (pnum-1)*psize, and the number of returned items is limited to psize.

func GetDataFromDbByPages(psize, pnum int) (dataList *[]UserDataModel) {
	result := db.Offset((pnum - 1) * psize).Limit(psize).Find(&dataList)
	if result.RowsAffected == 0 {
		log.Println("Failed to retrieve data")
		return nil
	} else {
		return dataList
	}
}

Complete Backend Code (Demo)#

package main

import (
	"encoding/json"
	"log"
	"net/http"
	"strconv"

	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
)

// Listen on local port 8848 and handle requests from /api/datalist
func main() {
	server := http.Server{
		Addr: "127.0.0.1:8848",
	}
	http.HandleFunc("/api/datalist", DataListApi)
	log.Println("Server is running on http://127.0.0.1:8848")
	if err := server.ListenAndServe(); err != nil {
		log.Println(err)
	}
}

// Handle /api/datalist request, which requires psize and pnum parameters
func DataListApi(w http.ResponseWriter, r *http.Request) {
	params := r.URL.Query()
	pageSize, _ := strconv.Atoi(params.Get("psize"))
	pageNum, _ := strconv.Atoi(params.Get("pnum"))
	dataList := GetDataFromDbByPages(pageSize, pageNum)
	if dataList != nil {
		jsonData, _ := json.Marshal(dataList)
		_, err := w.Write(jsonData)
		if err != nil {
			log.Println(err)
		}
	}
}

// Structure for storing data in the database
type UserDataModel struct {
	Id   int    `gorm:"column:id;type:INTEGER NOT NULL;primaryKey;autoIncrement;"`
	Name string `gorm:"column:name;type:TEXT NOT NULL UNIQUE;"`
	Desc string `gorm:"column:desc;type:TEXT NOT NULL;"`
}

// Customize table name
func (UserDataModel) TableName() string {
	return "user_data"
}

var db *gorm.DB

// Initialize the SQLite database before the main function is executed
func init() {
	var err error
	db, err = gorm.Open(sqlite.Open("./data.db"), &gorm.Config{})
	if err != nil {
		log.Fatal(err)
	}

	sqlDB, _ := db.DB()
	sqlDB.SetMaxOpenConns(1)

	tableInit(&UserDataModel{})
}

// Check if the table exists, if not, create it
func tableInit(table interface{}) {
	if db.Migrator().HasTable(table) {
		return
	} else {
		db.Migrator().CreateTable(table)
		genTestData()
	}
}

// Read data from the database based on psize and pnum
func GetDataFromDbByPages(psize, pnum int) (dataList *[]UserDataModel) {
	result := db.Offset((pnum - 1) * psize).Limit(psize).Find(&dataList)
	if result.RowsAffected == 0 {
		log.Println("Failed to retrieve data")
		return nil
	} else {
		return dataList
	}
}

// Generate test data (100 records)
func genTestData() {
	for i := 1; i <= 100; i++ {
		testData := UserDataModel{
			Id:   i,
			Name: "user" + strconv.Itoa(i),
			Desc: "Hello, I'm user" + strconv.Itoa(i),
		}
		db.Create(&testData)
	}
}

Testing#

Download the demo: https://yzbtdiy.lanzoul.com/iICXi0pung7c

When running for the first time, a SQLite database named data.db will be generated in the current directory.

Then create the user_data table and insert 100 test data.

Listen on port 8848 and access /api/datalist to get data. The psize and pnum parameters need to be passed.

You can test it using a browser or command line.

~ $ curl "http://127.0.0.1:8848/api/datalist?psize=10&pnum=1"
[{"Id":1,"Name":"user1","Desc":"Hello, I'm user1"},{"Id":2,"Name":"user2","Desc":"Hello, I'm user2"},{"Id":3,"Name":"user3","Desc":"Hello, I'm user3"},{"Id":4,"Name":"user4","Desc":"Hello, I'm user4"},{"Id":5,"Name":"user5","Desc":"Hello, I'm user5"},{"Id":6,"Name":"user6","Desc":"Hello, I'm user6"},{"Id":7,"Name":"user7","Desc":"Hello, I'm user7"},{"Id":8,"Name":"user8","Desc":"Hello, I'm user8"},{"Id":9,"Name":"user9","Desc":"Hello, I'm user9"},{"Id":10,"Name":"user10","Desc":"Hello, I'm user10"}]

~ $ curl "http://127.0.0.1:8848/api/datalist?psize=5&pnum=3"
[{"Id":11,"Name":"user11","Desc":"Hello, I'm user11"},{"Id":12,"Name":"user12","Desc":"Hello, I'm user12"},{"Id":13,"Name":"user13","Desc":"Hello, I'm user13"},{"Id":14,"Name":"user14","Desc":"Hello, I'm user14"},{"Id":15,"Name":"user15","Desc":"Hello, I'm user15"}]