Node.js基于pg连接postgreSQL数据库的模块封装

使用的是pg来操作的 https://www.npmjs.com/package/pg

文档参考:https://node-postgres.com/

准备工作,首先要安装好postgreSQL数据库,推荐使用pgadmin这个图形化查看

和mongose区别是,mongoose可以js里写表结构和表模型;

但是postgreSQL需要先创建好,并订好格式,然后再操作;

mongoose的可以参看 https://zhubangbang.com/mongooses-table-structure-and-table-model.html

postgre的数据类型可以参看:https://www.cnblogs.com/kungfupanda/p/4478917.html

基础语法参考文档;

封装如下

/* 用法

let pgclient = require('./PG');// 引用上述文件

//插入
pgclient.insert('test',{"name": "xiaoming","age":20},(res)=>{
    console.log("select result",res)
});

//查
pgclient.select('test',{"name": "anbang"},[],(res)=>{
    console.log("select result",res)
});

//更新age
pgclient.update('test',{"name": "anbang"},{"age": 30},(res)=>{
    console.log("select result",res)
});

//删除
pgclient.delete('test',{"name": "anbang"},(res)=>{
    console.log("select result",res)
});

*/


const { Client } = require('pg')

// 数据库配置
let config = {
    host: '********',
    port: 5432,
    user: "postgres",
    password: "abc123456",
    database: "canonchain_explorer",
    // 扩展属性
    max: 20, // 连接池最大连接数
    idleTimeoutMillis: 3000, // 连接最大空闲时间 3s
}
let client = new Client(config);

let PG = function () {
    console.log("准备数据库连接...");
};

// PG.prototype.getConnection = function () {
    // client.connect(function (err) {
    //     if (err) {
    //         return console.error('数据库链接失败:', err);
    //     }
    //     client.query('SELECT NOW() AS "theTime"', function (err, result) {
    //         if (err) {
    //             return console.error('error running query', err);
    //         }
    //         console.log("数据库连接成功...");
    //     });
    // });
// };

// 查询函数
//@param str    查询语句
//@param value  相关值
//@param cb     回调函数
let clientHelper = function (str, value, cb) {
    // console.log(`client.query(${str},${value}) `)
    client.connect(function (err) {
        if (err) {
            return console.error('数据库链接失败:', err);
        }
        client.query(str, value, function (err, result) {
            // console.log("result",result)
            if (err) {
                cb("error");
            } else {
                if (result.rows != undefined) {
                    cb(result.rows);
                } else {
                    cb();
                }
                client.end()
            }
        });
    });
}

// let clientHelper = function (str, value, cb) {
//     // console.log(`client.query(${str},${value}) `)
//     client.query(str, value, function (err, result) {
//         // console.log("result",result)
//         if (err) {
//             cb("error");
//         } else {
//             if (result.rows != undefined) {
//                 cb(result.rows);
//             } else {
//                 cb();
//             }
//         }
//     });
// }

//增加
//@param tablename 数据表名称
//@param fields 更新的字段和值,json格式
//@param cb 回调函数
//pgclient.insert('test',{"name":'xiaoming',"age" : "20"},cb);
PG.prototype.insert = function (tablename, fields, cb) {
    if (!tablename) return;
    let str = "INSERT INTO " + tablename + "(";//"insert into test("
    let field = [];
    let value = [];
    let num = [];
    let count = 0;
    for (let i in fields) {
        count++;
        field.push(i);//["name","age"]
        value.push(fields[i]);//["xiaoming","20"]
        num.push("$" + count);//[$1,$2]
    }
    str += field.join(",") + ") VALUES(" + num.join(",") + ")";// "insert into test(name,age) values($1,$2)"
    clientHelper(str, value, cb);//clientHelper("insert into test(name,age) values($1,$2)",["xiaoming","20"],cb)
    //RUN client.query("insert into test(name, age) values($1::varchar, $2::int)", ["xiaoming","20"])
};

