@allstar/pg-query NPM

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 <esatterwhite@wi.rr.com>

postgresql postgres pg SQL query builder

pg-migrate

1.0.0

8 years ago