src/utils/database.ts - master - foult080/college-schedule-app

src/utils/database.ts
254 строки9 KB
Foult080
feat(ai): updates from kimi
06 июл 2026, 19:22
06 июл 2026, 19:22f578858
import { Pool, PoolConfig, QueryResult, QueryResultRow } from 'pg';

const poolConfig: PoolConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '5432', 10),
  database: process.env.DB_NAME || 'students_sheldue',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || 'postgres',
  max: parseInt(process.env.DB_POOL_MAX || '10', 10),
  idleTimeoutMillis: parseInt(process.env.DB_IDLE_TIMEOUT || '30000', 10),
  connectionTimeoutMillis: parseInt(process.env.DB_CONNECTION_TIMEOUT || '5000', 10),
};

export const pool = new Pool(poolConfig);

/**
 * Run SQL query with params
 * @param query SQL query with parameters ($1, $2, ...)
 * @param params Array of parameter values
 */
export async function query<T extends QueryResultRow = Record<string, unknown>>(query: string, params?: unknown[]): Promise<QueryResult<T>> {
  return pool.query<T>(query, params);
}

export async function checkDatabaseConnection(): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query('SELECT 1');
  } finally {
    client.release();
  }
}

export async function closeDatabaseConnection(): Promise<void> {
  await pool.end();
}

/**
 * Database helper class for CRUD operations with automatic query generation
 * Provides safe, parameterized queries to prevent SQL injection
 */
