Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 37 ยท Querying

Sorting, Pagination and Selecting Columns

Sort results, paginate with take/skip and limit/offset, switch to keyset pagination for big tables, and select only the columns you need in TypeORM and Sequelize, with runnable demos.

Real lists need an order, a page size and often only a few columns. Get these right early: sorting and pagination decide whether a list page takes five milliseconds or five seconds on a big table.

Library code is read-only; the stand-ins show the SQL behaviour that matters.

Sorting

TypeScript
// TypeORM
await products.find({ order: { price: "ASC", title: "DESC" } });

// Sequelize
await Product.findAll({ order: [["price", "ASC"], ["title", "DESC"]] });

Always add a tie-breaker (usually the primary key) so the order is deterministic. Without one, rows with equal prices can appear in different orders between requests, and pages overlap or skip rows.

Offset pagination

TypeScript
// TypeORM
const [rows, total] = await products.findAndCount({
  order: { id: "ASC" },
  take: 20,                  // LIMIT
  skip: (page - 1) * 20,     // OFFSET
});

// Sequelize
const { rows, count } = await Product.findAndCountAll({
  order: [["id", "ASC"]],
  limit: 20,
  offset: (page - 1) * 20,
});

findAndCount runs a second COUNT(*) query for the total. That count can be the slowest part on large tables; if you do not need a page number display, skip it and show "next" only.

Node.js Pages with take and skip

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.

The offset problem

OFFSET 100000 makes the database walk and discard 100,000 rows before returning the page, so deep pages are slow. Rows inserted while a user pages also shift everything by one. Keyset pagination (also called cursor pagination) remembers the last row seen and asks for rows after it:

TypeScript
// TypeORM
await products.find({
  where: { id: MoreThan(lastSeenId) },
  order: { id: "ASC" },
  take: 20,
});

// Sequelize
await Product.findAll({
  where: { id: { [Op.gt]: lastSeenId } },
  order: [["id", "ASC"]],
  limit: 20,
});

With an index on id, the cost is the same for page 1 and page 10,000. The trade-off: you cannot jump to page 57 directly. When sorting by a non-unique column (price), the cursor must include both: (price, id) > (lastPrice, lastId).

Node.js Offset vs keyset

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.

Selecting columns

Loading every column wastes memory and bandwidth, and may expose secrets.

TypeScript
// TypeORM
await users.find({ select: { id: true, name: true } });
await users.find({ select: ["id", "name"] });           // array form

// Sequelize
await User.findAll({ attributes: ["id", "name"] });
await User.findAll({ attributes: { exclude: ["passwordHash"] } });
await User.findAll({ attributes: ["id", ["full_name", "name"]] });   // alias

Selecting a subset returns partial entities. Do not call save() on a partial entity unless you know that unselected fields stay untouched (TypeORM updates only changed properties; Sequelize save writes the attributes it knows changed).

Distinct and limits

TypeScript
// Sequelize
await Order.findAll({ attributes: ["customerId"], group: ["customerId"] });  // or DISTINCT via fn()

// TypeORM
await orders.createQueryBuilder("o").select("o.customerId").distinct(true).getRawMany();

A quick safety rule: cap page sizes on the server (for example Math.min(requested, 100)) so a client cannot ask for a million rows.

Node.js Selecting and capping

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.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why add the primary key as a final sort column?

    Rows with equal sort values need a tie-breaker to have a stable order.
  2. 2. Which TypeORM options implement LIMIT and OFFSET?

    TypeORM find options use take and skip; Sequelize uses limit and offset.
  3. 3. What is the main advantage of keyset pagination?

    It filters from the last seen key instead of discarding offset rows.
  4. 4. How do you hide a passwordHash column from Sequelize results?

    attributes controls the selected columns; exclude removes the listed ones.

Summary

  • Sort with order; always include a unique tie-breaker.
  • Offset pagination is simple but slow for deep pages and unstable under inserts.
  • Keyset pagination filters after the last seen key and scales better.
  • Select only the columns you need and cap page sizes on the server.
  • findAndCount costs an extra COUNT(*); skip it when you can.

Next: Query Builders and Raw SQL