Skip to content

Pagination ​

Pagination splits a query's results into pages. Holo has three paginators — pick one based on what your UI needs to show. They work the same on the query builder and on models.

ts
import Post from '../models/Post'

const page = await Post
  .where('status', 'published')
  .latest('published_at')
  .paginate(15, 2)

page.data         // the 15 posts on page 2
page.meta.total   // total number of published posts
page.meta.lastPage

Choosing a paginator ​

MethodGives youCostBest for
paginate(perPage, page)total count, last page, page numbersone extra COUNT querynumbered page links
simplePaginate(perPage, page)"is there a next page?"no count queryPrevious / Next buttons
cursorPaginate(perPage, cursor)opaque next / previous tokensno count, no OFFSETinfinite scroll, feeds, large tables

All three default to 15 rows per page. Any limit() or offset() already on the query is replaced by the paginator's own.

Paginate in a route ​

Read the page number from the query string and return the paginator as JSON. It serializes to { data, meta }.

ts
// app/api/posts/route.ts
import Post from '@/server/models/Post'

export async function GET(request: Request) {
  const url = new URL(request.url)
  const page = Number.parseInt(url.searchParams.get('page') ?? '1', 10) || 1

  const posts = await Post
    .where('status', 'published')
    .latest('published_at')
    .paginate(15, Math.max(1, page))

  return Response.json(posts)
}
ts
// server/api/posts.get.ts
import Post from '../models/Post'

export default defineEventHandler(async (event) => {
  const page = Number.parseInt(String(getQuery(event).page ?? '1'), 10) || 1

  return await Post
    .where('status', 'published')
    .latest('published_at')
    .paginate(15, Math.max(1, page))
})
ts
// src/routes/api/posts/+server.ts

import type { RequestHandler } from './$types'
import Post from '../../../../server/models/Post'

export const GET: RequestHandler = async ({ url }) => {
  const page = Number.parseInt(url.searchParams.get('page') ?? '1', 10) || 1

  const posts = await Post
    .where('status', 'published')
    .latest('published_at')
    .paginate(15, Math.max(1, page))

  return Response.json(posts)
}

WARNING

perPage and page must be positive integers. Passing 0, a negative number, or NaN throws an error, so always parse and clamp values that come from the request.

Length-aware pagination ​

paginate() runs a COUNT query so it can tell you the total number of rows and pages.

ts
import { DB } from '@holo-js/db'

const page = await DB.table('orders')
  .where('status', 'paid')
  .orderBy('id')
  .paginate(25, 3)

The JSON looks like this:

json
{
  "data": [{ "id": 51, "total": 120 }, "..."],
  "meta": {
    "total": 240,
    "perPage": 25,
    "pageName": "page",
    "currentPage": 3,
    "lastPage": 10,
    "from": 51,
    "to": 75,
    "hasMorePages": true
  }
}

from and to are the positions of the first and last rows on the page, or null when the page is empty.

Simple pagination ​

simplePaginate() skips the count query. It fetches one extra row to find out whether there is a next page.

ts
const page = await DB.table('audit_logs')
  .latest()
  .simplePaginate(50, 1)
json
{
  "data": ["..."],
  "meta": {
    "perPage": 50,
    "pageName": "page",
    "currentPage": 1,
    "from": 1,
    "to": 50,
    "hasMorePages": true
  }
}

Cursor pagination ​

cursorPaginate() uses the values of the last row you saw to fetch the next page (WHERE id > ?) instead of OFFSET. It stays fast on large tables and doesn't skip or repeat rows when new rows are inserted.

Pass null (or nothing) for the first page, then pass back the cursor you received:

ts
const first = await DB.table('posts').orderBy('id').cursorPaginate(20)

const second = await DB.table('posts').orderBy('id').cursorPaginate(20, first.nextCursor)

The result has no meta key:

json
{
  "data": ["..."],
  "perPage": 20,
  "cursorName": "cursor",
  "nextCursor": "eyJ2YWx1ZXMiOlsyMF19",
  "prevCursor": null
}

nextCursor is null on the last page, and prevCursor is null on the first. Pass prevCursor to go back a page.

In a route, read the cursor from the query string:

ts
const cursor = url.searchParams.get('cursor')

const feed = await Post.where('status', 'published').latest().cursorPaginate(20, cursor)

Ordering rules ​

Cursor pagination needs a stable sort order:

  • Order by real columns. inRandomOrder() and unsafeOrderBy() aren't allowed.
  • If you don't call orderBy(), Holo orders by the primary key it knows from the model or table definition. When Holo has no primary-key metadata for the table, call orderBy() yourself.
  • When Holo knows the table's primary key, it adds it as a tie-breaker, so sorting by a non-unique column such as created_at is safe. Without that metadata, add a unique column yourself: .orderBy('created_at').orderBy('id').

Holo throws a SecurityError for random, raw, or missing ordering, and a CompilerError when it can't tell which column of a union to order by. Both end in a 500 response, and Holo checks the ordering before it reads the cursor. Holo can't tell whether your own columns are unique, though: without primary-key metadata, an ordering that isn't unique can skip rows that share a value at a page boundary.

Invalid cursors ​

The cursor comes from the client, so it can be garbled, truncated, or left over from a page that ordered by different columns. Holo checks it before it runs the query and throws an InvalidCursorException when:

  • it isn't canonical base64url-encoded UTF-8 JSON,
  • its values aren't a list of strings, finite numbers, booleans and null,
  • its previous flag is present but isn't a boolean, or
  • it holds a different number of values than the query orders by.

Keep the same ordering for every page. Holo only checks that a cursor has one value per ordered column, so a cursor issued by orderBy('id') is still accepted by orderBy('id', 'desc') and returns the wrong page.