export class DatabaseHelper {
  /**
   * Escape identifier (table/column name) to prevent SQL injection
   */
  private static escapeIdentifier(name: string): string {
    if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(name)) {
      throw new Error(`Invalid identifier name: "${name}"`);
    }
    return `"${name}"`;
  }

  /**
   * Build parameterized SELECT query
   * @param table Table name
   * @param fields Fields to select (default: *)
   * @param where Where clause conditions
   * @param orderBy Order by clause
   * @param limit Limit results
   * @param offset Offset for pagination
   */
  private static buildSelectQuery(
    table: string,
    fields: string[] = ['*'],
    where?: Record<string, unknown>,
    orderBy?: Record<string, 'ASC' | 'DESC'>,
    limit?: number,
    offset?: number,
  ): { query: string; params: unknown[] } {
    const escapedFields = fields.map((f) => (f === '*' ? '*' : this.escapeIdentifier(f))).join(', ');
    const escapedTable = this.escapeIdentifier(table);

    let sql = `SELECT ${escapedFields} FROM ${escapedTable}`;
    const params: unknown[] = [];

    if (where && Object.keys(where).length > 0) {
      const whereClauses: string[] = [];
      Object.entries(where).forEach(([key, value], _index) => {
        if (value === null) {
          whereClauses.push(`${this.escapeIdentifier(key)} IS NULL`);
        } else {
          params.push(value);
          whereClauses.push(`${this.escapeIdentifier(key)} = $${_index + 1}`);
        }
      });
      sql += ` WHERE ${whereClauses.join(' AND ')}`;
    }

    if (orderBy && Object.keys(orderBy).length > 0) {
      const orderClauses = Object.entries(orderBy).map(([key, dir]) => `${this.escapeIdentifier(key)} ${dir}`);
      sql += ` ORDER BY ${orderClauses.join(', ')}`;
    }

    if (limit !== undefined) {
      params.push(limit);
      sql += ` LIMIT $${params.length}`;
    }

    if (offset !== undefined) {
      params.push(offset);
      sql += ` OFFSET $${params.length}`;
    }

    return { query: sql, params };
  }

  /**
   * Build parameterized INSERT query
   * @param table Table name
   * @param data Object with column-value pairs
   * @param returning Fields to return (default: *)
   */
  private static buildInsertQuery(table: string, data: Record<string, unknown>, returning: string[] = ['*']): { query: string; params: unknown[] } {
    const keys = Object.keys(data);
    const values = keys.map((_, index) => `$${index + 1}`);
    const escapedTable = this.escapeIdentifier(table);
    const escapedKeys = keys.map((key) => this.escapeIdentifier(key));
    const escapedReturning = returning.map((f) => (f === '*' ? '*' : this.escapeIdentifier(f))).join(', ');

    const sql = `INSERT INTO ${escapedTable} (${escapedKeys.join(', ')}) VALUES (${values.join(', ')}) RETURNING ${escapedReturning}`;

    return { query: sql, params: Object.values(data) };
  }

  /**
   * Build parameterized UPDATE query
   * @param table Table name
   * @param data Object with column-value pairs to update
   * @param where Where clause conditions
   * @param returning Fields to return (default: *)
   */
  private static buildUpdateQuery(
    table: string,
    data: Record<string, unknown>,
    where: Record<string, unknown>,
    returning: string[] = ['*'],
  ): { query: string; params: unknown[] } {
    const keys = Object.keys(data);
    const setClauses: string[] = [];
    const params: unknown[] = [];

    keys.forEach((key, index) => {
      setClauses.push(`${this.escapeIdentifier(key)} = $${index + 1}`);
      params.push(data[key]);
    });

    const escapedTable = this.escapeIdentifier(table);
    const escapedReturning = returning.map((f) => (f === '*' ? '*' : this.escapeIdentifier(f))).join(', ');

    let sql = `UPDATE ${escapedTable} SET ${setClauses.join(', ')}`;

    if (Object.keys(where).length > 0) {
      const whereClauses: string[] = [];
      Object.entries(where).forEach(([key, value], _index) => {
        const paramIndex = params.length + 1;
        if (value === null) {
          whereClauses.push(`${this.escapeIdentifier(key)} IS NULL`);
        } else {
          params.push(value);
          whereClauses.push(`${this.escapeIdentifier(key)} = $${paramIndex}`);
        }
      });
      sql += ` WHERE ${whereClauses.join(' AND ')}`;
    }

    sql += ` RETURNING ${escapedReturning}`;

    return { query: sql, params };
  }

  /**
   * Build parameterized DELETE query
   * @param table Table name
   * @param where Where clause conditions
   * @param returning Fields to return (default: *)
   */
  private static buildDeleteQuery(table: string, where: Record<string, unknown>, returning: string[] = []): { query: string; params: unknown[] } {
    const escapedTable = this.escapeIdentifier(table);

    let sql = `DELETE FROM ${escapedTable}`;
    const params: unknown[] = [];

    if (Object.keys(where).length > 0) {
      const whereClauses: string[] = [];
      Object.entries(where).forEach(([key, value], _index) => {
        const paramIndex = params.length + 1;
        if (value === null) {
          whereClauses.push(`${this.escapeIdentifier(key)} IS NULL`);
        } else {
          params.push(value);
          whereClauses.push(`${this.escapeIdentifier(key)} = $${paramIndex}`);
        }
      });
      sql += ` WHERE ${whereClauses.join(' AND ')}`;
    }

    if (returning.length > 0) {
      const escapedReturning = returning.map((f) => this.escapeIdentifier(f)).join(', ');
      sql += ` RETURNING ${escapedReturning}`;
    }

    return { query: sql, params };
  }

  /**
   * SELECT - Fetch data from table
   * @param table Table name
   * @param fields Fields to select
   * @param where Where clause conditions
   */
  static async select<T extends QueryResultRow = Record<string, unknown>>(table: string, fields?: string[], where?: Record<string, unknown>): Promise<QueryResult<T>> {
    const { query: sql, params } = this.buildSelectQuery(table, fields, where);
    return pool.query<T>(sql, params);
  }

  /**
   * INSERT - Insert data into table
   * @param table Table name
   * @param data Object with column-value pairs
   * @param returning Fields to return
   */
  static async insert<T extends QueryResultRow = Record<string, unknown>>(table: string, data: Record<string, unknown>, returning?: string[]): Promise<QueryResult<T>> {
    const { query: sql, params } = this.buildInsertQuery(table, data, returning);
    return pool.query<T>(sql, params);
  }

  /**
   * UPDATE - Update data in table
   * @param table Table name
   * @param data Object with column-value pairs to update
   * @param where Where clause conditions
   * @param returning Fields to return
   */
  static async update<T extends QueryResultRow = Record<string, unknown>>(
    table: string,
    data: Record<string, unknown>,
    where: Record<string, unknown>,
    returning?: string[],
  ): Promise<QueryResult<T>> {
    const { query: sql, params } = this.buildUpdateQuery(table, data, where, returning);
    return pool.query<T>(sql, params);
  }

  /**
   * DELETE - Delete data from table
   * @param table Table name
   * @param where Where clause conditions
   * @param returning Fields to return
   */
  static async delete<T extends QueryResultRow = Record<string, unknown>>(table: string, where: Record<string, unknown>, returning?: string[]): Promise<QueryResult<T>> {
    const { query: sql, params } = this.buildDeleteQuery(table, where, returning);
    return pool.query<T>(sql, params);
  }
}