Prisma Pagination — Offset and Cursor-Based Pagination

// Offset pagination
const page = 1;
const pageSize = 20;

const users = await prisma.user.findMany({
  skip: (page - 1) * pageSize,
  take: pageSize,
  orderBy: { createdAt: 'desc' },
});

// With total count
const [users, total] = await Promise.all([
  prisma.user.findMany({
    skip: (page - 1) * pageSize,
    take: pageSize,
    orderBy: { createdAt: 'desc' },
  }),
  prisma.user.count(),
]);

// Pagination metadata
const paginationMeta = {
  page,
  pageSize,
  total,
  totalPages: Math.ceil(total / pageSize),
  hasNextPage: page * pageSize < total,
  hasPreviousPage: page > 1,
};

# offset_pagination.py
# Offset-based pagination

def offset_pattern():
    print("Offset Pagination Pattern:")
    print()
    print("Request:")
    print("  GET /api/users?page=1&pageSize=20")
    print()
    print("Query:")
    print("  prisma.user.findMany({")
    print("    skip: (page - 1) * pageSize,")
    print("    take: pageSize,")
    print("    orderBy: { createdAt: 'desc' }")
    print("  })")
    print()
    print("Response metadata:")
    print("  {")
    print("    data: [...records],")
    print("    pagination: {")
    print("      page: 1,")
    print("      pageSize: 20,")
    print("      total: 150,")
    print("      totalPages: 8,")
    print("      hasNextPage: true,")
    print("      hasPreviousPage: false")
    print("    }")
    print("  }")

offset_pattern()

// Cursor-based pagination (first page)
const firstPage = await prisma.user.findMany({
  take: 21, // Fetch one extra to check hasNextPage
  orderBy: { id: 'asc' },
});

const users = firstPage.slice(0, 20);
const hasNextPage = firstPage.length > 20;
const nextCursor = hasNextPage ? users[users.length - 1].id : null;

// Cursor-based pagination (subsequent pages)
const nextPage = await prisma.user.findMany({
  take: 21,
  skip: 1,        // Skip the cursor record itself
  cursor: { id: nextCursor },
  orderBy: { id: 'asc' },
});

// Using a compound cursor (e.g., sort by createdAt)
const page = await prisma.file.findMany({
  take: 20,
  orderBy: [
    { createdAt: 'desc' },
    { id: 'asc' },  // Tiebreaker for same timestamps
  ],
  cursor: lastRecord 
    ? { id: lastRecord.id } 
    : undefined,
  skip: lastRecord ? 1 : 0,
});

# cursor_pagination.py
# Cursor-based pagination

def cursor_pattern():
    print("Cursor-Based Pagination:")
    print()
    print("Benefits over offset:")
    print("  - Consistent results (no duplicates/misses)")
    print("  - Fast on large datasets (no OFFSET)")
    print("  - Works well with real-time data")
    print()
    print("How it works:")
    print("  1. First page: findMany({ take: pageSize + 1 })")
    print("  2. Get cursor from last record's ID")
    print("  3. Next page: findMany({ cursor: { id }, skip: 1, take: pageSize + 1 })")
    print()
    print("Response format:")
    print("  {")
    print("    data: [...records],")
    print("    nextCursor: 'abc123',")
    print("    hasNextPage: true")
    print("  }")

cursor_pattern()

// pagination.service.ts
interface PaginatedResult<T> {
  data: T[];
  meta: {
    total?: number;
    page?: number;
    pageSize?: number;
    totalPages?: number;
    hasNextPage: boolean;
    hasPreviousPage?: boolean;
    nextCursor?: string;
  };
}

async function paginateOffset<T>(
  model: any,
  args: any,
  page: number,
  pageSize: number
): Promise<PaginatedResult<T>> {
  const skip = (page - 1) * pageSize;
  
  const [data, total] = await Promise.all([
    model.findMany({ ...args, skip, take: pageSize }),
    model.count({ where: args.where }),
  ]);
  
  return {
    data,
    meta: {
      total,
      page,
      pageSize,
      totalPages: Math.ceil(total / pageSize),
      hasNextPage: page * pageSize < total,
      hasPreviousPage: page > 1,
    },
  };
}

async function paginateCursor<T>(
  model: any,
  args: any,
  cursor?: string,
  pageSize: number = 20
): Promise<PaginatedResult<T>> {
  const results = await model.findMany({
    ...args,
    take: pageSize + 1,
    ...(cursor ? { cursor: { id: cursor }, skip: 1 } : {}),
  });
  
  const hasNextPage = results.length > pageSize;
  const data = results.slice(0, pageSize);
  const nextCursor = hasNextPage ? data[data.length - 1].id : null;
  
  return { data, meta: { hasNextPage, nextCursor } };
}

# pagination_service.py
# Pagination service patterns

def service_patterns():
    print("Pagination Service Comparison:")
    print()
    print("Offset Pagination:")
    print("  + Simple, intuitive (page numbers)")
    print("  + Supports total count")
    print("  + Can jump to any page")
    print("  - Slow for large datasets")
    print("  - Inconsistent if data changes")
    print()
    print("Cursor Pagination:")
    print("  + Fast on any dataset size")
    print("  + Consistent results")
    print("  + Good for infinite scroll")
    print("  - No page number support")
    print("  - Cannot jump to specific page")
    print()
    print("Choose based on:")
    print("  - Search results: Offset (page numbers expected)")
    print("  - Feeds/timelines: Cursor (infinite scroll)")

service_patterns()

def pagination_module():
    print("Pagination Module Design:")
    print()
    print("PaginationStrategy (interface)")
    print("  offset: { skip, take }")
    print("  cursor: { cursor, take, skip }")
    print()
    print("PaginationResult<T>")
    print("  data: T[]")
    print("  meta: {")
    print("    total?: number")
    print("    page?: number")
    print("    hasNextPage: boolean")
    print("    nextCursor?: string")
    print("  }")
    print()
    print("Usage:")
    print("  paginate(model, args, { type: 'offset', page: 1, pageSize: 20 })")
    print("  paginate(model, args, { type: 'cursor', cursor: 'abc', pageSize: 20 })")

pagination_module()