@norvento/api-utils v0.2.0

Norvento query builder

Usage

Create a custom class extending BaseQueryConfig

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

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

    {
        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
    }

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'