TypeORMとNestJSでカーソル型ページネーションを実装する

import {
  Column,
  CreateDateColumn,
  Entity,
  PrimaryGeneratedColumn,
  UpdateDateColumn,
} from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn({
    type: 'integer',
  })
  id!: number;

  @Column({
    type: 'text',
  })
  name!: string;

  @Column({
    type: 'text',
  })
  bio!: string;

  @CreateDateColumn({
    type: 'datetime',
    name: 'created_at',
  })
  createdAt!: Date;

  @UpdateDateColumn({
    type: 'datetime',
    name: 'updated_at',
  })
  updatedAt!: Date;
}

export type CreateUser = Pick<User, 'name' | 'bio'>;

export type UpdateUser = Pick<User, 'name' | 'bio'>;

import {
  Column,
  CreateDateColumn,
  PrimaryColumn,
  UpdateDateColumn,
} from 'typeorm';

export class Post {
  @PrimaryColumn({
    type: 'text',
  })
  slug!: string;

  @Column({
    type: 'text',
  })
  title!: string;

  @Column({
    type: 'text',
  })
  content!: string;

  @CreateDateColumn({
    type: 'datetime',
    name: 'created_at',
  })
  createdAt!: Date;

  @UpdateDateColumn({
    type: 'datetime',
    name: 'updated_at',
  })
  updatedAt!: Date;
}

export type CreatePost = Pick<Post, 'slug' | 'title' | 'content'>;

export type UpdatePost = Pick<Post, 'title' | 'content'>;

import { Injectable } from '@nestjs/common';
import { Repository } from 'typeorm';
import { CreateUser, UpdateUser, User } from './user.entity';
import { InjectRepository } from '@nestjs/typeorm';

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User)
    protected readonly repo: Repository<User>,
  ) {}

  async findAll(): Promise<User[]> {
    return this.repo.find();
  }

  async findOne(id: number): Promise<User> {
    return this.repo.findOne({ where: { id } });
  }

  async create(user: CreateUser): Promise<User> {
    return this.repo.save(user);
  }

  async update(id: number, user: UpdateUser): Promise<void> {
    await this.repo.update(id, user);
  }

  async remove(id: number): Promise<void> {
    await this.repo.delete(id);
  }
}

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { CreatePost, Post, UpdatePost } from './post.entity';
import { Repository } from 'typeorm';

@Injectable()
export class PostService {
  constructor(
    @InjectRepository(Post)
    protected readonly repo: Repository<Post>,
  ) {}

  async findAll(): Promise<Post[]> {
    return this.repo.find();
  }

  async findOne(slug: string): Promise<Post> {
    return this.repo.findOne({ where: { slug } });
  }

  async create(post: CreatePost): Promise<Post> {
    return this.repo.save(post);
  }

  async update(slug: string, post: UpdatePost): Promise<void> {
    await this.repo.update(slug, post);
  }

  async remove(slug: string): Promise<void> {
    await this.repo.delete(slug);
  }
}

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { TypeOrmModule } from '@nestjs/typeorm';
import { User } from './user.entity';
import { UserService } from './user.service';
import { PostService } from './post.service';
import { Post } from './post.entity';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'sqlite',
      database: 'db.sqlite3',
      synchronize: true,
      logging: true,
      entities: [User],
    }),
    TypeOrmModule.forFeature([User, Post]),
  ],
  controllers: [AppController],
  providers: [AppService, UserService, PostService],
})
export class AppModule {}

import { Injectable } from '@nestjs/common';
import crypto from 'node:crypto';
import { Entity, TokenData } from './token-data';

@Injectable()
export class TokenService {
  // crypto.randomBytes(32).toString('base64')
  private readonly key = Buffer.from(
    'MjJLJ+R1uAie4PVY4Y4DbWb2X/FNSBSyrdDQIsymtT8=',
    'base64',
  );
  // crypto.randomBytes(16).toString('base64')
  private readonly iv = Buffer.from('JgFk9UdYVmz9oCFdAe1CmQ==', 'base64');
  private readonly method = 'aes-256-cbc' as const;
  private readonly encoding: crypto.Encoding = 'base64' as const;

