sqlite3 stable cursor pagination with uuid's

0-sqlite-pagination.md

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

1-posts.csv