pg-query

A simple query builder for postgres

Install

npm install @helpdotcom/pg-query --save

Usage

The query builder is only responsible for constructing complex WHERE clauses. You must supply the base query with at least one initial WHERE condition

const PGQuery = require('@helpdotcom/pg-query')
const postgres = require('@helpdotcom/postgres')

db = new postgres({ ... })

const query = new PGQuery({
  text: `
  SELECT * FROM users
  `
, values: []
})
.where('organization_id', 'b45d6a26-1011-4242-b4fe-fad3f01d5d66')
.contains('email', 'mail.com')
.gt('age', 30)
.toJSON()

db.query(query, (err, res) => {
  console.log(res.rows)
})

Field Mappings

There is limited support for array typed columns including comparisons of entire arrays as well as finding items in arrays. Currently, the contains, in and nin filters can be used for array fields. To enable array functionality, you must specify a field as an array field

const query = new PGQuery({
  text: 'SELECT * from users WHERE name IS NOT NULL'
, field: {
    roles: 'array'
  , names: 'array'
  }
})
query
  .in('roles', 'ticket_agent,chat_agent')
  .nin('names', ['billy', 'william', 'will'])

// WHERE name IS NOT NULL AND (
//  (roles && {ticket_agent,chat_agent})
// AND
//  ( NOT (names && {billy,william,will}))
//)

API

new PGQuery(options)

Creates a new query object

Parameters

• options <Object> An key value pairing of alias names to document field names in object path notation
  • text <String> A valid postgresSQL query. First where condition can be included or set using where(field, value, comparison). Doing both will cause malformed SQL
  • operator (optional) <String> The operator used to combine all where clauses added to the base query. Operators can be one of AND or OR - The default is AND

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
})
.where('age', 24, 'gt')
.eq('color', 'red')
.ed('color', 'blue')

// WHERE age > 24 AND (( color = 'red' ) AND ( color = 'blue' ))

const query = new PGQuery({
  text: 'SELECT * FROM users'
, operator: 'OR'
})
.where('age', 24, 'gt')
.eq('color', 'red')
.ed('color', 'blue')

// WHERE age > 24 AND (( color = 'red' ) OR ( color = 'blue' ))

compile(options)

Generates query object from a nano-richendpoint object

Parameters

• options <Object>: The parsed endpoint object from nano-richendpoint
  • page (optional) <Object>: Pagination information
    • limit (optional) <Number>: The maximum number of records to return
    • offset (optional) <Number>: The number of records to skip if the total exceeds the limit
  • order (optional) <Object>: Ordering information
  • filter (optional) <Object>: Filtering information

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
})
  .where('name', null, 'ne')
const opts = {
  page: {
    limit: 50
  , offset: 100
  }
, order: {
    foo: 'asc'
  , bar: 'desc'
  }
, filter: {
    foo: {
      startswith: 'foo'
    , endswith: 'bar'
    }
  , baz: {
      in: [1, 2, 3]
    }
  }
}

query.compile(opts)
PGQUERY.pprint(query)

Produces

SELECT *
FROM users
WHERE name IS NOT NULL
AND (
    (foo LIKE 'foo' || '%')
  AND
    (foo LIKE '%' || 'bar')
  AND
    (baz IN (1, 2, 3))
)
ORDER BY foo ASC, bar DESC
LIMIT 50 OFFSET 100

// OR clauses
const query = new PGQuery({
  text: 'SELECT * FROM users'
})
  .where('name', null, 'ne')
const opts = {
  page: {
    limit: 50
  , offset: 100
  }
, order: {
    foo: 'asc'
  , bar: 'desc'
  }
, filter: {
    foo: {
      startswith: 'foo'
    , endswith: 'bar'
    }
  , baz: {
      in: [1, 2, 3]
    }
  }
}

query.compile(opts)
query.toString('OR')

Produces

SELECT *
FROM users
WHERE name IS NOT NULL
AND (
    (foo LIKE 'foo' || '%')
  OR
    (foo LIKE '%' || 'bar')
  OR
    (baz IN (1, 2, 3))
)
ORDER BY foo ASC, bar DESC
LIMIT 50 OFFSET 100

const opts = req.$.endpoint.parsed
const query = new PGQuery({
  text: 'SELECT * FROM users'
})
  .where('name', null, 'ne')

query.compile(opts)

Produces

