How to implement pagination for SQL query results in Go?

To implement pagination for SQL query results in Go, you can follow these steps:

Define a struct to represent the pagination parameters. This struct can include fields like PageNumber, PageSize, SortBy, and SortOrder.

type Pagination struct {
    PageNumber int
    PageSize   int
    SortBy     string
    SortOrder  string
}

Write a function to execute the SQL query with the pagination parameters. This function should take the database connection, the SQL query, and the pagination parameters as input arguments. It should return the paginated results and the total number of records.

import (
    "database/sql"
)

func executeQueryWithPagination(db *sql.DB, query string, pagination Pagination) ([]Result, int, error) {
    // Calculate the offset based on the page number and page size
    offset := (pagination.PageNumber - 1) * pagination.PageSize
    
    // Add the LIMIT and OFFSET clauses to the query
    query = fmt.Sprintf("%s ORDER BY %s %s LIMIT %d OFFSET %d", query, pagination.SortBy, pagination.SortOrder, pagination.PageSize, offset)
    
    // Execute the query to get the paginated results
    rows, err := db.Query(query)
    if err != nil {
        return nil, 0, err
    }
    defer rows.Close()
    
    var results []Result
    for rows.Next() {
        var result Result
        err := rows.Scan(&result.Field1, &result.Field2) // Replace with your column names
        if err != nil {
            return nil, 0, err
        }
        results = append(results, result)
    }
    
    // Get the total number of records for the query
    totalRecords := 0
    err = db.QueryRow("SELECT COUNT(*) FROM my_table").Scan(&totalRecords) // Replace with your table name
    if err != nil {
        return nil, 0, err
    }
    
    return results, totalRecords, nil
}

Call the executeQueryWithPagination function by passing in the necessary arguments.

func main() {
    db, err := sql.Open("driverName", "dataSourceName") // Replace with your database driver and connection string
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    pagination := Pagination{
        PageNumber: 1,      // Page number to fetch
        PageSize:   10,     // Number of records per page
        SortBy:     "name", // Sort by column
        SortOrder:  "DESC", // Sort order (ASC or DESC)
    }
    
    query := "SELECT * FROM my_table" // Replace with your SQL query
    results, totalRecords, err := executeQueryWithPagination(db, query, pagination)
    if err != nil {
        log.Fatal(err)
    }
    
    fmt.Println("Total Records:", totalRecords)
    for _, result := range results {
        fmt.Println(result)
    }
}

Make sure to replace the placeholders (result, my_table, driverName, dataSourceName, etc.) as per your requirements and database configuration.