Pagination & sorting

findAll(filters?, pagination?, options?) returns a paginated result envelope, not a bare array.

Return shape

type FindAllResult<T> = {
  data: T[];
  total: number;   // total rows matching the filter, ignoring pagination
  page: number;    // echo of the page you requested (1-indexed)
  limit: number;   // echo of the limit you requested, after capping
};

const { data, total, page, limit } = await service.findAll(
  { status: 'active' },
  { page: 2, limit: 25 },
  { search: { term: 'john', columns: ['name', 'email'] } },
);
// data: 25 active users matching "john" for page 2
// total: 240 active users matching "john"
// page: 2
// limit: 25

Pagination options

How do I skip the count query?

Pass withCount: false. findAll then runs one query instead of two, total is null, and hasMore says whether another page follows:

const page = await posts.findAll({}, { page: 3, limit: 20, withCount: false });
// { data: [...20 rows], total: null, page: 3, limit: 20, hasMore: true }

hasMore comes from fetching one row more than the page and dropping it, so it costs nothing extra. COUNT(*) is the expensive half of a list on a big table, and infinite scroll or a "next" button does not need it. From a query string, ?withCount=false sets it.

The return type follows the option: with withCount: false written in the call, total is typed null and hasMore is a boolean; with nothing (or true), total is a number. When the value is only known at run time, as with the output of parseQuery, total is typed number | null. With the count on, the envelope has no hasMore key, the same as before.

Sorting

Explicit sortBy

await service.findAll(
  {},
  { sortBy: 'createdAt', sortOrder: 'desc' },
);

A single string sorts by one column in sortOrder, which defaults to 'desc', as it always has.

How do I sort by several columns?

Pass a list. Each entry sorts ascending unless it starts with -; an entry can also be a { column, order } object. A comma separated string means the same list.

await service.findAll({}, { sortBy: ['status', '-createdAt'] });
// ORDER BY status ASC, created_at DESC, id ASC

await service.findAll({}, { sortBy: [{ column: 'views', order: 'desc' }, { column: 'title' }] });
await service.findAll({}, { sortBy: 'status,-createdAt' }); // same as the first

How do I sort by a related column?

Write it as relation.column. It works for a top-level belongs-to relation, which is joined for the data query only (the count query stays unjoined):

await countries.findAll({}, { sortBy: ['region.name', 'name'] });
// LEFT JOIN regions ... ORDER BY regions.name ASC, countries.name ASC, countries.id ASC

A has-many or many-to-many column cannot be sorted on, because a row has many of them; that is a 400. So is an unknown column, and a column outside sortableColumns. Over HTTP, parseQuery reads ?sortBy=region.name,-name.

Why does every query end with the primary key?

So that pages are stable. Postgres returns rows that tie on the sort columns in no fixed order, and without any ORDER BY the order can change between two queries. Offset pagination over an unstable order repeats some rows and skips others. Since 3.3.1 the primary key is always the last ORDER BY column (ascending), unless it is already in the list:

await service.findAll({}, { sortBy: 'views', sortOrder: 'desc' });
// ORDER BY views DESC, id ASC

await service.findAll();
// no sortBy and no defaultSort: ORDER BY id ASC

Default sort per entity

Define an ordered list of columns in forFeature config. They're applied as the ORDER BY clause when no explicit sortBy is passed. The first column is the primary sort, the rest are tiebreakers.

DrizzleCrudModule.forFeature([
  {
    service: PostsService,
    table: posts,
    config: {
      defaultSort: [
        { column: 'position', order: 'asc' },
        { column: 'created_at', order: 'desc' }, // tiebreaker
      ],
    },
  },
])

// ORDER BY position ASC, created_at DESC, id ASC
await service.findAll();

// ORDER BY name DESC, id ASC : the default is replaced, not appended
await service.findAll({}, { sortBy: 'name' });

Opt-in created_at fallback

Set defaults.sortOrder in forRoot to apply a created_at fallback to every entity that doesn't define its own defaultSort:

DrizzleCrudModule.forRoot({
  dialect: 'postgresql',
  connectionString: process.env.DATABASE_URL,
  schema,
  defaults: {
    sortOrder: 'desc', // every findAll() orders by created_at DESC unless overridden
  },
});

Per-column order defaults to 'asc' when you don't specify it. Unknown columns in defaultSort are skipped and emit a [nestjs-drizzle-crud] defaultSort references unknown column "<col>"; skipping. warning at startup. They never throw. (Caller-supplied sortBy is different: it throws on unknown columns, see below.)

Cap on limit

limit is capped at defaults.pagination.maxLimit. Anything over the cap is silently lowered to the cap. To return more rows per page, raise the cap in forRoot:

defaults: {
  pagination: { defaultLimit: 20, maxLimit: 200 },
}

Either key can be set on its own, at the project level or per entity. pagination: { defaultLimit: 5 } keeps maxLimit at 100. Before 3.3.1 a per-entity override with one key dropped the other, and the query ran with no limit at all.

Clamping & fail-fast

Pagination and sorting inputs are normalized before they reach SQL. Bad input is either silently clamped to a safe value or rejected as BadRequestException (→ 400), depending on the failure mode.

Silently clamped (never throws):

A LIMIT 0 or negative OFFSET can never reach SQL. Every value is clamped via the package's shared resolvePagination helper.

Throws BadRequestException (→ 400):

For the full per-method exception table (404 / 409 / 400 / 500 mapping), see Errors.

Cursor-based pagination

findAll is offset based. For deep pages, infinite scroll or lists that change while people read them, use findByCursor, which returns a nextCursor instead of a page number. See Cursor pagination.

Next

• → Cursor pagination with findByCursor
• → Relations: eager loading via options.relations
• → Bulk operations: transactional pagination-free reads