System (5xxx)

• 5001 – masterdata (PIPE)
• 5002 – jobpost (JAY) เพิ่มแล้ว pg.provider.spec.ts
• 5003 – candidates (JAY) เพิ่มแล้ว pg.provider.spec.ts
• 5004 – users (PIPE)
• 5006 – jobapplication (JAY) เพิ่มแล้ว pg.provider.spec.ts
• 5007 – batch (JAY) เพิ่มแล้ว pg.provider.spec.ts
• 5011 – job-appointment (PIPE)
• 5012 – notification (GONG)
• 5013 – report (JAY) เพิ่มแล้ว pg.provider.spec.ts

แก้ query FROM, JOIN เป็น app.{table} ถ้าเป็น user auth.{table}¶

แก้ไฟล์ main.ts¶

import { NestFactory, Reflector } from "@nestjs/core";
import { AppModule } from "./app.module";
import { ResponseEnvelopeInterceptor } from "@shared/http/response-envelope.interceptor";
import { AllExceptionsFilter } from "@shared/http/all-exceptions.filter";
import { WINSTON_MODULE_NEST_PROVIDER } from "nest-winston";
import helmet from "helmet";
import { ValidationPipe, Logger, VersioningType } from "@nestjs/common";
import type { NestExpressApplication } from "@nestjs/platform-express";
import { AppEnv, ENV } from "../config/env";
import { Pool } from "pg";

export async function bootstrap(): Promise<void> {
  let logger: Logger | null = null;

  try {
    // Test Database Connection First
    await testDatabaseConnection();

    const app = await NestFactory.create<NestExpressApplication>(AppModule, {
      bufferLogs: true,
    });

    app.useLogger(app.get(WINSTON_MODULE_NEST_PROVIDER));
    logger = app.get<Logger>(WINSTON_MODULE_NEST_PROVIDER);

    const { PORT, APP_NAME, NODE_ENV, ERROR_PREFIX, CORS_ORIGIN } = AppEnv;

    const port: number = PORT;
    const appName: string = APP_NAME;
    const nodeEnv: string = NODE_ENV;
    const errorPrefix: string = ERROR_PREFIX;
    const corsOrigin: string = CORS_ORIGIN;
    const enumNodeEnv: ENV = nodeEnv.toLowerCase() as ENV;

    app.use(
      helmet({
        contentSecurityPolicy: enumNodeEnv === ENV.PROD ? undefined : false,
        crossOriginEmbedderPolicy: enumNodeEnv === ENV.PROD,
      }),
    );

    app.enableCors({
      origin: corsOrigin,
      credentials: true,
      methods: ["GET", "POST", "OPTIONS"],
      allowedHeaders: [
        "Content-Type",
        "Authorization",
        "sender",
        "refer",
        "forward",
        "sendDate",
        "clientId",
        "x-transaction-id",
        "x-request-timestamp",
      ],
    });

    app.set("trust proxy", 1);
    // Enable API versioning
    app.enableVersioning({
      type: VersioningType.URI,
      defaultVersion: "1",
      prefix: "v",
    });

    app.useGlobalPipes(
      new ValidationPipe({
        whitelist: true,
        forbidNonWhitelisted: true,
        transform: true,
        transformOptions: {
          enableImplicitConversion: true,
        },
      }),
    );

    const reflector = app.get(Reflector);
    app.useGlobalInterceptors(new ResponseEnvelopeInterceptor(reflector));
    app.useGlobalFilters(new AllExceptionsFilter(errorPrefix));
    app.enableShutdownHooks();

    await app.listen(port, "0.0.0.0");

    const appUrl = await app.getUrl();

    logger.log("=".repeat(50), "Bootstrap");
    logger.log(`Application: ${appName}`, "Bootstrap");
    logger.log(`Environment: ${nodeEnv}`, "Bootstrap");
    logger.log(`URL: ${appUrl}`, "Bootstrap");
    logger.log(`Error Prefix: ${errorPrefix}`, "Bootstrap");
    logger.log(`CORS Origin: ${corsOrigin}`, "Bootstrap");
    logger.log(`Database: Connected ✓`, "Bootstrap");
    logger.log(`Started at: ${new Date().toISOString()}`, "Bootstrap");
    logger.log("=".repeat(50), "Bootstrap");

    setupGracefulShutdown(app, logger);
  } catch (error) {
    if (logger) {
      if (error instanceof Error) {
        logger.error("Failed to start application", error.stack ?? "", {
          error: error.message,
        });
      } else {
        logger.error("Failed to start application", "", {
          error: String(error),
        });
      }
    } else if (error instanceof Error) {
      // If logger is not available, use console.error
      console.error("Failed to start application:", error.message);
    } else {
      console.error("Failed to start application:", String(error));
    }
    process.exit(1);
    return; // Return after exit to prevent further execution in tests
  }
}

