typescript关于postgres数据库的API封装

文章结构

    1 新建 postgres.ts 文件

    2 配置文件说明

    3 依赖引用说明

    4 API使用示例

 

1 新建 postgres.ts 文件，代码如下：

import { Pool, PoolClient, QueryResult } from 'pg';
import { CONFIG } from './config';

const pg_pool = new Pool(CONFIG.pg);
pg_pool.on('error', (err, client) => {
  console.error('Error on idle client', err.message || err + '');
});

class DB {
  private mPool: Pool;
  constructor(pool: Pool) {
    this.mPool = pool;
  }
  async connect(): Promise<DBSession> {
    const client = await this.mPool.connect();
    return new DBSession(client);
  }

  async doTrans(callback: (dbs: DBSession) => Promise<unknown>): Promise<unknown> {
    const dbs = await this.connect();
    try {
      await dbs.begin();
      const result = await callback(dbs);
      await dbs.commit();
      return result;
    } catch (e) {
      await dbs.rollback();
      console.error('DB.doTrans() error, rollback:', e);
      throw e;
    } finally {
      dbs.close();
    }
  }

  async run(callback: (dbs: DBSession) => Promise<unknown>): Promise<unknown> {
    const dbs = await this.connect();
    try {
      return await callback(dbs);
    } catch (e) {
      console.error('DB.execute() error:', e);
      throw e;
    } finally {
      dbs.close();
    }
  }

  async queryList(sql: string, ...parameters: any[]): Promise<any[]> {
    const result = await this.mPool.query(sql, parameters);
    if (!result || !(result.rows instanceof Array)) {
      return [];
    }
    return result.rows;
  }

  async queryFirst(sql: string, ...parameters: any[]): Promise<any> {
    const result = await this.mPool.query(sql, parameters);
    if (result && result.rowCount > 0) {
      return result.rows[0];
    } else {
      return null;
    }
  }

  async queryValue(sql: string, ...parameters: any[]): Promise<unknown> {
    const result = await this.mPool.query(sql, parameters);
    if (result && result.rowCount > 0) {
      const key = result.fields[0].name;
      return result.rows[0][key];
    } else {
      return null;
    }
  }

  async query(sql: string, ...parameters: any[]): Promise<QueryResult<any>> {
    return await this.mPool.query(sql, parameters);
  }
}

const pgDB = new DB(pg_pool);
export { pgDB };

class DBSession {
  private _client: PoolClient;
  private _transaction = false;

  constructor(client: PoolClient) {
    this._client = client;
  }
  async begin(): Promise<void> {
    await this._client.query('begin');
    this._transaction = true;
  }

  async commit(): Promise<void> {
    await this._client.query('commit');
    this._transaction = false;
  }

  async savepoint(id: string): Promise<void> {
    await this._client.query('savepoint $1', [id]);
  }

  async rollback(savepoint?: string): Promise<void> {
    if (savepoint) {
      await this._client.query('rollback to savepoint $1', [savepoint]);
    } else {
      await this._client.query('rollback');
      this._transaction = false;
    }
  }
  async queryList(sql: string, ...parameters: any[]): Promise<any[]> {
    const result = await this._client.query(sql, parameters);
    if (!result || !(result.rows instanceof Array)) {
      return [];
    }
    return result.rows;
  }
  async queryFirst(sql: string, ...parameters: any[]): Promise<any> {
    const result = await this._client.query(sql, parameters);
    if (result && result.rowCount > 0) {
      return result.rows[0];
    } else {
      return null;
    }
  }
  async queryValue(sql: string, ...parameters: any[]): Promise<unknown> {
    const result = await this._client.query(sql, parameters);
    if (result && result.rowCount > 0) {
      const key = result.fields[0].name;
      return result.rows[0][key];
    } else {
      return null;
    }
  }
  async query(sql: string, ...parameters: any[]): Promise<QueryResult<any>> {
    return await this._client.query(sql, parameters);
  }

  close() {
    if (this._transaction) {
      this.rollback();
    }
    this._client.release();
  }
}

 

2 新建config.ts配置文件，配置数据源

import ps from 'process';

export const CONFIG = {
    pg: {
        connectionString: ps.env.PG_URL || 'postgresql://用户名:密码@IP:端口/数据库',
        max: 1,
        min: 0,
        idleTimeoutMillis: 30000,
    },
};

　　对于数据源的配置，账号密码注意对符号进行转义。如 @ 需要转为 %40等。

 

3 文章中依赖引用说明

postgres依赖： "@types/pg": "^8.6.1" 

 

4 使用示例

首先在需要的文件中导入API。文件位置按照实际进行更改。

import { pgDB } from '../postgres';

 

 4.1  查询单值

const QUERY_VALUE_SQL = `
  select name from tb_table a where a.id = $1
`;
const id = await pgDB.queryValue(
            QUERY_VALUE_SQL ,
            5
        );

 

4.2 查询单行数据

const QUERY_SQL = `
  select id,name from tb_table a where a.id = $1
`;
const result = await pgDB.queryFirst(QUERY_SQL , 1);

 

4.3 查询List多行数据

const QUERY_LIST_SQL =   `
  select id,name from tb_table a where id = $1 or id = $2
`;

const result = await pgDB.queryList(QUERY_LIST_SQL , 1, 2);

 

 修改，删除，事务等示例后续补充。