Cursors aren't signed. Holo binds each cursor value as a query parameter, and turns a null value into IS NULL / IS NOT NULL checks, so an edited value can't inject SQL. It isn't checked against the column type either. A value the database can't compare with the column, such as text for an integer column on PostgreSQL, fails like any other query.

The Next, Nuxt and SvelteKit adapters handle an InvalidCursorException exactly like a failed validate(), with the error under the cursor parameter name. Where a failed validate() returns a 422 JSON response, so does an invalid cursor:

json
{
  "ok": false,
  "status": 422,
  "valid": false,
  "message": "The cursor is invalid.",
  "bag": "default",
  "values": {},
  "errors": { "cursor": ["The cursor is invalid."] }
}

Set your own message with the invalidCursorMessage option. It must contain a non-whitespace character, even on pages without a cursor, and defaults to 'The cursor is invalid.'. The error key follows cursorName, so cursorName can't contain __proto__, constructor or prototype as a dot-separated segment:

ts
const feed = await Post.latest().cursorPaginate(20, url.searchParams.get('after'), {
  cursorName: 'after',
  invalidCursorMessage: 'This page is no longer available. Reload the feed.',
})

To handle it yourself, catch the exception. It has statusCode (422), code ('INVALID_CURSOR'), cursorName, and errors:

ts
import { InvalidCursorException } from '@holo-js/db'

try {
  return await Post.latest().cursorPaginate(20, cursor)
} catch (error) {
  if (error instanceof InvalidCursorException) return await Post.latest().cursorPaginate(20)
  throw error
}

Paginating models ​

On a model query, data is a collection of model instances, so you can keep working with them before you return the page:

ts
const page = await Post.with('user').latest().paginate(10, 1)

for (const post of page.data) {
  console.log(post.get('title'))
}

When the page goes straight to the client, use the ...Json variants. They return plain serialized objects — with hidden attributes removed and loaded relations included:

ts
const page = await Post.with('user').latest().paginateJson(10, 1)
// { data: [{ id: 1, title: '...', user: { ... } }], meta: { ... } }
MethodReturns
paginateJson(perPage, page, options?){ data, meta }
simplePaginateJson(perPage, page, options?){ data, meta }
cursorPaginateJson(perPage, cursor, options?){ data, perPage, cursorName, nextCursor, prevCursor }

Several paginators on one page ​

The pageName and cursorName options label which query-string parameter a paginator belongs to. The name is included in the result so your UI can build the right links.

ts
const posts = await Post.latest().paginate(10, postsPage, { pageName: 'postsPage' })

const comments = await DB.table('comments')
  .orderBy('id')
  .cursorPaginate(20, commentsCursor, { cursorName: 'commentsCursor' })

Holo doesn't read the request for you — you still pass the page number or cursor yourself.

Paginator reference ​

Each paginator returns its rows in data, its position in meta (length-aware and simple paginators), or cursor fields at the top level (cursor paginator). The methods below are available on the result.

paginate() ​

Returns a page with a total count. Arguments: perPage (default 15), page (default 1), and options with pageName (default 'page').

ts
const page = await DB.table('posts').orderBy('id').paginate(15, 2, { pageName: 'p' })

simplePaginate() ​

Returns a page without a count query. Takes the same arguments as paginate().

ts
const page = await DB.table('posts').orderBy('id').simplePaginate(15, 2)

cursorPaginate() ​

Returns a page from a cursor. Arguments: perPage (default 15), cursor (default null), and options with cursorName (default 'cursor') and invalidCursorMessage, which must contain a non-whitespace character (default 'The cursor is invalid.'). An invalid cursor throws an InvalidCursorException.

ts
const page = await DB.table('posts').orderBy('id').cursorPaginate(15, null, { cursorName: 'after' })

Meta fields ​

paginate() returns these meta fields:

FieldMeaning
totalNumber of rows that match the query
perPageRows per page
pageNameQuery-string name of the page parameter
currentPageThe page you requested
lastPageLast page number (at least 1)
from / toPositions of the first and last rows on the page, or null
hasMorePagestrue when a later page exists

simplePaginate() returns the same fields except total and lastPage.

items() ​

Returns the rows on this page. It returns the same array as data.

ts
const posts = page.items()

firstItem() ​

Returns the position of the first row on the page, or null when the page is empty. It returns the same value as meta.from.

ts
console.log(`Showing from ${page.firstItem()}`)

lastItem() ​

Returns the position of the last row on the page, or null when the page is empty. It returns the same value as meta.to.

ts
console.log(`Showing to ${page.lastItem()}`)

hasPages() ​

Returns true when more than one page exists. On a simple paginator, it returns true when the current page is past the first or a next page exists.

ts
const showPageLinks = page.hasPages()

hasMorePages() ​

Returns true when a next page exists. On a cursor paginator, it checks whether nextCursor is set.

ts
const hasNext = feed.hasMorePages()

getPageName() ​

Returns the query-string name of the page parameter. Available on paginate() and simplePaginate() results.

ts
const param = page.getPageName() // 'page'

getCursorName() ​

Returns the query-string name of the cursor parameter. Available on cursorPaginate() results.

ts
const param = feed.getCursorName() // 'cursor'

nextCursorToken() ​

Returns the cursor for the next page, or null on the last page. Available on cursorPaginate() results.

ts
const next = feed.nextCursorToken()

previousCursorToken() ​

Returns the cursor for the previous page, or null on the first page. Available on cursorPaginate() results.

ts
const previous = feed.previousCursorToken()

toJSON() ​

Returns the serialized page: { data, meta } for length-aware and simple paginators, or { data, perPage, cursorName, nextCursor, prevCursor } for cursor paginators. Returning the paginator from a route uses this method.

ts
return Response.json(page.toJSON())

Next steps ​

Released under the MIT License.