NestJS Report Library

A comprehensive, enterprise-ready NestJS library for dynamic report generation and management. This library provides a flexible, database-driven approach to creating, configuring, and executing reports with customizable parameters and output formats.

Table of Contents

• Overview
• Features
• Prerequisites
• Installation
• Database Setup
• Configuration
• Usage
• API Reference
• Examples
• Contributing
• License

Overview

The NestJS Report Library enables developers to build dynamic reporting systems where report configurations, parameters, and result mappings are stored in a database. This approach allows for runtime report creation and modification without code changes, making it ideal for applications requiring flexible reporting capabilities.

Features

• Dynamic Report Configuration: Database-driven report definitions
• Flexible Parameter System: Support for various input types and validation
• Customizable Output Mapping: Define how query results are presented
• Multiple Export Formats: Generate reports in various formats
• Enterprise Integration: Seamless integration with existing NestJS applications
• TypeORM Integration: Built-in support for TypeORM entities
• Extensible Architecture: Easy to extend and customize
• RESTful API: Ready-to-use REST endpoints for report operations

Prerequisites

• Node.js >= 16.x
• NestJS >= 9.x
• TypeORM >= 0.3.x
• PostgreSQL >= 12.x (or compatible database)

Installation

Install the package using npm:

npm install nestjs-report-lib

Database Setup

The library requires three main tables to store report configurations. Execute the following SQL scripts in your PostgreSQL database:

1. Report Table

This Table is responsible for defining the reports like What is report name and what it will do.

CREATE TABLE IF NOT EXISTS report (
    id          serial PRIMARY KEY,
    name        varchar(255),
    label       varchar(255),
    end_point   varchar(255),
    query       text,
    created_at  timestamp WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at  timestamp WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    deleted_at  timestamp WITHOUT TIME ZONE,
    created_by  varchar(255),
    updated_by  varchar(255),
    deleted_by  varchar(255),
    order_no    integer
);

2. Report Parameter Table

This Table is responsible for defining the parameters / filter like What is the filter required for and .

