StabilizeORM

Pagination

Three ways to page through results — offset, count-and-slice, and cursor.
Pick offset for admin tables, cursor for infinite feeds.

Offset Pagination

paginate(page, pageSize) runs the count and the page fetch for you and returns the page metadata alongside the rows. Pages are 1-based.

paginate.ts
const { data, total, page, pageSize } = await repo.paginate(2, 20);


// data      => the 20 rows on page 2
// total     => total row count across all pages (soft-deleted rows excluded)
// page      => 2
// pageSize  => 20


const totalPages = Math.ceil(total / pageSize);

paginate() takes no filter

The signature is paginate(page, pageSize, options), and options is never read — it is accepted and discarded. There is no way to hand this method a condition, and passing one has no effect rather than raising:

typescript
// The where is silently dropped. `data` is page 2 of EVERY row,
// and `total` counts every row - not just the active ones.
await repo.paginate(2, 20, { where: { status: "active" } });

To page a filtered set, build the query yourself and count it separately — findAndCountAll({ page, pageSize, conditions }) below is the filtered equivalent, and it does honour its conditions.

Building the Query First

On the QueryBuilder, paginate() is chainable and only sets LIMIT/OFFSET — it does not run a count. Reach for this when the filter is complex and you would rather count separately, or skip the count entirely.

query-paginate.ts
const rows = await repo
  .find()
  .where("status = ?", "active")
  .orderBy("createdAt", "DESC")
  .paginate(1, 10)
  .execute(db.client);

The underlying primitives are available directly: limit()/take() and offset()/skip() are two spellings of the same pair, and first() is limit(1).

primitives.ts
repo.find().take(10).skip(5)     // LIMIT 10 OFFSET 5
repo.find().limit(10).offset(5)  // identical
repo.find().first()              // LIMIT 1


// Inspect the SQL without running it
const { query, params } = repo.find().where("status = ?", "active").take(10).toSQL();

Counting

findAndCount() returns every matching row plus the total — no slicing. findAndCountAll() adds the page window and defaults to page 1, size 20. Both return { data, total }.

find-and-count.ts
// All matching rows + count
const { data, total } = await repo.findAndCount({ relations: ["author"] });


// One page + count (note: does not echo page/pageSize back)
const page1 = await repo.findAndCountAll({ page: 2, pageSize: 10 });
const filtered = await repo.findAndCountAll({
  page: 1,
  pageSize: 25,
  conditions: { status: "active" },
});

Cursor Pagination

Offset pagination degrades on large tables — the database still walks every skipped row, and rows inserted mid-scroll shift the page boundary, so a user can see the same record twice. findMany() takes a cursor instead and produces a WHERE column > ? predicate, which stays fast at any depth and cannot skip or repeat a row.

cursor.ts
const page = await repo.findMany({
  where: { status: "active" },
  cursor: { field: "id", value: lastSeenId, direction: "forward" },
  orderBy: { field: "id", direction: "ASC" },
  take: 20,
});


// Next page: feed the last row's id back in
const next = await repo.findMany({
  where: { status: "active" },
  cursor: { field: "id", value: page[page.length - 1].id, direction: "forward" },
  orderBy: { field: "id", direction: "ASC" },
  take: 20,
});

findMany() returns a plain array — not a { data, total } wrapper — because a cursor walk deliberately avoids counting. Set direction: "backward" with a DESC order to walk the other way. take becomes LIMIT and skip becomes OFFSET.

Which to Use

choosing.ts
paginate(page, size)   // numbered pages, needs a total for the UI
findAndCountAll(...)   // numbered pages with a filter, total included
findAndCount()         // total only, no slicing
findMany({ cursor })   // infinite scroll / large tables, no total needed