Index-based SQL pagination

pagination.go

package foo

import (
	"context"
	"net/http"
	"strconv"
)

// PaginateParams Paginate required params
type PaginateParams struct {
	Index int64
	Limit int64
}

type key int

const (
	// ParamCtx param context index
	ParamCtx key = iota
)

// PaginateHandler Sets paginate required params to context
func PaginateHandler(next http.Handler) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		// Get page query value or set default
		page, err := strconv.ParseInt(r.URL.Query().Get("page"), 10, 64)
		if page <= 0 || err != nil {
			page = 1
		}

		// Get limit query value or set default
		limit, err := strconv.ParseInt(r.URL.Query().Get("limit"), 10, 64)
		if limit <= 0 || err != nil || limit > 100 {
			limit = 5
		}

		// Pagination algorithm
		// Index = ab-a
		// Where a = limit and b = page
		index := (limit * page) - limit

		ctx := context.WithValue(r.Context(), ParamCtx, &PaginateParams{index, limit})

		next.ServeHTTP(w, r.WithContext(ctx))
	})
}

repository.go

// FetchAll Get all
func (u *FooRepository) FetchAll(limit, index int64) ([]*entity.FooModel, error) {
	rows, err := u.DB.Query(fmt.Sprintf(`SELECT * FROM foo WHERE id > %d ORDER BY id ASC FETCH FIRST %d ROWS ONLY`, index, limit))
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	foos := make([]*entity.FooModel, 0)
	for rows.Next() {
		foo := new(entity.FooModel)
		err := rows.Scan(&foo.ID, &foo.Name, ...)
		if err != nil {
			return nil, err
		}
		foos = append(foos, foo)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return foos, nil
}