voltron-postgres-adapter

voltron-postgres-adapter is a Promise-focused adapter for connecting to PostgreSQL databases. It makes use of node-sql, node-postgres, and Q. It's a componeont of voltron.io, but can be used independently of any other voltron components.

While it's fairly easy to use the node-postgres driver directly, this adapter provides an interface to simplify common actions.

Getting Started

Install with NPM:

npm install --save voltron-postgres-adapter

Usage

Adapters should be instantiated on a one-to-one basis with tables in your applicaton's DB. A simple example would be the following:

var PostgresAdapter = require('voltron-postgres-adapter');

var adapter = new PostgresAdapter('users');

adapter.executeQuery('SELECT * from users')
  .then(function (result) {
    //do something with result.rows
  });

Or a query with values:

adapter.executeQuery('SELECT * from users WHERE users.name = $1', ['joe']);

Values supplied to executeQuery are parsed in order to avoid common mistakes - arrays are cast as stirngs with '{' delimiters, and commas and single quotes are escaped.

Integration with node-sql

voltron-postgres-adapter's power is greatly improved by its integration with node-sql, which allows for significantly more concise queries.

The following example demonstrates a complex query with node-sql:

var PostgresAdapter = require('voltron-postgres-adapter');

var userAdapter = new PostgresAdapter('users', ['user_id', 'name']);
var postAdapter = new PostgresAdapter('posts', ['post_id', 'author_id']);

var Users = userAdapter.relation;
var Posts = postAdapter.relation;

adapter.executeQuery(Users.where(Posts.author_id.equals(Users.user_id)));
  .then(function (result) {
    //do something with result.rows
  });

That node-sql query above is automatically issued as the SQL:

SELECT "users".* FROM "users" WHERE ("posts"."author_id" = "users"."user_id")

See the node-sql project page for more information.

Transactions

voltron-postgres-adapter aims to make transactions significantly easier. The basic logic is:

1. Retrieve a node-postgres client from the connection pool, and retain it.
2. Issue queries in the transaction as normal.
3. End the transaction with a ROLLBACK or COMMIT depending on query results
4. Release the node-postgres client back to the connection pool.

In practice...

var client;
var transaction = userAdapter.startTransaction()
var promise = transaction
  .begin()
  .then(function (c) {
    client = c;
    return OtherModel.doSomething(params, client);
  })
  .then(function (resultOfLastAction) {
    return doSomethingElse(params, client);
  })

transaction.end(promise);

The retained client instance is also stored as a property on the transaction, so the transaction itself can be passed as an argument, from which a chain of actions can be executed.

Interface

PostgresAdapter

Constructor

Arguments: tableName[String], fields[Array<String>]

Instantiate a new PostgresAdapter, setting the tableName on the new instance. If fields are passed, a node-sql relation is created for the provided tableName and fields.

PostgresAdapter::configure

Arguments: config[node-postgres driver configuration object]

Global configuration function that changes the config for ALL instances of adapters. See the node-postgres docs for further information.

config (get/set)

Arguments: config[node-postgres driver configuration object]

Configure a Postgres connection for a single adapter instance.

executeQuery

Arguments: query[String OR Object], client[node-postgres client]

Execute a query. If a client is provided, use that client to issue the query, otherwise one will be retrieved from the pool. A query argument is required, and can be in the form of:

• a String
• a node-sql query object (has toQuery interface)
• a generic Object (with text: String and optional values: Array)

Values will be parsed for common syntax errors: arrays and nested arrays will be cast to Strings with '{' delimeters, and commas and quotes will be parameterized.

batchQuery

Arguments: queries[Array<String> or Array<Object>], client[node-postgres client]

Execute a series of queries with a single call to the DB. Works the same as executeQuery, except queries are batched.

startTransaction

Arguments: n/a

Instantiate a new Transaction, passing the adapter instance. Returns the transaction.

Transaction

begin

Arguments: n/a

Begin a transaction by retrieving a client from the pool, pausing drain on it, and issuing a BEGIN query. Returns a promise that resolves with the client.

commit

Arguments: n/a

Issue a COMMIT query with the transaction's client.

rollback

Arguments: n/a

Issue a ROLLBACK query with the transaction's client.

end

Arguments: promise[promise from executed queries]

At the resolution or rejection of a query's promise, end the transaction with a COMMIT (for a resolved promise) or ROLLBACK (for rejected promise). Returns a promise that either resolves with the value of the last query's promise, or is rejected with the value of the last query promise's error.

Release History

v0.2

• First stable release, test coverage, and documentation

License

Copyright (c) 2013 Justin Reidy Licensed under the MIT license.