Guides

Pagination Guide

How to implement server-side pagination with generated collections in Nuxt Crouton

Pagination Guide

This guide explains how to implement pagination in Nuxt Crouton, including how to modify generated collections for server-side pagination.

Understanding Pagination in Crouton

Nuxt Crouton supports two pagination modes:

Mode	Best For	How It Works
Client-side (default)	Small datasets (< 500 items)	Fetches all data, paginates in browser
Server-side	Large datasets (> 500 items)	Fetches one page at a time from API

Generated collections use client-side pagination by default. All data is fetched at once, and pagination happens in the browser. This works well for small datasets but doesn't scale.

When to Add Server-Side Pagination

Add server-side pagination when:

Your collection has more than 500 items
Initial load time is slow
You need to filter by date ranges (e.g., bookings, orders)
Memory usage is a concern
Users frequently access only recent data

Adding Server-Side Pagination to Generated Collections

The generator creates working CRUD endpoints, but without pagination support. Here's how to add it:

Step 1: Add Paginated Query Function

Edit the generated queries.ts file in your layer:

// layers/[layer]/collections/[collection]/server/database/queries.ts

// Keep existing imports
import { eq, and, desc, sql } from 'drizzle-orm'

// Add this new function alongside existing ones
export async function getPaginated[PascalCasePlural](
  teamId: string,
  options: {
    page?: number
    limit?: number
    sortBy?: string
    sortDirection?: 'asc' | 'desc'
  } = {}
) {
  const {
    page = 1,
    limit = 20,
    sortBy = 'createdAt',
    sortDirection = 'desc'
  } = options

  const offset = (page - 1) * limit
  const db = useDB()

  // Run count and data queries in parallel
  const [items, countResult] = await Promise.all([
    db.select()
      .from(tables.[tableName])
      .where(eq(tables.[tableName].teamId, teamId))
      .orderBy(
        sortDirection === 'desc'
          ? desc(tables.[tableName][sortBy])
          : asc(tables.[tableName][sortBy])
      )
      .limit(limit)
      .offset(offset),

    db.select({ count: sql<number>`count(*)` })
      .from(tables.[tableName])
      .where(eq(tables.[tableName].teamId, teamId))
  ])

  const totalItems = Number(countResult[0]?.count || 0)

  return {
    items,
    pagination: {
      currentPage: page,
      pageSize: limit,
      totalItems,
      totalPages: Math.ceil(totalItems / limit)
    }
  }
}

Example for bookings:

// layers/bookings/collections/bookings/server/database/queries.ts

export async function getPaginatedBookingsBookings(
  teamId: string,
  options: { page?: number; limit?: number } = {}
) {
  const { page = 1, limit = 20 } = options
  const offset = (page - 1) * limit
  const db = useDB()

  const [items, countResult] = await Promise.all([
    db.select()
      .from(tables.bookingsBookings)
      .where(eq(tables.bookingsBookings.teamId, teamId))
      .orderBy(desc(tables.bookingsBookings.date))
      .limit(limit)
      .offset(offset),

    db.select({ count: sql<number>`count(*)` })
      .from(tables.bookingsBookings)
      .where(eq(tables.bookingsBookings.teamId, teamId))
  ])

  return {
    items,
    pagination: {
      currentPage: page,
      pageSize: limit,
      totalItems: Number(countResult[0]?.count || 0),
      totalPages: Math.ceil(Number(countResult[0]?.count || 0) / limit)
    }
  }
}

Step 2: Update the API Endpoint

Modify the generated index.get.ts to accept pagination parameters:

// layers/[layer]/collections/[collection]/server/api/teams/[id]/[api-path]/index.get.ts

// Add import for new paginated function
import {
  getAll[PascalCasePlural],
  get[PascalCasePlural]ByIds,
  getPaginated[PascalCasePlural]  // Add this
} from '../../../../database/queries'

export default defineEventHandler(async (event) => {
  // ... existing team auth code ...

  const query = getQuery(event)

  // Handle ID-based queries (unchanged)
  if (query.ids) {
    const ids = String(query.ids).split(',')
    return await get[PascalCasePlural]ByIds(team.id, ids)
  }

  // NEW: Handle pagination
  if (query.page || query.limit || query.paginate === 'true') {
    const page = Number(query.page) || 1
    const limit = Number(query.limit) || 20
    const sortBy = String(query.sortBy || 'createdAt')
    const sortDirection = query.sortDirection === 'asc' ? 'asc' : 'desc'

    return await getPaginated[PascalCasePlural](team.id, {
      page,
      limit,
      sortBy,
      sortDirection
    })
  }

  // Default: return all (backwards compatible)
  return await getAll[PascalCasePlural](team.id)
})

