For building SQL in object-oriented structure
npm install node-jql``js`
type Type = 'string'|'number'|'boolean'|'object'|'Date'|'Array'|'any'
IResultColumn
IFromTable
IConditionalExpression
IGroupBy
IOrderBy
ILimitOffset
` js
// full query
new Query(IQuery)
// normal query - SELECT ... FROM ... WHERE
new Query(IResultColumn[], IFromTable|string, ...IConditionalExpression[])
// simple query - SELECT * FROM [database].[table]
new Query(string|null, string)
// simple query - SELECT * FROM [table]
new Query(string)
{
// DISTINCT
"$distinct": boolean|undefined,
// SELECT ...
"$select": IResultColumn[]|IResultColumn|string|undefined,
// FROM ... JOIN ...
"$from": IFromTable[]|IFromTable|string|undefined,
// WHERE ...
"$where": IConditionalExpression[]|IConditionalExpression|undefined,
// GROUP BY ...
"$group": IGroupBy|string|undefined,
// ORDER BY ...
"$order": IOrderBy[]|IOrderBy|string|undefined,
// LIMIT ... OFFSET ...
"$limit": ILimitOffset|number|undefined
}
`
` js
new ResultColumn(IResultColumn)
new ResultColumn(IExpression|string, string?)
// [expression] AS [$as]
{
"expression": IExpression,
"$as": string|undefined
}
`
`js
new FromTable(IFromTable)
new FromTable(string|IQuery|[string, string], ...IJoinClause[])
new FromTable(string|IQuery|[string, string], string, ...IJoinClause[])
// [database].[table] AS [$as]
{
"database": string|undefined,
"table": string|IQuery|IRemoteTable,
"$as": string|undefined,
"joinClauses": IJoinClause[]|IJoinClause|undefined
}
`
IFromTable
IConditionalExpression
`js
new JoinClause(IJoinClause)
new JoinClause('INNER'|'CROSS'|'LEFT'|'RIGHT'|'FULL', IFromTable|string, ...IConditionalExpression[])
// [operator] JOIN [tableOrSubquery] AS [$on]
{
"operator": 'INNER'|'CROSS'|'LEFT'|'RIGHT'|'FULL'|undefined,
"tableOrSubquery": IFromTable|string
"$on": IConditionalExpression[]|IConditionalExpression|undefined
}
`
IExpression
IConditionalExpression
`js
new GroupBy(IGroupBy)
new GroupBy(IExpression|string)
new GroupBy(Array
// GROUP BY [expressions] HAVING [$having]
{
"expressions": IExpression[]|IExpression,
"$having": IConditionalExpression[]|IConditionalExpression|undefined
}
`
`js
new OrderBy(IOrderBy)
new OrderBy(IExpression|string, 'ASC'|'DESC'?)
// ORDER BY [expression] [order], [expression] [order], ...
{
"expression": IExpression,
"order": 'ASC'|'DESC'|undefined
}
`
`js
new LimitOffset(ILimitOffset)
new LimitOffset(IValue|number, IValue|number?)
// LIMIT [$limit] OFFSET [$offset]
{
"$limit": IValue|number,
"$offset": IValue|number|undefined,
}
`
** classname is a required field in IExpression that specifies the class of the Expression
`js
new CaseExpression(ICaseExpression)
new CaseExpression(ICase[], IExpression?)
// CASE [cases] ELSE [$else]
{
"cases": ICase[]|ICase,
"$else": IExpression|undefined
}
// ICase
// WHEN [$when] THEN [$then]
{
"$when": IConditionalExpression,
"$then": any
}
`
`js
// column with table specified
new ColumnExpression(IColumnExpression)
new ColumnExpression(string|null, string)
// column only
new ColumnExpression(string)
// [table].[name]
{
"table": string|undefined,
"name": string
}
`
`js
// column with table specified
new ColumnsExpression(IColumnsExpression)
new ColumnsExpression(IColumnExpression[])
// ([table].[name], ...)
{
"columns": IColumnExpression[]
}
`
supports most of the SQL built-in functions and works similarly
IParameterExpression
`js
new FunctionExpression(IFunctionExpression)
new FunctionExpression(name, ...any[])
// name
{
"name": string,
"parameters": any[]|any|undefined
}
`
`js
new MathExpression(IMathExpression)
new MathExpression(IExpression|any, '+'|'-'|'*'|'/'|'%'|'MOD'|'DIV', IExpression|any?)
// [left] [operator] [right]
{
"left": IExpression|any,
"operator": '+'|'-'|'*'|'/'|'%'|'MOD'|'DIV',
"right": IExpression|any|undefined
}
`
`js
new ParameterExpression(IParameterExpression)
new ParameterExpression(string|null, IExpression|any, string?)
// [prefix] [expression] [suffix]
// e.g. prefix is used for cases like COUNT(DISTINCT id)
{
"prefix": string|undefined,
"expression": IExpression|any,
"suffix": string|undefined
}
`js
new Unknown(IUnknown?)
new Unknown(...Type[])
// ?
{
"type": Type[]|Type|undefined
}
`
`js
new Value(IValue)
new Value(any)
// [value]
{
"type": Type[]|Type|undefined,
"value": any
}
`
`js
new Raw(IRaw|string) // aka new Keyword(IRaw|string)
// [sql]
{
"sql": string
}
`
extends IExpression
`js
new BetweenExpression(IBetweenExpression)
new BetweenExpression(IExpression|any, boolean, IExpression|any?, IExpression|any?)
// [left] [$not] BETWEEN [start] AND [end]
{
"left": IExpression|any,
"$not": boolean|undefined,
"start": IExpression|any|undefined,
"end": IExpression|any|undefined
}
`
`js
new BinaryExpression(IBinaryExpression)
new BinaryExpression(IExpression|any, operator, IExpression|any?)
// [left] [operator] [right]
{
"left": IExpression|any,
"operator": '='|'<>'|'<'|'<='|'>'|'>=',
"right": IExpression|any|undefined
}
`
`js
new ExistsExpression(IExistsExpression)
new ExistsExpression(IQuery, boolean?)
// [$not] EXISTS [query]
{
"$not": boolean|undefined,
"query": IQuery
}
`
`js
new AndExpressions(IGroupedExpressions)
new AndExpressions(IExpression[])
new OrExpressions(IGroupedExpressions)
new OrExpressions(IExpression[])
// ([expression] AND/OR [expression] ...)
{
"expressions": IExpression[]
}
`
`js
new Phrase(IGroupedExpressions)
new Phrase(IExpression[])
// [expression] [expression] ...
`
IExpression
IUnknown
IQuery
IValue
`js
new InExpression(IInExpression)
new InExpression(IExpression|any, boolean, IUnknown|IQuery|IValue|any[]?)
// [left] [$not] IN [right]
{
"left": IExpression|any,
"$not": boolean|undefined,
"operator": 'IN',
"right": IUnknown|IQuery|IValue|any[]|undefined
}
`
`js
new IsNullExpression(IIsNullExpression)
new IsNullExpression(IExpression|any, boolean)
// [left] IS [$not] NULL
{
"left": IExpression|any,
"$not": boolean|undefined
}
`
`js
new LikeExpression(ILikeExpression)
new LikeExpression(IExpression|any, boolean, IUnknown|string?)
// [left] [$not] LIKE [right]
{
"left": IExpression|any,
"$not": boolean|undefined,
"right": IUnknown|string|undefined
}
`
`js
new RegexpExpression(IRegexpExpression)
new RegexpExpression(IExpression|any, boolean, IUnknown|Regexp|string?)
// [left] [$not] REGEXP [right]
{
"left": IExpression|any,
"$not": boolean|undefined,
"right": IUnknown|Regexp|string|undefined
}
``