pageboy

README

pageboy

pageboy is a pagination library with GORM v2.

Overviews

• 💪　Support both of before/after (Cursor) and page/per (Pager) DB pagination.
• 🤗　Accept human readable queries.
  • Like them: ?page=1&per_page=2 and ?before=1585706584&limit=10
  • We can also customize it if needed.
• 💖　We can write smart code using GORM scopes.
• 👌　Supports all DB engine officialy supported by GORM.
  • MySQL, PostgreSQL, SQLite, SQL Server

Installation

To install it, run:

go get -u github.com/soranoba/pageboy/v3

Usage

Cursor

Cursor can be used to indicate a range that is after or before that value.
It can sort using by time or integer.
For example, when we sort using CreatedAt and ID, it can prevent duplicate values from occurring.

Query Formats

• Simple numbers
  • https://example.com/api/users?before=1&limit=10
• Unix Timestamp in seconds
  • https://example.com/api/users?before=1585706584&limit=10
• Unix Timestamp in milliseconds (Depends on settings on your database)
  • https://example.com/api/users?before=1585706584.25&limit=10
• Unix Timestamp and Sub-Element (e.g. ID)
  • https://example.com/api/users?before=1585706584.25_20&limit=10

Index Settings

You should create an index when using a Cursor.
Example using CreatedAt and ID for sorting:

CREATE INDEX created_at_id ON users (created_at DESC, id DESC);

Usage in Codes

db, _ := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
// Please execute it only once immediately after opening DB.
pageboy.RegisterCallbacks(db)

type UsersRequest struct {
	pageboy.Cursor
}

func getUsers(ctx echo.Context) error {
	// Set to Default Limit
	req := &UsersRequest{Cursor: pageboy.Cursor{Limit: 10}}
	// Read from query or body
	if err := ctx.Bind(req); err != nil {
		return err
	}
	// Validation
	if err := req.Validate(); err != nil {
		return err
	}
	// Read from DB
	var users []*User
	if err := db.Scopes(req.Cursor.Paginate("CreatedAt", "ID").Order("DESC", "DESC").Scope()).Find(&users).Error; err != nil {
		return err
	}
}

NULLS FIRST / NULLS LAST

PostgresSQL can accept NULLS FIRST or NULLS LAST for index.
In that case, it can use the index by adding NULLS FIRST or NULLS LAST in Order.

It is not supported other engines because they cannot accept these for index.

cursor.Paginate("CreatedAt", "UpdatedAt").Order("DESC NULLS LAST", "ASC NULLS FIRST").Scope()

Pager

Pager can be used to indicate a range that is specified a page size and a page number.

Query Formats

It includes a page which is 1-Based number, and per_page.

• https://example.com/users?page=1&per_page=10

Usage in Codes

db, _ := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
// Please execute it only once immediately after opening DB.
pageboy.RegisterCallbacks(db)

type UsersRequest struct {
	pageboy.Pager
}

func getUsers(ctx echo.Context) error {
	// Set to Default
	req := &UsersRequest{Pager: pageboy.Pager{Page: 1, PerPage: 10}}
	// Read from query or body
	if err := ctx.Bind(req); err != nil {
		return err
	}
	// Validation
	if err := req.Validate(); err != nil {
		return err
	}
	// Read from DB
	var users []*User
	if err := db.Scopes(req.Pager.Scope()).Order("id ASC").Find(&users).Error; err != nil {
		return err
	}
}

Attentions

This library is only available for the kind of functions that the Query callback is executed on.
That is, it cannot be used with Row or Scan

Documentation

Overview

Package pageboy is a pagination library with GORM.

Index

• func RegisterCallbacks(db *gorm.DB)
• type Cursor
  • func NewCursor() *Cursor
  • func (cursor *Cursor) BuildNextPagingUrls(base *url.URL) *CursorPagingUrls
  • func (cursor *Cursor) GetNextAfter() pbc.CursorString
  • func (cursor *Cursor) GetNextBefore() pbc.CursorString
  • func (cursor *Cursor) Order(orders ...string) *Cursor
  • func (cursor *Cursor) Paginate(columns ...string) *Cursor
  • func (cursor *Cursor) Scope() func(db *gorm.DB) *gorm.DB
  • func (cursor *Cursor) Validate() error
• type CursorPagingUrls
• type Pager
  • func NewPager() *Pager
  • func (pager *Pager) Scope() func(db *gorm.DB) *gorm.DB
  • func (pager *Pager) Summary() *PagerSummary
  • func (pager *Pager) Validate() error
• type PagerSummary
• type ValidationError
  • func (err *ValidationError) Error() string

Examples

• Cursor
• Cursor.Order
• Pager

Functions

func RegisterCallbacks

func RegisterCallbacks(db *gorm.DB)

