gem-hunter

Getting started

Installation

$ npm install postgres

Usage

// db.js
import postgres from 'postgres'

const sql = postgres({ /* options */ }) // will use psql environment variables

export default sql

// users.js
import sql from './db.js'

async function getUsersOver(age) {
  const users = await sql`
    select
      name,
      age
    from users
    where age > ${ age }
  `
  // users = Result [{ name: "Walter", age: 80 }, { name: 'Murray', age: 68 }, ...]
  return users
}


async function insertUser({ name, age }) {
  const users = await sql`
    insert into users
      (name, age)
    values
      (${ name }, ${ age })
    returning name, age
  `
  // users = Result [{ name: "Murray", age: 68 }]
  return users
}

Dynamic imports and CommonJS

const { default: postgres } = await import('postgres')

const postgres = require('postgres').default

Table of Contents

Connection

postgres([url], [options])

const sql = postgres('postgres://username:password@host:port/database', {
  host                 : '',            // Postgres ip address[s] or domain name[s]
  port                 : 5432,          // Postgres server port[s]
  database             : '',            // Name of database to connect to
  username             : '',            // Username of database user
  password             : '',            // Password of database user
  ...and more
})

Queries

await sql`...` -> Result[]

const xs = await sql`
  insert into users (
    name, age
  ) values (
    'Murray', 68
  )

  returning *
`

// xs = [{ user_id: 1, name: 'Murray', age: 68 }]

Query parameters

const name = 'Mur'
    , age = 60

const users = await sql`
  select
    name,
    age
  from users
  where
    name like ${ name + '%' }
    and age > ${ age }
`
// users = [{ name: 'Murray', age: 68 }]

Parameter types

await sql`select date_trunc('day', ${ date }::timestamptz)`
await sql`select ${ flag }::bool as flag`

Dynamic column selection

const columns = ['name', 'age']

await sql`
  select
    ${ sql(columns) }
  from users
`

// Which results in:
select "name", "age" from users

Dynamic inserts

const user = {
  name: 'Murray',
  age: 68
}

await sql`
  insert into users ${
    sql(user, 'name', 'age')
  }
`

// Which results in:
insert into users ("name", "age") values ($1, $2)

// The columns can also be given with an array
const columns = ['name', 'age']

await sql`
  insert into users ${
    sql(user, columns)
  }
`

Multiple inserts in one query

const users = [{
  name: 'Murray',
  age: 68,
  garbage: 'ignore'
},
{
  name: 'Walter',
  age: 80
}]

await sql`insert into users ${ sql(users, 'name', 'age') }`

// Is translated to:
insert into users ("name", "age") values ($1, $2), ($3, $4)

// Here you can also omit column names which will use object keys as columns
await sql`insert into users ${ sql(users) }`

// Which results in:
insert into users ("name", "age") values ($1, $2), ($3, $4)

Dynamic columns in updates

const user = {
  id: 1,
  name: 'Murray',
  age: 68
}

await sql`
  update users set ${
    sql(user, 'name', 'age')
  }
  where user_id = ${ user.id }
`

// Which results in:
update users set "name" = $1, "age" = $2 where user_id = $3

// The columns can also be given with an array
const columns = ['name', 'age']

await sql`
  update users set ${
    sql(user, columns)
  }
  where user_id = ${ user.id }
`

Multiple updates in one query

const users = [
  [1, 'John', 34],
  [2, 'Jane', 27],
]

await sql`
  update users set name = update_data.name, age = (update_data.age)::int
  from (values ${sql(users)}) as update_data (id, name, age)
  where users.id = (update_data.id)::int
  returning users.id, users.name, users.age
`

Dynamic values and where in

const users = await sql`
  select
    *
  from users
  where age in ${ sql([68, 75, 23]) }
`

const [{ a, b, c }] = await sql`
  select
    *
  from (values ${ sql(['a', 'b', 'c']) }) as x(a, b, c)
`

Named arguments in function calls

await sql`
  select make_interval(${ sql({ days: 1, hours: 2 }) })
`

// Which results in:
select make_interval("days" => $1,"hours" => $2)

Building queries

Partial queries

const olderThan = x => sql`and age > ${ x }`

const filterAge = true

await sql`
  select
   *
  from users
  where name is not null ${
    filterAge
      ? olderThan(50)
      : sql``
  }
`
// Which results in:
select * from users where name is not null
// Or
select * from users where name is not null and age > 50

Dynamic filters

await sql`
  select
    *
  from users ${
    id
      ? sql`where user_id = ${ id }`
      : sql``
  }
`

// Which results in:
select * from users
// Or
select * from users where user_id = $1

const column = email ? 'notifications' : 'settings'

await sql`
  select * from users where ${ sql(column) }->>'email' = ${ 'true' }
`

// Which results in:
select * from users where "notifications"->>'email' = $1

Joining fragments

const where = sql`and`.join([
  sql`age > ${ age }`,
  name && sql`name = ${ name }`
])