  encode(param: TokenData<Entity, any>): string {
    const cipher = crypto.createCipheriv(this.method, this.key, this.iv);

    const encrypted = cipher.update(JSON.stringify(param));
    const concat = Buffer.concat([encrypted, cipher.final()]);

    const token = concat.toString(this.encoding);

    console.log('encoded token', { param, token });

    return token;
  }

  decode<T extends Entity, U>(token: string): TokenData<T, U> {
    const decipher = crypto.createDecipheriv(this.method, this.key, this.iv);

    const decrypted = decipher.update(token, this.encoding);
    const concat = Buffer.concat([decrypted, decipher.final()]);

    const data = JSON.parse(concat.toString()) as TokenData<T, U>;

    console.log('decoded token', { token, data });

    return data;
  }
}

import { Injectable } from '@nestjs/common';
import {
  DataSource,
  EntityTarget,
  FindManyOptions,
  FindOperator,
  FindOptionsOrder,
  FindOptionsWhere,
  LessThanOrEqual,
  MoreThanOrEqual,
  ObjectLiteral,
  Repository,
} from 'typeorm';

import { TokenService } from './token.service.js';
import {
  Entity,
  EntityKey,
  PaginatedEntityList,
  InitialTokenData,
  PaginateQuery,
  TokenData,
  isTokenData,
} from './token-data';
import { removeEmpty } from './remove-empty';

@Injectable()
export class PaginateService {
  constructor(
    protected readonly dataSource: DataSource,
    protected readonly tokenService: TokenService,
  ) {}

  async paginate<T extends ObjectLiteral, P extends PaginateQuery<T>>(
    repository: Repository<T>,
    entity: EntityTarget<T>,
    params: P,
    token: string | null,
    filterMaker: (params: P) => FindManyOptions<T>,
  ): Promise<PaginatedEntityList<T>>;
  async paginate<T extends ObjectLiteral>(
    repository: Repository<T>,
    entity: EntityTarget<T>,
    params: PaginateQuery<Entity<T>>,
    token: string | null,
  ): Promise<PaginatedEntityList<T>>;
  async paginate<T extends ObjectLiteral, P extends PaginateQuery<Entity<T>>>(
    repository: Repository<T>,
    entity: EntityTarget<T>,
    params: P,
    token: string | null,
    filterMaker?: (params: P) => FindManyOptions,
  ): Promise<PaginatedEntityList<T>> {
    const tokenData = this.getTokenData<P>(entity, params, token);
    const baseOp = this.completeBaseOption<T, P>(filterMaker, params, token);

    const options = this.makeFindManyOptions(entity, baseOp, {
      ...tokenData,
      size: tokenData.size + 1,
    } as InitialTokenData<T> | TokenData<T, P>);

    const entities = await repository.find(options);

    let next: T | undefined = undefined;
    if (entities.length > tokenData.size) {
      next = entities.pop();
    }

    const nextPageToken = next
      ? this.makeToken<T, P>(entity, params, tokenData, next)
      : null;

    return {
      entities,
      nextPageToken,
    };
  }

  protected getTokenData<P>(
    entity: EntityTarget<ObjectLiteral>,
    paginate: PaginateQuery<Entity>,
    token: string | null,
  ): InitialTokenData<ObjectLiteral> | TokenData<ObjectLiteral, P> {
    const defaultParam: InitialTokenData<ObjectLiteral> = {
      size: 100,
      direction: 'asc',
      sortBy: this.guessPrimaryColumn(entity),
      cursorBy: this.guessPrimaryColumn(entity),
    };

    if (token) {
      try {
        return this.tokenService.decode(token);
      } catch (e) {
        return defaultParam;
      }
    }

    return {
      size: paginate.size ?? defaultParam.size,
      direction: paginate.direction ?? defaultParam.direction,
      sortBy: paginate.sortBy ?? defaultParam.sortBy,
      cursorBy: this.getUniqueSortColumn(
        entity,
        paginate.sortBy ?? defaultParam.sortBy,
      ),
    } as InitialTokenData<ObjectLiteral>;
  }

