pg-extra

Requires Node 8.x+ and pg 7.3+.

A simple set of extensions and helpers for node-postgres.

Quick Overview

• Does not mutate pg module prototypes.

• extend(require('pg')) creates pg.extra namespace with pg.extra.Pool and pg.extra.Client.

• Extends pg.extra.Pool with prototype methods many, one, withTransaction, stream.

• Extends pg.extra.Client with prototype methods many, one.

• Extends both with .prepared(name).{query,many,one}()

• The above methods all return promises just like the existing pool.query() and client.query().

• Configures the client parser to parse postgres ints and numerics into javascript numbers (else SELECT 1::int8 would return a string "1").

• Exposes sql and _raw template literal helpers for writing queries.

  const uname = 'nisha42'
  const key = 'uname'
  const direction = 'desc'
  
  await pool.one(
      sql`
    SELECT *
    FROM users
    WHERE lower(uname) = lower(${uname})
  `.append(_raw`ORDER BY ${key} ${direction}`)
  )
  

• All query methods fail if the query you pass in is not built with the sql or _raw tag. This avoids the issue of accidentally introducing sql injection with template literals. If you want normal template literal behavior (dumb interpolation), you must tag it with _raw.

Install

npm install --save pg-extra pg

Usage / Example

const { extend, sql, _raw } = require('pg-extra')
const pg = extend(require('pg'))

const connectionString = 'postgres://user:[email protected]:5432/my-db'

const pool = new pg.extra.Pool({ connectionString, ssl: true })

exports.findUserByUname = async function(uname) {
    return pool.one(sql`
    SELECT *
    FROM users
    WHERE lower(uname) = lower(${uname})
  `)
}

exports.listUsersInCities = async function(cities, direction = 'DESC') {
    return pool.many(
        sql`
    SELECT *
    FROM users
    WHERE city = ANY (${cities})
  `.append(_raw`ORDER BY uname ${direction}`)
    )
}

exports.transferBalance = async function(from, to, amount) {
    return pool.withTransaction(async (client) => {
        await client.query(sql`
      UPDATE accounts SET amount = amount - ${amount} WHERE id = ${from}
    `)
        await client.query(sql`
      UPDATE accounts SET amount = amount + ${amount} WHERE id = ${to}
    `)
    })
}

Streaming

Return a readable stream of query results.

In this example, we want to stream all of the usernames in the database to the browser.

• pool.stream() returns Promise<stream.Readable> rather than just stream.Readable.
• Provide an optional second argument to transform each row.

const { _raw } = require('pg-extra')

router.get('/usernames', async (ctx) => {
    const stream = await pool.stream(
        _raw`
        SELECT uname
        FROM users
        ORDER BY uname
    `,
        (row) => row.uname
    )

    ctx.body = stream
})

Extensions

• pool.query(sql`string`): Resolves a postgres Result.
• pool.many(sql`string`): Resolves an array of rows.
• pool.one(sql`string`): Resolves one row or null.
• client.query(sql`string`): Resolves a postgres Result.
• client.many(sql`string`): Resolves an array of rows.
• client.one(sql`string`): Resolves one row or null.
• {pool,client}.prepared('funcName').query(sql`string`)
• {pool,client}.prepared('funcName').many(sql`string`)
• {pool,client}.prepared('funcName').one(sql`string`)
• {pool,client}._query(sql, [params], [cb]): The original .query() method. Useful when you want to bypass the sql/_raw requirement, like when executing sql files.

Query template tags

pg-extra forces you to tag template strings with sql or _raw. You usually use sql.

sql is a simple helper that translates this:

sql`
  SELECT *
  FROM users
  WHERE lower(uname) = lower(${'nisha42'})
    AND faveFood = ANY (${['kibble', 'tuna']})
`

into the sql bindings object that node-postgres expects:

{
  text: `
    SELECT *
    FROM users
    WHERE lower(uname) = lower($1)
      AND faveFood = ANY ($2)
  `,
  values: ['nisha42', ['kibble', 'tuna']]
}

_raw is how you opt-in to regular string interpolation, made ugly so that it stands out.

Use .append() to chain on to the query. The argument to .append() must also be tagged with sql or _raw.

sql`${'foo'} ${'bar'}`.append(_raw`${'baz'}`) // '$1 $2 baz'
_raw`${'foo'} ${'bar'}`.append(sql`${'baz'}`) // 'foo bar $1'

.append() mutates the sql statement object, but you can use .clone() to create a deep copy of an existing instance.

const statement1 = sql`SELECT 100`
const statement2 = statement1.clone().append(sql`, 200`)

statement1.text === 'SELECT 100'
statement2.text === 'SELECT 100 , 200'

Optimization: Bindings Reuse

(Added in v1.0)

Bindings are reused for equal (===) values.

For example:

const bbcode = '[b]hello world[/b]'

pool.query(sql`
    INSERT INTO posts (length, bbcode, html)
    VALUES (char_length(${bbcode}), ${bbcode}, bbcode_to_html(${bbcode}))
`)

pg-extra sends that query over the wire to Postgres as this:

{
    text: `
        INSERT INTO posts (length, bbcode, html)
        VALUES (char_length($1), $1, bbcode_to_html($1))
    `,
    values: ['[b]hello world[/b]']
}

rather than this:

{
    text: `
        INSERT INTO posts (length, bbcode, html)
        VALUES (char_length($1), $2, bbcode_to_html($3))
    `,
    values: ['[b]hello world[/b]', '[b]hello world[/b]', '[b]hello world[/b]']
}

Since bbcode === bbcode, pg-extra will reuse the $1 binding instead of creating and broadcasting additional bindings $2 and $3.

Keep in mind that === equality compares non-primitive objects like arrays by reference, so VALUES (${[1, 2]}, ${[1, 2]}) (i.e. two different arrays) will be sent as two bindings even though they have the same contents.

Cookbook

Dynamic Queries

Reply to issue: https://github.com/danneu/pg-extra/issues/1

Let's say you want to bulk-insert:

INSERT INTO users (username)
VALUES
('john'),
('jack'),
('jill');

...And you want to be able to use your bulk-insert query whether you're inserting one or one hundred records.

I recommend using a SQL-generator like knex:

const knex = require('knex')({ client: 'pg' })
const { extend, _raw } = require('pg-extra')
const pg = extend(require('pg'))

const pool = new pg.extra.Pool({
    connectionString: 'postgres://user:[email protected]:5432/my-db',
})

// `usernames` will look like ['jack', 'jill', 'john']
exports.insertUsers = function(usernames) {
    const sqlString = knex('users')
        // we want to pass [{ username: 'jack' }, { username: 'john' }, ...]
        // to the .insert() function, which is a mapping of column names
        // to values.
        .insert(usernames.map((username) => ({ username })))
        .toString()
    return pool.query(_raw`${sqlString}`)
}

Note: Or you can circumvent pg-extra entirely with pool._query(string).

Test

Setup local postgres database with seeded rows that the tests expect:

$ createdb pg_extra_test
$ psql -d pg_extra_test -c 'create table bars (n int not null);'
$ psql -d pg_extra_test -c 'insert into bars (n) values (1), (2), (3);'

Then run the tests:

npm test

CHANGELOG

• v2.0.0:
  • extend(require('pg')) now creates extended Pool/Client in a pg.extra.{Pool,Client} namespace instead of mutating pg's prototypes.
• v1.1.0:
  • Added SqlStatement#clone().
• v1.0.0:
  • Deprecated q and _unsafe.
  • Added bindings reuse optimization.

TODO

• Add withTransaction() to pg.extra.Client.
• Add stream() to pg.extra.Client.