a valiant effort at a simple query builder for postgres

pgquery.js

'use strict'

const kClause = Symbol('kQueryClause')
const kPosition = Symbol('kQueryPosition')
const kWhere = Symbol('kQueryWhere')

const AND_BREAK = '\nAND '
const ISNULL_EXP = /true|1/
const SELECT_EXP = /^SELECT/i
const SELECT_REPLACEMENT = 'SELECT 1::SMALLINT as __id, '
const CSV_SEP = ','
const OP_OR = 'OR'
const OP_AND = 'AND'
const OP_NULL = 'NULL'
const OP_NOT_NULL = 'NOT NULL'

const OP_EQUAL = 'eq'
const OP_NE = 'ne'
const OP_LT = 'lt'
const OP_LTE = 'lte'
const OP_GT = 'gt'
const OP_GTE = 'gte'
const OP_IN = 'in'
const OP_NIN = 'nin'
const OP_RANGE = 'range'
const OP_EXACT = 'exact'
const OP_IEXACT = 'iexact'
const OP_CONTAINS = 'contains'
const OP_ICONTAINS = 'icontains'
const OP_STARTSWITH = 'startswith'
const OP_ISTARTSWITH = 'istartswith'
const OP_ENDSWITH = 'endswith'
const OP_IENDSWITH = 'iendswith'
const OP_ISNULL = 'isnull'

const TYPE_ARRAY = 'array'

const FILTERS = new Map([
  [OP_EQUAL, `_${OP_EQUAL}`]
, [OP_NE, `_${OP_NE}`]
, [OP_LT, `_${OP_LT}`]
, [OP_LTE, `_${OP_LTE}`]
, [OP_GT, `_${OP_GT}`]
, [OP_GTE, `_${OP_GTE}`]
, [OP_IN, `_${OP_IN}`]
, [OP_NIN, `_${OP_NIN}`]
, [OP_RANGE, `_${OP_RANGE}`]
, [OP_EXACT, `_${OP_EXACT}`]
, [OP_IEXACT, `_${OP_IEXACT}`]
, [OP_CONTAINS, `_${OP_CONTAINS}`]
, [OP_ICONTAINS, `_${OP_ICONTAINS}`]
, [OP_STARTSWITH, `_${OP_STARTSWITH}`]
, [OP_ISTARTSWITH, `_${OP_ISTARTSWITH}`]
, [OP_ENDSWITH, `_${OP_ENDSWITH}`]
, [OP_IENDSWITH, `_${OP_IENDSWITH}`]
, [OP_ISNULL, `_${OP_ISNULL}`]
])

const OPERATOR_MAP = new Map([
  [OP_LT, '<']
, [OP_LTE, '<=']
, [OP_GT, '>']
, [OP_GTE, '>=']
])

function toArray(item) {
  if (!item) return []
  if (Array.isArray(item)) return item
  return typeof item === 'string' ? item.split(CSV_SEP) : [item]
}

