How to Implement Server-Side Pagination with Drizzle ORM

Server-side pagination keeps page loads fast by fetching only the data needed for the current page. Here are three approaches.

Approach 1: Offset Pagination

The simplest and most common approach.

// lib/pagination.ts
import { db } from "@/db";
import { products } from "@/db/schema";
import { desc, sql, like, and } from "drizzle-orm";

interface PaginationParams {
  page: number;
  limit: number;
  search?: string;
}

export async function getProductsPaginated({ page, limit, search }: PaginationParams) {
  const offset = (page - 1) * limit;

  const conditions = [];
  if (search) {
    conditions.push(like(products.name, `%${search}%`));
  }

  const where = conditions.length > 0 ? and(...conditions) : undefined;

  const [items, countResult] = await Promise.all([
    db
      .select()
      .from(products)
      .where(where)
      .orderBy(desc(products.createdAt))
      .limit(limit)
      .offset(offset),
    db
      .select({ count: sql<number>`count(*)` })
      .from(products)
      .where(where),
  ]);

  const total = countResult[0].count;
  const totalPages = Math.ceil(total / limit);

  return {
    items,
    pagination: {
      page,
      limit,
      total,
      totalPages,
      hasNext: page < totalPages,
      hasPrev: page > 1,
    },
  };
}

Server Component Page

// app/products/page.tsx
import { getProductsPaginated } from "@/lib/pagination";
import { Pagination } from "@/components/Pagination";

interface SearchParams {
  page?: string;
  search?: string;
}

export default async function ProductsPage({
  searchParams,
}: {
  searchParams: Promise<SearchParams>;
}) {
  const params = await searchParams;
  const page = Math.max(1, Number(params.page ?? 1));
  const search = params.search ?? undefined;

  const { items, pagination } = await getProductsPaginated({
    page,
    limit: 20,
    search,
  });

  return (
    <div className="container py-10">
      <h1 className="mb-6 text-2xl font-bold">Products</h1>

      {/* Search */}
      <form className="mb-6">
        <input
          name="search"
          type="text"
          defaultValue={search}
          placeholder="Search products..."
          className="w-full max-w-sm rounded-lg border px-3 py-2"
        />
      </form>

      {/* Product grid */}
      <div className="grid gap-4 sm:grid-cols-2 lg:grid-cols-4">
        {items.map((product) => (
          <div key={product.id} className="rounded-lg border p-4">
            <h2 className="font-medium">{product.name}</h2>
            <p className="mt-1 text-sm text-gray-600">${product.price}</p>
          </div>
        ))}
      </div>

      {items.length === 0 && (
        <p className="text-gray-500">No products found.</p>
      )}

      {/* Pagination */}
      <Pagination
        currentPage={pagination.page}
        totalPages={pagination.totalPages}
        total={pagination.total}
        baseUrl="/products"
        searchParams={params}
      />
    </div>
  );
}

Pagination Component

// components/Pagination.tsx
import Link from "next/link";

interface PaginationProps {
  currentPage: number;
  totalPages: number;
  total: number;
  baseUrl: string;
  searchParams?: Record<string, string | undefined>;
}

