Skip to main content

Pagination

Overview

Effective pagination is crucial for handling large datasets in your API responses. The starter kit provides a standardized pagination solution that works seamlessly with TypeORM and NestJS.

Pagination Types

The starter kit supports two pagination styles:

  1. Offset-based pagination: Using limit and offset parameters
  2. Cursor-based pagination: Using a cursor for more efficient pagination of large datasets

Pagination DTOs

Base Pagination DTO

export class PaginationDto {
@IsOptional()
@IsInt()
@Min(1)
@Type(() => Number)
limit?: number = 10;

@IsOptional()
@IsInt()
@Min(0)
@Type(() => Number)
offset?: number = 0;

@IsOptional()
@IsString()
sortBy?: string = 'createdAt';

@IsOptional()
@IsEnum(['ASC', 'DESC'])
sortOrder?: 'ASC' | 'DESC' = 'DESC';
}

Cursor Pagination DTO

export class CursorPaginationDto {
@IsOptional()
@IsInt()
@Min(1)
@Type(() => Number)
limit?: number = 10;

@IsOptional()
@IsString()
cursor?: string;

@IsOptional()
@IsString()
sortBy?: string = 'createdAt';

@IsOptional()
@IsEnum(['ASC', 'DESC'])
sortOrder?: 'ASC' | 'DESC' = 'DESC';
}

Pagination Response Interface

export interface PaginatedResult<T> {
data: T[];
meta: {
total: number;
limit: number;
offset: number;
};
}

export interface CursorPaginatedResult<T> {
data: T[];
meta: {
hasMore: boolean;
nextCursor: string | null;
limit: number;
};
}

Pagination Service

The starter kit includes a pagination service for standard operations:

@Injectable()
export class PaginationService {
async paginate<T>(
queryBuilder: SelectQueryBuilder<T>,
options: PaginationDto,
): Promise<PaginatedResult<T>> {
const total = await queryBuilder.getCount();

// Apply sorting
if (options.sortBy) {
const entity = queryBuilder.alias;
queryBuilder.orderBy(
`${entity}.${options.sortBy}`,
options.sortOrder,
);
}

// Apply pagination
const data = await queryBuilder
.skip(options.offset)
.take(options.limit)
.getMany();

return {
data,
meta: {
total,
limit: options.limit,
offset: options.offset,
},
};
}

async paginateWithCursor<T>(
queryBuilder: SelectQueryBuilder<T>,
options: CursorPaginationDto,
cursorColumn: string = 'id',
): Promise<CursorPaginatedResult<T>> {
// Apply cursor-based condition if cursor is provided
if (options.cursor) {
const decodedCursor = Buffer.from(options.cursor, 'base64').toString('utf-8');
const [column, value] = decodedCursor.split(':');

const operator = options.sortOrder === 'DESC' ? '<' : '>';
const entity = queryBuilder.alias;

queryBuilder.andWhere(
`${entity}.${column} ${operator} :value`,
{ value },
);
}

// Apply sorting
const sortColumn = options.sortBy || cursorColumn;
const entity = queryBuilder.alias;
queryBuilder.orderBy(
`${entity}.${sortColumn}`,
options.sortOrder,
);

// Get one extra item to determine if there are more results
const data = await queryBuilder
.take(options.limit + 1)
.getMany();

const hasMore = data.length > options.limit;

// Remove the extra item
if (hasMore) {
data.pop();
}

// Generate next cursor
let nextCursor = null;
if (hasMore && data.length > 0) {
const lastItem = data[data.length - 1];
const cursorValue = lastItem[sortColumn];
const cursorString = `${sortColumn}:${cursorValue}`;
nextCursor = Buffer.from(cursorString).toString('base64');
}

return {
data,
meta: {
hasMore,
nextCursor,
limit: options.limit,
},
};
}
}

Usage Examples

Controller Implementation