CREATE TABLE IF NOT EXISTS report_parameter (
    id               serial PRIMARY KEY,
    report_id         integer,
    parameter_name    varchar(255),
    label             varchar(255),
    data_type         varchar(100),
    created_at        timestamp WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at        timestamp WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    deleted_at        timestamp WITHOUT TIME ZONE,
    created_by        varchar(255),
    updated_by        varchar(255),
    deleted_by        varchar(255),
    query_parameter   varchar(255),
    input_field_type  varchar(100),
    option_type       varchar(100),
    option            jsonb,
    order_no          integer,
    CONSTRAINT report_parameter_report_id_fkey 
        FOREIGN KEY (report_id)
        REFERENCES public.report (id)
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

3. Report Result Mapping Table

CREATE TABLE IF NOT EXISTS public.report_result_mapping (
    id                    serial PRIMARY KEY,
    report_id              integer,
    query_parameter_name   varchar(255),
    variable_name          varchar(255),
    label                  varchar(255),
    data_type              varchar(100),
    alignment              varchar(200),
    created_at             timestamp WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at             timestamp WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    deleted_at             timestamp WITHOUT TIME ZONE,
    created_by             varchar(255),
    updated_by             varchar(255),
    deleted_by             varchar(255),
    CONSTRAINT report_result_mapping_report_id_fkey 
        FOREIGN KEY (report_id)
        REFERENCES public.report (id)
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

Configuration

Using Default Entities

For quick setup with default entity configurations:

For Default Entities Defination:

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { ReportModule, entities } from 'nestjs-report-lib';
import { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    ReportModule.register(),,
    TypeOrmModule.forRoot({
      type: 'Type',
      host: 'HOST',
      port: 'PORT',
      username: 'YOUR_USERNAME',
      password: 'YOUR_PASSWORD',
      database: 'YOUR_DATABASE_NAME',
      entities: entities,
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Using Custom Entities

For advanced scenarios where you need to customize the entities:

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { ReportModule } from 'nestjs-report-lib';
import { TypeOrmModule } from '@nestjs/typeorm';
import { entities, Report, ReportParameter } from './entities';

@Module({
  imports: [
    ReportModule.register([Report, ReportParameter]),,
    TypeOrmModule.forRoot({
      type: 'Type',
      host: 'HOST',
      port: 'PORT',
      username: 'YOUR_USERNAME',
      password: 'YOUR_PASSWORD',
      database: 'YOUR_DATABASE_NAME',
      entities: entities,
    }),
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Usage

Create Two Basic Tables

CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO customers (name, email, status, created_at) VALUES
('Alice Johnson', '[email protected]', 'active', '2025-01-10'),
('Bob Smith', '[email protected]', 'inactive', '2025-02-15'),
('Charlie Brown', '[email protected]', 'active', '2025-03-20'),
('Diana Prince', '[email protected]', 'active', '2025-04-05'),
('Ethan Hunt', '[email protected]', 'inactive', '2025-05-12');

CREATE TABLE IF NOT EXISTS sales (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    region VARCHAR(100),
    amount DECIMAL(10,2),
    date DATE DEFAULT CURRENT_DATE
);
INSERT INTO sales (customer_id, region, amount, date) VALUES
(1, 'North', 1200.50, '2025-07-01'),
(1, 'North', 800.00, '2025-07-15'),
(2, 'South', 500.00, '2025-06-20'),
(3, 'East', 1500.75, '2025-07-10'),
(4, 'West', 2000.00, '2025-07-18'),
(4, 'West', 300.00, '2025-06-28'),
(5, 'North', 750.25, '2025-05-30');

Basic Report Creation

1. Insert a Report Configuration:

INSERT INTO report (
    name, label, end_point, query, created_by, updated_by, order_no
) VALUES 
('sales_summary', 'Sales Summary Report', '/reports/sales-summary',
 'SELECT region, SUM(amount) AS total_sales FROM sales WHERE date BETWEEN :start_date AND :end_date GROUP BY region',
 'admin', 'admin', 1),
('customer_list', 'Customer List Report', '/reports/customers',
 'SELECT id, name, email, created_at FROM customers WHERE status = :status',
 'admin', 'admin', 2);

2. Define Report Parameters:

INSERT INTO report_parameter (
    report_id, parameter_name, label, data_type, created_by, updated_by, 
    query_parameter, input_field_type, option_type, option, order_no
) VALUES
(1, 'start_date', 'Start Date', 'date', 'admin', 'admin', 
 ':start_date', 'date-picker', 'static', NULL, 1),
(1, 'end_date', 'End Date', 'date', 'admin', 'admin', 
 ':end_date', 'date-picker', 'static', NULL, 2),
(2, 'status', 'Customer Status', 'string', 'admin', 'admin', 
 ':status', 'dropdown', 'static', 
 '[{"label":"Active","value":"active"},{"label":"Inactive","value":"inactive"}]', 1);

3. Configure Result Mapping:

INSERT INTO report_result_mapping (
    report_id, query_parameter_name, variable_name, label, data_type, alignment, 
    created_by, updated_by
) VALUES
(1, 'region', 'region', 'Region', 'string', 'left', 'admin', 'admin'),
(1, 'total_sales', 'totalSales', 'Total Sales', 'number', 'right', 'admin', 'admin'),
(2, 'name', 'customerName', 'Customer Name', 'string', 'left', 'admin', 'admin'),
(2, 'email', 'customerEmail', 'Email', 'string', 'left', 'admin', 'admin');

API Reference

The library exposes the following REST endpoints:

Get All Reports

GET /report/reports_list

Response:

{
    "statusCode": 200,
    "message": "Reports fetched successfully",
    "data": {
        "reportList": [
            {
                "id": 1,
                "name": "sales_summary",
                "label": "Sales Summary Report"
            },
            {
                "id": 2,
                "name": "customer_list",
                "label": "Customer List Report"
            }
        ]
    }
}

Get Report Details

GET /report/:id

Parameters:

• id (number): Report ID

Response:

{
  "success": true,
  "data": {
    "id": 1,
    "name": "user-analytics",
    "label": "User Analytics Report",
    "description": "Comprehensive user analytics with order statistics",
    "parameters": [...],
    "resultMapping": [...]
  }
}

Generate Report

POST /generate/:report

Parameters:

• report (string): Report name or endpoint

Request Body:

{
  "start_date": "2024-01-01",
  "end_date": "2024-12-31",
  "page": 1,
  "limit": 50
}

Response:

{
  "success": true,
  "data": {
    "results": [
      {
        "user_id": 1,
        "user_name": "John Doe",
        "user_email": "[email protected]",
        "registration_date": "2024-01-15T10:30:00Z",
        "total_orders": 25
      }
    ],
    "metadata": {
      "total": 150,
      "page": 1,
      "limit": 50,
      "totalPages": 3
    }
  }
}

Download Report

POST /download/:report

Parameters:

• report (string): Report name or endpoint

Request Body:

{
  "start_date": "2024-01-01",
  "end_date": "2024-12-31",
}

Response: File download (Excel, CSV, or PDF)

Examples

Advanced Parameter Configuration

-- Select dropdown with options
INSERT INTO report_parameter (
  report_id, parameter_name, label, data_type, 
  query_parameter, input_field_type, option_type, option
) VALUES (
  1, 'status', 'User Status', 'string', 
  '$3', 'select', 'raw', 
  '{
  "data": [{"label": "All","value": "#99#"},{"label": "Reportees","value": "Reportees"},{"label": "Higher Ups","value": "Higher Ups"}]}'::jsonb
);

-- Dynamic dropdown from database
INSERT INTO report_parameter (
    report_id, parameter_name, label, data_type, created_by, updated_by, 
    query_parameter, input_field_type, option_type, option, order_no
) VALUES
(2, 'status', 'Customer Status', 'string', 'admin', 'admin', 
 ':status', 'dropdown', 'query', 
 '{"raw_query": "select * from (select ''All'' as label, ''#99#'' as value, 0 as sort_order union select distinct from_reference_status as label, from_reference_status as value, 1 as sort_order from public.vr_user_hierarchy) as t order by sort_order, label"}', 1);

NOTE : FOR ALL THE DROPDOWN VALUE USE '#99#'

Configuration Options In Tables

Report Table Columns

| Column | Table | Description | Options | |--------|-------|-------------|---------| | name | report | Unique identifier for the report used in API calls | Any string (alphanumeric with hyphens/underscores recommended) | | label | report | Display name shown in the UI | Any string | | end_point | report | API endpoint path for accessing the report | Any string (URL-safe characters recommended) | | query | report | SQL query to execute for generating the report | Valid SQL query with parameter placeholders ($1, $2, etc.) | | order_no | report | Display order for sorting reports in lists | Any integer |

Report Parameter Table Columns

| Column | Table | Description | Options | |--------|-------|-------------|---------| | report_id | report_parameter | Reference to the parent report | Valid report ID (foreign key) | | parameter_name | report_parameter | Internal name for the parameter used in code | Any string (camelCase or snake_case recommended) | | label | report_parameter | Display name shown in the UI for the parameter | Any string | | data_type | report_parameter | Data type of the parameter for validation | string, integer, decimal, date, datetime, boolean | | query_parameter | report_parameter | Placeholder in SQL query ($1, $2, etc.) | $1, $2, $3, etc. | | input_field_type | report_parameter | UI input field type for parameter entry | text, number, date, datetime-local, select, checkbox, radio, textarea | | option_type | report_parameter | Type of options for select/radio fields | static, dynamic, api, null | | option | report_parameter | Configuration for select/radio field options | JSON object or array (see examples below) | | order_no | report_parameter | Display order for parameters in forms | Any integer |

Report Result Mapping Table Columns

| Column | Table | Description | Options | |--------|-------|-------------|---------| | report_id | report_result_mapping | Reference to the parent report | Valid report ID (foreign key) | | query_parameter_name | report_result_mapping | Column name from SQL query result | Column name as returned by SQL query | | variable_name | report_result_mapping | Internal variable name for API response | Any string (camelCase recommended) | | label | report_result_mapping | Display name shown in UI (table headers, etc.) | Any string | | data_type | report_result_mapping | Data type for formatting and display | string, integer, decimal, date, datetime, boolean, currency | | alignment | report_result_mapping | Text alignment in UI tables | left, center, right |

Error Handling

The library provides comprehensive error handling with detailed error messages:

try {
  const result = await reportService.generateReport('invalid-report', {});
} catch (error) {
  if (error instanceof ReportNotFoundError) {
    // Handle report not found
  } else if (error instanceof InvalidParameterError) {
    // Handle invalid parameters
  } else if (error instanceof DatabaseError) {
    // Handle database errors
  }
}

Performance Considerations

• Use database indexes on frequently queried columns
• Implement pagination for large result sets
• Consider caching for frequently accessed reports
• Optimize SQL queries in report configurations
• Monitor database performance and query execution times

Security Best Practices

• Validate all input parameters
• Use parameterized queries to prevent SQL injection
• Implement proper authentication and authorization
• Audit report generation activities
• Restrict database permissions for the report user
• Sanitize output data if necessary

Contributing

We welcome contributions! Please see our Contributing Guide for details.

License

This project is licensed under the MIT License - see the LICENSE file for details.

Support

For support and questions:

• GitHub Issues: Report bugs or request features
• Documentation: Full API documentation
• Community: Join our Discord server

Version: 1.0.0
Last Updated: August 2025