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.
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
}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: 25total 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.
pageOptionalnumber
Defaults to 1
limitOptionalnumber
Defaults to pagination.defaultLimit (20)
sortByOptionalstring | Array<string | { column: string; order?: 'asc' | 'desc' }>
Defaults to the entity's defaultSort
sortOrderOptional'asc' | 'desc'
Defaults to 'asc'
withCountOptionalboolean
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.
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:
| Call | total type | hasMore |
|---|---|---|
withCount: false written in the call | null | boolean, always present |
withCount left out or true | number | absent |
A value known only at run time (the output of parseQuery) | number | null | boolean | undefined |
How do I sort by one column?
Pass the column as a string. A single string sorts in sortOrder, which defaults to 'desc'.
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 ASCA - 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.
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 firstsortOrder does not apply to list entries. A column listed twice is sorted once, by its first entry.
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 unless a filter needs the relation.
await this.postsService.findAll({}, { sortBy: ['author.name', '-created_at'] });
// LEFT JOIN authors ... ORDER BY authors.name ASC, posts.created_at DESC, posts.id ASCThese 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
sortableColumnswhen 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.
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 ASCHow 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.
DrizzleCrudModule.forFeature([
{
service: PostsService,
table: posts,
config: {
defaultSort: [
{ column: 'position', order: 'asc' },
{ column: 'created_at', order: 'desc' }, // tiebreaker
],
},
},
]);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 extendedsortBy 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.
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.defaultLimitOptionalnumber
Defaults to 20
pagination.maxLimitOptionalnumber
Defaults to 100
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:
| Input | Becomes |
|---|---|
page missing or NaN | 1 |
page < 1 | 1 |
limit missing or NaN | defaultLimit |
limit <= 0 | 1 |
limit > maxLimit | maxLimit |
A fraction, such as limit: 10.7 | Rounded 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):
| Input | Why |
|---|---|
A caller sortBy that is not a column of the table or of a belongs-to relation | A typo surfaces at once instead of returning rows in the wrong order. |
A caller sortBy outside sortableColumns | See 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 Infinity | It would reach SQL as an opaque 500. |
| A filter key that is not a column or relation, or an unknown operator | Dropping it would return every row for a typo. See unknown filter keys. |
| A date filter value that is not a date | For 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
20 rows, from pagination.defaultLimit. A caller can ask for up to pagination.maxLimit rows, which is 100 unless you raise it in forRoot or forFeature.
No. It is lowered to maxLimit, and the response's limit field shows the value that was used.
The call passed withCount: false (or ?withCount=false), which skips the COUNT(*) query. Use hasMore to decide whether to show a next page.
It replaces it. Only the primary key tiebreaker is still added at the end.
No. A row has many values for that column, so the sort is a 400. Only top-level belongs-to columns, written as relation.column, can be sorted on.
Next steps
Cursor pagination with Drizzle
Keyset pages with findByCursor for deep lists and infinite scroll.
Filtering with Drizzle in NestJS
Operators, OR groups and search to narrow the rows before you page them.
Parse query strings into filters
Turn ?page=2&sortBy=-created_at into findAll arguments.
NestJS Drizzle filter and column allowlists
Limit which columns a caller may sort by with sortableColumns.
Database indexes for fast Drizzle queries
The index a sort, a filter and the count need on a large table.
NestJS query string filters for Drizzle
Parse NestJS query string filters for Drizzle with parseQuery. It turns ?age[gte]=18&status=active into typed filters, pagination and options.
Cursor pagination with Drizzle
Drizzle cursor pagination with findByCursor: pass each nextCursor back for the next page. With an index on the sort columns, page 500 is as fast as page 1.