await sql`
  select * from users ${ where.length ? sql`where ${ where }` : sql`` }
`

// Which results in:
select * from users where age > $1 and name = $2

Dynamic ordering

const id = 1
const order = {
  username: 'asc',
  created_at: 'desc'
}
await sql`
  select
    *
  from ticket
  where account = ${ id }
  order by ${
    Object.entries(order).flatMap(([column, order], i) =>
      [i ? sql`,` : sql``, sql`${ sql(column) } ${ order === 'desc' ? sql`desc` : sql`asc` }`]
    )
  }
`

SQL functions

const date = null

await sql`
  update users set updated_at = ${ date || sql`now()` }
`

// Which results in:
update users set updated_at = now()

Table names

const table = 'users'
    , column = 'id'

await sql`
  select ${ sql(column) } from ${ sql(table) }
`

// Which results in:
select "id" from "users"

Quick primer on interpolation

Advanced query methods

Cursors

await sql``.cursor([rows = 1], [fn])

callback function

await sql`
  select
    *
  from generate_series(1,4) as x
`.cursor(async([row]) => {
  // row = { x: 1 }
  await http.request('https://example.com/wat', { row })
})

for await...of

// for await...of
const cursor = sql`select * from generate_series(1,4) as x`.cursor()

for await (const [row] of cursor) {
  // row = { x: 1 }
  await http.request('https://example.com/wat', { row })
}

await sql`
  select
    *
  from generate_series(1,1000) as x
`.cursor(10, async rows => {
  // rows = [{ x: 1 }, { x: 2 }, ... ]
  await Promise.all(rows.map(row =>
    http.request('https://example.com/wat', { row })
  ))
})

await sql`
  select * from generate_series(1,1000) as x
`.cursor(row => {
  return Math.random() > 0.9 && sql.CLOSE // or sql.END
})

Instant iteration

await sql``.forEach(fn)

await sql`
  select created_at, name from events
`.forEach(row => {
  // row = { created_at: '2019-11-22T14:22:00Z', name: 'connected' }
})

// No more rows

Query Descriptions

await sql``.describe() -> Result[]

Rows as Array of Values

sql``.values()

Rows as Raw Array of Buffers

sql``.raw()

Queries in Files

await sql.file(path, [args], [options]) -> Result[]

const result = await sql.file('query.sql', ['Murray', 68])

Multiple statements in one query

await sql``.simple()

await sql`select 1; select 2;`.simple()

Copy to/from as Streams

await sql`copy ... from stdin`.writable() -> Writable

import { pipeline } from 'node:stream/promises'

// Stream of users with the default tab delimitated cells and new-line delimitated rows
const userStream = Readable.from([
  'Murray\t68\n',
  'Walter\t80\n'
])

const query = await sql`copy users (name, age) from stdin`.writable()
await pipeline(userStream, query);
query.count // 2, the number of rows copied, once the stream has finished

await sql`copy ... to stdout`.readable() -> Readable

Using Stream Pipeline

import { pipeline } from 'node:stream/promises'
import { createWriteStream } from 'node:fs'

const readableStream = await sql`copy users (name, age) to stdout`.readable()
await pipeline(readableStream, createWriteStream('output.tsv'))
// output.tsv content: `Murray\t68\nWalter\t80\n`

Using for await...of

const readableStream = await sql`
  copy (
    select name, age
    from users
    where age = 68
  ) to stdout
`.readable()
for await (const chunk of readableStream) {
  // chunk.toString() === `Murray\t68\n`
}

Canceling Queries in Progress

const query = sql`select pg_sleep 100`.execute()
setTimeout(() => query.cancel(), 100)
const result = await query

await sql`select * from report()`.signal(AbortSignal.timeout(5000))

Execute

await sql``.execute()

Inspecting a query

const query = sql`select * from users where id = ${ 1 } and name = ${ 'Murray' }`
query.text       // 'select * from users where id = $1 and name = $2'
query.parameters // [1, 'Murray']

Unsafe raw string queries

await sql.unsafe(query, [args], [options]) -> Result[]

sql.unsafe('select ' + danger + ' from users where id = ' + dragons)

sql.unsafe('select * from users where id = $1 and age > $2', [id, age])

const triggerName = 'friend_created'
const triggerFnName = 'on_friend_created'
const eventType = 'insert'
const schema_name = 'app'
const table_name = 'friends'

await sql`
  create or replace trigger ${sql(triggerName)}
  after ${sql.unsafe(eventType)} on ${sql.unsafe(`${schema_name}.${table_name}`)}
  for each row
  execute function ${sql(triggerFnName)}()
`

await sql`
  create role friend_service with login password ${sql.unsafe(`'${password}'`)}
`

Transactions

BEGIN / COMMIT await sql.begin([options = ''], fn) -> fn()

