pg-x

Robust helpers for pg.

Synopsis

Using pg's in-built pool:

const pool = new pg.Pool({
  connectionString : 'postgres://pgx@localhost/pgx',
})

Then you can pass the pool (a pg.pool) to these functions:

// promise
const selCount = 'SELECT count(*) AS count FROM tablename'
const row = await pgx.one(client, selCount)

// callback
const selCount = 'SELECT count(*) AS count FROM tablename'
pgx.one(client, selCount, (err, row) => {
  if (err) throw err

  console.log(Row:', row || 'none')
})

You can also pass a pg.client instead of a pg.pool to any method, since they both have a .query() method that works the same.

// promise - checkout a client
pool.connect()
  .then((client, done) => {
    const selCount = 'SELECT count(*) AS count FROM tablename'
    return pgx.one(client, selCount)
  })
  .finally(done)
;

// callback - checkout a client
pool.connect((err, client, done) => {
  if (err) throw err

  // one
  const selCount = 'SELECT count(*) AS count FROM tablename'
  pgx.one(client, selCount, (err, row) => {
    done()
    // do something with error
    if (err) return null

    console.log(Row:', row || 'none')
  })
})

API

Note: poc means "pool or client" which is either a pg.pool or a pg.client.

.query(poc, query, callback)

Pass it a query much as you would with a pg.pool or a pg.client. The rows is equivalent to result.rows and result is exactly what is given back to us by pg.

It is just a convenience function around the pool.query() and client.query() functions to also get the rows out in one call. That's all. Perhaps this is better named just .query() to reflect the pool.query() or client.query() in pg.

// query
const insBlah = 'INSERT INTO blah(name) VALUES($1)'
const query = {
  text : insBlah,
  values : [ 'John Doe' ],
}
pgx.query(pool, query, (err, rows, result) => {
  if (err) throw err

  console.log(`${result.rowCount} rows were affected`)
})

.one(poc, query, callback)

Get one row from the results (or null if there are no rows returned).

Example:

// one
const selCount = 'SELECT count(*) AS count FROM tablename'
pgx.one(pool, selCount, (err, row) => {
  if (err) throw err
  console.log(Found row:', row || 'none')
})

.sel(poc, table, col, val, callback)

Get all matching rows from a table where col = val (or [] if nothing matches).

Example:

// sel
pgx.sel(pool, 'blog', 'account_id', 23, (err, rows) => {
  if (err) throw err

  // `rows` is always an array - but empty if there are no matching rows
  // It is never `null` or `undefined` unless there was an `err`.
  console.log(`There are ${rows.length} rows`)
})

.all(poc, query, callback)

Get all rows from a table (or [] if nothing matches).

Example with direct SQL:

// all
const selAll = 'SELECT * FROM tablename'
pgx.all(pool, selAll, (err, rows) => {
  if (err) throw err

  // `rows` is always an array - but empty if there are no matching rows
  // It is never `null` or `undefined` unless there was an `err`.
  console.log(`There are ${rows.length} rows`)
  console.log(rows)
})

Example using a query:

// all
const query = {
  text : 'SELECT * FROM tablename WHERE col = $1',
  values : [ 'bob' ],
}
pgx.all(pool, query, (err, rows) => {
  if (err) throw err

  // `rows` is always an array - but empty if there are no matching rows
  // It is never `null` or `undefined` unless there was an `err`.
  console.log(rows)
})

.get(poc, table, col, val, callback)

Gets one row from the table specified, or null if it doesn't exist.

Example, fetching a user called bob:

pgx.get(pool, 'user', 'username', 'bob', (err, row) => {
  if (err) throw err

  // here, `row` is either `null` or an object with the cols/values
  if ( !row ) {
    console.log('No user found')
    return
  }

  // yes, the user exists
  console.log('User:', row)
})

Using .get() is equivalent to using .one() with the following query:

// using `.get()`
pgx.get(pool, 'user', 'username', 'bob', console.log)

// using `.one()`
const query = {
  text: 'SELECT * FROM user WHERE username = $1',
  values: [ 'bob' ],
}
pgx.one(pool, query, console.log)

.ins(poc, table, obj, callback)

Inserts a row into the table, using the key/value pairs in the obj. Returns the same rows and results as .query() above.

const user = {
  username : 'chilts',
  email : '[email protected]',
  website : 'https://chilts.org',
}
pgx.ins(client, 'account', user, (err, rows, result) => {
  // ...
})

.upd(poc, tablename, col, val, obj, callback)

Updates one or more rows using the col/val pairs in obj and using WHERE col = val.

const newDetails = {
  email : '[email protected]',
}
pgx.upd(client, 'account', 'username', 'chilts', newDetails, (err, rows, result) => {
  // ...
})

.del(poc, tablename, col, val, callback)

Deletes one or more rows using the col/val pairs in obj and using WHERE col = val. you don't delete ALL rows.

pgx.del(client, 'account', 'username', 'chilts', (err, rows, result) => {
  // ...
})

Author

Written by Andrew Chilton:

• Blog
• Twitter
• GitHub
• Instagram

License

ISC

(Ends)