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

上官自明

2023-12-01

首先是源代码

下文是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: string;
  members: Array<string>;
  delayReason?: string;
}
interface TaskDO {
  projectId: string;
  level: string;
  taskName: string;
  planStartTime: number;
  planEndTime: number;
  realStartTime?: number;
  realEndTime?: number;
  status: string;
  progress: string;
  leader: string;
  members: Array<string>;
  delayReason?: string;
}
interface PageData {
  page: {
    current: number;
    size: number;
  };
  search: any;
}

/**
 * PgUtil 接口
 */
interface TableName {
  tableName: 'project' | 'task' | 'member';
}
interface PgUtilQueryParams extends TableName {
  autoDisconnect?: Boolean;
}
interface PgUtilMatchParams {
  compare?: Array<PgUtilCompare>;
  contains?: Array<{
    key: string;
    value: string | number;
  }>;
  arrayIn?: Array<{
    key: string;
    values: Array<any>;
  }>;
}
interface PgUtilPointer extends TableName {
  originKey?: string; // 主表原始key
  key: string; // 查询表key
  columnName: string; // 查询表字段
  selectName?: string; // 查询字段重命名
}
interface PgUtilPointerArr {
  tableName: TableName;
  joinKey: string; // 关联表匹配条件key
  originName: string; // 关联左表待转化列名
  selectName?: string; // 查询字段重命名
}
interface SaveParams extends PgUtilQueryParams {
  filelds: Object;
}
interface DeleteParams extends PgUtilQueryParams, PgUtilMatchParams {}
interface UpdateParams extends SaveParams, PgUtilMatchParams {}
interface SelectParams extends PgUtilQueryParams, PgUtilMatchParams {
  pointer?: PgUtilPointer[];
  pointerArr?: PgUtilPointerArr[];
  page?: {
    size: number;
    current: number;
  };
  returnFields?: Array<string>;
}
interface PgUtilCompare {
  key: string;
  operator: '=' | '!=' | '>' | '<' | '>=' | '<=';
  value: any;
}

使用范例

实例化操作类,创建数据库连接池

const pgUtil = new PgUtil({
  user: 'postgres', // 数据库用户名
  database: 'postgres', // 数据库
  password: 'postgres', // 数据库密码
  host: 'xxx.xxx.xxx.xx',
  port: 1111,
});
pgUtil.connenct().then(() => {
	// TODO 这里可以使用pgUtil实例的数据库操作方法了
}).catch(err => console.error(err));

pgUtil实例可以调用的方法说明

select

入参说明

tableName：连接的数据库表名
compare: 条件比较查询，包括’=’ | ‘!=’ | ‘>’ | ‘<’ | ‘>=’ | ‘<=’
arrayIn: where条件in查询
contains: like查询
pointer: 附加表关联
- tableName：附加表名
- originKey：原始表的key
- key: 附加表的key
- columnName：查询附加表字段
- selectName: 重新命名查询附加表的字段（可选参数）
pointerArr：参数与pointer一样，却别在于pointerArr对应附加表的字段是个数组类型
returnFields：查询返回的字段
autoDisconnect：查询成功是否自动关闭数据库连接池，关闭后当前实例无法调用，需要重新调用connect才能使用
page：分页查询
- size：查询每页数据
- current：查询页数

调用范例

const pgUtil = new PgUtil({
  user: 'postgres', // 数据库用户名
  database: 'postgres', // 数据库
  password: 'postgres', // 数据库密码
  host: 'xxx.xxx.xxx.xx',
  port: 1111,
});
pgUtil.connenct()
	.then(() => {
		pgUtil.select({
		    "tableName":"project",
		    "contains":[{"key":"name","value":"测"}],
		    "returnFields":["id","name"],
		    "autoDisconnect":true
		});
	})
	
	.then(res => {
		// 查询结果列表 res.rows
	})
	.catch(err => console.error(err));

调用范例实际生成的sql语句

SELECT id,name FROM project  WHERE  "name" LIKE '%测%'

selectCount

入参说明

tableName：连接的数据库表名
compare: 条件比较查询，包括’=’ | ‘!=’ | ‘>’ | ‘<’ | ‘>=’ | ‘<=’
arrayIn: where条件in查询
contains: like查询
autoDisconnect：查询成功是否自动关闭数据库连接池，关闭后当前实例无法调用，需要重新调用connect才能使用

调用范例

// 这里省略实例化代码
pgUtil.connenct()
	.then(() => {
		pgUtil.select({
	        "tableName":"task",
	        "compare":[
	          {"key":"level","operator":"=","value":"01"},
	          {"key":"leader","operator":"=","value":"xxxxxxxxxx"},
	          {"key":"status","operator":"=","value":"01"},
	          {"key":"progress","operator":"=","value":"02"}
	        ],
	        "autoDisconnect":false
		});
	})
	
	.then(res => {
		// 查询结果列表 res.rows
	})
	.catch(err => console.error(err));

