Pagination and sorting with Drizzle

Drizzle pagination in NestJS with findAll: page and limit (20 rows by default, at most 100), withCount: false to skip COUNT(*), and multi-column sorts.

For Drizzle pagination in NestJS, call findAll(filters, { page, limit, sortBy }) on your service. It returns { data, total, page, limit }, not a bare array. page starts at 1, limit defaults to 20 and is capped at 100, and total counts every matching row. sortBy takes one column, a list such as ['status', '-created_at'], or a belongs-to column such as author.name. The primary key is always the last sort column, so pages never repeat or skip rows.

Column names are table keys

sortBy, defaultSort and the examples on this page name the key in your Drizzle table object (created_at in created_at: timestamp('created_at')), not the SQL column name. The examples use a posts table with snake_case keys and a PostsService.

What does findAll return?

A page envelope: the rows, the count of matching rows, and the page and limit that were actually used.

query.interface.ts
interface CrudPage<T> {
  data: T[];
  total: number;     // rows matching the filter and search, ignoring pagination
  page: number;      // the page used, 1-indexed, after clamping
  limit: number;     // the limit used, after clamping to maxLimit
  hasMore?: boolean; // only with withCount: false
}
posts.controller.ts
const { data, total, page, limit } = await this.postsService.findAll(
  { status: 'published' },
  { page: 2, limit: 25 },
  { search: { term: 'drizzle', columns: ['title', 'body'] } },
);
// data:  25 published posts matching "drizzle", rows 26 to 50
// total: 240 published posts matching "drizzle"
// page:  2
// limit: 25

total is counted after filters, options.search, the tenant scope and soft delete, but before LIMIT and OFFSET, so it stays the same on every page. The data query and the count query run at the same time.

The pagination argument is optional. Leave it out and findAll returns the first page, up to defaultLimit rows.

Which pagination options are there?

Five fields, all optional.

pageOptional

number

1-indexed page number. Values below 1 become 1.

Defaults to 1

limitOptional

number

Rows per page. Capped at pagination.maxLimit (100 by default).

Defaults to pagination.defaultLimit (20)

sortByOptional

string | Array<string | { column: string; order?: 'asc' | 'desc' }>

One column, a list, or a comma separated string. A dotted name sorts by a belongs-to relation column.

Defaults to the entity's defaultSort

sortOrderOptional

'asc' | 'desc'

Direction for a single sortBy string without a - or + prefix. Any other value is a 400.

Defaults to 'asc'

withCountOptional

boolean

false skips the COUNT(*) query: total is null and hasMore is set.

Defaults to true

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.

posts.controller.ts
const page = await this.postsService.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 (on 200,000 rows a page took 23 ms with it and 0.6 ms without), and infinite scroll or a "next" button does not need it. From a query string, ?withCount=false sets it. For deep pages, cursor pagination skips both the count and the OFFSET; Database indexes for fast Drizzle queries lists the index each sort needs.

The return type follows the option:

Calltotal typehasMore
withCount: false written in the callnullboolean, always present
withCount left out or truenumberabsent
A value known only at run time (the output of parseQuery)number | nullboolean | undefined

How do I sort by one column?

Pass the column as a string. A single string sorts in sortOrder, which defaults to 'desc'.

posts.controller.ts
await this.postsService.findAll({}, { sortBy: 'created_at' });
// ORDER BY created_at DESC, id ASC

await this.postsService.findAll({}, { sortBy: 'created_at', sortOrder: 'asc' });
// ORDER BY created_at ASC, id ASC

A - prefix forces descending and a + prefix forces ascending, whatever sortOrder says.

How do I sort by several columns?

Pass a list. In a list, each entry sorts ascending unless it starts with -. An entry can also be a { column, order } object, where order defaults to 'asc'. A comma separated string means the same list.

posts.controller.ts
await this.postsService.findAll({}, { sortBy: ['status', '-created_at'] });
// ORDER BY status ASC, created_at DESC, id ASC

await this.postsService.findAll({}, {
  sortBy: [{ column: 'views', order: 'desc' }, { column: 'title' }],
});
// ORDER BY views DESC, title ASC, id ASC

await this.postsService.findAll({}, { sortBy: 'status,-created_at' }); // same as the first

sortOrder does not apply to list entries. A column listed twice is sorted once, by its first entry.

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 unless a filter needs the relation.

posts.controller.ts
await this.postsService.findAll({}, { sortBy: ['author.name', '-created_at'] });
// LEFT JOIN authors ... ORDER BY authors.name ASC, posts.created_at DESC, posts.id ASC