const [user, account] = await sql.begin(async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
    returning *
  `

  const [account] = await sql`
    insert into accounts (
      user_id
    ) values (
      ${ user.user_id }
    )
    returning *
  `

  return [user, account]
})

const result = await sql.begin(sql => [
  sql`update ...`,
  sql`update ...`,
  sql`insert ...`
])

await sql.begin(async sql => {
  const [movie] = await sql`select * from movie where name = 'Star Wars'`

  await Promise.all([
    sql`insert into movie_actor ...${ movie.id }`,
    sql`insert into movie_release ...${ movie.id }`
  ])

  await sql`select ...`
})

SAVEPOINT await sql.savepoint([name], fn) -> fn()

sql.begin('read write', async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
  `

  const [account] = (await sql.savepoint(sql =>
    sql`
      insert into accounts (
        user_id
      ) values (
        ${ user.user_id }
      )
    `
  ).catch(err => {
    // Account could not be created. ROLLBACK SAVEPOINT is called because we caught the rejection.
  })) || []

  return [user, account]
})
.then(([user, account]) => {
  // great success - COMMIT succeeded
})
.catch(() => {
  // not so good - ROLLBACK was called
})

Nested sql.begin

async function transfer(sql, from, to, amount) {
  return sql.begin(async sql => {
    await sql`update accounts set balance = balance - ${ amount } where id = ${ from }`
    await sql`update accounts set balance = balance + ${ amount } where id = ${ to }`
  })
}

await transfer(sql, 1, 2, 10)                 // its own transaction
await sql.begin(sql => transfer(sql, 1, 2, 10)) // a savepoint in this one

ROLLBACK await sql.rollback()

const result = await sql.begin(async sql => {
  const [order] = await sql`insert into orders ${ sql(input) } returning *`
  if (!await inStock(sql, order))
    return (await sql.rollback(), { saved: false })
  return { saved: true, order }
})

PREPARE TRANSACTION await sql.prepare([name]) -> fn()

sql.begin('read write', async sql => {
  const [user] = await sql`
    insert into users (
      name
    ) values (
      'Murray'
    )
  `

  await sql.prepare('tx1')
})

Data Transformation

// Transform the column names to and from camel case
const sql = postgres({ transform: postgres.camel })

await sql`CREATE TABLE IF NOT EXISTS camel_case (a_test INTEGER, b_test TEXT)`
await sql`INSERT INTO camel_case ${ sql([{ aTest: 1, bTest: 1 }]) }`
const data = await sql`SELECT ${ sql('aTest', 'bTest') } FROM camel_case`

console.log(data) // [ { aTest: 1, bTest: '1' } ]

// Transform the column names only to camel case
// (for the results that are returned from the query)
postgres({ transform: postgres.toCamel })

await sql`CREATE TABLE IF NOT EXISTS camel_case (a_test INTEGER)`
await sql`INSERT INTO camel_case ${ sql([{ a_test: 1 }]) }`
const data = await sql`SELECT a_test FROM camel_case`

console.log(data) // [ { aTest: 1 } ]

// Transform the column names only from camel case
// (for interpolated inserts, updates, and selects)
const sql = postgres({ transform: postgres.fromCamel })

await sql`CREATE TABLE IF NOT EXISTS camel_case (a_test INTEGER)`
await sql`INSERT INTO camel_case ${ sql([{ aTest: 1 }]) }`
const data = await sql`SELECT ${ sql('aTest') } FROM camel_case`

console.log(data) // [ { a_test: 1 } ]

Transform undefined Values

// Transform the column names to and from camel case
const sql = postgres({
  transform: {
    undefined: null
  }
})

await sql`CREATE TABLE IF NOT EXISTS transform_undefined (a_test INTEGER)`
await sql`INSERT INTO transform_undefined ${ sql([{ a_test: undefined }]) }`
const data = await sql`SELECT a_test FROM transform_undefined`

console.log(data) // [ { a_test: null } ]

// Transform the column names to and from camel case
const sql = postgres({
  transform: {
    ...postgres.camel,
    undefined: null
  }
})

await sql`CREATE TABLE IF NOT EXISTS transform_undefined (a_test INTEGER)`
await sql`INSERT INTO transform_undefined ${ sql([{ aTest: undefined }]) }`
const data = await sql`SELECT ${ sql('aTest') } FROM transform_undefined`

console.log(data) // [ { aTest: null } ]

Custom Transform Functions

// Implement your own functions, look at postgres.toCamel, etc
// as a reference:
// toCamel and the others in types.js
function transformColumnToDatabase() { /* ... */ }
function transformColumnFromDatabase() { /* ... */ }

const sql = postgres({
  transform: {
    column: {
      to: transformColumnToDatabase,
      from: transformColumnFromDatabase,
    },
    value: { /* ... */ },
    row: { /* ... */ }
  }
})

Listen & notify

Listen await sql.listen(channel, onnotify, [onlisten]) -> { state }

await sql.listen('news', payload => {
  const json = JSON.parse(payload)
  console.log(json.this) // logs 'is'
})

await sql.listen(
  'jobs',
  (x) => run(JSON.pars