Cursor-based Pagination with Multiple Column Ordering in Go
Cursor-based pagination is a powerful technique for maintaining API performance with large datasets, enabling smooth navigation through thousands of rows. It also helps avoid issues like duplicate or missing rows when data is added or removed during pagination. In this post, we'll explore an implementation in Go that supports sorting by multiple columns.
At InnoPeak, we're building the Consulting Tool, a platform designed to support infinite scrolling, advanced filtering, and multi-column ordering using Mantine React Table. To handle these requirements efficiently, we chose cursor-based pagination for its seamless integration with Relay-style Connections and Apollo Client. This approach ensures smooth client-server communication and optimal performance when dealing with large datasets. In this post, we'll dive into our Go-based implementation, showcasing how cursor-based pagination can handle complex sorting and filtering scenarios effectively.
Cursor-based pagination uses row references to paginate, allowing clients to request elements that appear after or before the given row in our database. Cursors can be a simple string, although they are usually encoded in some way such as base64.
11|RaviAnand|MohabirOn the backend side of things, when receiving a cursor, we use the information in it to filter out elements that were already retrieved by the client in previous requests. So it's up to us to ensure all the column data is included that is used during pagination so we're able to properly perform the request.
Challenges with Cursor-based Pagination
Non-Unique Columns
While cursor pagination brings a lot of benefits, it can become challenging when allowing users to sort, especially when those sorted fields are non-unique.
Since we want to avoid returning the same row twice, we need to make sure to always append at least one unique column to our sorting, and include its value in the cursor. A simple solution to that is always including an ID as the final sorting column.
Multiple Sort Fields
When sorting by multiple fields, we want to try and find the next row after each individually sorted field, if the previous fields are the same.
For example, let's take a users table, we have the first_name and last_name columns, and two entries in our database with the first_name "Mark":
1| id | first_name | last_name |
2| -- | ---------- | --------- |
3| 1 | Mark | Brown |
4| 2 | Mark | Johnson |In order to account for the fact that we're going to be sorting by first_name and last_name we first need to check if first_name > :first_name , if not, we need to check if first_name = :first_name AND last_name > :last_name to account for the duplicate names.
A Simple Implementation
Now that we know the requirements of cursor-based pagination, we can write a simple implementation that takes all these things into account. We'll allow our client to pass us a list of fields to order by, as well as the after argument which is a cursor. Returning a list of our users along with the cursor.
To start we'll apply ordering. I'm using Squirrel to query my database, but you can use whatever you're most familiar with.
We're going to iterate through the orderBy provided to us by the client, and apply them to our query:
1package main
2
3import (
4 "database/sql"
5 "encoding/base64"
6 "encoding/json"
7 "fmt"
8 "log"
9 "os"
10 "strconv"
11 "strings"
12
13 "github.com/Dan6erbond/cursor-pagination-squirrel/model"
14 sq "github.com/Masterminds/squirrel"
15 _ "github.com/lib/pq"
16)
17
18type OrderDirection string
19
20const (
21 OrderDirectionASC OrderDirection = "ASC"
22 OrderDirectionDESC OrderDirection = "DESC"
23)
24
25type UserOrderField string
26
27const (
28 UserOrderFieldID UserOrderField = "ID"
29 UserOrderFieldFirstName UserOrderField = "FirstName"
30 UserOrderFieldLastName UserOrderField = "LastName"
31)
32
33type UserOrderBy struct {
34 Field UserOrderField
35 Direction *OrderDirection
36}
37
38type UserWithCursor struct {
39 model.User
40 Cursor string
41}
42
43var (
44 userOrderFieldMap = map[UserOrderField]string{
45 UserOrderFieldID: "id",
46 UserOrderFieldFirstName: "first_name",
47 UserOrderFieldLastName: "last_name",
48 }
49 db *sql.DB
50)
51
52func marshalCursor(user model.User) string {
53 return base64.StdEncoding.EncodeToString([]byte(fmt.Sprintf("%d|%s|%s", user.ID, user.FirstName, user.LastName)))
54}
55
56func find(orderBy []UserOrderBy, after *string, limit *uint64) ([]*UserWithCursor, error) {
57 users := sq.Select("*").From("users")
58
59 for _, ob := range orderBy {
60 direction := OrderDirectionASC
61
62 if ob.Direction != nil {
63 direction = *ob.Direction
64 }
65
66 users = users.OrderBy(userOrderFieldMap[ob.Field] + " " + string(direction))
67 }
68
69 if limit != nil {
70 users = users.Limit(*limit)
71 } else {
72 users = users.Limit(10)
73 }
74
75 rows, err := users.RunWith(db).Query()
76
77 if err != nil {
78 return nil, err
79 }
80
81 var us []*UserWithCursor
82 for rows.Next() {
83 var user UserWithCursor
84
85 if err := rows.Scan(&user.ID, &user.FirstName, &user.LastName); err != nil {
86 return nil, err
87 }
88
89 user.Cursor = marshalCursor(user.User)
90
91 us = append(us, &user)
92 }
93
94 return us, nil
95}Now, in order to return the next rows if the after parameter is provided, we have to check each ordered column's direction. If it's ascending, all elements after will use the > operator, otherwise for descending order we'll use <.
Additionally, as previously mentioned, for each order field we have to ensure that all previous fields are equal to the previous element, since we only want to order by a field if the previous fields were identical.
Then, finally, we're going to check if the user is ordering by a unique column - in this case ID - if not, we'll append it to the end.
1func unmarshalCursor(cursor string) (uint, string, string, error) {
2 bytes, err := base64.StdEncoding.DecodeString(cursor)
3
4 if err != nil {
5 return 0, "", "", err
6 }
7
8 str := string(bytes)
9
10 id, err := strconv.ParseUint(strings.Split(str, "|")[0], 10, 64)
11
12 if err != nil {
13 return 0, "", "", err
14 }
15
16 return uint(id), strings.Split(str, "|")[1], strings.Split(str, "|")[2], nil
17}
18
19func find(orderBy []UserOrderBy, after *string, limit *uint64) ([]*UserWithCursor, error) {
20 users := sq.Select("*").From("users").PlaceholderFormat(sq.Dollar)
21
22 var (
23 hasID bool
24 prevFields = make([]UserOrderField, 0)
25 cursorConditions = make(sq.Or, 0)
26 )
27 for _, ob := range orderBy {
28 direction := OrderDirectionASC
29
30 if ob.Direction != nil {
31 direction = *ob.Direction
32 }
33
34 users = users.OrderBy(userOrderFieldMap[ob.Field] + " " + string(direction))
35
36 if after != nil {
37 id, firstName, lastName, err := unmarshalCursor(*after)
38
39 if err != nil {
40 return nil, err
41 }
42
43 conds := make(sq.And, len(prevFields))
44 for i, field := range prevFields {
45 switch field {
46 case UserOrderFieldID:
47 conds[i] = sq.Eq{"id": id}
48 case UserOrderFieldFirstName:
49 conds[i] = sq.Eq{"first_name": firstName}
50 case UserOrderFieldLastName:
51 conds[i] = sq.Eq{"last_name": lastName}
52 }
53 }
54
55 if direction == OrderDirectionASC {
56 switch ob.Field {
57 case UserOrderFieldID:
58 conds = append(conds, sq.Gt{"id": id})
59 case UserOrderFieldFirstName:
60 conds = append(conds, sq.Gt{"first_name": firstName})
61 case UserOrderFieldLastName:
62 conds = append(conds, sq.Gt{"last_name": lastName})
63 }
64 } else {
65 switch ob.Field {
66 case UserOrderFieldID:
67 conds = append(conds, sq.Lt{"id": id})
68 case UserOrderFieldFirstName:
69 conds = append(conds, sq.Lt{"first_name": firstName})
70 case UserOrderFieldLastName:
71 conds = append(conds, sq.Lt{"last_name": lastName})
72 }
73 }
74
75 cursorConditions = append(cursorConditions, conds)
76
77 if ob.Field == UserOrderFieldID {
78 hasID = true
79 }
80 }
81
82 prevFields = append(prevFields, ob.Field)
83 }
84
85 if !hasID && after != nil {
86 id, firstName, lastName, err := unmarshalCursor(*after)
87
88 if err != nil {
89 return nil, err
90 }
91
92 conds := make(sq.And, len(prevFields))
93 for i, field := range prevFields {
94 switch field {
95 case UserOrderFieldID:
96 conds[i] = sq.Eq{"id": id}
97 case UserOrderFieldFirstName:
98 conds[i] = sq.Eq{"first_name": firstName}
99 case UserOrderFieldLastName:
100 conds[i] = sq.Eq{"last_name": lastName}
101 }
102 }
103
104 conds = append(conds, sq.Gt{"id": id})
105 cursorConditions = append(cursorConditions, conds)
106 }
107
108 if len(cursorConditions) != 0 {
109 users = users.Where(cursorConditions)
110 }
111
112 if limit != nil {
113 users = users.Limit(*limit)
114 } else {
115 users = users.Limit(10)
116 }
117
118 rows, err := users.RunWith(db).Query()
119
120 if err != nil {
121 return nil, err
122 }
123
124 var us []*UserWithCursor
125 for rows.Next() {
126 var user UserWithCursor
127
128 if err := rows.Scan(&user.ID, &user.FirstName, &user.LastName); err != nil {
129 return nil, err
130 }
131
132 user.Cursor = marshalCursor(user.User)
133
134 us = append(us, &user)
135 }
136
137 return us, nil
138}Given an ordering of first_name ASC, last_name DESC, and a cursor, this code will output the following SQL:
1SELECT *
2FROM users
3WHERE (
4 (first_name > $1)
5 OR (
6 first_name = $2
7 AND last_name < $3
8 )
9 OR (
10 first_name = $4
11 AND last_name = $5
12 AND id > $6
13 )
14 )
15ORDER BY first_name ASC,
16 last_name DESC
17LIMIT 10As you can see, with this logic we're ensuring that we're filtering based on the priority of the order fields. And by doing this in the same loop as the ordering itself, we get concise code that remains readable.
Final Words
While we explored a working implementation of cursor-based pagination in Go using the lightweight Squirrel library for dynamic queries, it's worth noting that every project should create its own set of helper functions. These helpers can standardize the columns and values used for pagination, reducing the reliance on switch statements in your main query methods. This approach not only streamlines your code but also makes it easier to add support for new order columns as your project evolves.
Thank you for reading! To see the full implementation and dive deeper into the code, visit the GitHub repository.