query2query-js is a simple utility for converting a JSON representation of a
query string into a SQL where clause. The package is designed to be used with
express and pg but may be useful in other contexts as well.
To install query2query-js simply run the following command:
npm install --save query2query-js
NOTE: query2query-js is not the same package as query2query.
query2query-js expects two arguments when called. The first argument is the
JSON representation of the query string you desire to parse. The second is a set
of optional instructions on how to translate the values into SQL in the form
of an object.
There are three fixed top level attributes available in the options object.
If provided query2query-js will start the generated query using AND instead
of WHERE and will append it's results to the string supplied by the
previusQuery attribute.
If whiteList is a truthy value any query string key that does not have a
coresponding key in the options object will be ignored when the query is
created. Whitelist has a default value of true.
NOTE: Always use whiteList: true for anything besides debugging/development
as it would leave you open to SQL injection attacks otherwise.
This option is used to obtain the desired order of the generated query. If
present sort should be an array of objects that have the following attributes:
| attribute | description |
|---|---|
| key | A column name on which sorting is allowed |
| namespace | An optional namespace to scope the key under in the created sort statement. |
The column and direction of sort will be derived from reading the options
sort and sortOrder from the query string object. The sort attribute of
the query string object will be expected to be a string value that coresponds
to one of the keys provided in the sort options array. sortOrder is
expected to be one of two string options: ASC or DESC.
All other attributes found in the options object will be assumed to be descriptions of allowed search criteria. The attribute keys should be the coresponding camel case value of a snake case column in your databasse schema. The value of the attribute should be an object with the following attributes:
| option | description |
|---|---|
| namespace | A name space to scope the item under in the created query. |
| type | The type of match desired. Options described below. |
query2query-js provides several options to format what the segment of a query
will look like. These options can be provided as the type attribute of the
options object as described above.
| type | description | SQL |
|---|---|---|
| string | A string ilike search. NOTE: default if no value is provided | some_item ILIKE $1 |
| integer | An exact match for an integer. | id = $1 |
| exact | An exact match for a string. | name = $1 |
| array | An array of string values. NOTE: does not currently work with integer values | name IN ($1,$2,$3,$4,...) |
const q2q = require('query2query-js')
const optionsObject = {
whiteList: true,
id: { type: 'integer', namespace: 'u' },
userName: { type: 'string' },
userEmail: { type: 'exact' },
userHobbies: { type: 'array' },
sort : [
{ key: 'id', namespace: 'u' },
{ key: 'userEmail' },
],
}
const queryString2 = {
id: 1,
userName: 'Bob',
userEmail: '[email protected]',
userHobbies: ['swimming', 'running', 'polka'],
sort: 'id',
sortOrder: 'ASC',
}
q2q(queryString, optionsObject)
//output
{
text: 'WHERE u.id = $1 AND user_name ILIKE $2 AND user_email = $3 AND user_hobbies IN ($4,$5,$6) ORDER BY u.id ASC',
values: [ 1, 'Bob%', '[email protected]', 'swimming', 'running', 'polka' ]
}