  protected makeFindManyOptions<T extends ObjectLiteral, P>(
    entity: EntityTarget<T>,
    baseOption: FindManyOptions<T>,
    tokenData: InitialTokenData<T> | TokenData<T, P>,
  ): FindManyOptions<T> {
    const where = isTokenData(tokenData)
      ? this.makeFindWhereOption<T>(
          tokenData.cursorBy,
          tokenData.direction ?? 'asc',
          tokenData.nextIdentifier,
        )
      : {};

    let mergedWhere: FindOptionsWhere<T>[] | FindOptionsWhere<T> | undefined =
      this.filterWhere(
        this.mergeFilterAndPaginateWhere(baseOption.where ?? {}, where),
      );
    if (Array.isArray(mergedWhere) && mergedWhere.length === 0) {
      mergedWhere = undefined;
    }

    return {
      ...baseOption,
      take: tokenData.size ?? undefined,
      where: mergedWhere,
      order: {
        ...baseOption.order,
        ...this.makeFindOrderOption(
          entity,
          tokenData.sortBy,
          tokenData.cursorBy,
          tokenData.direction ?? 'asc',
        ),
      },
    };
  }

  protected makeFindWhereOption<T extends ObjectLiteral>(
    cursorBy: EntityKey<T>,
    dir: 'asc' | 'desc',
    nextIdentifier: string | number,
  ): FindOptionsWhere<T> {
    const operator: FindOperator<string | number> =
      dir === 'asc'
        ? (MoreThanOrEqual(nextIdentifier) as FindOperator<string | number>)
        : (LessThanOrEqual(nextIdentifier) as FindOperator<string | number>);

    return {
      [cursorBy]: operator,
    } as FindOptionsWhere<T>;
  }

  protected mergeFilterAndPaginateWhere<T>(
    filter: FindOptionsWhere<T>[] | FindOptionsWhere<T>,
    paginate: FindOptionsWhere<T>,
  ): FindOptionsWhere<T>[] | FindOptionsWhere<T> {
    if (Array.isArray(filter)) {
      return filter.map((w) => ({ ...w, ...paginate }));
    }

    return { ...filter, ...paginate };
  }

  protected filterWhere<T extends Entity>(
    where: FindOptionsWhere<T>[] | FindOptionsWhere<T>,
  ): FindOptionsWhere<T>[] | FindOptionsWhere<T> {
    if (!Array.isArray(where)) return where;

    return where
      .map(removeEmpty)
      .filter(
        (w): w is FindOptionsWhere<T> =>
          w !== undefined && Object.keys(w).length > 0,
      );
  }

  protected makeFindOrderOption(
    entity: EntityTarget<ObjectLiteral>,
    sortBy: EntityKey<ObjectLiteral>,
    cursorBy: EntityKey<ObjectLiteral>,
    direction: 'asc' | 'desc',
  ): FindOptionsOrder<ObjectLiteral> {
    if (!this.checkColumnExists(entity, sortBy)) {
      return {};
    }

    return {
      [sortBy]: direction,
      [cursorBy]: direction,
    } as FindOptionsOrder<Entity>;
  }

  protected makeToken<T extends ObjectLiteral, P>(
    entity: EntityTarget<T>,
    filterParams: P | undefined,
    paginate: InitialTokenData<T>,
    nextEntity: T,
  ): string {
    const column = this.getUniqueSortColumn(entity, paginate.sortBy);
    const nextIdentifier = nextEntity[column as keyof T] as string | number;

    return this.tokenService.encode({
      size: paginate.size,
      direction: paginate.direction,
      sortBy: paginate.sortBy,
      cursorBy: column,
      nextIdentifier,
      filterParams: Object.keys(filterParams ?? {})
        .filter(
          (key) =>
            ![
              'nextPageToken',
              'size',
              'direction',
              'sortBy',
              'cursorBy',
            ].includes(key),
        )
        .reduce((obj, key) => {
          obj[key] = (filterParams ?? {})[key];
          return obj;
        }, {} as any),
    });
  }

