Custom Builder Sequelize

import { Injectable } from '@nestjs/common'
import sequelize, {
  FindOptions,
  Transaction,
  TransactionOptions,
  Optional,
  WhereOptions,
  Includeable,
  Op,
  IncludeOptions,
  CreateOptions,
  UpdateOptions,
  DestroyOptions,
  FindAttributeOptions,
} from 'sequelize'
import { Repository, Sequelize, Model } from 'sequelize-typescript'
import { MakeNullishOptional, NullishPropertiesOf } from 'sequelize/lib/utils'
import { EPGRelationAction } from '~/domain/enums/common.enum'
export { InjectModel as InjectRepository } from '@nestjs/sequelize'
export { Repository } from 'sequelize-typescript'

import { response } from '~/infrastructure/common/helpers/response.helper'

@Injectable()
export class SequelizeService {
  constructor(private sequelize: Sequelize) {}

  async transaction<T = any>(operation: (transaction: Transaction) => Promise<T>, options?: TransactionOptions): Promise<T> {
    try {
      const transaction = await this.sequelize.transaction({ autocommit: false, ...options })
      try {
        const responseTransaction: T = await operation(transaction)
        await transaction.commit()
        return responseTransaction
      } catch (e: any) {
        await transaction.rollback()
        throw response(e)
      }
    } catch (e: any) {
      response(e)
      return null
    }
  }

  async findOneAndUpdate<T = any>(repository: Repository<Model<T, T>>, data: Optional<T, NullishPropertiesOf<T>>, options: FindOptions<T>): Promise<T> {
    try {
      let repositoryModel: Model<T, T> = await repository.findOne(options)
      if (!repositoryModel) return null

      repositoryModel = Object.assign(repositoryModel, { ...data })
      return (await repositoryModel.save(options))?.dataValues
    } catch (e: any) {
      response(e)
      return null
    }
  }
}

export class SequelizeQueryBuilder<M extends Model> {
  private model: Repository<M>
  private options: FindOptions<M>

  constructor(model: Repository<M>) {
    this.model = model
    this.options = { where: {}, attributes: { include: [], exclude: [] } }
  }

  select(columns: FindAttributeOptions): this {
    this.options.attributes = columns
    return this
  }

  where(condition: WhereOptions<M>): this {
    this.options.where = condition
    return this
  }

  andWhere(condition: WhereOptions<M>): this {
    if (!this.options.where) {
      this.options.where = {}
    }
    Object.assign(this.options.where, condition)
    return this
  }

  orWhere(condition: WhereOptions<M>): this {
    if (!this.options.where[Op.or]) {
      this.options.where[Op.or] = []
    }
    this.options.where[Op.or].push(condition)
    return this
  }

  orWhereIn<T = any>(field: keyof M, values: T[]): this {
    if (!this.options.where[Op.or]) {
      this.options.where[Op.or] = []
    }
    this.options.where[Op.or].push({ [field]: { [Op.in]: values } })
    return this
  }

  andWhereIn<T = any>(field: keyof M, values: T[]): this {
    this.options.where[String(field)] = { [Op.in]: values }
    return this
  }

  whereNot(condition: WhereOptions<M>): this {
    this.options.where[Op.not] = condition
    return this
  }

  whereNotIn<T = any>(field: keyof M, values: T[]): this {
    this.options.where[String(field)] = { [Op.notIn]: values }
    return this
  }

  whereIn<T = any>(field: keyof M, values: T[]): this {
    this.options.where[String(field)] = { [Op.in]: values }
    return this
  }

  orderBy(field: keyof M, direction: 'ASC' | 'DESC' = 'ASC'): this {
    this.options.order = [[String(field), direction]]
    return this
  }

  limit(limit: number): this {
    this.options.limit = limit
    return this
  }

  offset(offset: number): this {
    this.options.offset = offset
    return this
  }

  include(include: Includeable | Includeable[]): this {
    this.options.include = Array.isArray(include) ? include : [include]
    return this
  }

  join(options: IncludeOptions | IncludeOptions[], action: EPGRelationAction, alias?: string): this {
    if (Array.isArray(options)) {
      options = options.map((option: IncludeOptions) => {
        if (alias) {
          option.as = alias
        }

        if (action === EPGRelationAction.INNER) {
          option.required = true
        } else if (action === EPGRelationAction.LEFT) {
          option.required = false
        } else if (action === EPGRelationAction.RIGH) {
          option.required = true
          option.right = true
        }

        return option
      })
    } else {
      if (alias) {
        options.as = alias
      }

      if (action === EPGRelationAction.INNER) {
        options.required = true
      } else if (action === EPGRelationAction.LEFT) {
        options.required = false
      } else if (action === EPGRelationAction.RIGH) {
        options.required = true
        options.right = true
      }
    }

    this.include(options)

    return this
  }

  insert(values: MakeNullishOptional<M>, options?: CreateOptions): Promise<M> {
    return this.model.create(values, options)
  }

  update(values: Partial<M>, options?: UpdateOptions & { returning?: boolean | Array<keyof M> }): Promise<[number, M[]]> {
    return this.model.update(values, { ...options, where: this.options.where, returning: options?.returning || true })
  }

  delete(options?: DestroyOptions & { returning?: boolean | Array<keyof M> }): Promise<number> {
    return this.model.destroy({ ...options, where: this.options.where })
  }

  like(field: keyof M, value: string): this {
    this.options.where = sequelize.where(sequelize.literal(`${String(field)}::text`), {
      [Op.iLike]: `%${value}%`,
    })
    return this
  }

  orLike(field: keyof M, value: string): this {
    if (!this.options.where[Op.or]) {
      this.options.where[Op.or] = []
    }
    this.options.where[Op.or].push(
      sequelize.where(sequelize.literal(`${String(field)}::text`), {
        [Op.iLike]: `%${value}%`,
      }),
    )
    return this
  }

  andLike(field: keyof M, value: string): this {
    if (!this.options.where) {
      this.options.where = {}
    }
    this.options.where = sequelize.where(sequelize.literal(`${String(field)}::text`), {
      [Op.iLike]: `%${value}%`,
    })
    return this
  }

  getOne(): Promise<M | null> {
    return this.model.findOne({ ...this.options, raw: false })
  }

  getMany(): Promise<M[]> {
    return this.model.findAll({ ...this.options, raw: false })
  }

  getOneRaw(): Promise<M | null> {
    return this.model.findOne({ ...this.options, raw: true })
  }

  getManyRaw(): Promise<M[]> {
    return this.model.findAll({ ...this.options, raw: true })
  }

  getCount(): Promise<number> {
    delete this.options.attributes
    return this.model.count(this.options)
  }
}