dbh-pg
v3.0.0
Published
Lightweight Database Handler for PostgreSQL writer upon node-postgres and bluebird.
Downloads
49
Readme
#
Lightweight Database Handler for PostgreSQL writer upon node-postgres and bluebird.
Why?
Because node-postgres is too low level and is not funny to write deeply nested functions for commons task such as create transactions.
Features
- Promises/A+ style by bluebird.
- Full Documented API.
- Full Tested API.
- Made with simple and clean code.
- Extra utils for sanitization and sql creation.
Installation
The latest stable version:
$ npm install dbh-pg
It is recommended that you also install bluebird for use Promise.using
:
$ npm install bluebird
Usage
- Require the dependencies.
- Instantiate the DBH (Internally creates a connection pool).
- Use
Promise.using
(or the shorhandDBH.using
) to get a connection from the pool and then auto release it. Is important that the callback function returns the connection promise.
// require dependences
var DBH = require('dbh-pg'),
Promise = require('bluebird'),
using = Promise.using
// instantiate the database
var db = new DBH('postgres://postgres@localhost/db2test')
using(db.conn(), function (conn) {
// a connection from the pool
return conn
.fetchOne('select * from user where id=$1', [10])
.then(function (user) {
console.log(user) // {id:10, name:...}
})
}) // automatic release the connection to pool
Transactions
- Call
conn.begin
to start the transaction- Use the transaction
- Explicit call
conn.commit
, else auto rollback is applied before release the connection to the pool.
// send 10 coins from user_id=3 to user_id=4
using(db.conn(), function (conn) {
return conn
.begin() // start transaction
.then(function () {
// 'this' points to the created connection 'conn'
return this.exec(
'update wallet \
set coins = coins - 10 \
where user_id=$1',
[3]
);
}).then(function () {
return this.exec(
'update wallet \
set coins = coins + 10 \
where user_id=$1',
[4]
);
}).then(function () {
// commit the transaction!
return this.commit();
});
});
conn.begin
conn.exec
conn.commit
Parallel task
// print array of data (from query) and the total items in the table
using(db.conn(), db.conn(), function (conn1, conn2) {
return Promise.join(
conn1.fetchAll('select * from user limit 10'),
conn2.fetchOne('select count(*) from user')
)
.then(function (items, total) {
console.log(items, total) // array of objects, number
})
})
Using Shorthands
// shorthands are static methods in the DBH 'class'.
// This is the same example used in Transaction
using(db.conn(), function (conn) {
conn
.begin()
.then(DBH.exec(
'update wallet \
set coins = coins - 10 \
where user_id=$1',
[3]
)).then(DBH.exec(
'update wallet \
set coins = coins + 10 \
where user_id=$1',
[4]
)).then(DBH.commit())
})
Using objects as replacement
// This is the first example, note that
// instead of $1 this uses $id
using(db.conn(), function (conn) {
return conn
.fetchOne('select * from user where id=$id', { id : 10 })
.then(function (user) {
console.log(user)
})
})
Prepared Statements
// DBH.prepare receives a SQL statement and return function that receives the
// replacement as an array of params.
// Note that DBH.prepare can be used outside the 'using'.
var prepared = DBH.prepare('insert into country_code values($1)')
using(db.conn(), function (conn) {
var me = this;
var promises = ['ar', 'cl', 'jp', 'pe', 'co'].map(function (code) {
return me.exec(prepared(code))
})
return Promise.all(promises)
})
Contributing
We ♥ contributions
Please create a (tested) pull request :)