dbh-pg

#!BDH-PG



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:
``sh
$ npm install dbh-pg
`
It is recommended that you also install [bluebird][] for use Promise.using:
`sh
$ npm install bluebird
`


Usage

> 1. Require the dependencies.
> 2. Instantiate the DBH (Internally creates a connection pool).
> 3. Use Promise.using (or the shorhand DBH.using) to get a connection from the pool and then auto release it. Is important that the callback function returns the connection promise.

`javascript
// 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
`
conn.fetchOne

$3

> 1. Call conn.begin to start the transaction
> 2. Use the transaction
> 3. Explicit call conn.commit, else auto rollback is applied before release the connection to the pool.

`javascript
// 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


$3

`javascript
// 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
})
})
`

$3

`javascript
// 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())
})
`
DBH shorthands

$3

`javascript
// 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)
})
})
`
named parameterized queries

$3

`javascript
// 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)
})
`
DBH.prepare


Contributing

We ♥ contributions

Please create a (tested) pull request :)

Alternatives

* pg-promise
* pg-using-bluebird`

License

MIT LICENSE

[node-postgres]: https://github.com/brianc/node-postgres#node-postgres
[bluebird]: https://github.com/petkaantonov/bluebird#introduction