handy-postgres

A handy API for Postgres which uses promises and this super library, which also uses pool handling.

Configuration

"pg": {
  "user": "postgres",
  "database": "postgres",
  "password": "password",
  "host": "localhost",
  "port": 5432,
  "max": 10, // Maximum number of connections in the pool
  "sql": "myfolder/sql", // optional location for sql files
  "idleTimeoutMillis": 30000,
}

Multiple Connections

You can specify a different config path by passing it in when instantiating the component:

HandyPg({ configPath: 'mykey' });

You can find configuration examples here

Loading SQL Files

By specifying the location for sql files, it will automatically read and cache all of the sql in the folder for use in your model, reducing boilerplate.

File:

/src/model/sql/test.sql

How to use these shorthand functions is explained below.

The API

After creating a Handy PG component, the following methods will be available:

| Property | Description | Promise result | | ------------- | ------------- | ----------- | | withTransaction | Begin a new named transaction pg.withTransaction(next) | (connection) => {} | | withConnection | Claims a connection from the pool pg.withConnection(next) | | query | Execute an unformatted query using shorthands SELECT $1::INT AS number defaulting to a raw query if it cannot find one | (result) => {} | | streamQuery | Same as query, but returns a stream ('data', 'error', 'end'). Multiple queries not possible (throws error). | (stream) => {} | | formattedQuery | Execute a formatted query using shorthands SELECT %L::INT AS %I | (result) => {} | | formattedStreamQuery | Same as formattedQuery but returns a stream ('data', 'error', 'end'). Multiple queries not possible (throws error). | (stream) => {} | | insert | Insert data (table, data, options) (object options is optional. It accepts the boolean property returning to retrieve inserted data) | () => {} | | update | Update data (table, update, where, options) (objects where and options are optional. where accepts where conditions, options is analogous to insert usage)| () => {} | | schema | Sets a schema and returns the query operations to use with that schema (schema)| ({ query, formattedQuery, insert, update }) => {} | | explain | Execute an explain plan for an unformatted query | | formattedExplain | Execute an explain plan for a formatted query | | copyFrom | Copy table contents from read stream | | copyTo | Copy table contents to write stream |

You can find some examples for query, formattedQuery, insert and update here

Transactions

Transactions are made easier via a helper withTransaction block. This helper takes a function that receives a 'transaction' object, returning a promise chain where all your operations will be placed. The 'transaction' object gives you the same 'query' helpers as explained above, reusing a single connection for all operations within the tx. The usual rollback, commit and begin operations are also exposed but they are abstracted away by the 'withTransaction' helper.

pg.withTransaction((tx) =>
  Promise.all([
    tx.schema('myschema').insert('films', myFilm1),
    tx.schema('myschema').insert('films', myFilm2),
  ])
)
.catch((err) => {
  // Error occurred (but it still rolled back and closed connection)
})

You can find some transactions examples here

Isolation Levels

Sometimes you will need to use a different transaction isolation level than the default one. You can read more about this here.

handy-postgres lets you specify your own in config:

{
  withSql: {
    ...
    isolationLevel: 'REPEATABLE READ',
    ...
  }
}

Also, you could override this configuration on specific transactions by passing the isolation level as second argument whenever you use the withTransaction operation:

pg.withTransaction((tx) =>
  Promise.all([
    tx.schema('myschema').insert('films', myFilm1),
    tx.schema('myschema').insert('films', myFilm2),
  ]), 'SERIALIZABLE' // I want this transaction in particular to use the SERIALIZABLE isolation level
)
.catch((err) => {
  // Error occurred (but it still rolled back and closed connection)
})

Migrations

Handy postgres uses marv to offer migration support. To use it, you need to specify marv options in migrations field. It will use handy-postgres configuration as connection options for marv.

"pg": {
  // ...
  "migrations": [{ "directory": "src/migrations", "namespace": "test", "filter": "\\.sql$" }],
}

You can also specify a different migration user, e.g.

"pg": {
  "migrationsUser": "marv",
  "migrationsPassword": "secret",
  "migrations": [{ "directory": "src/migrations", "namespace": "test", "filter": "\\.sql$" }],

Streams

If you would like to query a very large data set, you may have to use a stream, here's how:

Promise.resolve()
  .then(() => pg.streamQuery('SELECT loads FROM data'))
  .then((stream) => {
     return new Promise((resolve, reject) => {
       stream.on('data', (data) => {
         // do something with data...
       });
       stream.on('error', reject);
       stream.on('end', () => resolve({ result: /*...*/ }));
    });
  })

Also check out promisepipe and promise-streams