項目需要用Node.js去查詢訪問PostgresSQL數(shù)據庫盟榴。一時間不到趁手的ORM，干脆自己封裝一個吧栅贴。

不廢話著瓶，直接上代碼：

安裝pg庫

npm install --save pg

創(chuàng)建數(shù)據庫連接池

const pg = require('pg');

const pool = new pg.Pool({
    user: process.env.PG_USER,
    host: process.env.PG_HOST,
    database: process.env.PG_DBNAME,
    password: process.env.PG_PASSWD,
    port: process.env.PG_PORT,
    
    max: 20, // 連接最大連接數(shù)
    idleTimeoutMillis: 15000 // 連接最大空閑時間
});

封裝PgDatabase類

module.exports = class PgDatabase {
    client;
    constructor(){
        this.init();
    }

    async init(){        
        this.client = await pool.connect();
    }

    async queryBySql(sql){
        return await this.client.query(sql);
    }

    async update(table,data,where){
        var setSql = getSetSql(data);
        var whereSql = getWhereSql(where);
        var sql = `UPDATE "${table}" SET ${setSql}${whereSql};`;
        var result = await this.queryBySql(sql);
        return result.rowCount;
    }

    async delete(table,where){
        var whereSql = getWhereSql(where);
        var sql = `DELETE FROM "${table}"${whereSql};`;
        var result = await this.queryBySql(sql);
        return result.rowCount;
    }

    async insert(table,data){
        data = data || {};
        var keys = Object.keys(data);
        var values = keys.map(key => {
            return `'${data[key]}'`;
        });
        if(values.length == 0){
            return 0;
        }
        var fields = keys.join(" , ");
        var sql = `INSERT INTO "${table}" (${fields}) VALUES (${values.join(" , ")})`;
        var result = await this.queryBySql(sql);
        return result.rowCount;
    }

    async findCount(table,where){
        where =getWhereSql(where);
        var sql = `SELECT COUNT(1) AS total FROM "${table}"${where};`;
        var res = await this.queryBySql(sql);
        if(res.rowCount>0) return res.rows[0].total * 1;
        return 0;
    }

    async findOne(table,where,orderby){
        orderby = getOrderBySql(orderby);
        where =getWhereSql(where);
        var sql = `SELECT * FROM "${table}"${where}`;
        var res = await this.queryBySql(`${sql}${orderby} LIMIT 1;`);        
        if(res.rowCount>0) return res.rows[0];
        return null;
    }

    async findList(table,where,orderby,limit,offset){
        orderby = getOrderBySql(orderby);
        var sql = "SELECT * FROM \""+table+"\"" + getWhereSql(where);
        sql = `${sql} ${orderby}`;
        if(limit){
            sql += ` LIMIT ${limit}`;
        }
        if(offset){
            sql += ` OFFSET ${offset}`;
        }
        return await this.queryBySql(sql);
    }
}

公共函數(shù)

/**
* 將對象轉為KeyValue數(shù)組
* @param {*} obj
* @returns
*/
const mapAsArray=(obj)=>{
    var result = [];
    obj = obj || {};
    for (const key in obj) {
        if (Object.hasOwnProperty.call(obj, key)) {
            const val = obj[key];
            result.push(`"${key}"='${val}'`);
        }
    }
    return result;
}

const getWhereSql = (data) => {
    if(data && typeof data === "string"){
        return " WHERE " + data;
    }
    let arr = mapAsArray(data);
    if(arr.length > 0){
        return " WHERE " + arr.join(" AND ");
    }
    return "";
}

const getSetSql = (data) => {
    if(data && typeof data === "string"){
        return data;
    }
    let arr = mapAsArray(data);
    if(arr.length > 0){
        return arr.join(" , ");
    }
    return "";
}

const getOrderBySql = (data) => {
    if(data){
        return " ORDER BY " + data;
    }
    return "";
}

應用示例

const PgDatabase = require('../db/pgdatabase');
// 實例化
const db = new PgDatabase();

const tableName = "table_users";
// 查找對象
var name = "username";
let user = await pg.findOne(tableName, {name:name});

// 查找列表
var where = {status:1};
let result = await pg.findList(tableName,where);
if(result.rowCount>0){
    console.log(result.rows);
}

// 統(tǒng)計行數(shù)
let count = await pg.findCount(tableName,where);
console.log('count',count);

// 添加記錄
await db.insert(tableName,{
    name: "foobar",
    password:"12345",
    phone: "12345",
    email: "foobar@example.com",
    status: 1
});

// 添加記錄
where = {id:1};
await db.update(tableName,{
    password:"123456",
    status: 0
},where);

// 刪除記錄
where = {id:1};
await db.update(tableName,where);