export function setupGracefulShutdown(
  app: NestExpressApplication,
  logger: Logger,
): void {
  const signals: NodeJS.Signals[] = ["SIGTERM", "SIGINT", "SIGUSR2"];

  for (const signal of signals) {
    process.on(signal, () => {
      void (async () => {
        logger.warn(
          `Received ${signal}, starting graceful shutdown`,
          "Shutdown",
        );

        try {
          await app.close();
          logger.log("Application closed successfully", "Shutdown");
          process.exit(0);
        } catch (error) {
          if (error instanceof Error) {
            logger.error(
              "Error during shutdown",
              error.stack ?? "",
              "Shutdown",
            );
          } else {
            logger.error("Error during shutdown", "", "Shutdown");
          }
          process.exit(1);
        }
      })();
    });
  }

  process.on("uncaughtException", (error: Error) => {
    logger.error("Uncaught Exception", error.stack ?? "", {
      error: error.message,
    });
    process.exit(1);
  });

  process.on("unhandledRejection", (reason: unknown) => {
    const errorStack = reason instanceof Error ? reason.stack : undefined;
    const errorMessage =
      reason instanceof Error ? reason.message : String(reason);

    logger.error("Unhandled Promise Rejection", errorStack ?? "", {
      reason: errorMessage,
    });
    process.exit(1);
  });
}

async function testDatabaseConnection(): Promise<void> {
  const dbConfig = {
    host: AppEnv.DB_HOST,
    port: AppEnv.DB_PORT,
    user: AppEnv.DB_USERNAME,
    password: AppEnv.DB_PASSWORD,
    database: AppEnv.DB_NAME,
  };

  console.log("=".repeat(50));
  console.log("[DB TEST] Testing Database Connection...");
  console.log(`   Host: ${dbConfig.host}`);
  console.log(`   Port: ${dbConfig.port}`);
  console.log(`   Database: ${dbConfig.database}`);
  console.log(`   User: ${dbConfig.user}`);
  console.log("=".repeat(50));

  let pool: Pool | null = null;

  try {
    pool = new Pool(dbConfig);

    // Test connection
    const client = await pool.connect();

    // Run a simple query to verify connection
    const result = await client.query<{
      current_time: Date;
      pg_version: string;
    }>("SELECT NOW() as current_time, version() as pg_version");

    console.log("[SUCCESS] Database Connection Successful!");
    console.log(
      `   PostgreSQL Version: ${result.rows[0].pg_version.split(" ")[0] + " " + result.rows[0].pg_version.split(" ")[1]}`,
    );
    console.log(`   Server Time: ${String(result.rows[0].current_time)}`);
    console.log("=".repeat(50));

    client.release();
  } catch (error) {
    console.error("=".repeat(50));
    console.error("[ERROR] Database Connection Failed!");
    console.error("=".repeat(50));

    if (error instanceof Error) {
      console.error(`Error Type: ${error.name}`);
      console.error(`Error Message: ${error.message}`);

      // Provide helpful error messages based on error type
      if (error.message.includes("ECONNREFUSED")) {
        console.error("\n[INFO] Possible Causes:");
        console.error("   - Database server is not running");
        console.error("   - Wrong host or port");
        console.error("   - Firewall blocking connection");
      } else if (error.message.includes("password authentication failed")) {
        console.error("\n[INFO] Possible Causes:");
        console.error("   - Wrong username or password");
        console.error("   - User does not have access to the database");
      } else if (error.message.includes("does not exist")) {
        console.error("\n[INFO] Possible Causes:");
        console.error("   - Database does not exist");
        console.error("   - Wrong database name in configuration");
      } else if (error.message.includes("timeout")) {
        console.error("\n[INFO] Possible Causes:");
        console.error("   - Network connectivity issues");
        console.error("   - Database server is overloaded");
        console.error("   - Connection timeout settings too low");
      }

      console.error("\n[CONFIG] Configuration:");
      console.error(`   DB_HOST: ${dbConfig.host}`);
      console.error(`   DB_PORT: ${dbConfig.port}`);
      console.error(`   DB_NAME: ${dbConfig.database}`);
      console.error(`   DB_USERNAME: ${dbConfig.user}`);
      console.error("=".repeat(50));
    }

    throw error; // Re-throw to stop application startup
  } finally {
    // Clean up the test pool
    if (pool) {
      await pool.end();
    }
  }
}

