search

pilagod / gorm-cursor-paginator

A paginator doing cursor-based pagination based on GORM
https://github.com/pilagod/gorm-cursor-paginator
MIT License
192 stars 44 forks source link
cursor-pagination go golang gorm pagination

readme

gorm-cursor-paginator Build Status Coverage Status Go Report Card

A paginator doing cursor-based pagination based on GORM

This doc is for v2, which uses GORM v2. If you are using GORM v1, please checkout v1 doc.

Features

Installation

go get -u github.com/pilagod/gorm-cursor-paginator/v2

Usage By Example

import (
   "github.com/pilagod/gorm-cursor-paginator/v2/paginator"
)

Given an User model for example:

type User struct {
    ID          int
    JoinedAt    time.Time `gorm:"column:created_at"`
}

We first need to create a paginator.Paginator for User, here are some useful patterns:

  1. Configure by paginator.Option, those functions with With prefix are factories for paginator.Option:

    func CreateUserPaginator(
    cursor paginator.Cursor,
    order *paginator.Order,
    limit *int,
) *paginator.Paginator {
    opts := []paginator.Option{
        &paginator.Config{
            Keys: []string{"ID", "JoinedAt"},
            Limit: 10,
            Order: paginator.ASC,
        },
    }
    if limit != nil {
        opts = append(opts, paginator.WithLimit(*limit))
    }
    if order != nil {
        opts = append(opts, paginator.WithOrder(*order))
    }
    if cursor.After != nil {
        opts = append(opts, paginator.WithAfter(*cursor.After))
    }
    if cursor.Before != nil {
        opts = append(opts, paginator.WithBefore(*cursor.Before))
    }
    return paginator.New(opts...)
}

  2. Configure by setters on paginator.Paginator:

    func CreateUserPaginator(
    cursor paginator.Cursor,
    order *paginator.Order,
    limit *int,
) *paginator.Paginator {
    p := paginator.New(
        &paginator.Config{
            Keys: []string{"ID", "JoinedAt"},
            Limit: 10,
            Order: paginator.ASC,
        },
    )
    if order != nil {
        p.SetOrder(*order)
    }
    if limit != nil {
        p.SetLimit(*limit)
    }
    if cursor.After != nil {
        p.SetAfterCursor(*cursor.After)
    }
    if cursor.Before != nil {
        p.SetBeforeCursor(*cursor.Before)
    }
    return p
}

  3. Configure by paginator.Rule for fine grained setting for each key:

    Please refer to Specification for details of paginator.Rule.

    func CreateUserPaginator(/* ... */) {
    p := paginator.New(
        &paginator.Config{
            Rules: []paginator.Rule{
                {
                    Key: "ID",
                },
                {
                    Key: "JoinedAt",
                    Order: paginator.DESC,
                    SQLRepr: "users.created_at",
                    NULLReplacement: "1970-01-01",
                },
            },
            Limit: 10,
            // Order here will apply to keys without order specified.
            // In this example paginator will order by "ID" ASC, "JoinedAt" DESC.
            Order: paginator.ASC, 
        },
    )
    // ...
    return p
}

  4. By default the library encodes cursors with base64. If a custom encoding/decoding implementation is required, this can be implemented and passed as part of the configuration:

First implement your custom codec such that it conforms to the CursorCodec interface:

type CursorCodec interface {
    // Encode encodes model fields into cursor
    Encode(
        fields []pc.EncoderField,
        model interface{},
    ) (string, error)

    // Decode decodes cursor into model fields
    Decode(
        fields []pc.DecoderField,
        cursor string,
        model interface{},
    ) ([]interface{}, error)
}

type customCodec struct {}

func (cc *CustomCodec) Encode(fields []pc.EncoderField, model interface{}) (string, error) {
    ...
}

func (cc *CustomCodec) Decode(fields []pc.DecoderField, cursor string, model interface{}) ([]interface{}, error) {
    ...
}

