Fully Typed Drizzle ORM Dynamic Pagination Query

drizzle-paginate.md

Pagination Helper

This is simple helper for dynamic pagination using drizzle-orm using basic limit offset. The result is fully typed.

import { db } from "../yourdbinstance";
import { sql } from "drizzle-orm";
import type { PgSelect } from "drizzle-orm/pg-core";

interface PaginateParam {
  limit: number;
  page: number;
}
export async function paginateQuery<T extends PgSelect>(
  query: T,
  { limit, page }: PaginateParam,
) {
  const subQuery = query.as("sub");
  const rowCountQuery = db.select({ total: sql<number>`count(*)` }).from(
    subQuery,
  );

  const rowCountResult = await rowCountQuery.execute();
  const rowCount = Number(rowCountResult[0].total);
  const pageCount = Math.ceil(rowCount / limit);

  query.limit(limit).offset((page - 1) * limit);

  const data = (await query.execute()) as Awaited<ReturnType<T["execute"]>>;

  return {
    data,
    pagination: {
      rowCount,
      pageCount,
      page,
      limit,
    },
  };
}

Example Usage

Just pass the dynamic query into the pagination helper. Example:

    const query = db.select({
      id: users.id,
      username: users.username,
      email: users.email,
      created_at: users.created_at,
      full_name: sql`CONCAT(users.first_name, ' ', users.last_name)`.as(
        "full_name",
      ),
    })
      .from(users)
      .leftJoin(roles, eq(users.role_id, roles.id))
      .where(...)
      .orderBy(...);

    const res = await paginateQuery(query.$dynamic(), { limit, page });