Pagination & Sorting

Efficient pagination and sorting are essential for handling large datasets. Collections provides flexible options for both.

Pagination

Offset-Based Pagination

The simplest pagination method using limit and offset:

// First page
const page1 = await collections.posts.findMany({
  limit: 10,
  offset: 0
})

// Second page
const page2 = await collections.posts.findMany({
  limit: 10,
  offset: 10
})

// Third page
const page3 = await collections.posts.findMany({
  limit: 10,
  offset: 20
})

Generic pagination function:

const paginate = async <T>(
  query: (options: { limit: number; offset: number }) => Promise<T[]>,
  page: number,
  pageSize: number = 10
) => {
  const offset = (page - 1) * pageSize
  const data = await query({ limit: pageSize, offset })
  return data
}

// Usage
const posts = await paginate(
  (options) => collections.posts.findMany(options),
  2,  // Page 2
  10  // 10 items per page
)

Cursor-Based Pagination

More efficient for large datasets using a cursor:

// First page
const page1 = await collections.posts.findMany({
  orderBy: {
    createdAt: 'desc'
  },
  take: 10
})

// Get next page using last item's createdAt
const lastPost = page1[page1.length - 1]
const page2 = await collections.posts.findMany({
  orderBy: {
    createdAt: 'desc'
  },
  take: 10,
  skip: 1, // Skip the cursor
  cursor: {
    createdAt: lastPost.createdAt
  }
})

Cursor pagination helper:

interface PaginatedResult<T> {
  data: T[]
  nextCursor: string | null
  hasMore: boolean
}

const paginateWithCursor = async (
  cursor: string | null = null,
  limit: number = 10
): Promise<PaginatedResult<Post>> => {
  const posts = await collections.posts.findMany({
    orderBy: {
      createdAt: 'desc'
    },
    take: limit + 1, // Fetch one extra to check if there's more
    ...(cursor && {
      skip: 1,
      cursor: {
        id: parseInt(cursor)
      }
    })
  })

  const hasMore = posts.length > limit
  const data = hasMore ? posts.slice(0, -1) : posts
  const nextCursor = hasMore ? data[data.length - 1].id.toString() : null

  return { data, nextCursor, hasMore }
}

Sorting

Single Field Sorting

Sort by a single field:

// Ascending
const posts = await collections.posts.findMany({
  orderBy: {
    createdAt: 'asc'
  }
})

// Descending
const posts = await collections.posts.findMany({
  orderBy: {
    createdAt: 'desc'
  }
})

Multiple Field Sorting

Sort by multiple fields:

const posts = await collections.posts.findMany({
  orderBy: [
    { published: 'desc' },  // Published first
    { createdAt: 'desc' }   // Then by newest
  ]
})

Sort by fields in related records:

const posts = await collections.posts.findMany({
  include: {
    author: true
  },
  orderBy: {
    'author.name': 'asc'  // Sort by author name
  }
})

Pagination Metadata

Return pagination metadata with results:

interface PaginationMeta {
  page: number
  pageSize: number
  totalCount: number
  totalPages: number
  hasNext: boolean
  hasPrev: boolean
}

interface PaginatedPosts {
  posts: Post[]
  meta: PaginationMeta
}

const getPaginatedPosts = async (
  page: number,
  pageSize: number
): Promise<PaginatedPosts> => {
  const [posts, totalCount] = await Promise.all([
    collections.posts.findMany({
      limit: pageSize,
      offset: (page - 1) * pageSize,
      orderBy: {
        createdAt: 'desc'
      }
    }),
    collections.posts.count()
  ])

  const totalPages = Math.ceil(totalCount / pageSize)

  return {
    posts,
    meta: {
      page,
      pageSize,
      totalCount,
      totalPages,
      hasNext: page < totalPages,
      hasPrev: page > 1
    }
  }
}

Infinite Scroll

Implement infinite scroll pagination:

interface InfiniteScrollResult<T> {
  data: T[]
  nextCursor: string | null
  hasMore: boolean
}

