Skip to content
/ pg-Lazy Public

Helpers for node-postgres for Lazy devs. πŸ‘½

License

Notifications You must be signed in to change notification settings

uniibu/pg-Lazy

Folders and files

NameName
Last commit message
Last commit date

Latest commit

Β 

History

87 Commits
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 

Repository files navigation

pg-Lazy

Build Status NPM version Dependency Status js-semistandard-style Coverage Status

Simple functional helpers for node-postgres.

Requires Node >= ^10.0.0 and node-postgres ^8.0.0

Breaking Changes from v2.x.x to v3.x.x

  • 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.

Breaking Changes from v1.x.x to v2.x.x

  • 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

Installation

npm install pg-lazy pg --save or yarn add pg-lazy pg

Usage

Manual Pool initialization:

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 name = ${name}`);
  // 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 = ${id}`);
  // none for no result
  return pool.many(sql`SELECT * FROM TABLE WHERE id < 0`);
}
async function(){
  const username = await getUser('john',5)
}

Automatic Pool initialization:

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(sql`SELECT * FROM TABLE WHERE name = ${name}`);
  // 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 = ${id}`);
  // none for no result
  return pool.many(sql`SELECT * FROM TABLE WHERE id < 0`);
}
async function(){
  const username = await getUser('john',5)
}

Helpers

  • pg.Pool with prototype methods query,many, one, none, withTransaction, canConnect.

  • pg.Client with prototype methods query,many, one, none, canConnect.

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

  • All methods returns a Promise

  • Automatically defaults to Environment variables for DB config, that means you can also set your DB config via process.env

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

  • Accepts String, Objects and connectionString for configuration,

  • 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.

Example

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(_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}
    `)
  })
}

Check more examples on the Test folder

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'

Test

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`

Changelog

ChangeLog

Shouts

About

Helpers for node-postgres for Lazy devs. πŸ‘½

Resources

License

Stars

Watchers

Forks

Packages

No packages published