Skip to content

Instantly share code, notes, and snippets.

@sphrak
Last active August 20, 2022 23:43
Show Gist options
  • Save sphrak/c0824be44151636a8bfbcd8dc04df40b to your computer and use it in GitHub Desktop.
Save sphrak/c0824be44151636a8bfbcd8dc04df40b to your computer and use it in GitHub Desktop.
sqlite3 stable cursor pagination with uuid's

sqlite3 stable pagination with uuid

based on https://morningcoffee.io/stable-pagination.html.

In order for this to work I had to use uuidgen -t to generate time-based uuids otherwise the ordering would be messed up after 2-3 pages in.

create db

sqlite3 pagination.db

create table

sqlite> CREATE TABLE posts (
  id TEXT NOT NULL PRIMARY KEY,
  text TEXT,
  created_at INTEGER NOT NULL
);

create index

sqlite> CREATE UNIQUE INDEX idx_posts ON posts(id, created_at);

import data

sqlite> .mode csv
sqlite> .import 'import.csv' posts

query first page limit=n+1

omit the last result as the next page token for the next page query.

first page

sqlite> SELECT * FROM posts ORDER BY created_at DESC LIMIT 4;
f76d1b40-0a87-11ed-a1df-1bbf500f96d2,100,1658581460
f6d38d68-0a87-11ed-a1df-1bbf500f96d2,99,1658581459
f63a051c-0a87-11ed-a1df-1bbf500f96d2,98,1658581458
f5a0845a-0a87-11ed-a1df-1bbf500f96d2,97,1658581457 <-- omitted, used as next page token

second page

behaves as expected we get 97, 96, 95 and 94 is our next page token f3d3fc42-0a87-11ed-a1df-1bbf500f96d2

sqlite> SELECT id,text,created_at FROM posts WHERE (id, created_at) <= ('f5a0845a-0a87-11ed-a1df-1bbf500f96d2', 1658581457) ORDER BY created_at DESC LIMIT 4;
f5a0845a-0a87-11ed-a1df-1bbf500f96d2,97,1658581457
f506fbe6-0a87-11ed-a1df-1bbf500f96d2,96,1658581456
f46d75c0-0a87-11ed-a1df-1bbf500f96d2,95,1658581455
f3d3fc42-0a87-11ed-a1df-1bbf500f96d2,94,1658581454 <-- omitted, used as next page token

third page

as expected

sqlite> SELECT id,text,created_at FROM posts WHERE (id, created_at) <= ('f3d3fc42-0a87-11ed-a1df-1bbf500f96d2', 1658581454) ORDER BY created_at DESC LIMIT 4;
f3d3fc42-0a87-11ed-a1df-1bbf500f96d2,94,1658581454
f33a7888-0a87-11ed-a1df-1bbf500f96d2,93,1658581453
f2a0ef88-0a87-11ed-a1df-1bbf500f96d2,92,1658581452
f2076b9c-0a87-11ed-a1df-1bbf500f96d2,91,1658581451 <-- omitted, used as next page token

fourth page

sqlite> SELECT id,text,created_at FROM posts WHERE (id, created_at) <= ('f2076b9c-0a87-11ed-a1df-1bbf500f96d2', 1658581451) ORDER BY created_at DESC LIMIT 4;
f2076b9c-0a87-11ed-a1df-1bbf500f96d2,91,1658581451
f16de864-0a87-11ed-a1df-1bbf500f96d2,90,1658581450
f0d462de-0a87-11ed-a1df-1bbf500f96d2,89,1658581449
f03ad984-0a87-11ed-a1df-1bbf500f96d2,88,1658581448 <-- omitted, used as next page token

fifth page