const loadMorePosts = async (
  cursor: string | null = null,
  limit: number = 20
): Promise<InfiniteScrollResult<Post>> => {
  const posts = await collections.posts.findMany({
    orderBy: {
      createdAt: 'desc'
    },
    take: limit + 1, // Fetch one extra to determine if there's more
    ...(cursor && {
      skip: 1,
      cursor: {
        id: parseInt(cursor)
      }
    }
  })

  const hasMore = posts.length > limit
  const data = hasMore ? posts.slice(0, -1) : posts
  const nextCursor = hasMore ? data[data.length - 1].id.toString() : null

  return { data, nextCursor, hasMore }
}

// Usage in an API route
export async function GET(request: Request) {
  const { searchParams } = new URL(request.url)
  const cursor = searchParams.get('cursor')

  const result = await loadMorePosts(cursor)

  return Response.json(result)
}

Keyset Pagination

Use composite keys for stable pagination:

const getPostsPaginated = async (
  lastId: number | null = null,
  limit: number = 10
) => {
  return await collections.posts.findMany({
    orderBy: [
      { createdAt: 'desc' },
      { id: 'desc' }  // Tiebreaker
    ],
    take: limit,
    ...(lastId && {
      cursor: {
        id: lastId
      }
    })
  })
}

Sorting Options Reference

Prop

Type

Real-World Examples

Blog Listing with Pagination

const getBlogPosts = async (page: number, category?: string) => {
  const pageSize = 12
  const where = category
    ? { categories: { some: { slug: { equals: category } } } }
    : {}

  const [posts, totalCount] = await Promise.all([
    collections.posts.findMany({
      where,
      orderBy: {
        publishedAt: 'desc'
      },
      limit: pageSize,
      offset: (page - 1) * pageSize,
      include: {
        author: {
          select: {
            name: true,
            avatar: true
          }
        },
        categories: true
      }
    }),
    collections.posts.count({ where })
  ])

  return {
    posts,
    pagination: {
      page,
      pageSize,
      totalCount,
      totalPages: Math.ceil(totalCount / pageSize)
    }
  }
}

E-commerce Product Listing

const getProducts = async (options: {
  page: number
  sort?: 'price-asc' | 'price-desc' | 'name' | 'newest'
  category?: string
  minPrice?: number
  maxPrice?: number
}) => {
  const { page, sort = 'newest', category, minPrice, maxPrice } = options
  const pageSize = 24

  const orderBy = {
    'price-asc': { price: 'asc' },
    'price-desc': { price: 'desc' },
    'name': { name: 'asc' },
    'newest': { createdAt: 'desc' }
  }[sort]

  const where = {
    ...(category && { category: { equals: category } }),
    ...(minPrice && { price: { gte: minPrice } }),
    ...(maxPrice && { price: { lte: maxPrice } })
  }

  const [products, totalCount] = await Promise.all([
    collections.products.findMany({
      where,
      orderBy,
      limit: pageSize,
      offset: (page - 1) * pageSize,
      include: {
        categories: true,
        reviews: {
          select: {
            rating: true
          }
        }
      }
    }),
    collections.products.count({ where })
  ])

  return {
    products,
    pagination: {
      page,
      pageSize,
      totalCount,
      totalPages: Math.ceil(totalCount / pageSize)
    }
  }
}

Admin Dashboard with Sorting

const getUsers = async (options: {
  page: number
  sortBy?: string
  sortOrder?: 'asc' | 'desc'
  status?: string
}) => {
  const { page, sortBy = 'createdAt', sortOrder = 'desc', status } = options
  const pageSize = 50

  const [users, totalCount] = await Promise.all([
    collections.users.findMany({
      where: status ? { status: { equals: status } } : undefined,
      orderBy: {
        [sortBy]: sortOrder
      },
      limit: pageSize,
      offset: (page - 1) * pageSize,
      select: {
        id: true,
        name: true,
        email: true,
        status: true,
        createdAt: true,
        _count: {
          select: {
            posts: true,
            comments: true
          }
        }
      }
    }),
    collections.users.count()
  ])

  return {
    users,
    pagination: {
      page,
      pageSize,
      totalCount,
      totalPages: Math.ceil(totalCount / pageSize)
    }
  }
}

Performance Tips

Use indexes on sorted fields:

export const posts = collection({
  slug: 'posts',
  fields: {
    createdAt: field({
      type: timestamp(),
      indexed: true  // Index for sorting
    }),
    publishedAt: field({
      type: timestamp(),
      indexed: true
    })
  }
})

Limit page size - Large pages are slow
Use cursor pagination for large datasets
Avoid offset on large tables - Use cursor instead
Count with filters - Match your WHERE clause

Common Patterns

First/Last Item

// Get first item
const firstPost = await collections.posts.findFirst({
  orderBy: {
    createdAt: 'asc'
  }
})

// Get last item
const lastPost = await collections.posts.findFirst({
  orderBy: {
    createdAt: 'desc'
  }
})

Latest N Items

const latestPosts = await collections.posts.findMany({
  orderBy: {
    createdAt: 'desc'
  },
  take: 10
})

Top N Items by Field

const topPosts = await collections.posts.findMany({
  orderBy: {
    views: 'desc'
  },
  take: 10
})

Random Selection

// Get random posts (not efficient for large tables)
const posts = await collections.posts.findMany({
  orderBy: {
    id: 'asc'  // Any indexed field
  },
  limit: 100
})

const randomPosts = posts
  .sort(() => Math.random() - 0.5)
  .slice(0, 10)

Next Steps

Queries - Basic CRUD operations
Filters - Advanced filtering operators
Relations - Working with related data

Pagination & Sorting

On this page