Skip to content

Commit 548de7d

Browse files
EmyrkEmyrk
authored
feat: User pagination using offsets (coder#1062)
Offset pagination and cursor pagination supported
1 parent 2a95917 commit 548de7d

File tree

12 files changed

+446
-71
lines changed

12 files changed

+446
-71
lines changed

‎Makefile

Lines changed: 2 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@ coderd/database/dump.sql: $(wildcard coderd/database/migrations/*.sql)
1515
.PHONY: coderd/database/dump.sql
1616

1717
# Generates Go code for querying the database.
18-
coderd/database/generate: fmt/sql coderd/database/dump.sql $(wildcard coderd/database/queries/*.sql)
18+
coderd/database/generate: coderd/database/dump.sql $(wildcard coderd/database/queries/*.sql)
1919
coderd/database/generate.sh
2020
.PHONY: coderd/database/generate
2121

@@ -34,22 +34,10 @@ else
3434
endif
3535
.PHONY: fmt/prettier
3636

37-
fmt/sql: $(wildcard coderd/database/queries/*.sql)
38-
for fi in coderd/database/queries/*.sql; do \
39-
npx sql-formatter \
40-
--language postgresql \
41-
--lines-between-queries 2 \
42-
--tab-indent \
43-
$$fi \
44-
--output $$fi; \
45-
done
46-
47-
sed -i 's/@ /@/g' ./coderd/database/queries/*.sql
48-
4937
fmt/terraform: $(wildcard *.tf)
5038
terraform fmt -recursive
5139

52-
fmt: fmt/prettier fmt/sql fmt/terraform
40+
fmt: fmt/prettier fmt/terraform
5341
.PHONY: fmt
5442

5543
gen: coderd/database/generate peerbroker/proto provisionersdk/proto provisionerd/proto apitypings/generate

‎coderd/coderd.go

Lines changed: 15 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -35,13 +35,17 @@ type Options struct {
3535
Pubsub database.Pubsub
3636

3737
AgentConnectionUpdateFrequency time.Duration
38-
AWSCertificates awsidentity.Certificates
39-
AzureCertificates x509.VerifyOptions
40-
GoogleTokenValidator *idtoken.Validator
41-
ICEServers []webrtc.ICEServer
42-
SecureAuthCookie bool
43-
SSHKeygenAlgorithm gitsshkey.Algorithm
44-
TURNServer *turnconn.Server
38+
// APIRateLimit is the minutely throughput rate limit per user or ip.
39+
// Setting a rate limit <0 will disable the rate limiter across the entire
40+
// app. Specific routes may have their own limiters.
41+
APIRateLimit int
42+
AWSCertificates awsidentity.Certificates
43+
AzureCertificates x509.VerifyOptions
44+
GoogleTokenValidator *idtoken.Validator
45+
ICEServers []webrtc.ICEServer
46+
SecureAuthCookie bool
47+
SSHKeygenAlgorithm gitsshkey.Algorithm
48+
TURNServer *turnconn.Server
4549
}
4650

4751
// New constructs the Coder API into an HTTP handler.
@@ -52,6 +56,9 @@ func New(options *Options) (http.Handler, func()) {
5256
if options.AgentConnectionUpdateFrequency == 0 {
5357
options.AgentConnectionUpdateFrequency = 3 * time.Second
5458
}
59+
if options.APIRateLimit == 0 {
60+
options.APIRateLimit = 512
61+
}
5562
api := &api{
5663
Options: options,
5764
}
@@ -61,7 +68,7 @@ func New(options *Options) (http.Handler, func()) {
6168
r.Use(
6269
chitrace.Middleware(),
6370
// Specific routes can specify smaller limits.
64-
httpmw.RateLimitPerMinute(512),
71+
httpmw.RateLimitPerMinute(options.APIRateLimit),
6572
debugLogRequest(api.Logger),
6673
)
6774
r.Get("/", func(w http.ResponseWriter, r *http.Request) {

‎coderd/coderdtest/coderdtest.go

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,7 @@ type Options struct {
5555
AzureCertificates x509.VerifyOptions
5656
GoogleTokenValidator *idtoken.Validator
5757
SSHKeygenAlgorithm gitsshkey.Algorithm
58+
APIRateLimit int
5859
}
5960

6061
// New constructs an in-memory coderd instance and returns
@@ -125,6 +126,7 @@ func New(t *testing.T, options *Options) *codersdk.Client {
125126
GoogleTokenValidator: options.GoogleTokenValidator,
126127
SSHKeygenAlgorithm: options.SSHKeygenAlgorithm,
127128
TURNServer: turnServer,
129+
APIRateLimit: options.APIRateLimit,
128130
})
129131
t.Cleanup(func() {
130132
cancelFunc()

‎coderd/database/databasefake/databasefake.go

Lines changed: 64 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,7 @@ package databasefake
33
import (
44
"context"
55
"database/sql"
6+
"sort"
67
"strings"
78
"sync"
89

@@ -164,11 +165,72 @@ func (q *fakeQuerier) GetUserCount(_ context.Context) (int64, error) {
164165
return int64(len(q.users)), nil
165166
}
166167

167-
func (q *fakeQuerier) GetUsers(_ context.Context) ([]database.User, error) {
168+
func (q *fakeQuerier) GetUsers(_ context.Context, params database.GetUsersParams) ([]database.User, error) {
168169
q.mutex.RLock()
169170
defer q.mutex.RUnlock()
170171

171-
return q.users, nil
172+
users := q.users
173+
// Database orders by created_at
174+
sort.Slice(users, func(i, j int) bool {
175+
if users[i].CreatedAt.Equal(users[j].CreatedAt) {
176+
// Technically the postgres database also orders by uuid. So match
177+
// that behavior
178+
return users[i].ID.String() < users[j].ID.String()
179+
}
180+
return users[i].CreatedAt.Before(users[j].CreatedAt)
181+
})
182+
183+
if params.AfterUser != uuid.Nil {
184+
found := false
185+
for i := range users {
186+
if users[i].ID == params.AfterUser {
187+
// We want to return all users after index i.
188+
if i+1 >= len(users) {
189+
return []database.User{}, nil
190+
}
191+
users = users[i+1:]
192+
found = true
193+
break
194+
}
195+
}
196+
197+
// If no users after the time, then we return an empty list.
198+
if !found {
199+
return []database.User{}, nil
200+
}
201+
}
202+
203+
if params.Search != "" {
204+
tmp := make([]database.User, 0, len(users))
205+
for i, user := range users {
206+
if strings.Contains(user.Email, params.Search) {
207+
tmp = append(tmp, users[i])
208+
} else if strings.Contains(user.Username, params.Search) {
209+
tmp = append(tmp, users[i])
210+
} else if strings.Contains(user.Name, params.Search) {
211+
tmp = append(tmp, users[i])
212+
}
213+
}
214+
users = tmp
215+
}
216+
217+
if params.OffsetOpt > 0 {
218+
if int(params.OffsetOpt) > len(users)-1 {
219+
return []database.User{}, nil
220+
}
221+
users = users[params.OffsetOpt:]
222+
}
223+
224+
if params.LimitOpt > 0 {
225+
if int(params.LimitOpt) > len(users) {
226+
params.LimitOpt = int32(len(users))
227+
}
228+
users = users[:params.LimitOpt]
229+
}
230+
tmp := make([]database.User, len(users))
231+
copy(tmp, users)
232+
233+
return tmp, nil
172234
}
173235

174236
func (q *fakeQuerier) GetWorkspacesByTemplateID(_ context.Context, arg database.GetWorkspacesByTemplateIDParams) ([]database.Workspace, error) {

‎coderd/database/querier.go

Lines changed: 1 addition & 1 deletion
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries.sql.go

Lines changed: 52 additions & 2 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/users.sql

Lines changed: 39 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -56,4 +56,42 @@ WHERE
5656
SELECT
5757
*
5858
FROM
59-
users;
59+
users
60+
WHERE
61+
CASE
62+
-- This allows using the last element on a page as effectively a cursor.
63+
-- This is an important option for scripts that need to paginate without
64+
-- duplicating or missing data.
65+
WHEN @after_user :: uuid != '00000000-00000000-00000000-00000000' THEN (
66+
-- The pagination cursor is the last user of the previous page.
67+
-- The query is ordered by the created_at field, so select all
68+
-- users after the cursor. We also want to include any users
69+
-- that share the created_at (super rare).
70+
created_at >= (
71+
SELECT
72+
created_at
73+
FROM
74+
users
75+
WHERE
76+
id = @after_user
77+
)
78+
-- Omit the cursor from the final.
79+
AND id != @after_user
80+
)
81+
ELSE true
82+
END
83+
AND CASE
84+
WHEN @search :: text != '' THEN (
85+
email LIKE concat('%', @search, '%')
86+
OR username LIKE concat('%', @search, '%')
87+
OR 'name' LIKE concat('%', @search, '%')
88+
)
89+
ELSE true
90+
END
91+
ORDER BY
92+
-- Deterministic and consistent ordering of all users, even if they share
93+
-- a timestamp. This is to ensure consistent pagination.
94+
(created_at, id) ASC OFFSET @offset_opt
95+
LIMIT
96+
-- A null limit means "no limit", so -1 means return all
97+
NULLIF(@limit_opt :: int, -1);

‎coderd/httpmw/ratelimit.go

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,12 @@ import (
1313
// RateLimitPerMinute returns a handler that limits requests per-minute based
1414
// on IP, endpoint, and user ID (if available).
1515
func RateLimitPerMinute(count int) func(http.Handler) http.Handler {
16+
// -1 is no rate limit
17+
if count <= 0 {
18+
return func(handler http.Handler) http.Handler {
19+
return handler
20+
}
21+
}
1622
return httprate.Limit(
1723
count,
1824
1*time.Minute,

0 commit comments

Comments
 (0)