// Only run bootstrap when not in test environment
/* istanbul ignore next */
if (
  process.env.NODE_ENV !== "test" &&
  process.env.JEST_WORKER_ID === undefined
) {
  void bootstrap(); // NOSONAR - Cannot use top-level await in CommonJS module
}

pg.provider.spec.ts

import { Test, TestingModule } from "@nestjs/testing";

const mockPool = {
  query: jest.fn(),
  connect: jest.fn(),
  end: jest.fn(),
};

const mockSetTypeParser = jest.fn();

jest.mock("pg", () => ({
  Pool: jest.fn(() => mockPool),
  types: {
    setTypeParser: mockSetTypeParser,
  },
}));

process.env.NODE_ENV = "dev";

jest.mock("../../../config/env", () => ({
  AppEnv: {
    DB_HOST: "localhost",
    DB_PORT: 5432,
    DB_USERNAME: "test",
    DB_PASSWORD: "test",
    DB_NAME: "testdb",
  },
}));

import { DatabaseModule } from "./pg.provider";
import { AppEnv } from "../../../config/env";

// Import Pool after mocking
import { Pool } from "pg";

describe("DatabaseModule", () => {
  let module: TestingModule;

  beforeEach(async () => {
    // Don't clear mockSetTypeParser because it's already been called during module import
    mockPool.query.mockClear();
    mockPool.connect.mockClear();
    mockPool.end.mockClear();

    module = await Test.createTestingModule({
      imports: [DatabaseModule],
    }).compile();
  });

  it("should be defined", () => {
    expect(module).toBeDefined();
  });

  it("should provide PG_POOL", () => {
    const pool = module.get<typeof mockPool>("PG_POOL");
    expect(pool).toBeDefined();
  });

  it("should create pool with correct configuration", () => {
    module.get("PG_POOL");
    expect(Pool).toHaveBeenCalledWith({
      host: AppEnv.DB_HOST,
      port: AppEnv.DB_PORT,
      user: AppEnv.DB_USERNAME,
      password: AppEnv.DB_PASSWORD,
      database: AppEnv.DB_NAME,
    });
  });

  it("should set type parser for bigint (20)", () => {
    expect(mockSetTypeParser).toHaveBeenCalledWith(20, expect.any(Function));
    const calls = mockSetTypeParser.mock.calls as Array<
      [number, (value: string | null) => number | null]
    >;
    const bigintCall = calls.find((call) => call[0] === 20);
    expect(bigintCall).toBeDefined();

    if (bigintCall) {
      const parser = bigintCall[1];
      expect(parser(null)).toBeNull();
      expect(parser("12345")).toBe(12345);
    }
  });

  it("should set type parser for integer (23)", () => {
    expect(mockSetTypeParser).toHaveBeenCalledWith(23, expect.any(Function));
    const calls = mockSetTypeParser.mock.calls as Array<
      [number, (value: string | null) => number | null]
    >;
    const intCall = calls.find((call) => call[0] === 23);
    expect(intCall).toBeDefined();

    if (intCall) {
      const parser = intCall[1];
      expect(parser(null)).toBeNull();
      expect(parser("456")).toBe(456);
    }
  });

  it("should set type parser for date (1082)", () => {
    expect(mockSetTypeParser).toHaveBeenCalledWith(1082, expect.any(Function));
    const calls = mockSetTypeParser.mock.calls as Array<
      [number, (value: string) => string]
    >;
    const dateCall = calls.find((call) => call[0] === 1082);
    expect(dateCall).toBeDefined();

    if (dateCall) {
      const parser = dateCall[1];
      expect(parser("2024-01-01")).toBe("2024-01-01");
    }
  });

  it("should set type parser for boolean (16)", () => {
    expect(mockSetTypeParser).toHaveBeenCalledWith(16, expect.any(Function));
    const calls = mockSetTypeParser.mock.calls as Array<
      [number, (value: string | null) => boolean]
    >;
    const boolCall = calls.find((call) => call[0] === 16);
    expect(boolCall).toBeDefined();

    if (boolCall) {
      const parser = boolCall[1];
      expect(parser("t")).toBe(true);
      expect(parser("f")).toBe(false);
      expect(parser(null)).toBe(false);
    }
  });
});