@Controller('users')
export class UsersController {
constructor(
private usersService: UsersService,
private paginationService: PaginationService,
) {}

@Get()
async findAll(
@Query() paginationDto: PaginationDto,
@Query('search') search?: string,
): Promise<PaginatedResult<User>> {
return this.usersService.findAll(paginationDto, search);
}

@Get('cursor')
async findAllWithCursor(
@Query() paginationDto: CursorPaginationDto,
@Query('search') search?: string,
): Promise<CursorPaginatedResult<User>> {
return this.usersService.findAllWithCursor(paginationDto, search);
}
}

Service Implementation

@Injectable()
export class UsersService {
constructor(
@InjectRepository(User)
private usersRepository: Repository<User>,
private paginationService: PaginationService,
) {}

async findAll(
paginationDto: PaginationDto,
search?: string,
): Promise<PaginatedResult<User>> {
const queryBuilder = this.usersRepository.createQueryBuilder('user');

if (search) {
queryBuilder.where(
'user.email LIKE :search OR user.firstName LIKE :search OR user.lastName LIKE :search',
{ search: `%${search}%` },
);
}

return this.paginationService.paginate(queryBuilder, paginationDto);
}

async findAllWithCursor(
paginationDto: CursorPaginationDto,
search?: string,
): Promise<CursorPaginatedResult<User>> {
const queryBuilder = this.usersRepository.createQueryBuilder('user');

if (search) {
queryBuilder.where(
'user.email LIKE :search OR user.firstName LIKE :search OR user.lastName LIKE :search',
{ search: `%${search}%` },
);
}

return this.paginationService.paginateWithCursor(
queryBuilder,
paginationDto,
'createdAt',
);
}
}

Frontend Implementation

Example React Hook

function useOffsetPagination<T>(
apiEndpoint: string,
initialParams: PaginationParams = {
limit: 10,
offset: 0,
sortBy: 'createdAt',
sortOrder: 'DESC',
},
) {
const [data, setData] = useState<T[]>([]);
const [loading, setLoading] = useState<boolean>(false);
const [error, setError] = useState<Error | null>(null);
const [total, setTotal] = useState<number>(0);
const [params, setParams] = useState<PaginationParams>(initialParams);

useEffect(() => {
const fetchData = async () => {
setLoading(true);
try {
const queryParams = new URLSearchParams({
limit: params.limit.toString(),
offset: params.offset.toString(),
sortBy: params.sortBy,
sortOrder: params.sortOrder,
});

const response = await fetch(`${apiEndpoint}?${queryParams}`);
const result = await response.json();

setData(result.data);
setTotal(result.meta.total);
} catch (err) {
setError(err as Error);
} finally {
setLoading(false);
}
};

fetchData();
}, [apiEndpoint, params]);

return {
data,
loading,
error,
total,
params,
setParams,
};
}

Best Practices

  1. Always use DTOs: Define clear pagination DTOs for your API
  2. Consistent Response Format: Use standard pagination response interfaces
  3. Use Query Builders: For complex pagination scenarios, use TypeORM query builders
  4. Choose Appropriate Method: Use cursor-based pagination for large datasets
  5. Server-Side Sorting: Handle sorting on the server to reduce client-side load
  6. Add Indexes: Ensure database indexes on columns used for sorting and filtering
  7. Cache Results: For static data, consider caching paginated results

Performance Considerations

Offset Pagination Limitations

Offset-based pagination can become inefficient for very large datasets because the database must scan and discard all rows before the offset. Consider these alternatives:

  1. Use cursor-based pagination for large datasets
  2. Add appropriate indexes on sorting columns
  3. Cache previous results when possible
  4. Use LIMIT/TAKE with reasonable values (10-100 items per page)

Optimizing Count Queries

For large tables, count queries can be slow. Consider these optimizations:

  1. Separate count queries: Run count queries separately and cache the results
  2. Estimate counts: For very large tables, consider approximate counts
  3. Skip counts when possible: For cursor-based pagination, you often don't need exact counts