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.

• Query extendable.
• Multiple paging keys.
• Pagination across custom types (e.g. JSON)
• Paging rule customization for each key.
• GORM column tag supported.
• Error handling enhancement.
• Exporting cursor module for advanced usage.
• Implement custom codec for cursor encoding/decoding.

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

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 with fine grained setting for individual key:

   Please check paginator.Rule for more details.

   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
   }

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. 🎉

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

• Keys: Slice of field names in target model struct, the order presents sorting priority in query.

  default: []string["ID"]

• Rules: Slice of fine grained setting for individual field, the order presents sorting priority in query. This option takes precedence over Keys, please check paginator.Rule for more details.

• Limit: Page size.

  default: 10

• Order: Sorting field in ASC or DESC order, applying to fields in Keys, or in Rule without order specified.

  default: paginator.DESC

• After: After cursor for query.

• Before: Before cursor for query.

• AllowTupleCmp: Flag to enable/disable tuple comparison optimization.

  default: paginator.FALSE

  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.

• CursorCodec(v2.7.0): Custom cursor encoding/decoding implementation.

  By default this library encodes cursors with base64 (reference). You can provide your own custom cursor encoding/decoding implementation, which conforms to CursorCodec interface, via this option.

• Key: Field name in target model struct.

• Order: Order for this key only.

• SQLRepr: SQL representation used in raw SQL query.

  This is especially useful when you have JOIN or table alias in your SQL query. If SQLRepr is not specified, paginator will get table name from model, plus table key derived by below rules to form the SQL query:

  1. Find GORM tag column on struct field.
  2. If tag not found, convert struct field name to snake case.

• SQLType: SQL type used for type casting in the raw SQL query.

  This is especially useful when working with custom types (e.g. JSON).

• NULLReplacement(v2.2.0): Replacement for NULL value when paginating by nullable column.

  If you paginate by nullable column, you will encounter NULLS { FIRST | LAST } problems. This option let you decide how to order rows with NULL value. For instance, we can set this value to 1970-01-01 for a nullable date column, to ensure rows with NULL date will be placed at head when order is ASC, or at tail when order is DESC.

• CustomType: Extra information needed only when paginating across custom types (e.g. JSON).

  To support custom type pagination, the type needs to implement the CustomType interface:

  type CustomType interface {
    // GetCustomTypeValue returns the value corresponding to the meta attribute inside the custom type.
    GetCustomTypeValue(meta interface{}) (interface{}, error)
  }

  and provide the following information:

  • Meta: meta attribute inside the custom type. The paginator will pass this meta attribute to the GetCustomTypeValue function, which should return the actual value corresponding to the meta attribute. For JSON, meta would contain the JSON key of the element inside JSON to be used for pagination.

  • Type: GoLang type of the meta attribute.

  Also, when paginating across custom types, it is expected that the SQLRepr & SQLType are set. SQLRepr should contain the SQL query to get the meta attribute value, while SQLType should be used for type casting if needed. Check examples of JSON custom type and custom type setting.

• Add support for custom cursor encoding/decoding implementation via CursorCodec option (#66), credit to @sashahilton00.

• Fix slice encoding (#64), credit to @chrisroberts.

• Add flag AllowTupleCmp to enable SQL tuple comparison for performance optimization of composite cursors (#62), credit to @tadeboro.

• Export GetCursorEncoder & GetCursorDecoder on paginator.Paginator (#59).

• Support NULLReplacement for custom types (#58), credit to @zitnik.

• Cast NULLReplacement when SQLType is specified (#52), credit to @jpugliesi.

• Support NamingStrategy (#49), credit to @goxiaoy.

• Add CustomType to paginator.Rule to support custom data types, credit to @nikicc.

There are some adjustments to the signatures of cursor.NewEncoder and cursor.NewDecoder. Be careful when upgrading if you use them directly.

• Add NULLReplacement to paginator.Rule to overcome NULLS { FIRST | LAST } problems, credit to @nikicc.

• Let client control context, suggestion from @khalilsarwari.

• Fix order flip bug when paginating backward, credit to @sylviamoss.

© Cyan Ho (pilagod), 2018-NOW

Released under the MIT License