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:

const last: number = Number(req.query.last || 0);
const rows = db.prepare("SELECT id, status FROM orders WHERE id > ? ORDER BY id LIMIT 5").all(last) as { id: number; status: string }[];

Each answer also tells the client where the next page starts: "next_after" is the id of the page's last row 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 of type number set to 3, and make the route for GET /products read after from the query string as a number (0 when it is missing), fetch the id, name and price of the products whose id is bigger, in id order, at most PAGE_SIZE of them, as ProductSummary[], and answer {"products": […], "next_after": …}.

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

Hint
Read the parameter with req.query.after || 0 inside Number(...). Start with let nextAfter: number | null = null;, and only when the page is full, rows.length === PAGE_SIZE, set it to the id of the last row, rows[rows.length - 1].id.