Example for bookings:

// layers/bookings/collections/bookings/server/api/teams/[id]/bookings-bookings/index.get.ts

import {
  getAllBookingsBookings,
  getBookingsBookingsByIds,
  getPaginatedBookingsBookings
} from '../../../../database/queries'

export default defineEventHandler(async (event) => {
  // ... existing team auth code ...

  const query = getQuery(event)

  if (query.ids) {
    const ids = String(query.ids).split(',')
    return await getBookingsBookingsByIds(team.id, ids)
  }

  // Handle pagination
  if (query.page || query.limit) {
    return await getPaginatedBookingsBookings(team.id, {
      page: Number(query.page) || 1,
      limit: Number(query.limit) || 20
    })
  }

  return await getAllBookingsBookings(team.id)
})

Step 3: Use in Components

Now you can use server-side pagination in your components:

With CroutonTable

<script setup lang="ts">
const page = ref(1)
const pageSize = ref(20)

// Fetch with pagination params
const { data, pending, refresh } = await useFetch(
  () => `/api/teams/${teamId}/bookings-bookings`,
  {
    query: computed(() => ({
      page: page.value,
      limit: pageSize.value
    }))
  }
)

const items = computed(() => data.value?.items || [])
const paginationData = computed(() => data.value?.pagination || null)
</script>

<template>
  <CroutonTable
    collection="bookingsBookings"
    :rows="items"
    :columns="columns"
    server-pagination
    :pagination-data="paginationData"
    :refresh-fn="refresh"
  />
</template>

With useCollectionQuery

<script setup lang="ts">
const page = ref(1)
const pageSize = ref(20)

const { data, pending, refresh } = await useCollectionQuery('bookingsBookings', {
  query: computed(() => ({
    page: page.value,
    limit: pageSize.value
  }))
})

const items = computed(() => data.value?.items || [])
const paginationData = computed(() => data.value?.pagination)
</script>

<template>
  <CroutonTable
    collection="bookingsBookings"
    :rows="items"
    :columns="columns"
    server-pagination
    :pagination-data="paginationData"
    :refresh-fn="refresh"
  />
</template>

Manual Pagination Controls

<script setup lang="ts">
const page = ref(1)
const pageSize = ref(20)

const { data, refresh } = await useFetch('/api/teams/current/bookings-bookings', {
  query: computed(() => ({ page: page.value, limit: pageSize.value }))
})

const items = computed(() => data.value?.items || [])
const pagination = computed(() => data.value?.pagination)

async function goToPage(newPage: number) {
  page.value = newPage
  await refresh()
}

async function changePageSize(newSize: number) {
  pageSize.value = newSize
  page.value = 1  // Reset to first page
  await refresh()
}
</script>

<template>
  <div>
    <!-- Your list/table -->
    <div v-for="item in items" :key="item.id">
      {{ item.title }}
    </div>

    <!-- Pagination controls -->
    <CroutonTablePagination
      :page="page"
      :page-count="pageSize"
      :total-items="pagination?.totalItems || 0"
      @update:page="goToPage"
      @update:page-count="changePageSize"
    />
  </div>
</template>

Adding Filtering with Pagination

Often you'll want to combine pagination with filtering. Here's how:

Update the Query Function

export async function getPaginatedBookingsBookings(
  teamId: string,
  options: {
    page?: number
    limit?: number
    // Add filter options
    startDate?: string
    endDate?: string
    locationId?: string
    status?: string
  } = {}
) {
  const {
    page = 1,
    limit = 20,
    startDate,
    endDate,
    locationId,
    status
  } = options

  const offset = (page - 1) * limit
  const db = useDB()

  // Build conditions array
  const conditions = [eq(tables.bookingsBookings.teamId, teamId)]

  if (startDate) {
    conditions.push(gte(tables.bookingsBookings.date, new Date(startDate)))
  }
  if (endDate) {
    conditions.push(lte(tables.bookingsBookings.date, new Date(endDate)))
  }
  if (locationId) {
    conditions.push(eq(tables.bookingsBookings.locationId, locationId))
  }
  if (status) {
    conditions.push(eq(tables.bookingsBookings.status, status))
  }

  const whereClause = and(...conditions)

  const [items, countResult] = await Promise.all([
    db.select()
      .from(tables.bookingsBookings)
      .where(whereClause)
      .orderBy(desc(tables.bookingsBookings.date))
      .limit(limit)
      .offset(offset),

    db.select({ count: sql<number>`count(*)` })
      .from(tables.bookingsBookings)
      .where(whereClause)
  ])

  return {
    items,
    pagination: {
      currentPage: page,
      pageSize: limit,
      totalItems: Number(countResult[0]?.count || 0),
      totalPages: Math.ceil(Number(countResult[0]?.count || 0) / limit)
    }
  }
}