sqlite> SELECT id,text,created_at FROM posts WHERE (id, created_at) <= ('f03ad984-0a87-11ed-a1df-1bbf500f96d2', 1658581448) ORDER BY created_at DESC LIMIT 4;
f03ad984-0a87-11ed-a1df-1bbf500f96d2,88,1658581448
efa156ce-0a87-11ed-a1df-1bbf500f96d2,87,1658581447
ef07cd56-0a87-11ed-a1df-1bbf500f96d2,86,1658581446
ee6e4a64-0a87-11ed-a1df-1bbf500f96d2,85,1658581445
version
SQLite 3.37.1 2021-12-30 15:30:28 378629bf2ea546f73eee84063c5358439a12f7300e433f18c9e1bddd948dea62
zlib version 1.2.11
clang-12.0.1
id post created_at
bb58d1da-0a87-11ed-a1df-1bbf500f96d2 0 1658581359
bbf2583c-0a87-11ed-a1df-1bbf500f96d2 1 1658581360
bc8be010-0a87-11ed-a1df-1bbf500f96d2 2 1658581361
bd2577de-0a87-11ed-a1df-1bbf500f96d2 3 1658581362
bdbf0b4c-0a87-11ed-a1df-1bbf500f96d2 4 1658581363
be5892ee-0a87-11ed-a1df-1bbf500f96d2 5 1658581364
bef2201c-0a87-11ed-a1df-1bbf500f96d2 6 1658581365
bf8bb236-0a87-11ed-a1df-1bbf500f96d2 7 1658581366
c02535a0-0a87-11ed-a1df-1bbf500f96d2 8 1658581367
c0bec3e6-0a87-11ed-a1df-1bbf500f96d2 9 1658581368
c1584dcc-0a87-11ed-a1df-1bbf500f96d2 10 1658581369
c1f1d1c2-0a87-11ed-a1df-1bbf500f96d2 11 1658581370
c28b5acc-0a87-11ed-a1df-1bbf500f96d2 12 1658581371
c324ec78-0a87-11ed-a1df-1bbf500f96d2 13 1658581372
c3be7ff0-0a87-11ed-a1df-1bbf500f96d2 14 1658581373
c458095e-0a87-11ed-a1df-1bbf500f96d2 15 1658581374
c4f19222-0a87-11ed-a1df-1bbf500f96d2 16 1658581375
c58b1e74-0a87-11ed-a1df-1bbf500f96d2 17 1658581376
c624b598-0a87-11ed-a1df-1bbf500f96d2 18 1658581377
c6be4834-0a87-11ed-a1df-1bbf500f96d2 19 1658581378
c757d4ea-0a87-11ed-a1df-1bbf500f96d2 20 1658581379
c7f15fde-0a87-11ed-a1df-1bbf500f96d2 21 1658581380
c88ae712-0a87-11ed-a1df-1bbf500f96d2 22 1658581381
c92472ba-0a87-11ed-a1df-1bbf500f96d2 23 1658581382
c9bdeb34-0a87-11ed-a1df-1bbf500f96d2 24 1658581383
ca576d40-0a87-11ed-a1df-1bbf500f96d2 25 1658581384
caf0f6ea-0a87-11ed-a1df-1bbf500f96d2 26 1658581386
cb8a8260-0a87-11ed-a1df-1bbf500f96d2 27 1658581387
cc240bec-0a87-11ed-a1df-1bbf500f96d2 28 1658581388
ccbd967c-0a87-11ed-a1df-1bbf500f96d2 29 1658581389
cd570096-0a87-11ed-a1df-1bbf500f96d2 30 1658581390
cdf08dba-0a87-11ed-a1df-1bbf500f96d2 31 1658581391
ce8a1a02-0a87-11ed-a1df-1bbf500f96d2 32 1658581392
cf23a708-0a87-11ed-a1df-1bbf500f96d2 33 1658581393
cfbd301c-0a87-11ed-a1df-1bbf500f96d2 34 1658581394
d056b85e-0a87-11ed-a1df-1bbf500f96d2 35 1658581395
d0f04078-0a87-11ed-a1df-1bbf500f96d2 36 1658581396
d189cc3e-0a87-11ed-a1df-1bbf500f96d2 37 1658581397
d2233f36-0a87-11ed-a1df-1bbf500f96d2 38 1658581398
d2bcc692-0a87-11ed-a1df-1bbf500f96d2 39 1658581399
d3565d70-0a87-11ed-a1df-1bbf500f96d2 40 1658581400
d3efe9e0-0a87-11ed-a1df-1bbf500f96d2 41 1658581401
d48971fa-0a87-11ed-a1df-1bbf500f96d2 42 1658581402
d522f4d8-0a87-11ed-a1df-1bbf500f96d2 43 1658581403
d5bc87f6-0a87-11ed-a1df-1bbf500f96d2 44 1658581404
d6561024-0a87-11ed-a1df-1bbf500f96d2 45 1658581405
d6ef98ac-0a87-11ed-a1df-1bbf500f96d2 46 1658581406
d78924f4-0a87-11ed-a1df-1bbf500f96d2 47 1658581407
d822ae26-0a87-11ed-a1df-1bbf500f96d2 48 1658581408
d8bc3ea6-0a87-11ed-a1df-1bbf500f96d2 49 1658581409
d955c81e-0a87-11ed-a1df-1bbf500f96d2 50 1658581410
d9ef4f3e-0a87-11ed-a1df-1bbf500f96d2 51 1658581411
da88d2ee-0a87-11ed-a1df-1bbf500f96d2 52 1658581412
db2257b6-0a87-11ed-a1df-1bbf500f96d2 53 1658581413
dbbbdc1a-0a87-11ed-a1df-1bbf500f96d2 54 1658581414
dc6c89ca-0a87-11ed-a1df-1bbf500f96d2 55 1658581415
dd060ea6-0a87-11ed-a1df-1bbf500f96d2 56 1658581416
dd9f93dc-0a87-11ed-a1df-1bbf500f96d2 57 1658581417
de391822-0a87-11ed-a1df-1bbf500f96d2 58 1658581418
ded29e5c-0a87-11ed-a1df-1bbf500f96d2 59 1658581419
df6c18d4-0a87-11ed-a1df-1bbf500f96d2 60 1658581420
e005a2ba-0a87-11ed-a1df-1bbf500f96d2 61 1658581421
e09f2c64-0a87-11ed-a1df-1bbf500f96d2 62 1658581422
e138ae5c-0a87-11ed-a1df-1bbf500f96d2 63 1658581423
e1d23bf8-0a87-11ed-a1df-1bbf500f96d2 64 1658581424
e26bc5ac-0a87-11ed-a1df-1bbf500f96d2 65 1658581425
e3054da8-0a87-11ed-a1df-1bbf500f96d2 66 1658581426
e39ed4b4-0a87-11ed-a1df-1bbf500f96d2 67 1658581427
e4385c56-0a87-11ed-a1df-1bbf500f96d2 68 1658581428
e4d1e222-0a87-11ed-a1df-1bbf500f96d2 69 1658581429
e56b6d2a-0a87-11ed-a1df-1bbf500f96d2 70 1658581430
e608f130-0a87-11ed-a1df-1bbf500f96d2 71 1658581431
e6a27508-0a87-11ed-a1df-1bbf500f96d2 72 1658581432
e73bfa3e-0a87-11ed-a1df-1bbf500f96d2 73 1658581433
e7d580be-0a87-11ed-a1df-1bbf500f96d2 74 1658581434
e86efadc-0a87-11ed-a1df-1bbf500f96d2 75 1658581435
e9088044-0a87-11ed-a1df-1bbf500f96d2 76 1658581436
e9a207c8-0a87-11ed-a1df-1bbf500f96d2 77 1658581437
ea3b90c8-0a87-11ed-a1df-1bbf500f96d2 78 1658581438
ead51374-0a87-11ed-a1df-1bbf500f96d2 79 1658581439
eb6ea6e2-0a87-11ed-a1df-1bbf500f96d2 80 1658581440
ec081a5c-0a87-11ed-a1df-1bbf500f96d2 81 1658581441
eca1a35c-0a87-11ed-a1df-1bbf500f96d2 82 1658581442
ed3b2784-0a87-11ed-a1df-1bbf500f96d2 83 1658581443
edd4bcd2-0a87-11ed-a1df-1bbf500f96d2 84 1658581444
ee6e4a64-0a87-11ed-a1df-1bbf500f96d2 85 1658581445
ef07cd56-0a87-11ed-a1df-1bbf500f96d2 86 1658581446
efa156ce-0a87-11ed-a1df-1bbf500f96d2 87 1658581447
f03ad984-0a87-11ed-a1df-1bbf500f96d2 88 1658581448
f0d462de-0a87-11ed-a1df-1bbf500f96d2 89 1658581449
f16de864-0a87-11ed-a1df-1bbf500f96d2 90 1658581450
f2076b9c-0a87-11ed-a1df-1bbf500f96d2 91 1658581451
f2a0ef88-0a87-11ed-a1df-1bbf500f96d2 92 1658581452
f33a7888-0a87-11ed-a1df-1bbf500f96d2 93 1658581453
f3d3fc42-0a87-11ed-a1df-1bbf500f96d2 94 1658581454
f46d75c0-0a87-11ed-a1df-1bbf500f96d2 95 1658581455
f506fbe6-0a87-11ed-a1df-1bbf500f96d2 96 1658581456
f5a0845a-0a87-11ed-a1df-1bbf500f96d2 97 1658581457
f63a051c-0a87-11ed-a1df-1bbf500f96d2 98 1658581458
f6d38d68-0a87-11ed-a1df-1bbf500f96d2 99 1658581459
f76d1b40-0a87-11ed-a1df-1bbf500f96d2 100 1658581460
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment