[Need suggestion] Is there a paginator support in piccolo like DRF cursor paginator?

[dashsatish]

Hi,
I couldn't find any links in the docs that support pagination in piccolo. Is there any support for it which I can integrate with my FastAPI server endpoint? (Mostly looking on the lines of Django (DRF) like pagination support!)
Even thought of writing my own paginator but before that wanted to check with you @dantownsend if there's any other suggestion on this.

[dantownsend]

@dashsatish Piccolo currently supports offset and limit query clauses.

• https://piccolo-orm.readthedocs.io/en/latest/piccolo/query_clauses/offset.html
• https://piccolo-orm.readthedocs.io/en/latest/piccolo/query_clauses/limit.html

This isn't an ideal way of doing pagination, because using offset for pagination is bad performance wise.

As you mention, cursor based pagination would be far better, and it's something I'd definitely like in Piccolo, but haven't thought through the API yet.

If you're happy to use offset and limit, and are just exposing a single table as an endpoint, you may be interested in FastAPIWrapper. You can pass it __page_size and __page parameters:

http://localhost/api/my-table?__page_size=10&__page=2

[dashsatish]

Hi,
An update here:
We did implement a keyset based pagination to avoid performance issues pertaining to table scans in limit-offset based pagination.
@dantownsend I used your idea from piccolo-orm/piccolo_api#34 that has the shopify example.
(thanks 🙂 )

Digging more into cursor based pagination approach. I shall update here of any progress we make.

[dantownsend]

@dashsatish That's awesome - thanks for the update.