Page through query results
Skip earlier records while retrieving a bounded result set.
Drizzle ORM · Module
.offset
.offset() adds an offset clause to a Drizzle select query, skipping a specified number of rows. It is commonly paired with .limit() and ordering to implement pagination.
How it works
.offset() adds an offset clause that skips a specified number of rows before the query returns results. Pair it with .limit() for page-sized results, and order by a unique column so page boundaries remain stable.
Syntax
.offset(value)Parameters
| Name | Type | Description |
|---|---|---|
offset | number | Number of rows to skip before returning query results. |
Returns
A query builder with an offset clause; when executed, it returns rows after the specified number of rows are skipped.
Key points
- Use
.offset()with.limit()when a query needs to skip earlier rows and cap the returned page. - The numeric argument represents the count of rows discarded before result rows are produced.
- Treat
.limit()as the page size and.offset()as the number of rows skipped. - For large datasets, consider deferred joins to paginate a smaller subset rather than the entire table.
Common mistakes
- Use a unique ordering column for stable pagination; ordering only by a non-unique value can produce inconsistent results.
- For MSSQL, call
.orderBy()before.offset()because OFFSET belongs to the ORDER BY clause.
Example: Paginate a select query
import { asc } from "drizzle-orm";
import { users } from "./schema";
const page = 2;
const pageSize = 10;
const rows = await db
.select()
.from(users)
.orderBy(asc(users.id))
.limit(pageSize)
.offset((page - 1) * pageSize);Fetches the second page by skipping the first page's rows and returning the next page-sized batch.
When to use it
Window into an ordered result set
Start reading after a known number of rows in an ordered query.
Build reusable pagination
Apply page-number arithmetic in a reusable query helper.
Interview questions
QWhat does .offset() do in Drizzle?
.offset() specifies how many rows to skip before returning results. For pagination, calculate it as (page - 1) * pageSize and combine it with .limit(pageSize).
Follow-up: Why should pagination order by a unique column? Because non-unique ordering can make rows cross page boundaries between executions; append a unique column when needed.
QHow do limit and offset work together for pagination?
The offset is the number of rows to skip, while the limit is the number of rows to return. A typical page query orders rows, applies the page size with .limit(), and skips prior pages with .offset().
Follow-up: How can a non-unique sort be made safer? Add a unique column, such as the primary key, after the non-unique sort column.
QWhat MSSQL-specific ordering rule applies to .offset()?
In MSSQL, OFFSET and FETCH are part of the ORDER BY clause, so .orderBy() must precede .offset(). Drizzle can then emit an OFFSET clause, optionally followed by .fetch().
Follow-up: What should a reusable MSSQL pagination helper do? Accept a query builder and apply .offset((page - 1) * pageSize).fetch(pageSize) after ordering.
Related
limitorderByfetchfindManydeferred join