@norvento/query-builder
v1.2.1
Published
Norvento db query builder
Downloads
48
Keywords
Readme
Norvento query builder
Usage
Create a custom class extending BaseQueryConfig
import BaseQueryConfig from '@norvento/query-builder';
const QUERY_DEFINITION = {
columns: {
"id": {
"name": {
"es-ES": "c.name_es",
"en-GB": "c.name_en"
},
},
},
defaultOrder: {
column: "c",
direction: "ASC"
},
from: "customer c"
}
export default new BaseQueryConfig(QUERY_DEFINITION);
Get the query builder corresponding to your DB Vendor with your query config class:
import MyQueryConfigClass from './MyQueryConfigClass';
import QueryBuilder from '@norvento/query-builder';
const mySQLQueryBuilder = QueryBuilder.getQueryBuilder("MySQL", MyQueryConfigClass);
const myQuery = mySQLQueryBuilder.buildSelect({
cols: ["name"],
filters: [{
name: "test"
}],
order: [{
column: "name",
direction: "ASC"
}],
pagination: {
numPage: 1,
tamPage: 20
},
locale: 'es-ES'
})
Suported filter operators:
- IN
- NOT IN
- LIKE
- RAW_LIKE
- STARTING_LIKE
- ENDING_LIKE
- NOT LIKE
- IS
- IS NOT
- BETWEEN
- LT
- GT
- GTE
- LTE
- EQ
- NEQ
Docs
getQueryBuilder(dbVendor, queryConfig)
dbVendor: "MySQL | "PostgreSQL" | "OracleDB"
queryConfig: object extending BaseQueryConfig
Returns object extending BaseQueryBuilder
BaseQueryConfig
methods:
constructor(queryDefinition) Params
- queryDefinition: object with the shape:
{ columns: { [key: string]: ColumnMapping }, defaultOrder: Order | Order[], from: string }
getFrom(filters) This method is intended to be overwritten in classes than extends BaseQueryConfig
Params:
- filters: object with the shape:
{ [column: string]: (any | { operation: string }), OR?: ((column?: string) => (any | { operation: string }))[] }
BaseQueryBuilder methods:
- buildSelect(selectParams):
Creates a select SQL query with the given params
Params:
- selectParams: object with the shape:
{ cols?: string[], filters?: Filter[], pagination?: Pagination, order?: Order | Order[], locale?: string, distinct?: boolean }
- buildCount(countParams):
Creates a count SQL query with the given params
Params:
- countParams: object with the shape:
{ cols?: string[], filters?: Filter[], locale?: string, distinct?: boolean }
Query definition options:
columns: Tables column mapping. defaultOrder: The order used when no order is provided for the query. from: SQL from sentence.
Column definition options
name: string | {
'en-GB': string,
'es-ES': string,
...
},
type?: "date" | "timestamp"
groupBy?: boolean
Examples
Select query with no params
This gives a sql select with the default columns, default locale and default order
mySQLQueryBuilder.buildSelect()
Select query with pagination
mySQLQueryBuilder.buildSelect({
pagination: {
numPage: 1,
tamPage: 20
}
})
Select query with order
mySQLQueryBuilder.buildSelect({
order: [
{
column: "id",
direction: "ASC"
}
{
column: "name",
direction: "ASC"
}
],
})
Select query with locale
mySQLQueryBuilder.buildSelect({
locale: 'es-ES'
})
Select query with columns
mySQLQueryBuilder.buildSelect({
cols: ['id', 'name']
})
Select query with simple filter
mySQLQueryBuilder.buildSelect({
filters: [{
id: 5
}]
})
Select query with operators in filters
mySQLQueryBuilder.buildSelect({
filters: [{
id: {
GT: 1
}
}]
})
Select query with AND clauses
mySQLQueryBuilder.buildSelect({
filters: [
{
id: 1
},
{
name: "test"
}
]
})
This generates the following where clause: WHERE id = 1 AND name = 'test'
Select query with OR clauses
mySQLQueryBuilder.buildSelect({
filters: [
{
OR :[
{
id: 1
},
{
name: "test"
}
]
}
]
})
This generates the following where clause: WHERE id = 1 OR name = 'test'