使用nodejs封装pg数据库操作类

首先是源代码

下文是TypeScipt的源代码，如果想要JavaScript的源码，点击下载。

import pg from 'pg';
interface ConnectError {
  (result: Result): void;
}
interface ConnectSuccessParams {
  err: Error;
  client: pg.PoolClient;
}
interface ConnectSuccess {
  (obj: ConnectSuccessParams): void;
}

function getMatchSql({ compare, arrayIn, contains }: PgUtilMatchParams) {
  let sqlStr = '';
  let needAnd = false;
  if ((compare && compare.length) || (arrayIn && arrayIn.length) || (contains && contains.length)) {
    sqlStr += ' WHERE';
  }
  if (compare && compare.length) {
    sqlStr += compare
      .map((item) => ` "${item.key}" ${item.operator} '${item.value}'`)
      .join(' AND ');
    needAnd = true;
  }
  if (arrayIn) {
    sqlStr +=
      `${needAnd ? ' AND ' : ''} ` +
      arrayIn.map((item) => {
        return ` "${item.key} IN (${item.values.map((value) => ` "${value}" `).join(',')})"`;
      });
    needAnd = true;
  }
  if (contains) {
    sqlStr +=
      `${needAnd ? ' AND ' : ''} ` +
      contains.map((item) => ` "${item.key}" LIKE '%${item.value}%'`).join(',');
  }
  return sqlStr;
}
export class PgUtil {
  private pgConfig: pg.PoolConfig = {
    user: 'postgres', // 数据库用户名
    database: 'postgres', // 数据库
    password: 'postgres', // 数据库密码
    host: 'xxx.xxx.xxx.xx',
    port: 1111,
  };
  private pool: pg.Pool;
  private status: 'ready' | 'connect' | 'disconnect' | 'end';
  public done: ((release?: any) => void) | undefined;
  public client!: pg.PoolClient;
  constructor(pgConfig) {
    this.pool = new pg.Pool(this.pgConfig);
    this.pgConfig = pgConfig;
    this.status = 'ready';
  }
  /**
   * 连接数据库
   */
  connect() {
    return new Promise((resolve: ConnectSuccess, reject: ConnectError) => {
      this.pool.connect((err, client, done) => {
        if (err) {
          const result: Result = {
            code: '9999',
            message: err.message,
          };
          this.status = 'end';
          reject(result);
        } else {
          this.client = client;
          this.done = done;
          this.status = 'connect';
          resolve({ err, client });
        }
      });
    });
  }
  /**
   * 断开连接
   */
  disconnnect(): void {
    if (this.status === 'connect' && this.done && typeof this.done === 'function') {
      this.done();
    }
  }
  /**
   *
   * @param sqlStr sql语句
   * @param args SQL语句设计参数列表
   * @param autoDisconnect 查询结束是否自动关闭连接池
   */
  clientQeury(
    sqlStr: string,
    args: Array<any>,
    autoDisconnect?: Boolean
  ): Promise<pg.QueryResult<any>> {
    return new Promise((resolve, reject) => {
      if (this.status !== 'connect') {
        const result: Result = {
          code: '9999',
          message: '数据库未连接',
        };
        reject(result);
        return;
      }
      console.log(sqlStr);
      this.client.query(sqlStr, args, (err, res) => {
        if (autoDisconnect) {
          this.disconnnect();
        }
        if (err) {
          const result: Result = {
            code: '9999',
            message: err.message,
          };
          reject(result);
        } else {
          resolve(res);
        }
      });
    });
  }
  /**
   * 插入数据库表
   * @param param0 SaveParams
   */
  save(obj: SaveParams): Promise<pg.QueryResult<any>> {
    const { tableName, filelds, autoDisconnect }: SaveParams = obj;
    const values = Object.values(filelds);

    let sqlStr = `INSERT INTO ${tableName} 
                  (${Object.keys(filelds)
                    .map((item) => ` "${item}" `)
                    .join(',')}) 
                  VALUES(${values.map((item, index) => `$${index + 1}`).join(',')})`;
    return this.clientQeury(sqlStr, values, autoDisconnect);
  }
  /**
   * 删除数据库表
   * @param param0 DeleteParams
   */
  delete(obj: DeleteParams): Promise<pg.QueryResult<any>> {
    const { tableName, compare, arrayIn, contains, autoDisconnect }: DeleteParams = obj;
    let sqlStr = `DELETE FROM ${tableName} `;
    sqlStr += getMatchSql({ compare, arrayIn, contains });
    return this.clientQeury(sqlStr, [], autoDisconnect);
  }
  /**
   * 更新数据库表
   * @param param0: UpdateParams
   */
  update(obj: UpdateParams): Promise<pg.QueryResult<any>> {
    const { tableName, compare, arrayIn, contains, filelds, autoDisconnect }: UpdateParams = obj;
    let sqlStr = `UPDATE ${tableName} SET ${Object.keys(filelds)
      .map((item, index) => ` "${item}"=$${index + 1}`)
      .join(',')}`;
    const values = Object.values(filelds);
    sqlStr += getMatchSql({ compare, arrayIn, contains });
    return this.clientQeury(sqlStr, values, autoDisconnect);
  }
  /**
   * 查询结果的总数
   */
  selectCount(obj: SelectParams): Promise<pg.QueryResult<any>> {
    const { tableName, compare, arrayIn, contains, autoDisconnect }: SelectParams = obj;
    console.log(JSON.stringify({ tableName, compare, arrayIn, contains, autoDisconnect }));
    let sqlStr = `SELECT COUNT(1) as total FROM ${tableName} `;
    sqlStr += getMatchSql({ compare, arrayIn, contains });
    return this.clientQeury(sqlStr, [], autoDisconnect).then((res) => {
      return res;
    });
  }
  /**
   * 分页查询（与select不一样的是，分页查询返回了page对象包含total查询结果总数）
   */
  selectPage({
    tableName,
    compare,
    arrayIn,
    contains,
    pointer,
    pointerArr,
    returnFields,
    autoDisconnect,
    page,
  }: SelectParams) {
    return Promise.all([
      this.select({
        tableName,
        compare,
        arrayIn,
        contains,
        pointer,
        pointerArr,
        returnFields,
        autoDisconnect,
        page,
      }),
      this.selectCount({ tableName, compare, arrayIn, contains, autoDisconnect }),
    ]).then((resArr) => {
      const [res, resTotal] = resArr;
      return {
        rows: res.rows,
        page: {
          ...page,
          total: resTotal.rows[0].total - 0,
        },
      };
    });
  }
  /**
   * 查询
   */
  select({
    tableName,
    compare,
    arrayIn,
    contains,
    pointer,
    pointerArr,
    returnFields,
    autoDisconnect,
    page,
  }: SelectParams): Promise<pg.QueryResult<any>> {
    let sqlStr = `SELECT ${returnFields ? returnFields.join(',') : '*'} FROM ${tableName} `;
    sqlStr += getMatchSql({ compare, arrayIn, contains });
    if (page && page.size) {
      const { size, current } = page;
      const limit = size;
      const offset = (current - 1) * size;
      sqlStr += ` LIMIT ${limit} OFFSET ${offset}`;
    }
    // 把id转成其他表的name
    if (pointer && pointer.length) {
      const selectFiled = pointer
        .map(
          ({ tableName, selectName, columnName }) =>
            ` "${tableName}"."${columnName}" AS "${selectName || columnName}" `
        )
        .join(',');
      const tableList = pointer.map((item) => `${item.tableName}`).join(',');
      const matchRules = pointer
        .map(({ originKey, key, tableName }) => `t."${originKey || key}" = "${tableName}"."${key}"`)
        .join(' AND ');
      sqlStr = `SELECT t.*,${selectFiled} from (${sqlStr}) t, ${tableList}
                WHERE ${matchRules}`;
    }
    // 将id列表，转成其他表的name列表
    // 如把members [id1,id2] 转成 [name1, name2]
    if (pointerArr && pointerArr.length) {
      const pointerArrStr = pointerArr
        .map(
          ({ tableName, joinKey, originName, selectName }) =>
            `(
              (select array_agg("${tableName}".name) from (
                select distinct * from 
                  regexp_split_to_table(array_to_string(pointerarr."${originName}", ','),',')
                  as id
              ) pointerarr1 left join "${tableName}" on "${tableName}"."${joinKey}" = pointerarr1."id"	)
            ) as "${selectName || originName}"`
        )
        .join(',');
      sqlStr = `SELECT *,
                    	${pointerArrStr}
                FROM (${sqlStr}) pointerarr`;
    }
    return this.clientQeury(sqlStr, [], autoDisconnect).then((res) => {
      return res;
    });
  }
}

export default PgUtil;

type.d.ts 类型定义文件

interface Result {
   
   
  code: string;
  message: string;
}
interface TaskVO {
   
   
  projectName?: string;
  projectId?: string;
  level: string;
  taskName: string;
  planStartTime: number;
  planEndTime: number;
  realStartTime?: number;
  realEndTime?: number;
  status: string;
  progress: string;
  leader: strin