调用范例实际生成sql语句

SELECT COUNT(1) as total
FROM task
WHERE "level" = '01'
AND  "leader" = 'xxxxxxxxxx'
AND  "status" = '01'
AND  "progress" = '02'

selectPage

入参说明

tableName：连接的数据库表名
compare: 条件比较查询，包括’=’ | ‘!=’ | ‘>’ | ‘<’ | ‘>=’ | ‘<=’
arrayIn: where条件in查询
contains: like查询
pointer: 附加表关联
- tableName：附加表名
- originKey：原始表的key
- key: 附加表的key
- columnName：查询附加表字段
- selectName: 重新命名查询附加表的字段（可选参数）
pointerArr：参数与pointer一样，却别在于pointerArr对应附加表的字段是个数组类型
returnFields：查询返回的字段
autoDisconnect：查询成功是否自动关闭数据库连接池，关闭后当前实例无法调用，需要重新调用connect才能使用
page：分页查询
- size：查询每页数据
- current：查询页数

调用范例

// 这里省略实例化代码
pgUtil.connenct()
	.then(() => {
		pgUtil.select({
            "tableName":"task",
             "compare":[
               {"key":"level","operator":"=","value":"01"},
               {"key":"leader","operator":"=","value":"xxxxxxxxxx"},
               {"key":"status","operator":"=","value":"01"},
               {"key":"progress","operator":"=","value":"02"}
             ],
             "pointer":[
               {"originKey":"projectId","key":"id","tableName":"project","columnName":"name","selectName":"projectName"},
               {"originKey":"leader","key":"id","tableName":"member","columnName":"name","selectName":"leaderName"}
             ],
             "pointerArr":[{"originKey":"id","key":"id","tableName":"member","columnName":"members","selectName":"membersName"}],
             "returnFields":["*"],
             "autoDisconnect":false,
             "page":{"current":1,"size":10}
		});
	})
	
	.then(res => {
		// 查询结果列表 res.rows
		// 查询分页参数 res.page(包含total，结果总数，分页查询用)
	})
	.catch(err => console.error(err));

调用范例实际生成sql语句

SELECT  *,
		(
			(select array_agg("member".name) from (
				select distinct * from
				regexp_split_to_table(array_to_string(pointerarr."members", ','),',') as id
         	) pointerarr1
         	left join "member" on "member"."id" = pointerarr1."id"	)
       ) as "membersName"
       FROM (
				SELECT t.*, "project"."name" AS "projectName" , "member"."name" AS "leaderName"
				FROM (
                  SELECT * FROM task
                  WHERE "level" = '01'
                  AND  "leader" = 'xxxxxxxxxx'
                  AND  "status" = '01'
                  AND  "progress" = '02' LIMIT 10 OFFSET 0
                ) t, project,member
                WHERE t."projectId" = "project"."id"
                AND t."leader" = "member"."id"
		) pointerarr

clientQuery

入参说明

sqlStr： sql语句
args： sql语句上面等参数如sql语句 select * from task wher id=$1，args传[‘xxxx’]，实际会发出“select * from task wher id=xxxx ”sql语句查询
autoDisconnect：查询成功后是否自动断开数据库连接池

调用范例

const pgUtil = new PgUtil();
pgUtil
  .connect()
  .then(() => {
    return pgUtil.clientQeury(taskAnalysis, [], true);
  })
  .then((res) => {
    appRes.send({
      code: '10000',
      message: '',
      data: res.rows,
    });
  })
  .catch((err) => dealCatch(err, appRes));

taskAnalysis

const taskAnalysis = `
SELECT 
  * ,
  (select count(1) FROM (
    SELECT count(1), m.* 
    FROM (
      SELECT distinct *
      FROM regexp_split_to_table(
        (	SELECT string_agg(array_to_string(members, ','), ',')
          from task t  
              where t.level = statistic.level
        ), ','
      ) as id
      ) t1 left join member m
      ON t1.id = m.id GROUP BY m.id) tmp) as member,
  (SELECT COUNT(1) FROM member) as "memberCount"
FROM
  (SELECT 
    level, 
    count(1) as "taskCount",
    (SELECT COUNT(1) FROM task t1 WHERE t1.level = t.level and t1.progress ='04') as "taskDone"
  FROM task t 
  GROUP BY level) statistic`

save（增）delete（删）update（改）

增删改比较简单，就不做范例了，对照上面的既可。

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

首先是源代码

使用范例

实例化操作类,创建数据库连接池

pgUtil实例可以调用的方法说明

select

入参说明

调用范例

调用范例实际生成的sql语句

selectCount

入参说明

调用范例

调用范例实际生成sql语句

selectPage

入参说明

调用范例

调用范例实际生成sql语句

clientQuery

入参说明

调用范例

taskAnalysis

save（增）delete（删）update（改）

相关阅读

相关文章

相关问答

相关文档