postgre-SQL

A PostgreSQL support for node.js, based on node-postgres and used Pool.

安装

npm i postgre-sql

配置

``js
const {connect} = require('postgre-sql');
connect({user:'postgres',database:'test',max:2,idleTimeoutMillis:100});

`

使用DEMO


`js
const {client} = require('postgre-sql');
let db = new client();
(async ()=>{
try{
let res = await db.table('public.users')
.where({vip:true},"create_at>'2017-01-01' and coordinate <-> point(116,39) < 1")
.field('uid,user_name').order('uid desc').page(2).select();
// res -> [{uid:34,use_name:'alice'},{uid:35,user_name:'💘'}...]
}catch (e){
// something else
}
try{
await db.begin();
let new_uid = await db.table('public.users').add({user_name:'charles'});
await db.commit();
}catch (e){
db.rollback();
}

})();

`

会话模式

默认是statement模式，这个模式的语句会平均负载到池中所有的连接。适合并行查询！

会话模式会根据业务自动调整。

$3


`js
const {client} = require('postgre-sql');
let db = new client();
(async ()=>{
try{
let res = await db.table('public.users')
.where({vip:true},"create_at>'2017-01-01' and coordinate <-> point(116,39) < 1")
.field('uid,user_name').order('uid desc').page(2).select();
// res -> [{uid:34,use_name:'alice'},{uid:35,user_name:'💘'}...]
}catch (e){
// something else
}
})();
`

$3

`js
const {client} = require('postgre-sql');
let db = new client();
(async ()=>{
try{
await db.connect();
let res = await db.table('public.users')
.where({vip:true},"create_at>'2017-01-01' and coordinate <-> point(116,39) < 1")
.field('uid,user_name').order('uid desc').page(2).select();
// res -> [{uid:34,use_name:'alice'},{uid:35,user_name:'💘'}...]
await db.release(); // Attention! if connection a session don't forget release it!
}catch (e){
await db.release(); // Attention! if connection a session don't forget release it!
// something else
}
})();
`

$3

`js
const {client} = require('postgre-sql');
let db = new client();
(async ()=>{
try{
await db.begin();
let new_uid = await db.table('public.users').add({user_name:'charles'});
await db.commit(); // Attention! if start a transaction don't forget finish it!
}catch (e){
db.rollback(); // Attention! if start a transaction don't forget finish it!
}

})();
`

自动完成

如果表中有create_at、update_at、delete_at。在CUD的操作中会自动填充NOW()。

软删除只在db.table(tableName).where(conditions).delete();时影响结果。

在SELECT类型的语句中，不会主动识，别并剔除delete_at非空的记录。需要手动写在conditions`中。