  protected completeBaseOption<T extends ObjectLiteral, P>(
    filterMaker: ((params: P) => FindManyOptions<T>) | undefined,
    filterParams: P | undefined,
    pageToken: string | undefined,
  ): FindManyOptions<T> {
    const makeOptions = (params?: P): FindManyOptions<T> =>
      filterMaker && filterParams ? filterMaker(params ?? filterParams) : {};

    if (!pageToken) return makeOptions();

    try {
      const decoded = this.tokenService.decode<Entity, any>(pageToken);
      return makeOptions(decoded.filterParams);
    } catch (e) {
      return makeOptions();
    }
  }

  protected getUniqueSortColumn(
    entity: EntityTarget<ObjectLiteral>,
    sortBy: EntityKey<Entity>,
  ): EntityKey<Entity> {
    const isUniqueSortableColumn =
      this.isUniqueColumn(entity, sortBy) &&
      this.isNotNullColumn(entity, sortBy);

    return isUniqueSortableColumn ? sortBy : this.guessPrimaryColumn(entity);
  }

  protected isUniqueColumn(
    entity: EntityTarget<ObjectLiteral>,
    column: EntityKey<ObjectLiteral>,
  ): boolean {
    const meta = this.dataSource.getMetadata(entity);

    if (this.guessPrimaryColumn(entity) === column) return true;

    return meta.indices.some((i) => {
      return i.columns.map((c) => c.propertyName).includes(column);
    });
  }

  protected isNotNullColumn(
    entity: EntityTarget<ObjectLiteral>,
    column: EntityKey<Entity>,
  ): boolean {
    const meta = this.dataSource.getMetadata(entity);

    return meta.columns.some((c) => c.propertyName === column && !c.isNullable);
  }

  protected guessPrimaryColumn(
    entity: EntityTarget<ObjectLiteral>,
  ): EntityKey<Entity> {
    const meta = this.dataSource.getMetadata(entity);

    const columns = meta.primaryColumns;
    if (columns.length > 1) {
      throw new Error(
        `${this.constructor.name} supports only one primary column. but ${meta.targetName} has multiple primary columns.`,
      );
    }

    return columns[0].propertyName as EntityKey<Entity>;
  }

  protected checkColumnExists(
    entity: EntityTarget<ObjectLiteral>,
    column: EntityKey<Entity>,
  ): boolean {
    const meta = this.dataSource.getMetadata(entity);
    return meta.columns.some((c) => c.propertyName === column);
  }
}

export type UserFilterParam = Partial<Pick<User, 'username' | 'name' | 'bio'>>;

export type UserPaginateParam = PaginateQuery<User> & UserFilterParam;

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(User)
    protected readonly repo: Repository<User>,
    protected readonly paginate: PaginateService,
  ) {}

  async findAll(
    token: string | null,
    params?: UserPaginateParam,
  ): Promise<PaginatedEntityList<User>> {
    return this.paginate.paginate<User, UserPaginateParam>(
      this.repo,
      User,
      params,
      token,
      (p) => ({
        where: [
          { username: p.username ? Like(`%${p.username}%`) : undefined },
          { name: p.name ? Like(`%${p.name}%`) : undefined },
          { bio: p.bio ? Like(`%${p.bio}%`) : undefined },
        ],
      }),
    );
  }
}

@Controller('users')
export class UserController {
  constructor(protected readonly userService: UserService) {}

  @Get('/')
  async index(
    @Query('size', new ParseIntPipe({ optional: true }))
    size: number | undefined,
    @Query('sortBy') sortBy: EntityKey<User> | undefined,
    @Query('direction') direction: 'asc' | 'desc' | undefined,
    @Query('token') token: string | undefined,
    @Query('username') username: string | undefined,
    @Query('name') name: string | undefined,
    @Query('bio') bio: string | undefined,
  ): Promise<PaginatedEntityList<User>> {
    return await this.userService.findAll(token ?? null, {
      size,
      sortBy,
      direction,
      username,
      name,
      bio,
    });
  }
}