module.exports = class Query {
  constructor(opts) {
    if (!opts || typeof opts !== 'object') {
      throw new TypeError('options is required and must be an object')
    }

    if (!opts.text || typeof opts.text !== 'string') {
      throw new TypeError('options.text is required and must be a string')
    }
    this._query = opts.text
    this.values = []
    this.cache_name = undefined
    this._sort = []
    this._group = []
    this[kClause] = []
    this[kWhere] = []
    this[kPosition] = this.values.length + 1
    this._fields = Object.assign(Object.create(null), opts.fields)
    this._alias = Object.assign(Object.create(null), opts.alias)
    this._limit = null
    this._offset = null
    this._operator = (opts.operator || '').toUpperCase() === OP_OR
      ? OP_OR
      : OP_AND
  }

  compile(opts) {
    if (opts.page) {
      this.limit(opts.page.limit)
      this.offset(opts.page.offset)
    }

    if (opts.order) {
      for (var field in opts.order) {
        this.orderby(field, opts.order[field])
      }
    }

    if (opts.operator) {
      const op = opts.operator.toUpperCase()
      switch (op) {
        case OP_AND:
        case OP_OR:
          this._operator = op
          break
        default: {
          const err = new Error(`Unsupported query operator ${op}`)
          err.code = 'ENOOPERATOR'
          throw err
        }
      }
    }

    if (opts.filter) {
      const fields = Object.keys(opts.filter)
      for (var idx = 0; idx < fields.length; idx++) {
        const field = fields[idx]
        for (var filter in opts.filter[field]) {
          if (typeof this[filter] !== 'function') {
            const err = new Error(`Unsupported filter type: ${filter}`)
            err.code = 'ENOFILTER'
            throw err
          }
          this[filter](field, opts.filter[field][filter])
        }
      }
    }
    return this
  }

  prop(field) {
    return this._alias[field] || field
  }

  groupby(field) {
    this._group.push(this.prop(field))
    return this
  }

  orderby(field, order = 'desc') {
    this._sort.push(`${this.prop(field)} ${order}`)
    return this
  }

  cacheName(str) {
    this.cache_name = `${str}`
    return this
  }

  exact(field, value) {
    this[kClause].push(this._exact(field, value))
    return this
  }

  gt(field, value) {
    this[kClause].push(this._gt(field, value))
    return this
  }

  gte(field, value) {
    this[kClause].push(this._gte(field, value))
    return this
  }

  lt(field, value) {
    this[kClause].push(this._lt(field, value))
    return this
  }

  lte(field, value) {
    this[kClause].push(this._lte(field, value))
    return this
  }

  eq(field, value) {
    return this.exact(field, value)
  }

  ne(field, value) {
    this[kClause].push(this._ne(field, value))
    return this
  }

  range(field, values) {
    this[kClause].push(this._range(field, values))
    return this
  }

  iexact(field, value) {
    this[kClause].push(this._iexact(field, value))
    return this
  }

  compoundKey(fields, values, comparator = '') {
    const comp = comparator.toLowerCase()
    const op = OPERATOR_MAP.get(comp)
    if (!op) {
      const err = new Error(`Comparison operator \'${comp}\' is not supported`)
      throw err
    }
    if (!Array.isArray(fields) || !Array.isArray(values)) {
      const err = new Error(
        'compoundKey requires \'fields\' and \'values\' arrays'
      )
      throw err
    }
    if (!fields.length || !values.length) {
      const err = new Error('\'fields\' and \'values\' cannot be empty')
      throw err
    }
    if (fields.length !== values.length) {
      const err = new Error('\'fields\' and \'values\' length must be the same')
      throw err
    }
    this[kClause].push(this._compoundKey(fields, values, op))
    return this
  }

  contains(field, value) {
    this[kClause].push(this._contains(field, value))
    return this
  }

  icontains(field, value) {
    this[kClause].push(this._icontains(field, value))
    return this
  }

  startswith(field, value) {
    this[kClause].push(this._startswith(field, value))
    return this
  }

  istartswith(field, value) {
    this[kClause].push(this._istartswith(field, value))
    return this
  }

  endswith(field, value) {
    this[kClause].push(this._endswith(field, value))
    return this
  }

  iendswith(field, value) {
    this[kClause].push(this._iendswith(field, value))
    return this
  }

  in(field, values) {
    this[kClause].push(this._in(field, values))
    return this
  }

  nin(field, values) {
    if (this._fields[field] === TYPE_ARRAY) {
      this[kClause].push(this._array_nin(field, values))
      return this
    }

    const params = toArray(values)
    for (var idx = 0; idx < params.length; idx++) {
      this.values.push(params[idx])
      params[idx] = `$${this[kPosition]++}`
    }
    this[kClause].push(`( ${this.prop(field)} NOT IN (${params.join(', ')}) )`)
    return this
  }

  isnull(field, value) {
    this[kClause].push(this._isnull(field, value))
    return this
  }

  limit(value) {
    this._limit = value < 0 ? 0 : value
    return this
  }

  offset(value) {
    this._offset = value < 0 ? 0 : value
    return this
  }

  where(field, value, filter = OP_EQUAL) {
    const comp = filter.toLowerCase()
    const path = this.prop(field)
    const fn = FILTERS.get(comp)

    if (!fn) {
      const err = new Error(`Invalid comparison operator ${comp}`)
      throw err
    }

    if (value === null) {
      switch (comp) {
        case OP_NE:
          this[kWhere].push(this._isnull(path, false))
          return this
        case OP_EQUAL:
        case OP_EXACT:
          this[kWhere].push(this._isnull(path, true))
          return this
        default:
          const err = new Error('Operator for NULL must be \'ne\' or \'eq\'')
          throw err
      }

    }
    this[kWhere].push(this[fn](field, value))
    return this
  }

  toWhere(operator) {
    const op = operator || this._operator

    if (op !== OP_AND && op !== OP_OR) {
      const err = new Error(`Invalid query operator ${op}`)
      throw err
    }

    if (!this[kWhere].length && !this[kClause].length) return ''

    const clauses = this[kClause].join(`\n${op} `)
    const where = this[kWhere].join(AND_BREAK)
    const out = []

    if (where) out.push(where)
    if (clauses) out.push(`(${clauses})`)
    return out.join(AND_BREAK)
  }

  toString(operator) {
    const op = operator || this._operator
    if (op !== OP_AND && op !== OP_OR) {
      const err = new Error(`Invalid query operator ${op}`)
      throw err
    }

    const WHERE = this.toWhere(op)

    return (
      `${this._query}\n`
    + `${WHERE ? 'WHERE ' + WHERE + '\n' : ''}`
    + `${this._group.length ? 'GROUP BY ' + this._group.join(', ') + '\n' : ''}`
    + `${this._sort.length ? 'ORDER BY ' + this._sort.join(', ') + '\n' : ''}`
    + `${this._limit ? 'LIMIT ' + this._limit + '\n' : ' '}`
    + `${this._offset ? 'OFFSET ' + this._offset + '\n' : ' '}`
    ).trim()
  }

  toJSON(operator) {
    const op = operator || this._operator
    return {
      query: this._query
    , values: this.values
    , params: this[kPosition] - 1
    , text: this.toString(op, false)
    , name: this.cache_name
    , where: this.toWhere(op)
    , kWhere: Array.from(this[kWhere])
    , kClauses: Array.from(this[kClause])
    }
  }

  //TODO(esatterwhite) v5 should just take an object of options
  withCount(table, operator) {
    const WHERE = this.toWhere(operator || this._operator)
    const out = `
      WITH table_count AS (
        SELECT
          COUNT(*) AS total, 1::SMALLINT as __id
        FROM ${table}
        ${WHERE ? 'WHERE ' + WHERE : ''}
      ), query AS (
        ${this.toString(operator).replace(SELECT_EXP, SELECT_REPLACEMENT)}
      )
      SELECT
        table_count.total::integer,
        COALESCE(
          JSON_AGG(
            ROW_TO_JSON(query.*)
          ) FILTER (WHERE query.id IS NOT NULL),
          '[]'::json
        ) as data
      FROM
        table_count
      LEFT JOIN query ON table_count.__id = query.__id
      GROUP BY table_count.total
    `

    return {
      query: this.query
    , values: this.values
    , params: this[kPosition] - 1
    , text: out
    , where: WHERE
    , kWhere: this[kWhere]
    , kClauses: Array.from(this[kClause])
    }
  }

  _exact(field, value) {
    this.values.push(value)
    return `( ${this.prop(field)} = $${this[kPosition]++} )`
  }

  _gt(field, value) {
    this.values.push(value)
    return `( ${this.prop(field)} > $${this[kPosition]++} )`
  }

  _gte(field, value) {
    this.values.push(value)
    return `( ${this.prop(field)} >= $${this[kPosition]++} )`
  }

  _lte(field, value) {
    this.values.push(value)
    return `( ${this.prop(field)} <= $${this[kPosition]++} )`
  }

  _lt(field, value) {
    this.values.push(value)
    return `( ${this.prop(field)} < $${this[kPosition]++} )`
  }

  _eq(field, value) {
    return this._exact(field, value)
  }

  _ne(field, value) {
    this.values.push(value)
    return `( ${this.prop(field)} <> $${this[kPosition]++} )`
  }

  _range(field, values) {
    const params = toArray(values)
    if (params.length === 1) return this._gte(field, params[0])
    this.values.push(params[0], params[1])
    const path = this.prop(field)
    return (
      `( ${path} BETWEEN $${this[kPosition]++} AND $${this[kPosition]++} )`
    )
  }

  _iexact(field, value) {
    // this only makes sense for strings...
    // and the LOWER function will complain
    if (typeof value === 'string') {
      this.values.push(value)
      return `( LOWER(${this.prop(field)}) = LOWER($${this[kPosition]++}) )`
    }
    return this._exact(field, value)
  }

  _compoundKey(fields, values, comp) {
    this.values.push(...values)
    const columns = []
    const params = []
    for (let i = 0; i < fields.length; i++) {
      columns.push(this.prop(fields[i]))
      params.push(`$${this[kPosition]++}`)
    }
    return `( (${columns.join(', ')}) ${comp} (${params.join(', ')}) )`
  }

  _contains(field, value) {
    if (this._fields[field] === TYPE_ARRAY) {
      return this._array_contains(field, value)
    }
    this.values.push(value)
    return `( ${this.prop(field)} LIKE '%' || $${this[kPosition]++} || '%' )`
  }

  _icontains(field, value) {
    this.values.push(value)
    return `( ${this.prop(field)} ILIKE '%' || $${this[kPosition]++} || '%' )`
  }

  _startswith(field, value) {
    this.values.push(value)
    return `( ${this.prop(field)} LIKE $${this[kPosition]++} || '%' )`
  }

  _istartswith(field, value) {
    this.values.push(value)
    return `( ${this.prop(field)} ILIKE $${this[kPosition]++} || '%' )`
  }

  _endswith(field, value) {
    this.values.push(value)
    return `( ${this.prop(field)} LIKE '%' || $${this[kPosition]++} )`
  }

  _iendswith(field, value) {
    this.values.push(value)
    return `( ${this.prop(field)} ILIKE '%' || $${this[kPosition]++} )`
  }

  _in(field, values) {
    if (this._fields[field] === TYPE_ARRAY) {
      return this._array_in(field, values)
    }

    const params = toArray(values)
    for (var idx = 0; idx < params.length; idx++) {
      this.values.push(params[idx])
      params[idx] = `$${this[kPosition]++}`
    }
    return `( ${this.prop(field)} IN (${params.join(', ')}) )`
  }

  _isnull(field, value) {
    const op = ISNULL_EXP.test(value) ? OP_NULL : OP_NOT_NULL
    return `( ${this.prop(field)} IS ${op} )`
  }

  _array_contains(field, value) {
    this.values.push(`{${value}}`)
    return `( ${this.prop(field)} @> $${this[kPosition]++} )`
  }

  _array_in(field, values) {
    const params = toArray(values)
    this.values.push(`{${params.join(',')}}`)
    return `( ${this.prop(field)} && $${this[kPosition]++} )`
  }

  _array_nin(field, values) {
    const params = toArray(values)
    this.values.push(`{${params.join(',')}}`)
    return `( NOT ( ${this.prop(field)} && $${this[kPosition]++} ) )`
  }


  static format(text) {
    return require('./debug').format(text)
  }

  static colorize(text) {
    return require('./debug').colorize(text)
  }

  static pprint(text) {
    return this.colorize(this.format(text))
  }
}