simple-postgres is a small and powerful PostgreSQL interface for Node.js
Replace all of your database boilerplate with import db from 'simple-postgres'
and never look back.
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; --'
Many other postgres modules are bad. This one is good. Here's why:
- connects using the DATABASE_URL environment variable
- runs queries and returns the results
- automatic query parameterization
- escaping literals, identifiers, arrays
- transactions
- async/await ready
- sets application_name using package.json
- good test coverage
- trusted in production by my boss who trusts nothing
- no ORM
- no query builder
- no connect function
- no disconnect function
- no connection pool manager
- no configuration
- no initialization
- no callbacks
run a query
returns a promise, which resolves with a pg Result object
This is best for INSERT/UPDATE/DELETE/etc queries which will not return any rows. If you are doing a SELECT, you probably want one of the functions below.
let result = await db.query('UPDATE accounts SET enabled = true')
console.log(result.command + ' ' + result.rowCount) // => UPDATE 2
run a query
returns a promise, which resolves with an array of row objects
let accounts = await db.rows('SELECT * FROM accounts')
for (let account of accounts) {
console.log(account.id + ': ' + account.name) // => "1: ACME"
}
run a query
returns a promise, which resolves with the first row object
This will not automatically add LIMIT 1
to the end of the query.
let account = await db.row('SELECT * FROM accounts WHERE id = 1')
console.log(account.name) // => "ACME"
run a query
returns a promise, which resolves with the first column of the first row
This is useful for things like counts.
let accountName = await db.value('SELECT name FROM accounts WHERE id = 1')
console.log(accountName) // => "ACME"
run a query
returns a promise, which resolves with an array of the first values in each row
Example:
let oneThroughFive = await db.column('SELECT * FROM generate_series(1, 5)')
console.log(oneThroughFive) // => [1, 2, 3, 4, 5]
Any of the above functions can be used with template string literals to make
long queries more readable. Interpolated values will be moved to the params
array and replaced with $1, $2, etc.
Example:
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])
Do not use parentheses around your template string or you will open yourself up to SQL injection attacks and you will have a bad day.
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}
`)
If you need to interpolate an identifier such as a table name, the normal
escaping will wrap your value in single quotes. Use the db.identifier
function
instead.
Example:
let table = 'breakfast'
let type = 'pancake'
db.value`
SELECT COUNT(*)
FROM ${db.identifier(table)}
WHERE type = ${type}
`
perform a database transaction
block: should be a function which will perform work inside the transaction and return a promise. If the promise rejects, the transaction will be rolled back.
returns a promise, which should resolve with the return value of block or reject if the transaction failed
Example:
// 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
})
perform multiple queries sequentially on a single connection
block: should be a function which will perform work inside the connection and return a promise. When the promise resolves or rejects, the connection will be returned to the pool.
Example:
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')
})
The promises returned by db.query
, db.rows
, etc all have a cancel
method
which will kill the query on the backend.
Example:
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
An obscure note about cancellation: db.connection
and db.transaction
do not
have .cancel()
methods, although you can cancel individual queries you run
within them.
alias of db.escapeLiteral
escape a value for safe use in SQL queries, returns string
While this function is tested and probably secure, you should avoid using it. Instead, use bind vars, as they are much more difficult to mess up.
escape a value for safe use as an identifier in SQL queries, returns string
Same as the above function, except for things like table names, column names, etc.
escape an array of literals and join them with the given separator, returns string
db.escapeLiterals(['a', 'b', 'c']) === "'a', 'b', 'c'"
escape an array of identifiers and join them with the given separator, returns string
db.escapeIdentifiers(['a', 'b', 'c']) === '"a", "b", "c"'
escapes an identifier in such a way that it can be passed safely into a template query, returns object
Below, note the lack of parentheses around the SQL, with db.query being called as a template function.
let tableName = 'potentially "dangerous" table name'
db.query`
SELECT * FROM ${db.identifier(tableName)}
`
escapes multiple identifiers in such a way that they can be passed safely into a template query, returns object
let columns = ['id', 'name']
db.query`
SELECT ${db.identifiers(columns)} FROM accounts
`
escapes multiple literals in such a way that they can be passed safely into a template query, returns object
let accounts = [1, 2, 3]
db.query`
SELECT id FROM accounts WHERE name IN(${db.literals(accounts)})
`
Please send pull requests!