Nodejs Promise方式操作Postgresql

使用连接池，保证连接获取速度；

使用promise方式，保证代码执行时序；

安装pg包

npm install pg --save

创建文件：pgClient.js 

const pg = require('pg');

/**
 * pgPool instance
 */
var pgPool = null;

/**
 * pgClient Entity
 */
class pgClient {

    /**
     * init param
     * @param {*} options                   must    description
     * @param options.host                   M     db host ip
     * @param options.database               M     db name
     * @param options.port                   O     db port, default is 5432
     * @param options.user                   M     db login username
     * @param options.password               M     db login passcode
     * @param options.poolSize               O     db pool size, default is 5
     * @param options.poolIdleTimeout        O     idle timeout, default is 30000 millisecond
     * @param options.reapIntervalMillis     O     repeatInterval, default is 10000 millisecond
     * 
     */
    constructor(options) {
        let configObj = {};
        configObj.host = options.host;
        configObj.database = options.database;
        configObj.user = options.user;
        configObj.password = options.password;
        configObj.port = options.port ? options.port : '5432';
        configObj.poolSize = options.poolSize ? options.poolSize : 5;
        configObj.poolIdleTimeout = options.poolIdleTimeout ? options.poolIdleTimeout : 30000;
        configObj.reapIntervalMillis = options.reapIntervalMillis ? options.reapIntervalMillis : 10000;
        
        // init pool instance
        pgPool = new pg.Pool(configObj);

        // the pool will emit an error on behalf of any idle clients
        // it contains if a backend error or network partition happens
        pgPool.on('error', (err, client) => {
            console.error('Unexpected error on idle client', err)
            process.exit(-1)
        })
    }

    /**
     * execute logic
     * @param {*} sql  sql str   ex. 'select $1, $2 from $3 where id=$4'
     * @param {*} args args array   ex. ['id', 'name', 'user', '1']
     * @returns if false returned, meant no result
     */
    async exec(sql, args) {
        return pgPool.connect().then(client => {
            return client
                .query(sql, args)
                .then(res => {
                    console.log(`query success  sql:${sql}, args:${args}`);
                    client.release();
                    return res['rows'];
                })
                .catch(queryException => {
                    console.log(`query exception: ${queryException.stack}`);
                    client.release();
                    return false;
                });
        }).catch(connectException => {
            console.log(`connect Exception : ${connectException.stack}`);
            return false;
        });
    }
}

module.exports.pgClient = pgClient;

创建测试文件：test.js

const pgclient = require("./pgclient").pgClient;

async function Process() {

  let pg = new pgclient({
    host : "192.168.1.1",
    database : "postgres",
    user : "postgres",
    password : "postgres"
  });

  let res = await pg.exec("SELECT * FROM t_role WHERE role_name = $1", ['超级用户']);
  console.log(`result is : ${res}`); // result objs set
  return;
}

Process()
  .then(() => process.exit(0))
  .catch(error => {
    console.log(error);
    console.log(`error happened after process ${error.message}`);
  });

测试：

cd 到test.js

node ./test.js

更多内容参考：https://node-postgres.com/