In Prisma Client, how does cursor paging with cursor, skip and take differ from skip/take offset paging, and why is skip: 1 needed?
answer
- a unique-key position, not a filter
- compared against a subquery
- the cursor row is inclusive
- negative take walks backwards
- end the sort on a unique field
basics
~20 sskip and take compile to OFFSET and LIMIT. cursor names a row by unique key and starts the page at that row's sort position, including the row itself, so skip: 1 drops it; a negative take pages backwards.
solid answer
~50 sOffset paging is `skip: 40, take: 20`, which Prisma Client sends as `LIMIT 20 OFFSET 40`. Cursor paging passes `cursor: { id: lastId }`, a `WhereUniqueInput`, with `take` and an `orderBy`; Prisma 7.10 compiles it to a `WHERE` that compares the sort columns with a subquery reading the cursor row, such as `"id" >= (SELECT "id" ... WHERE "id" = $1)`. The comparison is inclusive, so the cursor row comes back first unless you add `skip: 1`, which becomes `OFFSET 1`. A negative `take` flips the comparison and the sort to read the page before the cursor, and the client restores your order. End `orderBy` on a unique key, such as `[{ createdAt: "desc" }, { id: "desc" }]`: with `createdAt` alone, 7.10 sends no `LIMIT` and pages in memory after reading every row from the cursor's timestamp onward. If the cursor row is deleted, the page comes back empty.
code
typescript · 13 linesasync function newestProducts(categoryId: number, after?: number) {
const items = await prisma.product.findMany({
where: { categoryId, inStock: true },
orderBy: [{ createdAt: "desc" }, { id: "desc" }],
take: 20,
...(after !== undefined ? { cursor: { id: after }, skip: 1 } : {}),
select: { id: true, name: true, createdAt: true },
});
return {
items,
nextCursor: items.length === 20 ? items[items.length - 1].id : null,
};
}go deeper
Recall that skip and take are offset and limit, that cursor names a row by unique key, and that skip: 1 drops the cursor row.
Explain how the cursor becomes a comparison against a subquery, why the result is inclusive, how a negative take pages backwards, and that no orderBy means primary-key order.
Show the production traps: a non-unique sort alone sends no LIMIT and pages in memory, a deleted cursor row empties the page, and filters must travel with the cursor.
Weigh numbered pages against cursor feeds per endpoint, and set a convention that every cursor sort ends on the primary key.
## Offset paging: `skip` and `take` The simplest paging in Prisma Client turns a page number into an offset: ```ts const page = await prisma.product.findMany({ where: { inStock: true }, orderBy: { id: "asc" }, skip: 40, take: 20, }); ``` `take` becomes `LIMIT 20` and `skip` becomes `OFFSET 40`. It supports "jump to page 7" links, and the client needs nothing but a number. Its weaknesses are SQL ones: the database still walks past every skipped row, and rows inserted between requests shift the pages. The SQL lesson on keyset pagination covers both. ## Cursor paging: what `cursor` does `cursor` takes a **`WhereUniqueInput`**: it must identify exactly one row by `id`, a `@unique` column or a compound unique key. Prisma Client does not match rows against it; it uses that row's **position in your `orderBy`** as the starting point. For `cursor: { id: 57 }, orderBy: { id: "asc" }, take: 20`, the statement Prisma 7.10 sends works like this: 1. a subquery reads the cursor row's sort value, `(SELECT "id" FROM "public"."Product" WHERE "id" = $1)`; 2. the outer `WHERE` keeps rows whose `id` is **greater than or equal to** that value, combined with your own `where`; 3. `ORDER BY "id" ASC LIMIT 20` closes the page, and `skip` becomes the `OFFSET`. If the query names no `orderBy`, Prisma orders by the primary key. ## Why `skip: 1` Because the comparison is inclusive, the cursor row belongs to the result. An infinite scroll fetches page two with the last id of page one as its cursor, so without `skip: 1` the first item of page two repeats the last item of page one. `skip: 1` becomes `OFFSET 1` and drops exactly that row. Keep `skip` at 0 when the cursor is a row the user has not seen yet, such as a deep link to one product. ## Paging backwards with a negative `take` `take: -20` reads the 20 rows on the other side of the cursor. The statement flips the comparison to `<=` and the sort to `DESC`, and Prisma Client reverses the rows before returning them, so the page still arrives in the order your `orderBy` asked for. Add `skip: 1` to exclude the cursor row in this direction too. ## Choosing an `orderBy` for a cursor A cursor's position is only well defined when the sort ends on a unique field. Prisma 7.10 compiles three shapes differently: | `orderBy` | Condition sent | `LIMIT` sent | |---|---|---| | `{ id: "asc" }`, or none | `"id" >= (subquery)` | yes | | `[{ createdAt: "desc" }, { id: "desc" }]` | `("createdAt" = sub AND "id" <= sub) OR "createdAt" < sub` | yes | | `{ createdAt: "desc" }` alone | `"createdAt" <= (subquery)` | **no** | The last row matters in production. With a non-unique sort alone, Prisma cannot place the cursor among rows that share its timestamp in SQL, so it reads **every row from the cursor's `createdAt` onward**, finds the cursor row in memory and slices the page there. On a large catalogue, each scroll request becomes a large read. Adding the primary key as the final sort key restores a bounded statement. ## Edge cases worth knowing - **Deleted cursor row.** The subquery finds no row and yields `NULL`, every comparison against it is unknown, and the page comes back empty. Treat an empty page after a known cursor as "restart from the top", or soft-delete rows that can serve as cursors. - **Changing filters.** The cursor fixes a position, not a list. Send the same `where` with every page, or encode it into the next-page token. - **Response shape.** Return the last item's id as `nextCursor`. A page shorter than `take` means nothing follows; a full page may still be followed by an empty one. - **Other methods.** `findFirst`, `count` and `aggregate` also accept `cursor`, `skip` and `take`, so "how many items remain after this cursor" is one `count` call. - **Totals.** A cursor feed usually shows no total. If the UI needs one, a separate `count` with the same `where` costs one more statement per request, so cache it or show an estimate.
- What does a client see if the product used as its cursor is deleted between two requests?An empty page. The cursor is resolved by a subquery reading that row's sort values; with the row gone it yields `NULL`, every comparison against it is unknown, and no row qualifies. Prisma does not resume from a neighbour, so the client should restart from the top or the app should soft-delete rows that serve as cursors.
- Can cursor paging jump straight to page 50?No. A cursor only says "continue from this row", so reaching page 50 means walking pages 1 to 49 or keeping their cursors. Numbered page links need `skip`/`take` offset paging, which gets more expensive as the offset grows; the SQL lesson on keyset pagination explains that cost.
A bookmark marks the page you stopped on, not the next one. Opening the book at the bookmark shows that page again, so to continue you turn one page first, which is what skip: 1 does. If someone tears the bookmarked page out, the bookmark no longer marks any place.
- Prisma's cursor excludes the cursor row on its own, so skip: 1 loses a real item.
- cursor accepts any filter, such as a createdAt value, not only a unique key.
- Sorting a cursor query by createdAt alone is fine; Prisma still sends a LIMIT.
- A negative take returns the page in reverse order of the orderBy.
- If the cursor row was deleted, Prisma resumes from the next row.
Assumes Prisma ORM 7.10.0: the statements described, including the missing LIMIT for a non-unique cursor sort, are what its query compiler emits; earlier engines compiled cursor queries their own way.