Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 36 ยท Client Queries

Selecting Fields, Sorting and Pagination

Choose which fields to return with select and omit, sort with orderBy, paginate with skip/take or cursors, and understand why cursor pagination scales better on large tables.

By default every query returns all scalar fields of every matching row. That is convenient and wasteful: sending a 10 KB body column to a page that only shows titles slows the database, the network and your server. This lesson shows how to trim results with select and omit, order them with orderBy, and split them into pages with skip/take or a cursor.

select: choose fields

TypeScript
const names = await prisma.product.findMany({
  select: { id: true, name: true, priceCents: true },
});
// [{ id: 1, name: 'Blue mug', priceCents: 1200 }, ...]

The result type is derived from the selection: only id, name and priceCents exist on each object, and TypeScript refuses to let you read .stock. When select is present, nothing else is returned, not even id unless you ask for it.

select also works on create, update, delete and findUnique. It is the right way to avoid leaking sensitive columns:

TypeScript
const user = await prisma.user.findUnique({
  where: { id: 1 },
  select: { id: true, email: true },   // never selects passwordHash
});

omit: everything except

TypeScript
const user = await prisma.user.findUnique({
  where: { id: 1 },
  omit: { passwordHash: true },
});

omit is the opposite of select: it returns all fields except the listed ones. You cannot use select and omit together in the same query level. You can also configure omitted fields globally in the client constructor, so passwordHash is never returned unless a query asks for it with omit: { passwordHash: false }.

orderBy: sorting

TypeScript
await prisma.product.findMany({ orderBy: { priceCents: "desc" } });

await prisma.product.findMany({
  orderBy: [
    { category: "asc" },
    { priceCents: "desc" },   // tie-breaker inside each category
  ],
});

Use an array to sort by several fields; the first has priority. Without orderBy the database may return rows in any order, which can change between runs, so always sort when you paginate. Optional fields can be sorted with { sort: "asc", nulls: "last" } on databases that support it. You can also sort by a related model's field or by a count of relations (chapter 4).

Offset pagination: skip and take

TypeScript
const page = 3;
const pageSize = 20;

const rows = await prisma.product.findMany({
  orderBy: { id: "asc" },
  skip: (page - 1) * pageSize,   // rows to skip
  take: pageSize,                // rows to return
});
const total = await prisma.product.count();
const pages = Math.ceil(total / pageSize);

It is simple and supports jumping to page 7. The cost: the database still reads and discards all the skipped rows, so page 5,000 is slow. Rows inserted or deleted while the user browses can also make items repeat or vanish between pages.

Cursor pagination

A cursor remembers where the last page ended, so the next query can start right after it:

TypeScript
const first = await prisma.product.findMany({
  orderBy: { id: "asc" },
  take: 20,
});
const lastId = first[first.length - 1].id;

const next = await prisma.product.findMany({
  orderBy: { id: "asc" },
  take: 20,
  skip: 1,                    // skip the cursor row itself
  cursor: { id: lastId },
});

The database jumps straight to the cursor using the index, so page 5,000 is as fast as page 1, and inserts do not shift your position. The price is that you can only go next or previous, not jump to an arbitrary page. Use it for infinite scroll and feeds; use offset for numbered pages on small tables. A negative take pages backwards.

For stable results the orderBy must include a unique field (the id, or a timestamp plus id), otherwise two rows with the same value can swap places.

Try it: select, sort and both kinds of pagination

The stand-in below implements select, omit, orderBy, skip, take and cursor on arrays, using the semantics described above.

Node.js Stand-in: select, orderBy, skip/take, cursor

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Building a paged API response

A tidy pattern is to return the data plus enough information for the next request:

TypeScript
async function listProducts(afterId?: number, limit = 20) {
  const rows = await prisma.product.findMany({
    orderBy: { id: "asc" },
    take: limit + 1,                           // fetch one extra
    ...(afterId && { cursor: { id: afterId }, skip: 1 }),
    select: { id: true, name: true, priceCents: true },
  });
  const hasMore = rows.length > limit;
  const items = hasMore ? rows.slice(0, limit) : rows;
  return { items, nextCursor: hasMore ? items[items.length - 1].id : null };
}

Fetching limit + 1 rows tells you whether another page exists without a second count query.

Node.js Stand-in: next-cursor logic

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

distinct

distinct removes duplicates by one or more fields:

TypeScript
const categories = await prisma.product.findMany({
  distinct: ["category"],
  select: { category: true },
  orderBy: { category: "asc" },
});

For large tables prefer groupBy (next lessons), because distinct may be applied in memory by Prisma Client rather than by the database.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does select: { id: true, name: true } return?

    select returns exactly the chosen fields, and the TypeScript type shrinks to match.
  2. 2. Why should you always provide orderBy when paginating?

    Databases do not guarantee an order without ORDER BY. Use a deterministic order, ending with a unique field.
  3. 3. What is the main advantage of cursor pagination over skip/take?

    A cursor lets the database start at an indexed position instead of reading and discarding skipped rows.
  4. 4. When using cursor: { id: lastId }, why add skip: 1?

    The cursor row is the first result, so skip: 1 starts after it.

Summary

  • select and omit shrink results (and types); never return fields like password hashes.
  • orderBy accepts one object or an array; always sort when paginating and include a unique tiebreaker.
  • skip/take give numbered pages but slow down deep; cursor gives constant-time next/previous pages.
  • Fetch limit + 1 rows to learn whether a next page exists.

Next: Updating, Deleting and Upserting Records