Generate TypeORM QueryBuilder Script

This script automates the generation of TypeORM QueryBuilder classes and corresponding DTOs for NestJS modules based on entity files. It parses the entity using ts-morph, extracts columns, relations, and indices, and produces boilerplate code for efficient database querying with filters, joins, and pagination support.

What It Does

The script:

• Parses the Entity: Reads the TypeORM entity file (e.g., booking.entity.ts) to identify:
  • Columns (@Column, @PrimaryGeneratedColumn, @CreateDateColumn, @UpdateDateColumn) and their types for generating filters (e.g., LIKE for strings, = for numbers/booleans, range queries for dates).
  • Relations (@ManyToOne for always-joined relations; @OneToMany for conditional includes via flags like includeProduct).
  • Indices (@Index) to inspire basic filters (multi-field indices use individual column filters; customize composites manually).
• Generates DTO: Creates a filter-{moduleName}.dto.ts interface with optional fields for all columns (plus Desde/Hasta for dates) and relation IDs/flags.
• Generates QueryBuilder: Produces {moduleName}Query.query.ts with:
  • Base QueryBuilder setup with left joins for ManyToOne relations.
  • Conditional joins for OneToMany (e.g., if (filters.includeProduct) { ... }).
  • Dynamic andWhere clauses for filters, using dayjs for date ranges and normalst for string normalization.
  • Default ordering by id DESC.
• Handles NestJS Structure: Assumes execution from project root, targets src/{moduleName}/, and adjusts imports (e.g., ../entities/ if the folder exists).

Example: For a booking entity (in src/booking/entities/booking.entity.ts), it generates a QueryBuilder similar to the provided sample, with filters for numbooking, date ranges for checkInDate, and conditional joins for product.

Limitations:

• Joins for OneToMany are basic; add deep/nested joins (e.g., product.supplier) manually in the generated code.
• Multi-field index queries (e.g., composite WHERE) are not auto-generated; use individual filters.
• Assumes standard TypeORM decorators; custom transformers (e.g., alias with normalst) are handled in filters.
• Booleans are treated as 0/1 in DB queries.

Prerequisites

• Node.js >= 18
• NestJS project with TypeORM entities in src/{moduleName}/entities/ (or root of module).
• dayjs and @/utils/common (for normalst) must be available in your project.

Installation

The script auto-installs ts-morph (dev dependency) on first run. No manual setup needed beyond placing the script.

1. Save the script as scripts/generate-query-builder.js in your project root.
2. Add to package.json for easy execution:

{
  "scripts": {
    "generate:qb": "node scripts/generate-query-builder.js"
  },
  "bin": {
    "generate-qb": "scripts/generate-query-builder.js"
  }
}

Usage

Execute from the project root:

npx generate-qb
# Or: npm run generate:qb

1. Prompt: Enter the module name (e.g., booking).
2. Auto-Detection: Script checks for entity at:
   • src/{moduleName}/entities/{moduleName}.entity.ts (preferred).
   • src/{moduleName}/{moduleName}.entity.ts (fallback).
3. Generation: Creates folders/files if needed:
   • src/{moduleName}/dto/filter-{moduleName}.dto.ts
   • src/{moduleName}/queryBuilders/{moduleName}Query.query.ts
4. Output: Console logs paths and reminders to review/customize (e.g., deep joins).

Example Run:

$ npx generate-qb
Ingrese el nombre del módulo (ej. "booking"): booking
Usando entidad: src/booking/entities/booking.entity.ts
¡Generado exitosamente en src/booking/!
- DTO: src/booking/dto/filter-booking.dto.ts
- QueryBuilder: src/booking/queryBuilders/bookingQuery.query.ts
Revisa y ajusta joins profundos o filtros especiales (ej. documento en cliente).

Generated Files Example

DTO (filter-booking.dto.ts)

import { IsOptional } from 'class-validator'; // Opcional para validación

export interface SearchReservaDto {
  id?: number;
  number?: string;
  numbooking?: string;
  // ... other columns
  checkInDate?: string;
  checkInDateDesde?: string;
  checkInDateHasta?: string;
  clientId?: number;
  includeProduct?: boolean;
  // ...
}

QueryBuilder (bookingQuery.query.ts)

import { Injectable } from "@nestjs/common";
import { EntityManager, SelectQueryBuilder } from "typeorm";
import { Reserva } from "../entities/booking.entity";
import { SearchReservaDto } from "../dto/filter-booking.dto";
import dayjs from "dayjs";
import { normalst } from "@/utils/common";

@Injectable()
export class ReservaQueryBuilder {
  constructor(private readonly manager: EntityManager) {}

  searchReservas(filters: SearchReservaDto): SelectQueryBuilder<Reserva> {
    let queryBuilder = this.manager
      .getRepository(Reserva)
      .createQueryBuilder("reserva")
      .leftJoinAndSelect("reserva.cliente", "cliente")
      .leftJoinAndSelect("reserva.proveedor", "proveedor")
      // ... other ManyToOne joins
      .orderBy("reserva.id", "DESC");

    if (filters.includeProductos) {
      queryBuilder = queryBuilder.leftJoinAndSelect("reserva.productos", "productos");
      // Agrega joins profundos manualmente aquí si es necesario
    }
    // ... conditional OneToMany joins

    if (filters.nroreserva) {
      queryBuilder = queryBuilder.andWhere("reserva.nroreserva LIKE :nroreserva", {
        nroreserva: `%${normalst(filters.nroreserva)}%`,
      });
    }
    // ... other filters (dates with dayjs ranges, etc.)

    if (filters.id) {
      queryBuilder = queryBuilder.andWhere("reserva.id = :id", { id: Number(filters.id) });
    }

    return queryBuilder;
  }
}

Customization

• Entity File Name: If not {moduleName}.entity.ts, rename or modify the script's path logic.
• Advanced Joins/Filters: Edit the generated file (e.g., add leftJoinAndSelect("productos.proveedor", "prov") inside conditionals).
• DTO Validation: Add @IsOptional() decorators manually if using class-validator.
• Date Formats: Assumes YYYY-MM-DD; adjust dayjs parsing if needed.
• String Normalization: Relies on normalst from @/utils/common; ensure it's imported.

Troubleshooting

• Entity Not Found: Verify path/module name; script logs checked paths.
• Parse Errors: Ensure entity uses standard TypeORM decorators; check console for ts-morph issues.
• Missing Dependencies: Script auto-installs ts-morph; run npm install after if needed.

License

MIT License - Feel free to use and modify. Contributions welcome!

Generated for NestJS/TypeORM projects. Last updated: November 10, 2025.