Security News
Oracle Drags Its Feet in the JavaScript Trademark Dispute
Oracle seeks to dismiss fraud claims in the JavaScript trademark dispute, delaying the case and avoiding questions about its right to the name.
By Sarah Gooding - Feb 07, 2025
New Case Study:See how Anthropic automated 95% of dependency reviews with Socket.Learn More →
@allstar/pg-query
Advanced tools
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 usingwhere(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 ofANDorOR- The default isAND
- text
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 fromnano-richendpoint
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
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>][]:trueto 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
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
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
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
FAQs
Minimal query helper for postgresql
We found that @allstar/pg-query demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Package last updated on 28 Apr 2018
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Related posts
Security News
Linux Foundation Warns Open Source Developers: Compliance with Sanctions Is Not Optional
The Linux Foundation is warning open source developers that compliance with global sanctions is mandatory, highlighting legal risks and restrictions on contributions.
By Sarah Gooding - Feb 06, 2025
Security News
Maven Central Adds Sigstore Signature Validation
Maven Central now validates Sigstore signatures, making it easier for developers to verify the provenance of Java packages.
By Sarah Gooding - Feb 06, 2025