Query Builder
Validate AIS-conformant query trees and serialise them as SQL strings.
import createQueryBuilder from '@peak-ai/query-builder';
import * as moment from 'moment';
const fieldMetadata = {
associationTypeFieldName: 'associationtype',
associationRankFieldName: 'associationrank',
};
const fieldOptions = [
{
name: 'useremail',
type: 'large',
},
{
name: 'ordercount',
type: 'integer',
},
{
name: 'customergender',
type: 'small',
},
];
const query = {
id: '1',
combinator: 'and',
rules: [
{
id: '1',
field: 'useremail',
value: 'joebloggs@gmail.com',
operator: '=',
},
{
id: '2',
combinator: 'or',
rules: [
{
id: '2',
field: 'ordercount',
value: '20',
operator: '>',
},
{
id: '3',
field: 'customergender',
value: 'male',
operator: '=',
},
],
},
],
};
const dateFormatter = (date: Date) => moment(date).format('YYYY-MM-DD');
const queryBuilder = createQueryBuilder({ dateFormatter });
console.log(queryBuilder.where(query, fieldOptions, fieldMetadata));
// => `(useremail = 'joebloggs@gmail.com' AND (ordercount > 20 OR customergender = 'male'))`
Getting started
You can install Query Builder from npm:
npm i -E @peak-ai/query-builder
The library comprises of a factory function, exposed via a default
binding, as well as an additional binding for the Mode
enum and operator bindings:
import createQueryBuilder, { Mode, operators } from '@peak-ai/query-builder';
If you're using CommonJS, this means you'll have to destructure and rename the default
binding:
const { default: createQueryBuilder, Mode, operators } = require('@peak-ai/query-builder');
API
createQueryBuilder(options: BuilderOptions)
const queryBuilder = createQueryBuilder({
dateFormatter: (date: Date) => moment(date).format('YYYY-MM-DD'),
mode: Mode.Display,
operators: operators.sql,
});
Creates a query builder API surface for the given options.
Arguments
-
options: BuilderOptions
: an object containing:-
dateFormatter: (date: Date) => string
: a function for converting dates to strings, the return value of which is used directly by the query builder -
mode: Mode
(optional, defaulting toMode.Validation
): determines how the query builder should handle invalid query trees (missing values, no top-level rules etc.):-
Mode.Display
: doesn't throw when the tree is deemed invalid, instead rendering fallback values within the rendered SQL strings -
Mode.Validation
: explicitly throws an error when the query tree is deemed invalid
-
-
operators: Operators
(optional, defaulting tooperators.sql
): specifies the operators from which the queries are built:-
operators.sql
: maps the rule operators to sql operators -
operators.recommender
: maps the rule operators to python operators, typically used for interactions with recommenders
-
-
Returns
QueryBuilder
: a query builder API surface
QueryBuilder#where(query: RootQuery, fieldOptions: FieldOption[], fieldMetadata: FieldMetadata)
queryBuilder.where(query, fieldOptions, fieldMetadata);
Builds, optionally validates, and returns an SQL string for the given query tree, intended to be used in an SQL WHERE
clause.
Arguments
-
query: RootQuery
: a tree representing your SQL query. See the example at the beginning of the README -
fieldOptions: FieldOption[]
: an array of objects containing the names and data types of all the possible fields that can be queried. See the example at the beginning of the README -
fieldMetadata: FieldMetadata
: an object containing the column names of association fields:-
associationTypeFieldName: string
: the column name of the association type field -
associationRankFieldName: string
: the column name of the association rank field
-
Returns
string
: an SQL-compliant WHERE
clause
Local development
Prerequisites:
- Fork this repo
git clone <your fork>
cd query-builder
nvm i
yarn
You can then run:
-
yarn lint
: runs ESLint against the source code -
yarn format
: fixes and overwrites any source files that don't adhere to our Prettier config -
yarn format:check
: checks the formatting of our source files and fails if any don't adhere to said config, for CI and prepublish purposes -
yarn build
: runs the TypeScript compiler against the project and produces distributable output -
yarn test
: runs the unit tests -
yarn test:dist
: runs the compiled unit tests against the compiled source. Typically used by our pre-commit hook, CI, and pre-publish script
Contributing
Given this library is rather specific to our commercial requirements, we can't accept any contributions that change the behaviour of the library. However, any contributions that may improve the underlying implementation (e.g. maintainability, performance etc.) will be considered given the test suite continues to pass. Please see the contribution guidelines for more info.