Drizzle ORM Adapter

If your app already uses Drizzle ORM, you don't have to implement count() or fetch() yourself—or pass an adapter option into paginate(). The official package, @evensurface/paginate-drizzle, connects Drizzle's query builders to the pagination core.

It supports PostgreSQL, MySQL, and SQLite query builders, including partial selects and joins. The examples below use PostgreSQL syntax; the pagination ideas are the same in other dialects.

1. Install

pnpm add @evensurface/paginate @evensurface/paginate-drizzle drizzle-orm

You still need your project's own Drizzle database connection and database driver.

In the examples below, assume db is your connected Drizzle database and users is a table containing:

// Example PostgreSQL table definition (schema.ts)
import { boolean, pgTable, serial, text, timestamp } from "drizzle-orm/pg-core"

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  active: boolean("active").notNull(),
  createdAt: timestamp("created_at", { mode: "date" }).notNull(),
})

The examples use import { db } from "./db" and import { users } from "./schema". Replace those imports with paths in your own project. db is your database connection; it is not exported by Paginate.

Page pagination

Use this when you need numbered pages and a total count. This is the same API from the Quick Start, with an additional filter and more sort fields.

import { asc, desc, eq } from "drizzle-orm"
import { paginate } from "@evensurface/paginate-drizzle"
import { db } from "./db"
import { users } from "./schema"

const where = eq(users.active, true)

const result = await paginate({
  query: () =>
    db
      .select({
        id: users.id,
        name: users.name,
        createdAt: users.createdAt,
      })
      .from(users)
      .where(where)
      .orderBy(desc(users.createdAt), asc(users.id)),
  countQuery: db.$count(users, where),
  page: 2,
  limit: 20,
})

What each input means

The full options type is DrizzlePaginationOptions<TItem>.

Why is query a function? Drizzle query builders can be mutated by .limit() and .offset(). By calling the function only when fetching a page, the adapter gets a fresh builder each time. Don't create a builder once and return the same builder repeatedly: the adapter detects reuse and throws DRIZZLE_QUERY_REUSED.

Why is countQuery separate? Paginate needs to know the total number of matching users to calculate the last page. db.$count(users, where) counts the users matching the same where filter. In this example, the adapter gets the count before creating the select query.

What comes back

If there are 95 matching users:

{
  items: [/* User rows selected above */],
  pagination: {
    page: 2,
    limit: 20,
    offset: 20,
    total: 95,
    totalPages: 5,
    hasNextPage: true,
    hasPreviousPage: true,
    firstItem: 21,
    lastItem: 40,
  },
}

The items type is inferred from .select({ ... }). In the example, it is an array of { id: number; name: string; createdAt: Date }. The result type is PaginatedResult<TItem> and its metadata is PaginationMetadata.

Another way to count

If your query uses a more complex count expression, the adapter also accepts a Drizzle select({ total: count() }) query:

import { count } from "drizzle-orm"

const countQuery = db
  .select({ total: count() })
  .from(users)
  .where(where)

The result must be exactly one row containing a non-negative safe-integer total. Direct db.$count() values must follow the same rule. Empty, grouped multi-row, invalid, negative, or fractional counts raise a coded Drizzle pagination error.

Offset pagination

Use paginateOffset() when the client supplies an arbitrary zero-based offset:

import { paginateOffset } from "@evensurface/paginate-drizzle"

const offsetResult = await paginateOffset({
  query: () =>
    db.select().from(users).where(where).orderBy(asc(users.id)),
  countQuery: db.$count(users, where),
  offset: 40,
  limit: 20,
})

Here the adapter counts users, then skips 40 and fetches up to 20. The output has items plus an OffsetPaginationMetadata object containing total, limit, offset, hasNextPage, and hasPreviousPage. There is no page number in this output.

The full input type is DrizzleOffsetPaginationOptions<TItem>. You can use the same query factory and count query as page mode.

Cursor pagination

Use paginateCursor() for forward-only feeds. It runs a real keyset query, without countQuery or a numeric offset.

import { and, eq } from "drizzle-orm"
import { paginateCursor } from "@evensurface/paginate-drizzle"

const cursorResult = await paginateCursor({
  query: ({ cursorWhere, orderBy }) =>
    db
      .select({
        id: users.id,
        name: users.name,
        createdAt: users.createdAt,
      })
      .from(users)
      // Combine your own access/filter conditions with the cursor condition.
      .where(and(eq(users.active, true), cursorWhere))
      // Use the order expressions supplied by the adapter.
      .orderBy(...orderBy),
  orderBy: [
    {
      key: "createdAt",
      column: users.createdAt,
      direction: "desc",
      getValue: (row) => row.createdAt,
    },
    {
      key: "id",
      column: users.id,
      direction: "desc",
      getValue: (row) => row.id,
    },
  ],
  limit: 20,
  // cursor: previousResult.pagination.nextCursor ?? undefined,
})

What the special cursor fields do

The full options type is DrizzleCursorPaginationOptions<TItem>.

cursorWhere is a Drizzle SQL condition the adapter generates to mean “rows after the last cursor position.” It is undefined on the first request. You must combine it with your regular filters using and(...) as shown above; otherwise you may return records from the wrong tenant or filter.

orderBy in the callback is an array of Drizzle SQL order expressions. Apply it using .orderBy(...orderBy) so the SQL ordering matches the cursor position.

orderBy in the input is the configuration that creates those expressions. Each getValue(row) extracts the value for the corresponding column from a selected result. The final order key should make the combination unique (usually the ID). All order columns must be non-null.

With the two descending keys above, the adapter builds a continuation condition equivalent to:

created_at < :cursor_created_at
OR (created_at = :cursor_created_at AND id < :cursor_id)

This is an illustration of the logical SQL condition, not an exact Drizzle-generated query. For mixed ascending and descending directions, the adapter chooses the correct > or < comparison for each column.

Cursor output and next request

{
  items: [/* up to 20 inferred rows */],
  pagination: {
    limit: 20,
    nextCursor: "<generated cursor or null>",
    hasNextPage: true,
  },
}

The result is CursorPaginatedResult<TItem> with CursorPaginationMetadata. Pass the string in nextCursor as the next request's cursor. When there are no more rows, the cursor is null and hasNextPage is false.

Cursor columns can represent strings, finite numbers, bigints, or dates. Custom Drizzle columns may require an explicit valueType; see DrizzleCursorOrderEntry<TItem>.

Important behavior

• Query reuse protection: page, offset, and cursor query factories must produce fresh select builders.
• Type inference: partial selects and joins retain the selected row type. Ensure count queries match the rows being paginated; one-to-many joins can otherwise count different entities than the fetched rows.
• Sorting: add a unique tie-breaker; create a suitable index for your filters and ordering, especially for cursor pagination.
• Consistency: page and offset execute separate count and fetch statements. Use your application's transaction/snapshot if you need them to see the same data.
• Permissions: always include user and tenant filters in each query. Cursor strings are not signed and cannot prove authorization.

For exported functions and exact types, see Drizzle API, Drizzle Types, and Errors.