postgres-gen expects there to be a reasonable ES6 promise implementation available. when.js is currently used in the test script, but any other implementation should work.
postgres-gen is a wrapper for pg or pg.js that facilitates the running PostgreSQL queries or transactions inside a generator using promises. There are a few scenarios in which this wrapper is particularly useful.
1. Using yield to execute a query or transaction inside another generator
All postgres-gen methods return a promise for the result of the query or transaction, so other flow-control generator libraries that handle promises, like koa, can handle the query appropriately.
var pg = 'connection string or config object here';myKoaApp
2. Using a generator to execute a transaction with proper failure handling
The transaction method takes a generator function and passes in a helper to execute queries that will be wrapped in a transaction. If any exceptions are thrown within the generator function or any promises that are yielded from within the generator are rejected, the transaction will be rolled back and the promise returned from the transaction method will be rejected with the error.
var pg = 'connection string or config object here';pg;
It's important to note that any queries that are not yielded will be run in sequence due to the way the pg driver query queue works, but they will not cause the transaction to fail properly if there are any errors, as the transaction runner will have no knowledge of them. Any subsequent queries will cause the transaction to fully abort, since the server-side transaction will be failed. Having the last query in the transaction not participate in the transaction would have unfortunate consequences. Always yield!
3. Named parameters
Starting with 0.1.0, postgres-gen supports querying with named parameters in addition to positional parameters. If a parameters object is passed instead of a parameters array, the sql string may contain object keys as parameters. The named parameters will be converted to positional parameters before the query is passed on the the pg driver.
4. ? parameters
node-postgres requires positional parameters to be numbered with $s. Sometimes it's more convenient to have a simple ? in a query than numbering each one.
select * from cars where classification in ($1, $3, $4) and id > $2;
Notice that array parameter is replaced with its first element with additional elements added to the end of the param references, so that this also works nicely with named and number parameters.
If you need to have an array converted into a literal
ARRAY[...] in the query, you can add a property,
literalArray, to the array and have it set to
true before passing the array as a parameter.
6. Tagged template SQL
If you happen to be on an ES6 platform (or using a transpiler), you can use any query methods as tagged template handlers. Any interpolations will be turned into SQL parameters, unless they're a special literal, in which case they are inserted into the query string just like a non-tagged template.
// in co or koa or some handy generator flow-control contextlet name = 'foo' table = 'bar' age = 22;let people = pgquery`select * from where name = and age > ).rows;
In this example, the query is turned into:
select * from bar where name = $1 and age > $2
and the record array is assigned to the people variable.
A note on connection pooling...
By default, all connections use the underlying pooling mechanism of
node-postgres, which gives you a 10 connection pool with a 30 second idle timeout. That fits nicely with the single-threaded event loop of node from what I've seen. This means that there is no way to immediately close a connection while pooling is enabled, because the connection will just return to the pool. That's what it's there for, after all - to eliminate the connection setup/teardown. If you want to manage connection lifecycle closely, you can disable pooling. With pooling disabled, a single query will open a connection, run the query and close the connection. The same goes for a transactions e.g. when the transaction starts, the connection is opened, and when it completes, it is closed.
0.9.0, you can now drain and dispose the pool associated with an instance by using the new
close method. You can also drain and dispose all of the pools by useing the new module
var pg = ;pgpg; // this is the node-postgres module used by postgres-genpg; // this closes all connections everywhere// it returns a promise that resolves when the connections are closedvar con = ;conpg; // also the node-postgres module used by postgres-gencon; // this closes all of the connections in this instance's connection pool (by connection string)// it returns a promise that resolves when the connections in this particular pool are closed
Obviously, if there are no connections open (no queries have been run), both of these new methods will resolve immediately - or at least as immediately as a Promise can resolve.