pg-dot-template

extends dot-template, with added niceties specific to the pg module

install

# peer dependencies
npm install @conjurelabs/dot-template

# required if using .query methods
npm install pg

# library
npm install @conjurelabs/pg-dot-template

use

pg-dot-template uses dot-tempalte to add extra utilities for dealing with postgres queries

activity-query.sql

select *
from activity
where added $PG{added}
limit $PG{limit}

const pgDotTemplate = require('@conjurelabs/pg-dot-template')

const client = new Client()
client.connect()

// required setup if using .query
pgDotTemplate.handleQuery = (queryString, queryArgs) => {
  return client.query(queryString, queryArgs)
}

async function main() {
  // pulls in template
  const template = pgDotTemplate('activity-query')

  // replace expressions
  const queryString = await template({
    added: '>= NOW() - interval 1 day',
    limit: 10
  })

  // prints:
  /*
    select *
    from activity
    where added >= NOW() - interval 1 day
    limit 10
   */
  console.log(queryString)

  // queries:
  /*
    select *
    from activity
    where added >= $1
    limit $2
   */
  // with query args:
  /*
    ['>= NOW() - interval 1 day', 10]
   */
  const result = await queryString.query()

  console.log(result.rows)
}
main()

postgres expression handlers

this library adds two unique handlers

$PG{expression}

$PG{expression} prints the value of expression to console, while passing an index reference (like $1) in the literal query

as expressions are evaluated, the library will construct an array of query arguments that are passed to the postgres client

select *
from authors
where name ilike '$PG{namePrefix}'

const template = pgDotTemplate('authors')

const queryString = template({
  namePrefix: 'clint '
})

console.log(queryString)

// prints:
/*
  select *
  from authors
  where name ilike 'clint '
 */

!PG{expression}

!PG{expression} prints a <REDACTED> message to console, while passing an index reference (like $1) in the literal query

this is to be used with PII, like names and email addresses

select *
from authors
where email = '!PG{email}'

const template = pgDotTemplate('authors')

const queryString = template({
  email: '[email protected]'
})

console.log(queryString)

// prints:
/*
  select *
  from authors
  where email = '<REDACTED>'
 */

.query()

there are two ways you can call .query()

const template = pgDotTemplate('activity-query')

// calling .query() via a template
const result1 = await template.query({
  added: '>= NOW() - interval 1 day',
  limit: 10
})

// calling .query() via a filled-in template
const queryString = template({
  added: '>= NOW() - interval 1 day',
  limit: 10
})
const result2 = await queryString.query()

also, if you need it, you will have access to the passed queryArgs

this will be an empty [] array until .query() is called

this attribute is only available when executing .query() on a filled-in query string

const template = pgDotTemplate('activity-query')

const queryString = template({
  added: '>= NOW() - interval 1 day',
  limit: 10
})

const result = await queryString.query()

// prints:
/*
  ['>= NOW() - interval 1 day', 10]
 */
console.log(result.queryArgs)

types

this library supports:

• strings
• numbers
• bigints
• booleans
• null
• arrays of the above

it does not support undefined or nested arrays

any unexpected values will trigger an error to be thrown

arrays will not be auto-wrapped in ()s

using pg's Pool

if you plan to use Pool to connect, you will want to call .connect() before each query, and then release() when finished:

const pool = new Pool()

pgDotTemplate.handleQuery = async (queryString, queryArgs) => {
  const connection = await pool.connect()

  return new Promise(async (resolve, reject) => {
    let result, err
    
    try {
      result = await connection.query(queryString, queryArgs)
    } catch(tryErr) {
      err = tryErr
    } finally {
      connection.release()
    }

    if (err) {
      return reject(err)
    }
    resolve(result)
  })
}

changing the redacted message

by default any redaction will show in terminal as <REDACTED>

you can change this string by setting the enironment variable PG_DOT_TEMPLATE_REDACTION_MESSAGE