Pagination

withPagination turns a Drizzle select into a paged list route that returns { edges, pageInfo }. It pages with cursors by default. Cursors keep page boundaries stable even while records are created or deleted, and they cost the same whether you are on page 2 or page 20,000.

When a list needs numbered pages, the 1, 2, 3 … 38 kind that admin tables show, the same route also accepts ?page=. You do not have to choose up front, because one route serves both. In this guide the site-notes plugin gets a paged notes route and a page that steps through it.

Before you beginCopy Anchor Link

• The site-notes plugin with its site_notes table, notes module and zodNote schema from the beginner path up to Database. Any table with a unique integer, bigint or UUID id works the same way.

Quick startCopy Anchor Link

import { z } from '@hono/zod-openapi'
import {
  withPagination,
  zodPaginationPageInfo,
  zodPaginationQuery,
} from '@vitnode/core/api/lib/with-pagination'

import { buildRoute } from '@/api/builders'
import { site_notes } from '@/database/notes'

import { zodNote } from './notes.schema'

export const pagedNotesRoute = buildRoute({
  route: {
    method: 'get',
    path: '/paged',
    request: { query: zodPaginationQuery },
    responses: {
      200: {
        content: {
          'application/json': {
            schema: z.object({
              edges: z.array(zodNote),
              pageInfo: zodPaginationPageInfo,
            }),
          },
        },
        description: 'One page of notes, newest first.',
      },
    },
  },
  handler: async c => {
    const data = await withPagination({
      c,
      params: { query: c.req.valid('query') },
      primaryCursor: site_notes.id,
      table: site_notes,
      orderBy: { column: site_notes.createdAt, order: 'desc' },
      query: async ({ cursorSelection, limit, offset, where, orderBy }) =>
        await c
          .get('db')
          .select({
            ...cursorSelection,
            id: site_notes.id,
            title: site_notes.title,
            body: site_notes.body,
          })
          .from(site_notes)
          .where(where)
          .orderBy(orderBy)
          .limit(limit)
          .offset(offset),
    })

    return c.json(data, 200)
  },
})

Cursors or numbered pages?Copy Anchor Link

Rule of thumb: numbered pages for admin tables, where people want page 7 and the lists are short; cursors for public feeds and infinite scroll, where depth is unbounded and nobody counts pages.

Index the column you order byCopy Anchor Link

Both modes need an index on the ordered tuple, and neither is fast without one:

import { camelCase, index } from 'drizzle-orm/pg-core'

export const site_notes = camelCase.table.withRLS(
  'site_notes',
  t => ({
    // columns unchanged
  }),
  t => [index('site_notes_created_at_id_idx').on(t.createdAt, t.id)], 
)

Generate and apply a migration afterwards, as in Database.

withPagination emits the cursor predicate as a row comparison, (createdAt, id) < ($1, $2), so Postgres can seek straight to the boundary on that index. Without the index, the same query scans and sorts the whole table on every page.

withPagination optionsCopy Anchor Link

Integer, bigint and UUID identifiersCopy Anchor Link

primaryCursor accepts integer, bigint and UUID columns. Bigints travel as decimal strings inside the opaque cursor, so values above JavaScript's safe-integer limit keep their precision. UUIDs stay strings and are validated before they reach PostgreSQL.

The order column and the identifier can use different types. A bigint order column can take a UUID primary key as its stable tiebreaker:

import { bigint, pgTable, uuid } from 'drizzle-orm/pg-core'

const events = pgTable('events', {
  id: uuid().defaultRandom().primaryKey(),
  sequence: bigint({ mode: 'bigint' }).notNull(),
})

await withPagination({
  // ...
  primaryCursor: events.id,
  orderBy: { column: events.sequence, order: 'asc' },
})

Keep the cursor opaque on the client and hand it back unchanged in the next request.

Response (pageInfo)Copy Anchor Link

