gem-hunter

simple-postgres

Getting started

npm install simple-postgres

import db from 'simple-postgres'

let accountName = 'ACME\'; DELETE FROM accounts; --'

// this is totally safe
await db.query('INSERT INTO accounts (name) VALUES ($1)', [accountName])

// this is also totally safe
let account = await db.row`
  SELECT *
  FROM accounts
  WHERE name = ${accountName}
`

console.log(account.name) // => 'ACME\'; DELETE FROM accounts; --'

Why?

simple-postgres has everything you need

simple-postgres doesn't have anything you don't need

API

db.query(sql, params = [])

let result = await db.query('UPDATE accounts SET enabled = true')
console.log(result.command + ' ' + result.rowCount) // => UPDATE 2

db.rows(sql, params = [])

let accounts = await db.rows('SELECT * FROM accounts')
for (let account of accounts) {
  console.log(account.id + ': ' + account.name) // => "1: ACME"
}

db.row(sql, params = [])

let account = await db.row('SELECT * FROM accounts WHERE id = 1')
console.log(account.name) // => "ACME"

db.value(sql, params = [])

let accountName = await db.value('SELECT name FROM accounts WHERE id = 1')
console.log(accountName) // => "ACME"

db.column(sql, params = [])

let oneThroughFive = await db.column('SELECT * FROM generate_series(1, 5)')
console.log(oneThroughFive) // => [1, 2, 3, 4, 5]

template string mode

let type = 'pancake'
// the following two calls are identical:
db.value`
  SELECT COUNT(*)
  FROM breakfast
  WHERE type = ${type}
`
db.value('SELECT COUNT(*) FROM breakfast WHERE type = $1', [type])

let type = 'pancake \'; DELETE FROM accounts; --'
// NOTE THE PARENTHESES AROUND THE BACKTICKS - DO NOT DO THIS
db.value(`
  SELECT COUNT(*)
  FROM breakfast
  WHERE type = ${type}
`)

let table = 'breakfast'
let type = 'pancake'

db.value`
  SELECT COUNT(*)
  FROM ${db.identifier(table)}
  WHERE type = ${type}
`

db.template`SELECT ${a}...`

let accountName = 'ACME'
let tableName = 'users'

let subquery = db.template`
  SELECT id
  FROM accounts
  WHERE name = ${accountName}
`
let query = db.template`
  SELECT a, b
  FROM ${db.identifier(tableName)}
  WHERE account_id IN (${subquery})
`

let results = await db.rows(query)
// [{a: , b: }, {a: , b: }, ...]

let rawSql = query.__unsafelyGetRawSql()
// SELECT a, b FROM "users" WHERE account_id IN (SELECT id FROM accounts WHERE name='ACME')

db.transaction(block)

// process one order
db.transaction(async function (trx) {
  let orderId = await trx.value('SELECT id FROM orders WHERE NOT shipped LIMIT 1 FOR UPDATE')

  await db.query('INSERT INTO shipments (order_id) VALUES ($1)', [orderId])

  // if this update fails, the above insert will be rolled back!
  await db.query('UPDATE orders SET fulfilled = true WHERE id = $1', [orderId])

  return orderId
})

db.connection(block)

let cookies = await db.connection(async function ({ query, value }) {
  // count the number of cookies, or timeout if it takes more than a minute
  await query('SET statement_timeout=60000')
  return value('SELECT COUNT(*) FROM cookies')
})

Query cancellation

let query = db.query('SELECT COUNT(*) FROM slow_table')

query.catch(err => {
  if (err instanceof db.Cancel) {
    console.log('query cancelled')
  } else {
    console.error('unexpected error', err)
  }
})

q.cancel().then(() => console.log('cancel resolved'))

// STDOUT:
// query cancelled
// cancel resolved

db.escape(value)

db.escapeIdentifier(value)

db.escapeLiterals(values, separator = ', ')

db.escapeLiterals(['a', 'b', 'c']) === "'a', 'b', 'c'"

db.escapeIdentifiers(values, separator = ', ')

db.escapeIdentifiers(['a', 'b', 'c']) === '"a", "b", "c"'

db.identifier(value)

let tableName = 'potentially "dangerous" table name'
db.query`
  SELECT * FROM ${db.identifier(tableName)}
`

db.identifiers(values, separator = ', ')

let columns = ['id', 'name']
db.query`
  SELECT ${db.identifiers(columns)} FROM accounts
`

db.literals(values, separator = ', ')

let accounts = [1, 2, 3]
db.query`
  SELECT id FROM accounts WHERE name IN(${db.literals(accounts)})
`

db.items(values, separator = ', ')

let defaultTitle = 'untitled'
let select = [
  'test string',
  db.identifier('id'),
  db.template`COALESCE(title, ${defaultTitle}) AS title`
]

let books = await db.rows`
  SELECT ${db.items(select)}
  FROM books
`
/*
SELECT 'test string', "id", COALESCE(title, 'untitled') AS title
FROM books
*/

db.setErrorHandler(callback)

Contributing