These are a 400:

  • a has-many or many-to-many column, because a row has many of them,
  • a column that does not exist on the table or the relation,
  • a column outside sortableColumns when that allowlist is set.

Over HTTP, parseQuery reads ?sortBy=author.name,-created_at. It always hands the service a list, and an unprefixed column sorts ascending unless ?sortOrder=desc is set: ?sortBy=views and findAll({}, { sortBy: 'views' }) are both views ASC. Before 3.5.2 the second was views DESC.

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.

posts.controller.ts
await this.postsService.findAll({}, { sortBy: 'views', sortOrder: 'desc' });
// ORDER BY views DESC, id ASC

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

How do I set a default sort for an entity?

Set defaultSort in the entity's forFeature config. It is an ordered list: the first column is the primary sort and the rest are tiebreakers. It applies whenever the caller passes no sortBy.

posts/posts.module.ts
DrizzleCrudModule.forFeature([
  {
    service: PostsService,
    table: posts,
    config: {
      defaultSort: [
        { column: 'position', order: 'asc' },
        { column: 'created_at', order: 'desc' }, // tiebreaker
      ],
    },
  },
]);
posts.controller.ts
await this.postsService.findAll();
// ORDER BY position ASC, created_at DESC, id ASC

await this.postsService.findAll({}, { sortBy: 'title' });
// ORDER BY title DESC, id ASC: the default is replaced, not extended

sortBy replaces defaultSort

An explicit sortBy replaces the entity's defaultSort entirely. Only the primary key tiebreaker is still added.

Each entry's order defaults to 'asc'. An unknown column in defaultSort is a config mistake, not a request error, so it never throws. The column is skipped, and each query that falls back to defaultSort logs [nestjs-drizzle-crud] defaultSort references unknown column "<col>"; skipping. A caller sortBy with an unknown column is a 400 instead.

Can every entity sort by created_at by default?

Yes. Set defaults.sortOrder in forRoot. Every entity that has no defaultSort of its own and has a timestamps.createdAt column then sorts by that column in this direction.

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

Leave defaults.sortOrder out and entities without a defaultSort sort by the primary key alone.

How do I change the page size limits?

Set pagination under defaults in forRoot for every entity, or in an entity's forFeature config for that entity alone.

pagination.defaultLimitOptional

number

Rows per page when the caller passes no limit.

Defaults to 20

pagination.maxLimitOptional

number

The largest limit a caller can get. A larger limit is lowered to this, without an error.

Defaults to 100

app.module.ts
DrizzleCrudModule.forRoot({
  dialect: 'postgresql',
  connectionString: process.env.DATABASE_URL,
  schema,
  defaults: {
    pagination: { defaultLimit: 20, maxLimit: 200 },
  },
});

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

The cap cannot be turned off

The cap applies on every call, so a client cannot ask for a million rows. For an export endpoint that needs every row, walk the table with cursor pagination, or write a custom service method over this.config.db.

Which inputs are clamped and which are a 400?

Pagination input is clamped to a safe value and never throws. Sort and filter input that cannot be right is a BadRequestException (400).

Clamped, never throws:

InputBecomes
page missing or NaN1
page < 11
limit missing or NaNdefaultLimit
limit <= 01
limit > maxLimitmaxLimit
A fraction, such as limit: 10.7Rounded down: 10

A LIMIT 0 or a negative OFFSET never reaches SQL. The same clamp (resolvePagination) runs in findAll, findByCursor and fullTextSearch.

A BadRequestException (400):

InputWhy
A caller sortBy that is not a column of the table or of a belongs-to relationA typo surfaces at once instead of returning rows in the wrong order.
A caller sortBy outside sortableColumnsSee allowlists.
A sortBy list entry that is neither a string nor { column, order }The shape is checked before any SQL runs.
A sortOrder other than 'asc' or 'desc'Before this check, anything other than 'desc' silently sorted ascending.
{ gt, gte, lt, lte } with NaN or InfinityIt would reach SQL as an opaque 500.
A filter key that is not a column or relation, or an unknown operatorDropping it would return every row for a typo. See unknown filter keys.
A date filter value that is not a dateFor example { created_at: { gte: 'garbage' } }.

For the full per-method table of 400, 404, 409 and 500 responses, see errors.

When should I use cursor pagination instead?

When people scroll deep into a list, or the list changes while they read it. findAll is offset based: page 500 still reads and throws away 499 pages of rows, and a row inserted on page 1 shifts every later page by one. findByCursor returns a nextCursor instead of a page number and avoids both. See cursor pagination with Drizzle.

Common questions

Next steps

Last updated View change history

On this page