Requires Node 7.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'))
createspg.extra
namespace withpg.extra.Pool
andpg.extra.Client
. -
Extends
pg.extra.Pool
with prototype methodsmany
,one
,withTransaction
,stream
. -
Extends
pg.extra.Client
with prototype methodsmany
,one
. -
Extends both with
.prepared(name).{query,many,one}()
-
The above methods all return promises just like the existing
pool.query()
andclient.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 -
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 = const pg = const connectionString = 'postgres://user:pass@localhost:5432/my-db' const pool = connectionString ssl: true exports { return pool} exports { return pool} exports { return pool}
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()
returnsPromise<stream.Readable>
rather than juststream.Readable
.- Provide an optional second argument to transform each row.
const _raw = router
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 thesql
/_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() AND faveFood = ANY ()`
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` ` // '$1 $2 baz'_raw` ` // '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 statement1text === 'SELECT 100'statement2text === '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
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 = client: 'pg' const extend _raw = const pg = const pool = connectionString: 'postgres://user:pass@localhost:5432/my-db' // `usernames` will look like ['jack', 'jill', 'john']exports { const sqlString = // we want to pass [{ username: 'jack' }, { username: 'john' }, ...] // to the .insert() function, which is a mapping of column names // to values. return pool}
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 apg.extra.{Pool,Client}
namespace instead of mutating pg's prototypes.
- v1.1.0:
- Added
SqlStatement#clone()
.
- Added
- v1.0.0:
- Deprecated
q
and_unsafe
. - Added bindings reuse optimization.
- Deprecated
TODO
- Add
withTransaction()
topg.extra.Client
. - Add
stream()
topg.extra.Client
.