使用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: 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語句

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（改）

繼續閱讀

Nodejs操作MySQL-增删改查

Nodejs+mysql傳回查詢的資料結果

NodeJS 建立TCP伺服器用戶端

nodejs伺服器與伺服器之間通訊問題（nodejs伺服器端建立用戶端）

Nodejs實作websocket的4種方式

node url重定向_使用Node.js跟蹤URL重定向

GitBook 安裝使用，及遇到的坑【Mac下】

多站點單點登入實作方案

WebScoket

postgresql 關于GBK和utf-8字元集問題

使用 vscode 搭建基于 Docker 容器的 Node.js 開發環境使用 vscode 搭建基于 Docker 容器的 Node.js 開發環境

nodejs庫

網易嚴選小程式學習筆記 2node.js後端

網易嚴選小程式學習筆記 1node.js後端

Docker上部署node項目Docker上部署node項目

手把手教你配置：Jenkins+Github+Webhook +Nginx自動化打包部署Vue項目