Update the API Endpoint

export default defineEventHandler(async (event) => {
  // ... auth code ...

  const query = getQuery(event)

  if (query.page || query.limit) {
    return await getPaginatedBookingsBookings(team.id, {
      page: Number(query.page) || 1,
      limit: Number(query.limit) || 20,
      startDate: query.startDate as string,
      endDate: query.endDate as string,
      locationId: query.locationId as string,
      status: query.status as string
    })
  }

  return await getAllBookingsBookings(team.id)
})

Use with Filters in Component

<script setup lang="ts">
const page = ref(1)
const pageSize = ref(20)
const filters = ref({
  startDate: null,
  endDate: null,
  locationId: null,
  status: null
})

const { data, refresh } = await useFetch('/api/teams/current/bookings-bookings', {
  query: computed(() => ({
    page: page.value,
    limit: pageSize.value,
    ...Object.fromEntries(
      Object.entries(filters.value).filter(([_, v]) => v != null)
    )
  }))
})

// Reset to page 1 when filters change
watch(filters, () => {
  page.value = 1
}, { deep: true })
</script>

Pagination Response Format

For consistency, paginated endpoints should return this structure:

interface PaginatedResponse<T> {
  items: T[]
  pagination: {
    currentPage: number    // Current page (1-indexed)
    pageSize: number       // Items per page
    totalItems: number     // Total count across all pages
    totalPages: number     // Total number of pages
  }
}

Best Practices

1. Default to Reasonable Limits

const limit = Math.min(Number(query.limit) || 20, 100)  // Cap at 100

2. Reset Page on Filter Changes

watch(filters, () => {
  page.value = 1  // Always reset to page 1
}, { deep: true })

3. Handle Edge Cases

// In query function
const totalItems = Number(countResult[0]?.count || 0)
const totalPages = Math.max(1, Math.ceil(totalItems / limit))

// Ensure page is valid
const validPage = Math.min(Math.max(1, page), totalPages)

4. Keep Backwards Compatibility

The modified endpoint still returns all items when no pagination params are passed, so existing code continues to work.

5. Consider Cursor-Based Pagination for Large Datasets

For very large datasets (100k+ items), consider cursor-based pagination instead of offset-based:

// Instead of page/offset
?cursor=abc123&limit=20

// Returns
{
  items: [...],
  nextCursor: "xyz789",
  hasMore: true
}

Troubleshooting

Pagination shows wrong total

Problem: Total items count is incorrect

Solution: Ensure the count query uses the same WHERE conditions as the data query:

// Both queries must have identical WHERE clauses
const whereClause = and(
  eq(tables.bookings.teamId, teamId),
  // ... same filters
)

const [items, countResult] = await Promise.all([
  db.select().from(tables.bookings).where(whereClause).limit(limit).offset(offset),
  db.select({ count: sql`count(*)` }).from(tables.bookings).where(whereClause)  // Same whereClause!
])

Page resets unexpectedly

Problem: Page keeps jumping back to 1

Solution: Check that your query params are reactive but not causing infinite loops:

// Use computed for query params
const queryParams = computed(() => ({
  page: page.value,
  limit: pageSize.value
}))

// NOT this (causes infinite loops)
watch([page, pageSize], refresh)  // Don't do this!

Empty results after filter change

Problem: Filter changes show empty results

Solution: Reset page to 1 when filters change:

watch(filters, () => {
  page.value = 1
  // The query will auto-refresh due to computed dependency
}, { deep: true })

Table Patterns - Complete table documentation
Querying Data - Query composables
API Reference - useCollectionQuery API

Best Practices

Recommended patterns and practices for building with Nuxt Crouton

Asset Management

Complete guide to managing media files with nuxt-crouton-assets