Pages That Stay Fast

A real shop may sell 100,000 products, and no answer should carry them all, so the list comes in pages. With LIMIT and OFFSET, a deep page like OFFSET 90000 makes the database walk past 90,000 rows only to throw them away: the deeper the page, the slower it gets.

A keyset page remembers where the last page ended instead: "the rows after id 6". The id is the primary key, which has its own index, so the database jumps straight to the first id after it, and page 1 and page 30,000 cost the same. Here is the idea for a list of orders, 5 per page:

app.MapGet("/orders", (int? last) => {
    last = last ?? 0;
    var cmd = RunQuery("SELECT id, status FROM orders WHERE id > $last ORDER BY id LIMIT 5");
    cmd.Parameters.AddWithValue("$last", last);
    ...
});

Each answer also tells the client where the next page starts: "next_after" is the id of the page's last product when the page is full, and null on a shorter, last page. Because a page starts after an id, not after a count of rows, a product removed between two pages doesn't make the next page skip one.

GET /products          →  200  {"next_after": 3, "products": [{"id": 1, "name": "Mug", "price": 12}, {"id": 2, …}, {"id": 3, …}]}
GET /products?after=3  →  200  {"next_after": 6, "products": [{"id": 4, "name": "Pen", "price": 2}, {"id": 5, …}, {"id": 6, …}]}
GET /products?after=6  →  200  {"next_after": null, "products": [{"id": 7, "name": "Stapler", "price": 9}, {"id": 8, …}]}
GET /products?after=8  →  200  {"next_after": null, "products": []}
(then the Notebook, id 2, is removed)
GET /products?after=3  →  200  {"next_after": 6, "products": [{"id": 4, …}, {"id": 5, …}, {"id": 6, …}]}
GET /products          →  200  {"next_after": 4, "products": [{"id": 1, …}, {"id": 3, …}, {"id": 4, …}]}

Your task: add a constant PAGE_SIZE = 3, and make the /products route use its int? after parameter (0 when the URL has none), fetch the id, name and price of the products whose id is bigger, in id order, at most PAGE_SIZE of them, and answer new { products = …, next_after }.

Assignment
Follow the Coding Tutorial to turn GET /products into keyset pages.

Hint
Give after a default with after ?? 0, and both $after and $size a value with AddWithValue. Start with object next_after = null;, and only when the page is full, result.Count == PAGE_SIZE, set it to the id of the last product, result[result.Count - 1]["id"].