README

Norvento query builder

Usage

Create a custom class extending BaseQueryConfig

import BaseQueryConfig from '@norvento/query-builder';

const QUERY_DEFINITION = {
    main_table: "customer",
    table_alias: {
        customer: "c"
    },
    columns: {
        "id": {
            "name": {
                "es-ES": "c.name_es",
                "en-GB": "c.name_en"
            },
            "table_field": true,
        },
    },
    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:

{
    main_table: string,
    table_alias: {
    [key: string]: string
    }, 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:

main_table: The table represented by the mapping. It will be used in update/delete sentences. table_alias: Definition for the alias used for the tables. 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"
table_field: boolean,
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'

Usage no npm install needed!