SELECT *
FROM users
WHERE name IS NOT NULL
AND (
    (foo LIKE 'foo' || '%')
  AND
    (foo LIKE '%' || 'bar')
  AND
    (baz IN (1, 2, 3))
)
ORDER BY foo ASC, bar DESC
LIMIT 50 OFFSET 100

where(field, value, comparison)

Generates the first statements for a WHERE clase before additional filtering is applied. Uses an equality filter function (eq) by default. This allows the developer to control the query conditions before end user input is applied

Strongly recommended when generating a query using withCount

Parameters

• field <String> The field name or alias to search on
• value <String> The word or word fragement to search for
• comparison <String> Comparison filter function to use for clause. Defaults to (eq)

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
})
.where('age', 24, 'gt')
.in('name', ['bill', 'max', 'fred'])

SELECT * FROM users WHERE age > 24 AND ((name IN ('bill', 'max', 'fred'))

const query = new PGQuery({
  text: 'SELECT * FROM users'
})
.where('organization_id', 'abc123')
.gt('age', 24)
.where('archived', false)
.lt('age', 50)

SELECT * FROM users
WHERE
  organization_id = 'abc123'
AND
  archived = false
AND (( age > 24 ) AND ( age < 50 ))

contains(field, value)

Includes a LIKE clause wrapping the term in wildcard characters %

Parameters

• field <String> The field name or alias to search on
• value <String> The word or word fragement to search for

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
})
  .where('name', null, 'ne')

query.contains('foo', 'ing')

// foo LIKE %ing%

exact

Includes a equality comparison clause (=)

Parameters

• field <String> The field name or alias to search on
• value <String> The word or word fragement to search for

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: []
})
  .where('name', 'Billy')

query.exact('foo', 'hello world')

// foo = 'hello world'

iexact

Includes a new equality clause for the field to match a specific value, in a case insensitive manner by wrapping terms with the LOWER function

Parameters

• field <String> The field name or alias to search on
• value <String> The word or word fragement to search for

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: []
})
  .where('name', 'Billy')

query.iexact('foo', 'hello woRlD')

// LOWER(foo) = LOWER('hello woRlD')

gt(field, value)

Includes a greater than comparison clause (>)

Parameters

• field <String>: The field to query on
• value <Number>|<Date>: the value

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: []
})
  .where('company', 'help.com')

query.gt('created_at', '2017-01-30T15:20:15')
// created_at > 2017-01-30T15:20:15

gte(field, value)

Includes a greater than or equal to comparison clause (>=)

Parameters

• field <String>: The field to query on
• value <Number>|<Date>: the value
• format <String>: The format to use for date values (default: 'yyyy-mm-dd')

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: []
})
  .where('company', 'help.com')

query.gte('created_at', '2017-01-30T15:20:15')
// created_at >= 2017-01-30T15:20:15

in(field, value)

Applies an IN cluase using a list of values. If field has been defined as and array column, arrays containing any of the values will be matched

Parameters

• field <String> The field name or alias to search on
• values <String>|<Array>: A specific value, an array of specific values, or a comma seperated string of values to match against

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: []
, fields: {
    foo: 'array'
  }
})
  .where('company', 'help.com')

query
  .in('foo', 'a,b,c')
  .in('bar', ['hello', 'world'])

// ( foo && '{a,b,c}' ) AND ( bar IN (hello, world) )

isnull(yes)

Includes an IS NULL or IS NOT NULL clause to the query

Parameters

• field <String>: The field name or alias to search on
• value [<Boolean>][]: true to find documents where the field is null. false

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: []
})
  .where('company', 'help.com')

query
  .isnull('foo', true)
  .isnull('bar', false)

// (foo IS NULL) AND (bar IS NOT NULL)

limit(maximum)

Restricts the result set to a maximum number of results

Parameters

• maximum <Number>: The maximum number of results to return

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: []
})
  .where('company', 'help.com')

query
  .limit(25)

// LIMIT 25

lt

Adds a partial, exclusionary range query do return documents where the value of a field is less than the specified value. Date fields must be analyzed as dates.

Parameters

• field <String>: The field to query on
• value <Number>|<Date>: the value

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: []
})
  .where('company', 'help.com')

query.lt('created_at', '2017-01-30T15:20:16.893Z')
// created_at < '2017-01-30T15:20:16.893Z'

lte

Adds a partial, inclusionary range query do return documents where the value of a field is less than or equal to the specified value.

Parameters

• field <String>: The field to query on
• value <Number>|<Date>: the value

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: []
})
  .where('company', 'help.com')

