# Pagination

> Use paginate() for offset pages and cursor() for cursor navigation.

URL: https://better-drizzle.com/docs/querying/pagination

`paginate()` is the offset helper. `cursor()` is the cursor helper. Both return
`{ data, pagination }`, but the metadata is specific to the strategy.

## Offset pagination

Use `limit` (or `take`) with `skip` when the consumer needs totals and page
numbers:



**better-drizzle**

```ts  
const {
	data, // User[]
	pagination: { type, page, perPage, total, pageCount, hasNext, hasPrevious }, // type: "offset"
} = await client.users.paginate({
	limit: 20,
	skip: 40,
	orderBy: [{ id: 'asc' }],
	where: { active: true },
});
```

**Drizzle**

```ts  
import { asc, count, eq } from 'drizzle-orm';

const perPage = 20;
const skip = 40;
const where = eq(users.active, true);

const [data, [{ total }]] = await Promise.all([
	db
		.select()
		.from(users)
		.where(where)
		.orderBy(asc(users.id))
		.limit(perPage)
		.offset(skip),
	db.select({ total: count() }).from(users).where(where),
]);

// shape the pagination metadata by hand
const page = {
	data,
	pagination: {
		type: 'offset' as const,
		page: Math.floor(skip / perPage) + 1,
		perPage,
		total,
		pageCount: Math.ceil(total / perPage),
		hasNext: skip + data.length < total,
		hasPrevious: skip > 0,
	},
};
```

`paginate()` reads these paging inputs:

| Input               | Meaning                                                             |
| ------------------- | ------------------------------------------------------------------- |
| `page`              | 1-based page number; computes `skip` and cannot be combined with it |
| `perPage` / `limit` | page size, default `10`                                             |
| `take`              | page size; wins over `perPage` and `limit`                          |
| `skip`              | rows to skip, default `0`                                           |

```ts
const { data, pagination } = await client.users.paginate({ page: 3, perPage: 20 });
```

In the result, `perPage` is the resolved page size, `page` is
`Math.floor(skip / perPage) + 1`, and `pageCount` is `0` when `total` is `0`.

## Cursor reads without metadata

`findMany({ cursor, take })` returns rows without pagination metadata. When
`orderBy` is omitted or empty, the first cursor field supplies ascending order.
A negative `take` selects the closest rows before the cursor and returns them
in that order. Include a unique tie-breaker in `orderBy` and the cursor when
the main sort field is not unique.



**better-drizzle**

```ts  
const previous = await client.posts.findMany({
	cursor: { id: 50 },
	take: -10,
}); // ids 40 through 49, ascending
```

**Drizzle**

```ts  
import { desc, lt } from 'drizzle-orm';

const previous = (await db.select().from(posts)
	.where(lt(posts.id, 50))
	.orderBy(desc(posts.id))
	.limit(10)).reverse();
```

## Cursor pagination

Use `cursor()` for feed-style navigation:



**better-drizzle**

```ts  
const {
	data: first,
	pagination: { nextCursor },
} = await client.users.cursor({
	limit: 2,
	orderBy: [{ id: 'asc' }],
});

const { data: second } = await client.users.cursor({
	limit: 2,
	orderBy: [{ id: 'asc' }],
	after: nextCursor, // { id: 2 }, or null when hasNext is false
});
```

**Drizzle**

```ts  
import { asc, gt } from 'drizzle-orm';

// fetch one extra row to detect the next page
const firstRows = await db
	.select()
	.from(users)
	.orderBy(asc(users.id))
	.limit(3);
const first = firstRows.slice(0, 2);
const nextCursor =
	firstRows.length > 2 ? { id: first[first.length - 1].id } : null;

const secondRows = await db
	.select()
	.from(users)
	.where(gt(users.id, nextCursor!.id))
	.orderBy(asc(users.id))
	.limit(3);
const second = secondRows.slice(0, 2);
// hasNext = secondRows.length > 2; hasPrevious needs one more probe query
```

### Navigating backwards

Pass `before` to move backwards. `cursor()` accepts `before` or `after`, never
both.



**better-drizzle**

```ts  
const {
	data: previous,
	pagination: { hasPrevious },
} = await client.users.cursor({
	limit: 2,
	orderBy: [{ id: 'asc' }],
	before: { id: 4 },
});
```

**Drizzle**

```ts  
import { desc, lt } from 'drizzle-orm';

// walk backwards: flip the order, fetch one extra row, then restore the order
const rows = await db
	.select()
	.from(users)
	.where(lt(users.id, 4))
	.orderBy(desc(users.id))
	.limit(3);
const previous = rows.slice(0, 2).reverse();
const hasPrevious = rows.length > 2;
```

`cursor()` page size comes from `limit`, then `take`, and defaults to `10`.
`after` and `before` take cursor objects, such as the `nextCursor` /
`previousCursor` from a previous page. Without `orderBy`, pages are ordered
by the full primary key ascending. With `orderBy`, missing primary-key columns
are appended as ascending tiebreakers so rows with the same sort value are not
skipped.

Cursor tokens include every ordered column, including the primary-key
tiebreakers. Pass the returned token unchanged and repeat the same `orderBy`
for each page. Older cursor tokens without tiebreakers are still accepted;
the next token contains the complete order.
Relation ordering works with offset pages.
Cursor tokens hold only the row's own column values, so `cursor()` throws
`INVALID_ARGS` when `orderBy` contains a
[relation key](/docs/querying/reads#ordering-by-relations). Use `paginate()` for
pages sorted by a relation.

## Pagination with projection

Both helpers accept the same `select` / `include` as a normal read:



**better-drizzle**

```ts  
const {
	data: page,
	pagination: { total },
} = await client.posts.paginate({
	limit: 10,
	orderBy: [{ id: 'desc' }],
	include: { author: true },
});

const {
	data: feed,
	pagination: { hasNext },
} = await client.posts.cursor({
	limit: 10,
	orderBy: [{ id: 'desc' }],
	include: { author: true },
});
```

**Drizzle**

```ts  
import { count, desc, eq, getColumns } from 'drizzle-orm';

const postsWithAuthor = () =>
	db
		.select({ ...getColumns(posts), author: getColumns(users) })
		.from(posts)
		.leftJoin(users, eq(posts.authorId, users.id))
		.orderBy(desc(posts.id));

const [page, [{ total }]] = await Promise.all([
	postsWithAuthor().limit(10),
	db.select({ total: count() }).from(posts),
]);
// ...then build the offset pagination metadata by hand

// fetch one extra row to detect the next page
const rows = await postsWithAuthor().limit(11);
const feed = rows.slice(0, 10);
const hasNext = rows.length > 10;
```

## Choosing offset vs cursor

| Strategy | Good for                                 | Trade-off                                                     |
| -------- | ---------------------------------------- | ------------------------------------------------------------- |
| Offset   | admin tables, reporting, simple lists    | easy to reason about; weaker on very large, changing datasets |
| Cursor   | feeds, timelines, large mutable datasets | needs stable ordering and cursor discipline                   |