Transactions, named parameters, and more for node-postgres
npm install pg-db

* Install
* Usage
* Features
* Transaction
* Named Parameter Support
* API
* Query API
* Transaction API - Control Flow
* Transaction API - Query and DML
* Transaction API - Success/Failure Hooks
* Events API
* Building and Testing
* License
$ npm install pg-db --save
// Create using default connection config of process.env.DATABASE_URL:
var db = require('pg-db')();
db.query('SELECT foo, bar, baz FROM some_table', function(err, rows){
if( err ) return console.error('Err: %s', err);
console.log('Rows: %j', rows);
});
done()*done(err)*... WHERE foo = :foo instead of ... WHERE foo = $1*client object to be used by separate parts of your application without having to manually pass it as an argument.Any other modules that use pg-db for query execution will automatically be part of the ongoing transaction. This allows you to easily compose multiple database interactions together without needing to pass a transactional context object to every single function.
// Foo.update(foo, cb) - Updates a Foo model object
// Audit.create(message, cb) - Creates an audit record
db.tx.series([
async.apply(Foo.update, foo),
async.apply(Audit.create, 'Updating foo id=' + foo.id)
], cb);
Example:
// SQL with numbered parameters:
db.queryOne('SELECT * FROM some_table WHERE foo = $1'
, [123]
, function(err, row) {...})
// SQL with named parameters:
db.queryOne('SELECT * FROM some_table WHERE foo = :foo'
, {foo: 123}
, function(err, row) {...})
A more complicated example:
// Classic style with positional parameters:
db.update('INSERT INTO user'
+ ' (id, name, email, password_hash)'
+ ' VALUES '
+ ' ($1, $2, $3, $4)'
, [1, 'alice', 'alice@example.org', hash('t0ps3cret')]
, function(err, rowCount) { / do something / });
// Same query with named parameters:
db.update('INSERT INTO user'
+ ' (id, name, email, password_hash)'
+ ' VALUES '
+ ' (:id, :name, :email, :passwordHash)'
, {id: 1, name: 'alice', email: 'alice@example.org', passwordHash: hash('t0ps3cret')}
, function(err, rowCount) { / do something / });
Another example with a model object:
var widget = {
id: 12345,
name: 'My Widget',
type: 'xg17',
owner: 'me@example.org'
};
// Classic style with positional parameters:
db.update('INSERT INTO widgets'
+ ' (id, name, type, owner)'
+ ' VALUES '
+ ' ($1, $2, $3, $4)'
, [widget.id, widget.name, widget.type, widget.owner]
, function(err, rowCount) { / do something / });
// Same query with named parameters:
db.update('INSERT INTO widgets'
+ ' (id, name, type, owner)'
+ ' VALUES '
+ ' (:id, :name, :type, :owner)'
// We can just pass in the object as is:
, widget
, function(err, rowCount) { / do something / });
client for the transactions.If no transaction is in progress then a random connection will be retrieved from the pool of connections. After execution completes the connection will be returned to the pool.
If an error occurrs then by default the pool wil be instructed to destroy the connection. Internally, this is done by invoking done(err).
This function is intended to be used with SQL that returns back a set of rows such as a SELECT ... statement. If no rows are returned then the callback will be invoked with an empty array.
This function is intended to be used with SQL that returns back a single row. If no rows are returned then the callback is invoked with a null value for row. If more than one row is returned then the callback is invoked with an Error.
__NOTE:__ This function will return an Error if multiple rows are returned. This is intentional as it probably means your SQL is wrong.
This function is intended to be used with SQL that performs DML (e.g. INSERT, UPDATE, DELETE).
This function is used internally by query, queryOne, and update. It's useful when you'd like to use both the rowCount and rows. Otherwise it's probably more convenient to use one of the other functions.
Internally this executes the tasks by calling async.series.
Internally this executes the tasks by calling async.parallel.
Internally this executes the tasks by calling async.auto.
Internally this executes the tasks by calling async.waterfall.
If no transaction is in progress then the callback is invoked with an error. Otherwise this behaves exactly like the non-tx version.
If no transaction is in progress then the callback is invoked with an error. Otherwise this behaves exactly like the non-tx version.
If no transaction is in progress then the callback is invoked with an error. Otherwise this behaves exactly like the non-tx version.
If no transaction is in progress then the callback is invoked with an error. Otherwise this behaves exactly like the non-tx version.
If a callback has an arity of 0, i.e. function() {...}, then it is assumed to be a synchronous function.
Otherwise it is assumed to accept a single paramater for the callback function that should be invoked, i.e. function(cb) {...}.
Any errors thrown or asynchronously returned back from callbacks are ignored.
Multiple callbacks are executed in the order they are registered.
The follow event types are supported:
* execute - triggered whenever a query is executed.
* executeComplete - triggered after a query is executed.
* begin - triggered before a transaction is started.
* beginComplete - triggered after a transaction is started.
* commit - triggered when a transaction is about to be committed.
* commitComplete - triggered after a transaction is committed.
* rollback - triggered when a transaction is about to be rolled back.
* rollbackComplete - triggered after a transaction is rolled back.
$ make
To run the tests first create a test/env file. You can use test/env.example as a template. Edit the DATABASE_URL property to point to a Postgres database.
Then, to run the tests run:
$ make test