{"_id":"@allstar/pg-query","_rev":"1-11d84058f38501113c6857c29246a017","name":"@allstar/pg-query","dist-tags":{"latest":"1.0.0"},"versions":{"1.0.0":{"name":"@allstar/pg-query","version":"1.0.0","description":"Minimal query helper for postgresql","main":"index.js","scripts":{"test:integration":"TEST_SUITE=1 tap -j 2 test --cov","posttest:integration":"tap --coverage-report=text-summary","test":"npm run test:ci","test:ci":"docker-compose -f compose/test.yml up --abort-on-container-exit --exit-code-from pg-query","pull:test":"docker-compose -f compose/test.yml pull","start":"npm run start:dev","start:dev":"docker-compose -f compose/dev.yml up --build","stop":"npm run stop:dev","stop:dev":"docker-compose -f compose/dev.yml down","seed":"node tools/seed.js","migrate":"pg-migrate -u postgres://$db__user:$db__password@$db__host:$db__port/$db__name -d migrations"},"keywords":["postgresql","postgres","pg","SQL","query","builder"],"repository":{"type":"git","url":"git+https://esatterwhite@bitbucket.org/hitlist/pg-builder.git"},"author":{"name":"Eric Satterwhite","email":"esatterwhite@wi.rr.com"},"license":"MIT","devDependencies":{"@allstar/postgres":"^1.0.1","keef":"^2.0.2","tap":"^11.1.4"},"dependencies":{"pg-migrate":"^2.0.1"},"gitHead":"dc612e3cdfd8c88aa45b244147126bda53db8b84","homepage":"https://bitbucket.org/hitlist/pg-builder#readme","_id":"@allstar/pg-query@1.0.0","_npmVersion":"5.5.1","_nodeVersion":"8.9.3","_npmUser":{"name":"esatterwhite","email":"esatterwhite@wi.rr.com"},"dist":{"integrity":"sha512-K4o0hNezs+zYOFT/jp2RpbEzHgRNcKBHEpOeih0vg3Tp4tiuhCIovBx4XGhXxCxMjuKJeAVamhSPu0d7WtWWyQ==","shasum":"b931f03c616f34927e704a33735599f281906557","tarball":"https://registry.npmjs.org/@allstar/pg-query/-/pg-query-1.0.0.tgz","fileCount":15,"unpackedSize":77780,"npm-signature":"-----BEGIN PGP SIGNATURE-----\r\nVersion: OpenPGP.js v3.0.4\r\nComment: https://openpgpjs.org\r\n\r\nwsFcBAEBCAAQBQJa5MOkCRA9TVsSAnZWagAAMc0P/A0p8wID6dl0eSpXGhcX\n4zuhF/QAaLcyS4Wh0hw5qUtt4yHZWLIr/pXL2WBKw16dxjRrridg26sRVFfj\nnW5xYVuKPBB5dxF/dtDJA6hglZptPBmmo9QiMVwW8k10Mx3hINQ/oa1c9cQS\nM+wkS7tBxjJXD4SjwOifhNz7fMfKHQwObdwx6indnlS/ewbtqvKL2BxZWYYH\nOTgcMvT+3JVqpaY4yGvtxuQLFSP0gMWRu+Z5WoUezn5WTYG7RVViTqpSc0+W\nwTyo6u2JriJIt2NzjT35yx+lwD1a5Vl0rhIl7K0BiSI5Srb+jIKsE8MKC4/W\nQzwPyLGBDOYq/Lk3p78l308UC6cwE5jaHiMONZ2qQ83bWCBORyAoQ8T+PWIg\nYTijkS6KL60FRwMhru7DjtYWFuvZn0h3NjH90bAeo5XIxku/Qos9gsMEYPvy\n+hUJdNhxQQhpBnUIE3lax0mDplIibyoT9sVqzRHkDE+Ytn8s8wXitHWUDunz\nfcVYTYc+j/O9wH/zuz/MpKcA+2Yeu8+s5nCcm5V6pR10GAwY7IomMlZPhGWs\nHhJEsQoFR3aRI5MPolIkFlgqzuVahDJwcHgaTVCnNyZh2SM575psDDD/1eGZ\n7H8aJt/xXA1n8vGdU2bZuOdFzMAXaEnMuv0TVtYuVYONkdVaMed54LyBTXlI\nGHog\r\n=6EF1\r\n-----END PGP SIGNATURE-----\r\n","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEUCICmoum/glXQlxvCToTNVWUybCmTN1OgWrQYp7NhY3Ps+AiEA44UWaWWeB32t3k/N6K/aTGVpNopIY9Wl2Qj6qIFJ7sM="}]},"maintainers":[{"name":"esatterwhite","email":"esatterwhite@wi.rr.com"}],"directories":{},"_npmOperationalInternal":{"host":"s3://npm-registry-packages","tmp":"tmp/pg-query_1.0.0_1524941730310_0.35347983828269647"},"_hasShrinkwrap":false}},"time":{"created":"2018-04-28T18:55:30.243Z","1.0.0":"2018-04-28T18:55:30.415Z","modified":"2022-04-04T13:34:49.049Z"},"maintainers":[{"name":"esatterwhite","email":"esatterwhite@wi.rr.com"}],"description":"Minimal query helper for postgresql","homepage":"https://bitbucket.org/hitlist/pg-builder#readme","keywords":["postgresql","postgres","pg","SQL","query","builder"],"repository":{"type":"git","url":"git+https://esatterwhite@bitbucket.org/hitlist/pg-builder.git"},"author":{"name":"Eric Satterwhite","email":"esatterwhite@wi.rr.com"},"license":"MIT","readme":"# pg-query\n\nA simple query builder for postgres\n\n# Install\n\n```bash\nnpm install @helpdotcom/pg-query --save\n```\n\n# Usage\n\nThe query builder is only responsible for constructing complex `WHERE` clauses.\nYou must supply the base query with at least one initial `WHERE` condition\n\n```js\nconst PGQuery = require('@helpdotcom/pg-query')\nconst postgres = require('@helpdotcom/postgres')\n\ndb = new postgres({ ... })\n\nconst query = new PGQuery({\n  text: `\n  SELECT * FROM users\n  `\n, values: []\n})\n.where('organization_id', 'b45d6a26-1011-4242-b4fe-fad3f01d5d66')\n.contains('email', 'mail.com')\n.gt('age', 30)\n.toJSON()\n\ndb.query(query, (err, res) => {\n  console.log(res.rows)\n})\n```\n\n## Field Mappings\n\nThere is limited support for array typed columns including comparisons of entire\narrays as well as finding items in arrays. Currently, the `contains`, `in` and `nin`\nfilters can be used for array fields. To enable array functionality, you must specify\na field as an `array` field\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * from users WHERE name IS NOT NULL'\n, field: {\n    roles: 'array'\n  , names: 'array'\n  }\n})\nquery\n  .in('roles', 'ticket_agent,chat_agent')\n  .nin('names', ['billy', 'william', 'will'])\n\n// WHERE name IS NOT NULL AND (\n//  (roles && {ticket_agent,chat_agent})\n// AND\n//  ( NOT (names && {billy,william,will}))\n//)\n```\n\n\n# API\n\n# new PGQuery(options)\n\nCreates a new query object\n\n**Parameters**\n\n* options [`<Object>`][] An key value pairing of alias names to document field\n  names in object path notation\n  * **text** [`<String>`] A valid postgresSQL query. First where condition can be included or set using\n    `where(field, value, comparison)`. **Doing both will cause malformed SQL**\n  * **operator** (*optional*) [`<String>`] The operator used to combine all where clauses added to the base query.\n    Operators can be one of `AND` or `OR` - The default is `AND`\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n})\n.where('age', 24, 'gt')\n.eq('color', 'red')\n.ed('color', 'blue')\n\n// WHERE age > 24 AND (( color = 'red' ) AND ( color = 'blue' ))\n```\n\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, operator: 'OR'\n})\n.where('age', 24, 'gt')\n.eq('color', 'red')\n.ed('color', 'blue')\n\n// WHERE age > 24 AND (( color = 'red' ) OR ( color = 'blue' ))\n```\n\n# compile(options)\n\nGenerates query object from a [`nano-richendpoint`][] object\n\n**Parameters**\n\n* **options** [`<Object>`][]: The parsed endpoint object from [`nano-richendpoint`][]\n  * **page** (*optional*) [`<Object>`][]: Pagination information\n    * **limit** (*optional*) [`<Number>`][]: The maximum number of records to return\n    * **offset** (*optional*) [`<Number>`][]: The number of records to skip if the total exceeds the limit\n  * **order** (*optional*) [`<Object>`][]: Ordering information\n  * **filter** (*optional*) [`<Object>`][]: Filtering information\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n})\n  .where('name', null, 'ne')\nconst opts = {\n  page: {\n    limit: 50\n  , offset: 100\n  }\n, order: {\n    foo: 'asc'\n  , bar: 'desc'\n  }\n, filter: {\n    foo: {\n      startswith: 'foo'\n    , endswith: 'bar'\n    }\n  , baz: {\n      in: [1, 2, 3]\n    }\n  }\n}\n\nquery.compile(opts)\nPGQUERY.pprint(query)\n```\n\nProduces\n\n```sql\n\nSELECT *\nFROM users\nWHERE name IS NOT NULL\nAND (\n    (foo LIKE 'foo' || '%')\n  AND\n    (foo LIKE '%' || 'bar')\n  AND\n    (baz IN (1, 2, 3))\n)\nORDER BY foo ASC, bar DESC\nLIMIT 50 OFFSET 100\n```\n\n\n```js\n// OR clauses\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n})\n  .where('name', null, 'ne')\nconst opts = {\n  page: {\n    limit: 50\n  , offset: 100\n  }\n, order: {\n    foo: 'asc'\n  , bar: 'desc'\n  }\n, filter: {\n    foo: {\n      startswith: 'foo'\n    , endswith: 'bar'\n    }\n  , baz: {\n      in: [1, 2, 3]\n    }\n  }\n}\n\nquery.compile(opts)\nquery.toString('OR')\n```\n\nProduces\n\n```sql\nSELECT *\nFROM users\nWHERE name IS NOT NULL\nAND (\n    (foo LIKE 'foo' || '%')\n  OR\n    (foo LIKE '%' || 'bar')\n  OR\n    (baz IN (1, 2, 3))\n)\nORDER BY foo ASC, bar DESC\nLIMIT 50 OFFSET 100\n```\n\n```js\nconst opts = req.$.endpoint.parsed\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n})\n  .where('name', null, 'ne')\n\nquery.compile(opts)\n```\n\nProduces\n\n```sql\nSELECT *\nFROM users\nWHERE name IS NOT NULL\nAND (\n    (foo LIKE 'foo' || '%')\n  AND\n    (foo LIKE '%' || 'bar')\n  AND\n    (baz IN (1, 2, 3))\n)\nORDER BY foo ASC, bar DESC\nLIMIT 50 OFFSET 100\n```\n\n# where(field, value, comparison)\n\nGenerates the first statements for a `WHERE` clase before additional\nfiltering is applied. Uses an equality filter function (`eq`) by default.\nThis allows the developer to control the query conditions before end user\ninput is applied\n\nStrongly recommended when generating a query using `withCount`\n\n**Parameters**\n\n* **field** [`<String>`][] The field name or alias to search on\n* **value** [`<String>`][] The word or word fragement to search for\n* **comparison** [`<String>`][] Comparison filter function to use for clause. Defaults to (`eq`)\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n})\n.where('age', 24, 'gt')\n.in('name', ['bill', 'max', 'fred'])\n```\n\n```sql\nSELECT * FROM users WHERE age > 24 AND ((name IN ('bill', 'max', 'fred'))\n```\n- - -\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n})\n.where('organization_id', 'abc123')\n.gt('age', 24)\n.where('archived', false)\n.lt('age', 50)\n```\n\n```sql\nSELECT * FROM users\nWHERE\n  organization_id = 'abc123'\nAND\n  archived = false\nAND (( age > 24 ) AND ( age < 50 ))\n```\n\n# contains(field, value)\n\nIncludes a `LIKE` clause wrapping the term in wildcard characters `%`\n\n**Parameters**\n\n* **field** [`<String>`][] The field name or alias to search on\n* **value** [`<String>`][] The word or word fragement to search for\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n})\n  .where('name', null, 'ne')\n\nquery.contains('foo', 'ing')\n\n// foo LIKE %ing%\n```\n\n# exact\n\nIncludes a equality comparison clause (`=`)\n\n**Parameters**\n\n* **field** [`<String>`][] The field name or alias to search on\n* **value** [`<String>`][] The word or word fragement to search for\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: []\n})\n  .where('name', 'Billy')\n\nquery.exact('foo', 'hello world')\n\n// foo = 'hello world'\n```\n\n# iexact\n\nIncludes a new equality clause for the field to match a specific value, in a\ncase insensitive manner by wrapping terms with the `LOWER` function\n\n**Parameters**\n\n* **field** [`<String>`][] The field name or alias to search on\n* **value** [`<String>`][] The word or word fragement to search for\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: []\n})\n  .where('name', 'Billy')\n\nquery.iexact('foo', 'hello woRlD')\n\n// LOWER(foo) = LOWER('hello woRlD')\n```\n\n# gt(field, value)\n\nIncludes a greater than comparison clause (`>`)\n\n**Parameters**\n\n* **field** [`<String>`][]: The field to query on\n* **value** [`<Number>`][]|[`<Date>`][]: the value\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: []\n})\n  .where('company', 'help.com')\n\nquery.gt('created_at', '2017-01-30T15:20:15')\n// created_at > 2017-01-30T15:20:15\n```\n\n# gte(field, value)\n\nIncludes a greater than or equal to comparison clause (`>=`)\n\n**Parameters**\n\n* **field** [`<String>`][]: The field to query on\n* **value** [`<Number>`][]|[`<Date>`][]: the value\n* **format** [`<String>`][]: The format to use for date values (default: 'yyyy-mm-dd')\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: []\n})\n  .where('company', 'help.com')\n\nquery.gte('created_at', '2017-01-30T15:20:15')\n// created_at >= 2017-01-30T15:20:15\n```\n\n# in(field, value)\n\nApplies an `IN` cluase using a list of values. If field has been defined as\nand array column, arrays containing any of the values will be matched\n\n**Parameters**\n\n* **field** [`<String>`][] The field name or alias to search on\n* **values** [`<String>`][]|[`<Array>`][]: A specific value, an array of\n  specific values, or a comma seperated string of values to match against\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: []\n, fields: {\n    foo: 'array'\n  }\n})\n  .where('company', 'help.com')\n\nquery\n  .in('foo', 'a,b,c')\n  .in('bar', ['hello', 'world'])\n\n// ( foo && '{a,b,c}' ) AND ( bar IN (hello, world) )\n```\n\n# isnull(yes)\n\nIncludes an `IS NULL` or `IS NOT NULL` clause to the query\n\n**Parameters**\n\n* **field** [`<String>`][]: The field name or alias to search on\n* **value** [`<Boolean>`][]: `true` to find documents where the field is null.\n  `false`\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: []\n})\n  .where('company', 'help.com')\n\nquery\n  .isnull('foo', true)\n  .isnull('bar', false)\n\n// (foo IS NULL) AND (bar IS NOT NULL)\n```\n\n# limit(maximum)\n\nRestricts the result set to a maximum number of results\n\n**Parameters**\n\n* **maximum** [`<Number>`][]: The maximum number of results to return\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: []\n})\n  .where('company', 'help.com')\n\nquery\n  .limit(25)\n\n// LIMIT 25\n```\n\n# lt\n\nAdds a partial, exclusionary range query do return documents where the value of a field\nis less than the specified value. Date fields must be analyzed as dates.\n\n**Parameters**\n\n* **field** [`<String>`][]: The field to query on\n* **value** [`<Number>`][]|[`<Date>`][]: the value\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: []\n})\n  .where('company', 'help.com')\n\nquery.lt('created_at', '2017-01-30T15:20:16.893Z')\n// created_at < '2017-01-30T15:20:16.893Z'\n```\n\n# lte\n\nAdds a partial, inclusionary range query do return documents where the value of a field\nis less than or equal to the specified value.\n\n**Parameters**\n\n* **field** [`<String>`][]: The field to query on\n* **value** [`<Number>`][]|[`<Date>`][]: the value\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: []\n})\n  .where('company', 'help.com')\n\nquery.lte('created_at', '2017-01-30T15:20:16.893Z')\n// created_at <= '2017-01-30T15:20:16.893Z'\n```\n\n# ne\n\nReturns records where the field value is *not* equal to the specified value\n\n* **field** [`<String>`][]: The field to query on\n* **value**: a value to match.\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: []\n})\n  .where('foo', 1)\n\nquery.ne('foobar', 'fizzbuzz')\n// (foobar <> 'fizzbuzz')\n```\n\n# nin\n\nThe opposition query to `in`, fetches records where the field value is *not*\nin the specified values\n\n**Parameters**\n\n* **field** [`<String>`][] The field name or alias to search on\n* **values** [`<String>`][]|[`<Array>`][]: A specific value, an array of\n  specific values, or a comma seperated string of values to match against\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: [1]\n})\n  .where('foo', 1)\n\nquery.nin('foobar', 'fizz,buzz,foobar')\n\n// foobar NOT IN ('fizz', 'buzz' 'foobar')\n\n```\n\n# offset\n\nFor pagination. Specifies the number of documents to skip in the result set.\nGenerally used with the `limit` function\n\n**Parameters**\n\n* **value** [`<Number>`][]: The number of documents to offset\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: []\n})\n  .where('organization_id', 1)\n\nquery\n  .limit(10)\n  .offset(20)\n\n// SELECT * FROM users WHERE organization_id = 1 LIMIT 10 OFFSET 20\n```\n\n# orderby(field, value)\n\nSpecifies an order on a specific fields. Valid order options are `asc` and `desc`\n\n**Parameters**\n\n* **field** [`<String>`][]: The field name or alias to order on\n* **value** [`<String>`][]: The order direction (`asc` | `desc`)\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n, values: []\n})\n  .where('organization_id', 1)\n\nquery\n  .orderby('created_at', 'desc')\n  .orderby('age', 'asc')\n\n// ORDER BY created_at DESC, age ASC\n```\n\n# range(field, values)\n\nAdds a range clause to restrict documents between a specific set of values\n\n**Parameters**\n\n* **field** [`<String>`][] The field name or alias to search on\n* **values** [`<String>`][]|[`<Array>`][]: A specific value, an array of\n  specific values, or a comma seperated string of values to match against\n\n## Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n})\n  .where('name', null, 'ne')\n\nquery.range('created_at', '2016-01-01,2016-02-01')\n// name IS NOT NULL AND (BETWEEN '2016-01-01' AND '2016-02-01')\n```\n\n# toJSON([operator])\n\nReturns the raw query object suitable for passing the the postgres driver\n\n**Parameters**\n\n* **operator** [`<String>`][] The operator to use to combine where clauses. If not specified\n  The default will be used\n\n### Example\n\n```js\nconst query = new PGQuery({\n  text: 'SELECT * FROM users'\n})\n  .where('name', null)\n\nquery\n  .gt('age', 12)\n  .in('foo', ['a', 'b', 'c']\n  .range('create_at', '2016-01-01,2016-02-01')\n  .toJSON()\n\ndb.query(query.toJSON(), (err, res) => {\n  ...\n})\n\nconsole.log(query.toJSON('OR'))\n\n{\n  query: 'SELECT * FROM users WHERE name IS NULL'\n, values: ['12', 'a', 'b', 'c', '2016-01-01', '2016-02-01']\n, params: 6\n, text: `<FULL SQL QUERY>`\n, where: `<GENERATED WHERE CLAUSE>`\n, clauses: `<ARRAY OF GENERATED WHERE CLAUSES>`\n}\n```\n\n# toString([operator])\n\nReturns the Rendered SQL query suitable for passing the the postgres driver\n\n**Parameters**\n\n* **operator** [`<String>`][] The operator to use to combine where clauses. If not specified\n  The default will be used\n\n# withCount(table_name, [operator])\n\nReturns The original query wrapped in an additional clause to return the\ntotal number of records that meet the query criteria prior to any pagination.\n\n**Parameters**\n\n* **table_name* [`<String>`][] The name of the table, or from cluase used being queried\n* **operator** [`<String>`][] The operator to use to combine where clauses. If not specified\n  The default will be used\n\n### Example\n\n```js\nconst query = new Query({\n  text: 'SELECT * from pgquery'\n})\n.where('organization_id', 'f2f31927-12a8-4000-8e73-7a28aaf8e27d')\n.gt('incr', 50)\n.orderby('foobar')\n.limit(5)\n.withCount('pgquery', 'AND')\n\npostgres.queryOne(query, (err, res) => {\n  console.log(res)\n})\n{\n  \"total\": 50\n, data: [{\n    ...\n  }, {\n    ...\n  }, {\n    ...\n  }, {\n    ...\n  }, {\n    ...\n  }]\n}\n```\n\nThe Generated SQL output might look like:\n\n```sql\nWITH table_count AS (\n  SELECT \n    COUNT(*) AS total, 1::SMALLINT as __id\n  FROM pgquery\n  WHERE (organization_id = 'f2f31927-12a8-4000-8e73-7a28aaf8e27d')\n  AND ( (incr > 50) )\n), query AS (\n\nWITH table_count AS (\n  SELECT 1::SMALLINT as __id, *\n  FROM pgquery\n  WHERE (organization_id = 'f2f31927-12a8-4000-8e73-7a28aaf8e27d')\n  AND ( (incr > 50) )\n  ORDER BY foobar DESC\n  LIMIT 5 OFFSET 0\n)\nSELECT\n  table_count.total::integer,\n  COALESCE(\n    JSON_AGG(\n      ROW_TO_JSON(query.*)\n    ) FILTER (WHERE query.id IS NOT NULL),\n    '[]'::json\n  ) as data\nFROM\n  table_count\nLEFT JOIN query ON table_count.organization_id = query.organization_id\nGROUP BY table_count.total\n```\n\n# Author\n\n* [**Eric Satterwhite**](mailto:esatterwhite@wi.rr.com) &lt;esatterwhite@wi.rr.com&gt;\n\n[`<String>`]: https://mdn.io/string\n[`<Object>`]: https://mdn.io/object\n[`<Number>`]: https://mdn.io/number\n[`<Date>`]: https://mdn.io/date\n[`<Array>`]: https://mdn.io/array\n[`nano-richendpoint`]: https://git.help.com/common-backend/nano-richendpoint\n","readmeFilename":"README.md"}