//查询
//@param tablename      数据表名称              test
//@param fields         条件字段和值,json格式
//@param returnfields   返回字段                *
//@param cb             回调函数
//pgclient.select('test',{'name': 'xiaoming'},cb);
PG.prototype.select = function (tablename, fields, returnfields, cb) {
    if (!tablename) { return; }

    let returnStr = "";
    if (returnfields.length == 0) {
        returnStr = '*';
    } else {
        returnStr = fields.join(",");
    }

    let str = "Select " + returnStr + " FROM " + tablename + " WHERE ";//"Select * FROM test WHERE "
    let field = [];
    let value = [];
    let count = 0;
    for (let i in fields) {
        count++;
        field.push(i + "=$" + count);//[ name = $1 ]
        value.push(fields[i]);//[ 'xiaoming' ]
    }
    str += field.join(" and ");//"Select * FROM test WHERE name = $1"
    clientHelper(str, value, cb);//clientHelper("Select * FROM test WHERE name = $1", [ 'xiaoming' ], cb)
    //RUN => client.query("Select * FROM test WHERE name = $1", ["xiaoming"])
};

//修改
//@param tablename 数据表名称
//@param fields 更新的字段和值,json格式
//@param mainfields 条件字段和值,json格式
//pgclient.update('test'{"name":'xiaoming'},{"age" : "21"},,cb); 更新age
PG.prototype.update = function (tablename, mainfields, fields, cb) {
    if (!tablename) return;
    let str = "UPDATE " + tablename + " SET ";//"UPDATE test SET"
    let field = [];
    let value = [];
    let count = 0;
    for (let i in fields) {
        count++;
        field.push(i + "=$" + count);//["age=$1"]
        value.push(fields[i]);//["21"]
    }
    str += field.join(",") + " WHERE ";//"UPDATE test SET age=$1 WHERE"
    field = [];
    for (let j in mainfields) {
        count++;
        field.push(j + "=$" + count);//["name=$2"]
        value.push(mainfields[j]);//["21","xiaoming"]
    }
    str += field.join(" and ");//"UPDATE test SET age=$1 WHERE name=$2"
    clientHelper(str, value, cb);//clientHelper("UPDATE test SET age=$1 WHERE name=$2",["21","xiaoming"])
    //client.query("UPDATE test SET age=$1 WHERE name=$2", [21, "xiaoming"])
}

//删除
//@param tablename 数据表名称
//@param fields 条件字段和值,json格式
//@param cb 回调函数
//pgclient.delete("test",{"name","xiaoming"},cb)
PG.prototype.delete = function (tablename, fields, cb) {
    if (!tablename) return;
    let str = "DELETE FROM " + tablename + " WHERE ";//DELETE FROM test WHERE
    let field = [];
    let value = [];
    let count = 0;
    for (let i in fields) {
        count++;
        field.push(i + "=$" + count);//[name=$1]
        value.push(fields[i]);//["xiaoming"]
    }
    str += field.join(" and ");//DELETE FROM test WHERE name=$1
    clientHelper(str, value, cb);//clientHelper("DELETE FROM test WHERE name=$1", ["xiaoming"], cb)
    //client.query("DELETE FROM test WHERE name=$1", ["xiaoming"])})
}

module.exports = new PG();

 

用方法参考

var pgclient = require('./PG');// 引用上述文件
// pgclient.getConnection()

var options={
    "account": "czr_1t8g1mosn5we4qytwqpfrt4ewopum6xcn34khe54jkcgc4t4ic88mn94tq6k",
    "type":1,
    "balance": 120000000000000000,
    "tran_count": 1,
}
pgclient.insert('accounts',options,(res)=>{
    console.log("select result",res)
});

// pgclient.select('test',{"name": "anbang"},[],(res)=>{
//     console.log("select result",res)
// });

// pgclient.update('test',{"name": "anbang"},{"age": 30},(res)=>{
//     console.log("select result",res)
// });

// pgclient.delete('accounts',{"account": "czr_1t8g1mosn5we4qytwqpfrt4ewopum6xcn34khe54jkcgc4t4ic88mn94tq6k"},(res)=>{
//     console.log("select result",res)
// });

 

~~

未经允许不得转载:朱邦邦的博客 » Node.js基于pg连接postgreSQL数据库的模块封装

赞 (0)

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址