在NestJS与TypeORM中实现通用分页（遵循DRY原则、可测试）

Great catch—duplicating pagination logic across TypeORM repositories is such a waste of time and a maintenance headache. Since limit/skip are purely pagination-focused (not tied to any specific entity), abstracting this into a reusable, testable solution is absolutely the way to go. Here's the optimal approach I've implemented in multiple production projects:

1. Start with a Reusable Pagination Contract #

First, define a type-safe interface for pagination options to standardize inputs across all your repositories:

export interface PaginationOptions {
  page: number; // 1-indexed page number
  limit: number; // Number of items per page
}

// Helper to calculate skip value (avoids repeating (page-1)*limit everywhere)
export const calculateSkip = (page: number, limit: number): number => {
  return Math.max(0, (page - 1) * limit); // Guard against negative skip values
};

2. Build a Generic Pagination Utility Function #

Create a standalone function that works with any TypeORM Repository<T>—this keeps your existing repositories clean and avoids inheritance if you don't want it:

import { Repository, SelectQueryBuilder } from "typeorm";

export async function paginate<T>(
  repository: Repository<T>,
  options: PaginationOptions,
  queryModifier?: (qb: SelectQueryBuilder<T>) => void
): Promise<{ data: T[]; total: number }> {
  const { page, limit } = options;
  const skip = calculateSkip(page, limit);

  // Initialize a query builder for the entity
  const queryBuilder = repository.createQueryBuilder("entity");

  // Allow custom query modifications (filtering, sorting, joins) via a callback
  if (queryModifier) {
    queryModifier(queryBuilder);
  }

  // Fetch paginated data + total count in one round trip
  const [data, total] = await queryBuilder
    .skip(skip)
    .take(limit)
    .getManyAndCount();

  return { data, total };
}

3. Usage in Your Service Layer #

This function is dead simple to use with any repository. For example, with a User entity:

import { getRepository } from "typeorm";
import { User } from "./entities/User";
import { paginate } from "./utils/paginate";

async function fetchActiveUsers(page: number = 1, limit: number = 10) {
  const userRepo = getRepository(User);

  return paginate(userRepo, { page, limit }, (qb) => {
    // Add custom filters, sorting, or joins here
    qb.where("entity.isActive = :isActive", { isActive: true })
      .orderBy("entity.createdAt", "DESC");
  });
}

4. Ensuring Testability #

The best part about this approach is how easy it is to test. You can mock TypeORM's Repository and SelectQueryBuilder with tools like Jest:

import { paginate } from "./utils/paginate";

describe("paginate utility", () => {
  it("returns paginated data and total count correctly", async () => {
    // Mock the query builder chain
    const mockQueryBuilder = {
      skip: jest.fn().mockReturnThis(),
      take: jest.fn().mockReturnThis(),
      getManyAndCount: jest.fn().mockResolvedValue([[{ id: 1, name: "Alice" }], 5]),
    };

    // Mock the repository to return our mock query builder
    const mockRepo = {
      createQueryBuilder: jest.fn().mockReturnValue(mockQueryBuilder),
    };

    const result = await paginate(mockRepo as any, { page: 1, limit: 10 });

    expect(mockRepo.createQueryBuilder).toHaveBeenCalledWith("entity");
    expect(mockQueryBuilder.skip).toHaveBeenCalledWith(0);
    expect(mockQueryBuilder.take).toHaveBeenCalledWith(10);
    expect(result).toEqual({
      data: [{ id: 1, name: "Alice" }],
      total: 5,
    });
  });

  it("applies custom query modifiers", async () => {
    const mockWhere = jest.fn().mockReturnThis();
    const mockOrderBy = jest.fn().mockReturnThis();
    const mockQueryBuilder = {
      skip: jest.fn().mockReturnThis(),
      take: jest.fn().mockReturnThis(),
      where: mockWhere,
      orderBy: mockOrderBy,
      getManyAndCount: jest.fn().mockResolvedValue([[], 0]),
    };

    const mockRepo = {
      createQueryBuilder: jest.fn().mockReturnValue(mockQueryBuilder),
    };

    await paginate(mockRepo as any, { page: 2, limit: 5 }, (qb) => {
      qb.where("entity.isActive = :isActive", { isActive: true })
        .orderBy("entity.createdAt", "DESC");
    });

    expect(mockWhere).toHaveBeenCalledWith("entity.isActive = :isActive", { isActive: true });
    expect(mockOrderBy).toHaveBeenCalledWith("entity.createdAt", "DESC");
    expect(mockQueryBuilder.skip).toHaveBeenCalledWith(5); // (2-1)*5 = 5
  });
});

5. Alternative: Extend TypeORM's Repository Class #

If you prefer having a paginate method directly on your repositories (instead of a utility function), you can create a base repository class:

import { Repository, SelectQueryBuilder } from "typeorm";
import { PaginationOptions, calculateSkip } from "./pagination.types";

export class BaseRepository<T> extends Repository<T> {
  async paginate(
    options: PaginationOptions,
    queryModifier?: (qb: SelectQueryBuilder<T>) => void
  ): Promise<{ data: T[]; total: number }> {
    const { page, limit } = options;
    const skip = calculateSkip(page, limit);

    const queryBuilder = this.createQueryBuilder("entity");

    if (queryModifier) {
      queryModifier(queryBuilder);
    }

    const [data, total] = await queryBuilder
      .skip(skip)
      .take(limit)
      .getManyAndCount();

    return { data, total };
  }
}

Then attach it to your entity:

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm";
import { BaseRepository } from "./BaseRepository";

@Entity({ repository: () => BaseRepository })
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;
}

Usage is just as clean:

const userRepo = getRepository(User) as BaseRepository<User>;
const result = await userRepo.paginate({ page: 1, limit: 20 }, (qb) => {
  qb.where("entity.name LIKE :name", { name: "%John%" });
});

Final Notes #

• The utility function approach is more flexible (no need to modify existing entities/repositories) and great for legacy projects.
• The base repository approach feels more integrated with TypeORM and is ideal for new projects.
• Both solutions are fully testable because they rely on TypeORM's abstract interfaces, making mocking straightforward.

内容的提问来源于stack exchange，提问作者deltakroneker