Pagination in Next.js App Router with Drizzle ORM -- offset pagination for SaaS tables

Every SaaS has tables. Tables need pagination. Here is the pattern this boilerplate uses -- repository query with offset/limit, a thin API route, URL-driven server component, and a Pagination component that stays out of your way.

The repository layer

Add findMany to your repository with page and limit params:

// modules/post/post.repo.ts
import { db } from '@/db/drizzle';
import { postTable } from '@/db/schema';
import { desc, count, eq } from 'drizzle-orm';
import type { PostFilter, Post } from './post.types';
 
const DEFAULT_LIMIT = 20;
 
export const postRepo = {
  async findMany(filter: PostFilter = {}, page = 1, limit = DEFAULT_LIMIT) {
    const offset = (page - 1) * limit;
    const where = filter.authorId
      ? eq(postTable.authorId, filter.authorId)
      : undefined;
 
    const [rows, [{ total }]] = await Promise.all([
      db
        .select()
        .from(postTable)
        .where(where)
        .orderBy(desc(postTable.createdAt))
        .limit(limit)
        .offset(offset),
      db.select({ total: count() }).from(postTable).where(where),
    ]);
 
    return { rows, total: Number(total) };
  },
};

Running the count query in parallel with Promise.all avoids two sequential round-trips to Neon DB.

The shared type

Put a PaginatedResult type in types/pagination.ts so every paginated endpoint shares the same shape:

// types/pagination.ts
export interface PaginatedResult<T> {
  data: T[];
  total: number;
  page: number;
  limit: number;
  totalPages: number;
}

The service layer

// modules/post/post.service.ts
import { postRepo } from './post.repo';
import type { PaginatedResult } from '@/types/pagination';
import type { Post, PostFilter } from './post.types';
 
export const postService = {
  async getPosts(
    filter: PostFilter,
    page: number,
    limit: number
  ): Promise<PaginatedResult<Post>> {
    const { rows, total } = await postRepo.findMany(filter, page, limit);
    return {
      data: rows,
      total,
      page,
      limit,
      totalPages: Math.ceil(total / limit),
    };
  },
};

The API route

// app/api/posts/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { postService } from '@/modules/post';
import { handleError } from '@/lib/errors';
 
export async function GET(req: NextRequest) {
  try {
    const { searchParams } = req.nextUrl;
    const page = Math.max(1, Number(searchParams.get('page') ?? '1'));
    const limit = Math.min(
      100,
      Math.max(1, Number(searchParams.get('limit') ?? '20'))
    );
 
    const result = await postService.getPosts({}, page, limit);
    return NextResponse.json(result);
  } catch (error: unknown) {
    return handleError(error);
  }
}

Cap limit at 100 so a caller cannot request unlimited rows in a single shot.

URL-driven server component

Pagination state lives in the URL so the browser back button works and links are shareable:

// app/(main)/blog/page.tsx
import { postService } from '@/modules/post';
import { PostList } from '@/components/blog/PostList';
import { Pagination } from '@/components/ui/Pagination';
 
interface Props {
  searchParams: Promise<{ page?: string }>;
}
 
export default async function BlogPage({ searchParams }: Props) {
  const { page: pageParam } = await searchParams;
  const page = Math.max(1, Number(pageParam ?? '1'));
 
  const result = await postService.getPosts({}, page, 20);
 
  return (
    <div className="space-y-8">
      <PostList posts={result.data} />
      <Pagination
        page={result.page}
        totalPages={result.totalPages}
        basePath="/blog"
      />
    </div>
  );
}

Call the service directly in server components -- no API round-trip needed when the data fetch happens on the server.

The Pagination component

// components/ui/Pagination.tsx
import Link from 'next/link';
import { Button } from '@/components/ui/button';
 
interface Props {
  page: number;
  totalPages: number;
  basePath: string;
}
 
export function Pagination({ page, totalPages, basePath }: Props) {
  if (totalPages <= 1) return null;
 
  const prev = page > 1 ? `${basePath}?page=${page - 1}` : null;
  const next = page < totalPages ? `${basePath}?page=${page + 1}` : null;
 
  return (
    <div className="flex items-center justify-center gap-4">
      {prev ? (
        <Button variant="outline" asChild>
          <Link href={prev}>Previous</Link>
        </Button>
      ) : (
        <Button variant="outline" disabled>Previous</Button>
      )}
 
      <span className="text-sm text-muted-foreground">
        Page {page} of {totalPages}
      </span>
 
      {next ? (
        <Button variant="outline" asChild>
          <Link href={next}>Next</Link>
        </Button>
      ) : (
        <Button variant="outline" disabled>Next</Button>
      )}
    </div>
  );
}

asChild on Button lets Next.js Link handle prefetching while keeping shadcn button styles intact.

When to use cursor-based instead

Offset pagination works well for admin tables, blog listings, and any UI where users jump to a specific page number. Switch to cursor-based pagination when:

• The table grows fast and users page forward continuously (feeds, activity logs)
• You need stable results under concurrent inserts -- offset rows shift as new rows land
• The count() query is expensive and you can drop the total entirely

For cursor-based, store the last row's id or createdAt as cursor in the URL and use .where(gt(postTable.id, cursor)) instead of .offset().

Takeaway

The pattern is: parallel count + rows in the repo, a shared PaginatedResult<T> type, URL search params driving page state in the server component, and a dumb Pagination component that only needs page, totalPages, and basePath. Add it to one module and reuse the component everywhere -- no extra state management required.