Then pass an instance of your codec during initialisation:

func CreateUserPaginator(/* ... */) {
    codec := &customCodec{}

    p := paginator.New(
        &paginator.Config{
            Rules: []paginator.Rule{
                {
                    Key: "ID",
                },
                {
                    Key: "JoinedAt",
                    Order: paginator.DESC,
                    SQLRepr: "users.created_at",
                    NULLReplacement: "1970-01-01",
                },
            },
            Limit: 10,
            // supply a custom implementation for the encoder/decoder 
            CursorCodec: codec,
            // Order here will apply to keys without order specified.
            // In this example paginator will order by "ID" ASC, "JoinedAt" DESC.
            Order: paginator.ASC, 
        },
    )
    // ...
    return p
}

After knowing how to setup the paginator, we can start paginating User with GORM:

func FindUsers(db *gorm.DB, query Query) ([]User, paginator.Cursor, error) {
    var users []User

    // extend query before paginating
    stmt := db.
        Select(/* fields */).
        Joins(/* joins */).
        Where(/* queries */)

    // create paginator for User model
    p := CreateUserPaginator(/* config */)

    // find users with pagination
    result, cursor, err := p.Paginate(stmt, &users)

    // this is paginator error, e.g., invalid cursor
    if err != nil {
        return nil, paginator.Cursor{}, err
    }

    // this is gorm error
    if result.Error != nil {
        return nil, paginator.Cursor{}, result.Error
    }

    return users, cursor, nil
}

The second value returned from paginator.Paginator.Paginate is a paginator.Cursor struct, which is same as cursor.Cursor struct:

type Cursor struct {
    After  *string `json:"after" query:"after"`
    Before *string `json:"before" query:"before"`
}

That's all! Enjoy paginating in the GORM world. :tada:

For more paginating examples, please checkout example/main.go and paginator/paginator_paginate_test.go

For manually encoding/decoding cursor exmaples, please check out cursor/encoding_test.go

Specification

paginator.Paginator

Default options used by paginator when not specified:

When cursor uses more than one key/rule, paginator instances by default generate SQL that is compatible with almost all database management systems. But this query can be very inefficient and can result in a lot of database scans even when proper indices are in place. By enabling the AllowTupleCmp option, paginator will emit a slightly different SQL query when all cursor keys are ordered in the same way.

For example, let us assume we have the following code:

paginator.New(
    paginator.WithKeys([]string{"CreatedAt", "ID"}),
    paginator.WithAfter(after),
    paginator.WithLimit(3),
).Paginate(db, &result)

The query that hits our database in this case would look something like this:

  SELECT *
    FROM orders
   WHERE orders.created_at > $1
      OR orders.created_at = $2 AND orders.id > $3
ORDER BY orders.created_at ASC, orders.id ASC
   LIMIT 4

Even if we index our table on (created_at, id) columns, some database engines will still perform at least full index scan to get to the items we need. And this is the primary use case for tuple comparison optimization. If we enable optimization, our code would look something like this:

paginator.New(
    paginator.WithKeys([]string{"CreatedAt", "ID"}),
    paginator.WithAfter(after),
    paginator.WithLimit(3),
    paginator.WithAllowTupleCmp(paginate.TRUE),
).Paginate(db, &result)

The query that hits our database now looks something like this:

  SELECT *
    FROM orders
   WHERE (orders.created_at, orders.id) > ($1, $2)
ORDER BY orders.created_at ASC, orders.id ASC
   LIMIT 4

In this case, if we have index on (created_at, id) columns, most DB engines will know how to optimize this query into a simple initial index lookup + scan, making cursor overhead negligible.

paginator.Rule

Changelog

v2.6.1

v2.6.0

v2.5.0

v2.4.2

v2.4.1

v2.4.0

v2.3.0

v2.2.0

v2.1.0

v2.0.1

License

© Cyan Ho (pilagod), 2018-NOW

Released under the MIT License