export function Pagination({
  currentPage,
  totalPages,
  total,
  baseUrl,
  searchParams = {},
}: PaginationProps) {
  if (totalPages <= 1) return null;

  function buildUrl(page: number) {
    const params = new URLSearchParams();
    Object.entries(searchParams).forEach(([key, value]) => {
      if (value && key !== "page") params.set(key, value);
    });
    params.set("page", page.toString());
    return `${baseUrl}?${params}`;
  }

  // Generate page numbers with ellipsis
  function getPageNumbers(): (number | "...")[] {
    const pages: (number | "...")[] = [];
    const delta = 2;

    for (let i = 1; i <= totalPages; i++) {
      if (
        i === 1 ||
        i === totalPages ||
        (i >= currentPage - delta && i <= currentPage + delta)
      ) {
        pages.push(i);
      } else if (pages[pages.length - 1] !== "...") {
        pages.push("...");
      }
    }

    return pages;
  }

  return (
    <div className="mt-8 flex items-center justify-between">
      <p className="text-sm text-gray-600">
        Showing {(currentPage - 1) * 20 + 1}-{Math.min(currentPage * 20, total)} of {total}
      </p>

      <nav className="flex items-center gap-1" aria-label="Pagination">
        {currentPage > 1 && (
          <Link
            href={buildUrl(currentPage - 1)}
            className="rounded-lg border px-3 py-2 text-sm hover:bg-gray-50"
          >
            Previous
          </Link>
        )}

        {getPageNumbers().map((page, i) =>
          page === "..." ? (
            <span key={`ellipsis-${i}`} className="px-2 text-gray-400">
              ...
            </span>
          ) : (
            <Link
              key={page}
              href={buildUrl(page)}
              className={`rounded-lg px-3 py-2 text-sm ${
                page === currentPage
                  ? "bg-blue-600 text-white"
                  : "border hover:bg-gray-50"
              }`}
            >
              {page}
            </Link>
          )
        )}

        {currentPage < totalPages && (
          <Link
            href={buildUrl(currentPage + 1)}
            className="rounded-lg border px-3 py-2 text-sm hover:bg-gray-50"
          >
            Next
          </Link>
        )}
      </nav>
    </div>
  );
}

Approach 2: Cursor Pagination

Better for large datasets and infinite scroll.

// lib/cursor-pagination.ts
import { db } from "@/db";
import { products } from "@/db/schema";
import { desc, lt, and, like } from "drizzle-orm";

interface CursorParams {
  cursor?: string; // ISO date string of last item's createdAt
  limit: number;
  search?: string;
}

export async function getProductsCursor({ cursor, limit, search }: CursorParams) {
  const conditions = [];

  if (cursor) {
    conditions.push(lt(products.createdAt, new Date(cursor)));
  }
  if (search) {
    conditions.push(like(products.name, `%${search}%`));
  }

  const items = await db
    .select()
    .from(products)
    .where(conditions.length > 0 ? and(...conditions) : undefined)
    .orderBy(desc(products.createdAt))
    .limit(limit + 1); // Fetch one extra to check if there are more

  const hasMore = items.length > limit;
  const data = hasMore ? items.slice(0, limit) : items;
  const nextCursor = hasMore
    ? data[data.length - 1].createdAt.toISOString()
    : null;

  return {
    items: data,
    nextCursor,
    hasMore,
  };
}

API Route for Cursor Pagination

// app/api/products/route.ts
import { NextRequest, NextResponse } from "next/server";
import { getProductsCursor } from "@/lib/cursor-pagination";

export async function GET(request: NextRequest) {
  const { searchParams } = new URL(request.url);
  const cursor = searchParams.get("cursor") ?? undefined;
  const search = searchParams.get("search") ?? undefined;
  const limit = Math.min(50, Math.max(1, Number(searchParams.get("limit") ?? 20)));

  const result = await getProductsCursor({ cursor, limit, search });

  return NextResponse.json(result);
}

Approach 3: Keyset Pagination

Most efficient for sorted datasets with unique ordering.

// lib/keyset-pagination.ts
import { db } from "@/db";
import { products } from "@/db/schema";
import { and, or, lt, eq, desc } from "drizzle-orm";

interface KeysetParams {
  afterId?: string;
  afterDate?: string;
  limit: number;
}

export async function getProductsKeyset({ afterId, afterDate, limit }: KeysetParams) {
  let where;

  if (afterDate && afterId) {
    // Combined key: (createdAt, id) for deterministic ordering
    where = or(
      lt(products.createdAt, new Date(afterDate)),
      and(
        eq(products.createdAt, new Date(afterDate)),
        lt(products.id, afterId)
      )
    );
  }

  const items = await db
    .select()
    .from(products)
    .where(where)
    .orderBy(desc(products.createdAt), desc(products.id))
    .limit(limit + 1);

  const hasMore = items.length > limit;
  const data = hasMore ? items.slice(0, limit) : items;
  const lastItem = data[data.length - 1];

  return {
    items: data,
    nextCursor: hasMore && lastItem
      ? { afterId: lastItem.id, afterDate: lastItem.createdAt.toISOString() }
      : null,
    hasMore,
  };
}

When to Use Each Approach

Need High-Performance Data Systems?

We build data-intensive applications with optimized queries and pagination strategies. Contact us to discuss your project.