RegisterCallbacks register the Callback used by pageboy in gorm.DB. This function MUST execute only once immediately after opening the DB. (https://pkg.go.dev/gorm.io/gorm#Open) DO NOT execute every time you create new Session (https://pkg.go.dev/gorm.io/gorm#DB.Session).

Types

type Cursor

type Cursor struct {
	Before  pbc.CursorString `json:"before"  query:"before"`
	After   pbc.CursorString `json:"after"   query:"after"`
	Limit   int              `json:"limit"   query:"limit"`
	Reverse bool             `json:"reverse" query:"reverse"`
	// contains filtered or unexported fields
}

Cursor is a builder that build a GORM scope that specifies a range from the cursor position of records. You can read it from query or json.

Example

db := openDB()
// Please execute it only once immediately after opening DB.
RegisterCallbacks(db)

type User struct {
	gorm.Model
	Name string
	Age  int
}

db.Migrator().DropTable(&User{})
db.AutoMigrate(&User{})

db.Create(&User{Name: "Alice", Age: 18})
db.Create(&User{Name: "Bob", Age: 22})
db.Create(&User{Name: "Carol", Age: 15})

// Get request url.
url, _ := url.Parse("https://localhost/path?q=%E3%81%AF%E3%82%8D%E3%83%BC")

// Default Values. You can also use `NewCursor()`.
cursor := &Cursor{Limit: 2, Reverse: false}

// Update values from a http request.

// Fetch Records.
var users []User
db.Scopes(cursor.Paginate("Age", "ID").Order("ASC", "DESC").Scope()).Find(&users)

fmt.Printf("len(users) == %d\n", len(users))
fmt.Printf("users[0].Name == \"%s\"\n", users[0].Name)
fmt.Printf("users[1].Name == \"%s\"\n", users[1].Name)

// Return the paging.
j, _ := json.Marshal(cursor.BuildNextPagingUrls(url))
fmt.Println(string(j))

Output:

len(users) == 2
users[0].Name == "Carol"
users[1].Name == "Alice"
{"next":"https://localhost/path?after=18_1\u0026q=%E3%81%AF%E3%82%8D%E3%83%BC"}

func NewCursor

func NewCursor() *Cursor

NewCursor returns a default Cursor.

func (*Cursor) BuildNextPagingUrls

func (cursor *Cursor) BuildNextPagingUrls(base *url.URL) *CursorPagingUrls

BuildNextPagingUrls returns URLs for the user to access from the next cursor position.

You can use GetNextBefore and GetNextAfter if you want to customize the behavior.

func (*Cursor) GetNextAfter

func (cursor *Cursor) GetNextAfter() pbc.CursorString

GetNextAfter returns a value of query to access if it exists some records after the current position.

func (*Cursor) GetNextBefore

func (cursor *Cursor) GetNextBefore() pbc.CursorString

GetNextBefore returns a value of query to access if it exists some records before the current position.

func (*Cursor) Order

func (cursor *Cursor) Order(orders ...string) *Cursor

Order set the pagination orders, and returns self. The orders must be same order as columns that set to arguments of Paginate.

Example

cursor := &Cursor{Limit: 2, Reverse: false}
// For usually
cursor.Paginate("CreatedAt", "ID").Order("DESC", "ASC").Scope()
// For PostgresSQL
cursor.Paginate("CreatedAt", "ID").Order("DESC NULLS LAST", "ASC NULLS FIRST").Scope()

func (*Cursor) Paginate

func (cursor *Cursor) Paginate(columns ...string) *Cursor

Paginate set the pagination target columns, and returns self.

func (*Cursor) Scope

func (cursor *Cursor) Scope() func(db *gorm.DB) *gorm.DB

Scope returns a GORM scope.

func (*Cursor) Validate

func (cursor *Cursor) Validate() error

Validate returns true when the Cursor is valid. Otherwise, it returns false. If you execute Paginate with an invalid value, it panic may occur.

type CursorPagingUrls

type CursorPagingUrls struct {
	Next string `json:"next,omitempty"`
}

CursorPagingUrls is for the user to access from the next cursor position. If it is no records at target of next, Next will be empty.

type Pager

type Pager struct {
	Page    int `json:"page"     query:"page"`
	PerPage int `json:"per_page" query:"per_page"`
	// contains filtered or unexported fields
}

Pager is a builder that build a GORM scope that specifies a range of records.

Example

db := openDB()
// Please execute it only once immediately after opening DB.
RegisterCallbacks(db)

type User struct {
	gorm.Model
	Name string
}

db.Migrator().DropTable(&User{})
db.AutoMigrate(&User{})

db.Create(&User{Name: "Alice"})
db.Create(&User{Name: "Bob"})
db.Create(&User{Name: "Carol"})

// Default Values.
pager := &Pager{Page: 1, PerPage: 2}

// Update values from a http request.

// Fetch Records.
var users []User
db.Scopes(pager.Scope()).Order("id ASC").Find(&users)

fmt.Printf("len(users) == %d\n", len(users))
fmt.Printf("users[0].Name == \"%s\"\n", users[0].Name)
fmt.Printf("users[1].Name == \"%s\"\n", users[1].Name)

// Return the Summary.
j, _ := json.Marshal(pager.Summary())
fmt.Println(string(j))

Output:

len(users) == 2
users[0].Name == "Alice"
users[1].Name == "Bob"
{"page":1,"per_page":2,"total_count":3,"total_page":2}

func NewPager

func NewPager() *Pager

NewPager returns a default Pager.

func (*Pager) Scope

func (pager *Pager) Scope() func(db *gorm.DB) *gorm.DB

Scope returns a GORM scope.

func (*Pager) Summary

func (pager *Pager) Summary() *PagerSummary

Summary returns a PagerSummary.

func (*Pager) Validate

func (pager *Pager) Validate() error

Validate returns true when the values of Pager is valid. Otherwise, it returns false. If you execute Paginate with an invalid values, it panic may occur.

type PagerSummary

type PagerSummary struct {
	Page       int   `json:"page"        query:"page"`
	PerPage    int   `json:"per_page"    query:"per_page"`
	TotalCount int64 `json:"total_count" query:"total_count"`
	TotalPage  int   `json:"total_page"  query:"total_page"`
}

PagerSummary is summary of the query.

type ValidationError

type ValidationError struct {
	Field   string
	Message string
}

ValidationError is a validation error.

func (*ValidationError) Error

func (err *ValidationError) Error() string