{
  "edges": [
    { "id": 12, "title": "Welcome", "body": "Our first note." }
  ],
  "pageInfo": {
    "totalCount": 120,
    "totalPages": 12,
    "currentPage": 1,
    "pageSize": 10,
    "count": 10,
    "hasNextPage": true,
    "hasPreviousPage": false,
    "startCursor": "eyJjIjoiY3Jl...",
    "endCursor": "eyJjIjoiY3Jl..."
  }
}

Step through pages in the browserCopy Anchor Link

import type { PluginRoutePageProps } from '@vitnode/core/routing'

import { definePluginRoute } from '@vitnode/core/routing'
import { fetcher } from '@vitnode/core/tanstack/fetcher'

import { manifest } from '@/manifest.gen'

interface BrowseSearch {
  cursor?: string
}

const loadPage = async (search: BrowseSearch) => {
  const response = await fetcher({
    plugin: manifest.id,
    method: 'get',
    module: 'notes',
    path: '/paged',
    args: { query: { first: '10', cursor: search.cursor } },
  })
  if (!response.ok) throw new Error('Could not load notes.')

  return await response.json()
}

type BrowseData = Awaited<ReturnType<typeof loadPage>>

export const route = definePluginRoute<BrowseData, BrowseSearch>({
  parseSearch: input => {
    const { cursor } = input as Record<string, unknown>

    return { cursor: typeof cursor === 'string' ? cursor : undefined }
  },
  load: async ({ search }) => await loadPage(search),
})

const BrowsePage = ({
  loaderData,
  navigate,
  search,
}: PluginRoutePageProps<BrowseData, BrowseSearch>) => (
  <main className="container mx-auto flex max-w-2xl flex-col gap-4 p-4">
    <ul className="flex flex-col gap-2">
      {loaderData.edges.map(note => (
        <li key={note.id}>{note.title}</li>
      ))}
    </ul>
    <button
      disabled={!loaderData.pageInfo.hasNextPage}
      onClick={() =>
        void navigate({
          resetScroll: false,
          search: { ...search, cursor: loaderData.pageInfo.endCursor ?? undefined }, 
        })
      }
      type="button"
    >
      Next page
    </button>
  </main>
)

export default BrowsePage

page('/site-notes/browse', {
  component: lazy(() => import('./pages/browse-page')),
}),

Check the resultCopy Anchor Link

Open /site-notes/browse. The page lists up to 10 notes, newest first. With more than 10 notes, Next page adds ?cursor=... to the URL and shows the next 10; on the last page the button is disabled.

Search and custom filtersCopy Anchor Link

withPagination filters with search (text columns matched case-insensitively against ?search=) and where (any extra condition). Neither is in zodPaginationQuery, so extend the query schema with the parameters your route accepts:

import { gte } from 'drizzle-orm'

// in route.request
query: zodPaginationQuery.extend({
  search: z.string().max(100).optional(), 
  since: z.iso.datetime().optional(), 
}),

// in the handler
const query = c.req.valid('query')
const data = await withPagination({
  c,
  params: { query },
  table: site_notes,
  primaryCursor: site_notes.id,
  orderBy: { column: site_notes.createdAt, order: 'desc' },
  search: [site_notes.title, site_notes.body], 
  where: query.since ? gte(site_notes.createdAt, new Date(query.since)) : undefined, 
  query: async ({ cursorSelection, limit, offset, where, orderBy }) =>
    await c
      .get('db')
      .select({
        ...cursorSelection,
        id: site_notes.id,
        title: site_notes.title,
        body: site_notes.body,
      })
      .from(site_notes)
      .where(where)
      .orderBy(orderBy)
      .limit(limit)
      .offset(offset),
})

GET /api/site-notes/notes/paged?search=welcome now returns only notes whose title or body contains "welcome". totalCount counts the filtered rows.

Best practicesCopy Anchor Link

RelatedCopy Anchor Link