query.lte('created_at', '2017-01-30T15:20:16.893Z')
// created_at <= '2017-01-30T15:20:16.893Z'

ne

Returns records where the field value is not equal to the specified value

• field <String>: The field to query on
• value: a value to match.

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: []
})
  .where('foo', 1)

query.ne('foobar', 'fizzbuzz')
// (foobar <> 'fizzbuzz')

nin

The opposition query to in, fetches records where the field value is not in the specified values

Parameters

• field <String> The field name or alias to search on
• values <String>|<Array>: A specific value, an array of specific values, or a comma seperated string of values to match against

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: [1]
})
  .where('foo', 1)

query.nin('foobar', 'fizz,buzz,foobar')

// foobar NOT IN ('fizz', 'buzz' 'foobar')

offset

For pagination. Specifies the number of documents to skip in the result set. Generally used with the limit function

Parameters

• value <Number>: The number of documents to offset

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: []
})
  .where('organization_id', 1)

query
  .limit(10)
  .offset(20)

// SELECT * FROM users WHERE organization_id = 1 LIMIT 10 OFFSET 20

orderby(field, value)

Specifies an order on a specific fields. Valid order options are asc and desc

Parameters

• field <String>: The field name or alias to order on
• value <String>: The order direction (asc | desc)

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
, values: []
})
  .where('organization_id', 1)

query
  .orderby('created_at', 'desc')
  .orderby('age', 'asc')

// ORDER BY created_at DESC, age ASC

range(field, values)

Adds a range clause to restrict documents between a specific set of values

Parameters

• field <String> The field name or alias to search on
• values <String>|<Array>: A specific value, an array of specific values, or a comma seperated string of values to match against

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
})
  .where('name', null, 'ne')

query.range('created_at', '2016-01-01,2016-02-01')
// name IS NOT NULL AND (BETWEEN '2016-01-01' AND '2016-02-01')

toJSON([operator])

Returns the raw query object suitable for passing the the postgres driver

Parameters

• operator <String> The operator to use to combine where clauses. If not specified The default will be used

Example

const query = new PGQuery({
  text: 'SELECT * FROM users'
})
  .where('name', null)

query
  .gt('age', 12)
  .in('foo', ['a', 'b', 'c']
  .range('create_at', '2016-01-01,2016-02-01')
  .toJSON()

db.query(query.toJSON(), (err, res) => {
  ...
})

console.log(query.toJSON('OR'))

{
  query: 'SELECT * FROM users WHERE name IS NULL'
, values: ['12', 'a', 'b', 'c', '2016-01-01', '2016-02-01']
, params: 6
, text: `<FULL SQL QUERY>`
, where: `<GENERATED WHERE CLAUSE>`
, clauses: `<ARRAY OF GENERATED WHERE CLAUSES>`
}

toString([operator])

Returns the Rendered SQL query suitable for passing the the postgres driver

Parameters

• operator <String> The operator to use to combine where clauses. If not specified The default will be used

withCount(table_name, [operator])

Returns The original query wrapped in an additional clause to return the total number of records that meet the query criteria prior to any pagination.

Parameters

• *table_name <String> The name of the table, or from cluase used being queried
• operator <String> The operator to use to combine where clauses. If not specified The default will be used

Example

const query = new Query({
  text: 'SELECT * from pgquery'
})
.where('organization_id', 'f2f31927-12a8-4000-8e73-7a28aaf8e27d')
.gt('incr', 50)
.orderby('foobar')
.limit(5)
.withCount('pgquery', 'AND')

postgres.queryOne(query, (err, res) => {
  console.log(res)
})
{
  "total": 50
, data: [{
    ...
  }, {
    ...
  }, {
    ...
  }, {
    ...
  }, {
    ...
  }]
}

The Generated SQL output might look like:

WITH table_count AS (
  SELECT 
    COUNT(*) AS total, 1::SMALLINT as __id
  FROM pgquery
  WHERE (organization_id = 'f2f31927-12a8-4000-8e73-7a28aaf8e27d')
  AND ( (incr > 50) )
), query AS (

WITH table_count AS (
  SELECT 1::SMALLINT as __id, *
  FROM pgquery
  WHERE (organization_id = 'f2f31927-12a8-4000-8e73-7a28aaf8e27d')
  AND ( (incr > 50) )
  ORDER BY foobar DESC
  LIMIT 5 OFFSET 0
)
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.organization_id = query.organization_id
GROUP BY table_count.total

Author

• Eric Satterwhite <[email protected]>