Helpers for node-postgres for Lazy devs
npm install pg-lazySimple functional helpers for node-postgres.
Requires Node >= ^10.0.0 and node-postgres ^8.0.0
- Pool and Client are no longer an instance of pg._Pool and pg._Client respectively.
- isConnected is renamed with canConnect
- Now user proper ES6 class extends.
- Due to new es6 codes, this module now requires Node v8.1.4 and above.
- This module no longer mutates pg.Pool and pg.Client, it instead extends them and store them as pg._Pool and pg._Client
- It no longer automatically initialize the Pool unless a third Object argument is passed {singleton:true}
- pg-Lazy now returns a default Object { pg, Pool, Client, sql, _raw } in which Pool is an instance of pg._Pool and Client is an instance of pg._Client. To get the original pg.Pool and pg.Client instances, you can use pg to access them.
- If {singleton:true} is passed as a third argument, it then adds pool from the returned Object. This pool is an already-initialized pg._Pool
- Read more changes here ChangeLog
npm install pg-lazy pg --save or yarn add pg-lazy pg
Manual Pool initialization:
``jsSELECT * FROM TABLE WHERE name = ${name}
const pgLazy = require('pg-lazy');
// create your configuration
const connectionString = 'postgres://localhost:5432/pg_test';
// pool instance is no longer initiated, you must initialize it using pg.Pool.
const { Pool, sql, _raw, pg } = pgLazy(require('pg'), { connectionString });
const pool = new Pool()
async function getUser(name,id){
// regular query
return pool.query(sql);SELECT * FROM TABLE WHERE id > ${id}
// many for more than 1 result
return pool.many(sql);SELECT * FROM TABLE WHERE id = ${id}
// one for single result
return pool.one(sql);SELECT * FROM TABLE WHERE id < 0
// none for no result
return pool.many(sql);`
}
async function(){
const username = await getUser('john',5)
}
Automatic Pool initialization:
`js
const pgLazy = require('pg-lazy');
// create your configuration
const connectionString = 'postgres://localhost:5432/pg_test';
// pool instance is automatically initialized when passing {singleton:true}
const { pool, sql, _raw, pg } = pgLazy(require('pg'), { connectionString }, {singleton:true});
async function getUser(name,id){
// regular query
return pool.query(sqlSELECT * FROM TABLE WHERE name = ${name});SELECT * FROM TABLE WHERE id > ${id}
// many for more than 1 result
return pool.many(sql);SELECT * FROM TABLE WHERE id = ${id}
// one for single result
return pool.one(sql);SELECT * FROM TABLE WHERE id < 0
// none for no result
return pool.many(sql);`
}
async function(){
const username = await getUser('john',5)
}Helpers
- pg.Pool with prototype methods query,many, one, none, withTransaction, canConnect.query
- pg.Client with prototype methods ,many, one, none, canConnect..prepared(name).{query,many,one}()
- Extends both with
- All methods returns a Promise
- Automatically defaults to Environment variables for DB config, that means you
can also set your DB config via
- Configures the client parser to parse postgres ints and numerics
into javascript numbers (else would return a string "1").sql
- Accepts String, Objects and connectionString for configuration,
- Exposes and _raw template literal helpers for writing queries.
` javascript
const uname = 'nisha42'
const key = 'uname'
const direction = 'desc'
await pool.one(sql
SELECT *
FROM users
WHERE lower(uname) = lower(${uname})
.append(_rawORDER BY ${key} ${direction}))`
- All query methods fail if the query you pass in is not built with the
or _raw tag. This avoids the issue of accidentally introducing_raw
sql injection with template literals. If you want normal template literal
behavior (dumb interpolation), you must tag it with .
` javascript
const pgLazy = require('pg-lazy');
const url = 'postgres://user:pass@localhost:5432/my-db'
const { pool, sql, _raw, pg } = pgLazy(require('pg'), { connectionString:url },{ singleton: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(_rawORDER 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}
)`
})
}
Check more examples on the Test folder
pg-extra forces you to tag template strings with sql or _raw.sql
You usually use .
sql is a simple helper that translates this:
` javascript
sql
SELECT *
FROM users
WHERE lower(uname) = lower(${'nisha42'})
AND faveFood = ANY (${['kibble', 'tuna']})
into the sql bindings object that node-postgres expects:
` javascript
{
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 or _raw.
` javascript${'foo'} ${'bar'}
sql.append(_raw${'baz'}) // '$1 $2 baz'${'foo'} ${'bar'}
_raw.append(sql${'baz'}) // 'foo bar $1'`
Setup local postgres database with seeded rows that the tests expect:
- psql -c 'create user lazy_test_user with password '"'lazy_test_pw'"';' -U postgres
- psql -c 'create database lazy_test owner lazy_test_user;' -U postgres
- psql -d lazy_test -c 'create table bars (n int not null);' -U lazy_test_user
- psql -d lazy_test -c 'insert into bars (n) values (1), (2), (3);' -U lazy_test_user
Then run the tests:
yarn test or npm test`
- Heavily inspired by pg-extra.