Mongo 风格的查询对象映射到 SQL 查询的 Node.js 库 json-sql

# Table of contents

API
Queries
Blocks
Condition operators

# API

# Initialization

To create new instance of json-sql builder you can use factory function:

var jsonSql = require('json-sql')(options);

or create instance by class constructor:

var jsonSql = new (require('json-sql').Builder)(options);

options are similar to configure method options.

# build(query)

Create sql query from mongo-style query object.

query is a json object that has required property type and a set of query-specific properties. type property determines the type of query. List of available values of type property you can see at Queries section.

Returns object with properties:

Property	Description
`query`	SQL query string
`value`	Array or object with values. Exists only if `separatedValues = true`.
`prefixValues()`	Method to get values with `valuesPrefix`. Exists only if `separatedValues = true`.
`getValuesArray()`	Method to get values as array. Exists only if `separatedValues = true`.
`getValuesObject()`	Method to get values as object. Exists only if `separatedValues = true`.

# configure(options)

Set options of json-sql builder instance.

# Available options

Option name	Default value	Description
`separatedValues`	`true`	If `true` - create placeholder for each string value and put it value to result `values`. If `false` - put string values into sql query without placeholder (potential threat of sql injection).
`namedValues`	`true`	If `true` - create hash of values with placeholders p1, p2, ... If `false` - put all values into array. Option is used if `separatedValues = true`.
`valuesPrefix`	`'$'`	Prefix for values placeholders Option is used if `namedValues = true`.
`dialect`	`'base'`	Active dialect. See setDialect for dialects list.
`wrappedIdentifiers`	`true`	If `true` - wrap all identifiers with dialect wrapper (name -> "name").
`indexedValues`	`true`	If `true` - uses auto-generated id for values placeholders after the value prefix

# setDialect(name)

Set active dialect, name can has value 'base', 'mssql', 'mysql', 'postgresql' or 'sqlite'.

# Queries

# type: 'select'

[ with | withRecursive ]
[ distinct ]
[ fields ]
table | query | select | expression
[ alias ]
[ join ]
[ condition ]
[ group ]
[ sort ]
[ limit ]
[ offset ]

Example:

var sql = jsonSql.build({
    type: 'select',
    fields: ['a', 'b']
    table: 'table'
});

sql.query
// select "a", "b" from "table";

1
2
3
4
5
6
7
8

If fields is not specified in query, result fields is * (all columns of the selected rows).

Example:

var sql = jsonSql.build({
    type: 'select',
    table: 'table'
});

sql.query
// select * from "table";

1
2
3
4
5
6
7

# type: 'insert'

[ with | withRecursive ]
[ or ]
table
values
[ condition ]
[ returning ]

Example:

var sql = jsonSql.build({
    type: 'insert',
    table: 'table',
    values: {a: 4}
});

sql.query
// insert into "table" ("a") values (4);

1
2
3
4
5
6
7
8

# type: 'update'

[ with | withRecursive ]
[ or ]
table
modifier
[ condition ]
[ returning ]

Example:

var sql = jsonSql.build({
    type: 'update',
    table: 'table',
    modifier: {a: 5}
});

sql.query
// update "table" set a = 5;

1
2
3
4
5
6
7
8

# type: 'remove'

[ with | withRecursive ]
table
[ condition ]
[ returning ]

Example:

var sql = jsonSql.build({
    type: 'remove',
    table: 'table'
});

sql.query
// delete from "table";

1
2
3
4
5
6
7

# type: 'union' | 'intersect' | 'except'

[ all ]
[ with | withRecursive ]
queries
[ sort ]
[ limit ]
[ offset ]

type: 'union' example:

var sql = jsonSql.build({
    type: 'union',
    queries: [
        {type: 'select', table: 'table1'},
        {type: 'select', table: 'table2'}
    ]
});

sql.query
// (select * from "table1") union (select * from "table2");

1
2
3
4
5
6
7
8
9
10

type: 'intersect' example:

var sql = jsonSql.build({
    type: 'intersect',
    queries: [
        {type: 'select', table: 'table1'},
        {type: 'select', table: 'table2'}
    ]
});

sql.query
// (select * from "table1") intersect (select * from "table2");

1
2
3
4
5
6
7
8
9
10

type: 'except' example:

var sql = jsonSql.build({
    type: 'except',
    queries: [
        {type: 'select', table: 'table1'},
        {type: 'select', table: 'table2'}
    ]
});

sql.query
// (select * from "table1") except (select * from "table2");

1
2
3
4
5
6
7
8
9
10

# Blocks

Blocks are small chunks of query.

# with, withRecursive

Should be an array or an object.

If value is an array, each item of array should be an object and should conform the scheme:

name
[ fields ]
query | select | expression

Example:

var sql = jsonSql.build({
    'with': [{
        name: 'table',
        select: {table: 'withTable'}
    }],
    table: 'table'
});

sql.query
// with "table" as (select * from "withTable") select * from "table";

1
2
3
4
5
6
7
8
9
10

If value is an object, keys of object interpret as names and each value should be an object and should conform the scheme:

[ name ]
[ fields ]
query | select | expression

Example:

var sql = jsonSql.build({
    'with': {
        table: {
            select: {table: 'withTable'}
        }
    },
    table: 'table'
});

sql.query
// with "table" as (select * from "withTable") select * from "table";

1
2
3
4
5
6
7
8
9
10
11

# distinct

Should be a boolean:

distinct: true

Example:

var sql = jsonSql.build({
    distinct: true,
    table: 'table'
});

sql.query
// select distinct * from "table";

1
2
3
4
5
6
7

# fields

Should be an array or an object.

If value is an array, each item interprets as term block.

Example:

var sql = jsonSql.build({
    fields: [
        'a',
        {b: 'c'},
        {table: 'd', name: 'e', alias: 'f'},
        ['g']
    ],
    table: 'table'
});

sql.query
// select "a", "b" as "c", "d"."e" as "f", "g" from "table";

1
2
3
4
5
6
7
8
9
10
11
12

If value is an object, keys of object interpret as field names and each value should be an object and should conform the scheme:

[ name ]
[ table ]
[ cast ]
[ alias ]

Example:

var sql = jsonSql.build({
    fields: {
        a: 'b',
        d: {table: 'c', alias: 'e'}
    },
    table: 'table'
});

sql.query
// select "a" as "b", "c"."d" as "e" from "table";

1
2
3
4
5
6
7
8
9
10

# term

Should be:

a string - interprets as field name;
another simple type or an array - interprets as value;
an object - should conform the scheme:

query | select | field | value | func | expression
[ cast ]
[ alias ]

# field

Should be a string or an object.

If value is a string:

field: 'fieldName'

Example:

var sql = jsonSql.build({
    fields: [{field: 'a'}],
    table: 'table'
});

sql.query
// select "a" from "table";

1
2
3
4
5
6
7

If value is an object it should conform the scheme:

name
[ table ]

Example:

var sql = jsonSql.build({
    fields: [{field: {name: 'a', table: 'table'}}],
    table: 'table'
});

sql.query
// select "table"."a" from "table";

1
2
3
4
5
6
7

# value

Can have any type.

Example:

var sql = jsonSql.build({
    fields: [
        {value: 5},
        {value: 'test'}
    ],
    table: 'table'
});

sql.query
// select 5, $p1 from "table";

sql.values
// {p1: 'test'}

1
2
3
4
5
6
7
8
9
10
11
12
13

# table

Should be a string:

table: 'tableName'

Example:

var sql = jsonSql.build({
    table: 'table'
});

sql.query
// select * from "table";

1
2
3
4
5
6

# query

Should be an object. Value interprets as sub-query and process recursively with build(query) method.

Example:

var sql = jsonSql.build({
    query: {type: 'select', table: 'table'}
});

sql.query
// select * from (select * from "table");

1
2
3
4
5
6

# select

Should be an object. Value interprets as sub-select and process recursively with build(query) method.

Example:

var sql = jsonSql.build({
    select: {table: 'table'}
});

sql.query
// select * from (select * from "table");

1
2
3
4
5
6

# func

Should be a string or an object.

If value is a string:

func: 'random'

Example:

var sql = jsonSql.build({
    fields: [{func: 'random'}],
    table: 'table'
});

sql.query
// select random() from "table";

1
2
3
4
5
6
7

If value is an object it should conform the scheme:

name
[ args ]

where name is a string name of function, args is an array that contains it arguments.

Example:

var sql = jsonSql.build({
    fields: [{
        func: {
            name: 'sum',
            args: [{field: 'a'}]
        }
    }],
    table: 'table'
});

sql.query
// select sum("a") from table;

1
2
3
4
5
6
7
8
9
10
11
12

# expression

Should be a string or an object.

If value is a string:

expression: 'random()'

Example:

var sql = jsonSql.build({
    expression: 'generate_series(2, 4)'
});

sql.query
// select * from generate_series(2, 4);

1
2
3
4
5
6

If value is an object it should conform the scheme:

pattern
[ values ]

where pattern is a string pattern with placeholders {placeholderName}, values is a hash that contains values for each placeholderName.

Example:

var sql = jsonSql.build({
    expression: {
        pattern: 'generate_series({start}, {stop})',
        values: {start: 2, stop: 4}
    }
});

sql.query
// select * from generate_series(2, 4);

1
2
3
4
5
6
7
8
9

# alias

Should be a string or an object.

If value is a string:

alias: 'aliasName'

Example:

var sql = jsonSql.build({
    table: 'table',
    alias: 'alias'
});

sql.query
// select * from "table" as "alias";

1
2
3
4
5
6
7

If value is an object it should conform the scheme:

name
[ columns ]

Example:

var sql = jsonSql.build({
    table: 'table',
    alias: {name: 'alias'}
});

sql.query
// select * from "table" as "alias";

1
2
3
4
5
6
7

# join

Should be an array or an object.

If value is an array, each item of array should be an object and should conform the scheme:

[ type ]
table | query | select | expression
[ alias ]
[ on ]

Example:

var sql = jsonSql.build({
    table: 'table',
    join: [{
        type: 'right',
        table: 'joinTable',
        on: {'table.a': 'joinTable.b'}
    }]
});

sql.query
// select * from "table" right join "joinTable" on "table"."a" = "joinTable"."b";

1
2
3
4
5
6
7
8
9
10
11

If value is an object, keys of object interpret as table names and each value should be an object and should conform the scheme:

[ type ]
[ table | query | select | expression ]
[ alias ]
[ on ]

Example:

var sql = jsonSql.build({
    table: 'table',
    join: {
        joinTable: {
            type: 'inner',
            on: {'table.a': 'joinTable.b'}
        }
    }]
});

sql.query
// select * from "table" inner join "joinTable" on "table"."a" = "joinTable"."b";

1
2
3
4
5
6
7
8
9
10
11
12

Join with sub-select example:

var sql = jsonSql.build({
    table: 'table',
    join: [{
        select: {table: 'joinTable'},
        alias: 'joinTable',
        on: {'table.a': 'joinTable.b'}
    }]
});

sql.query
// select * from "table" join (select * from "joinTable") as "joinTable" on "table"."a" = "joinTable"."b";

1
2
3
4
5
6
7
8
9
10
11

# condition

Should be an array or an object.

array example:

var sql = jsonSql.build({
    table: 'table',
    condition: [
        {a: {$gt: 1}},
        {b: {$lt: 10}}
    ]
});

sql.query
// select * from "table" where "a" > 1 and "b" < 10;

1
2
3
4
5
6
7
8
9
10

object example:

var sql = jsonSql.build({
    table: 'table',
    condition: {
        a: {$gt: 1},
        b: {$lt: 10}
    }
});

sql.query
// select * from "table" where "a" > 1 and "b" < 10;

1
2
3
4
5
6
7
8
9
10

# group

Should be a string or an array.

If value is a string:

group: 'fieldName'

Example:

var sql = jsonSql.build({
    table: 'table',
    group: 'a'
});

sql.query
// select * from "table" group by "a";

1
2
3
4
5
6
7

If value is an array:

group: ['fieldName1', 'fieldName2']

Example:

var sql = jsonSql.build({
    table: 'table',
    group: ['a', 'b']
});

sql.query
// select * from "table" group by "a", "b";

1
2
3
4
5
6
7

# sort

Should be a string, an array or an object.

If value is a string:

sort: 'fieldName'

Example:

var sql = jsonSql.build({
    table: 'table',
    sort: 'a'
});

sql.query
// select * from "table" order by "a";

1
2
3
4
5
6
7

If value is an array:

sort: ['fieldName1', 'fieldName2']

Example:

var sql = jsonSql.build({
    table: 'table',
    sort: ['a', 'b']
});

sql.query
// select * from "table" order by "a", "b";

1
2
3
4
5
6
7

If value is an object:

sort: {
    fieldName1: 1,
    fieldName2: -1
}

1
2
3
4

Example:

var sql = jsonSql.build({
    table: 'table',
    sort: {a: 1, b: -1}
});

sql.query
// select * from "table" order by "a" asc, "b" desc;

1
2
3
4
5
6
7

# limit

Should be a number.

limit: limitValue

Example:

var sql = jsonSql.build({
    table: 'table',
    limit: 5
});

sql.query
// select * from "table" limit 5;

1
2
3
4
5
6
7

# offset

Should be a number.

offset: offsetValue

Example:

var sql = jsonSql.build({
    table: 'table',
    offset: 5
});

sql.query
// select * from "table" offset 5;

1
2
3
4
5
6
7

# or

Should be a string.

Available values: 'rollback', 'abort', 'replace', 'fail', 'ignore'.

or: 'orValue'

Example:

var sql = jsonSql.build({
    type: 'insert',
    or: 'replace',
    table: 'table',
    values: {a: 5}
});

sql.query
// insert or replace into "table" ("a") values (5);

1
2
3
4
5
6
7
8
9

# values

Should be an array or an object.

If value is an array, each item should be an object and interprets as single inserted row where keys are field names and corresponding values are field values.

Example:

var sql = jsonSql.build({
    type: 'insert',
    table: 'table',
    values: [
        {a: 5, b: 'text1'},
        {a: 6, b: 'text2'}
    ]
});

sql.query
// insert into "table" ("a", "b") values (5, $p1), (6, $p2);

sql.values
// {p1: 'text1', p2: 'text2'}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

If value is an object, it interprets as single inserted row where keys are field names and corresponding values are field values.

Example:

var sql = jsonSql.build({
    type: 'insert',
    table: 'table',
    values: {a: 5, b: 'text'}
});

sql.query
// insert into "table" ("a", "b") values (5, $p1);

sql.values
// {p1: 'text'}

1
2
3
4
5
6
7
8
9
10
11

Also you can specify fields array. If there no key in value object it value is null.

Example:

var sql = jsonSql.build({
    type: 'insert',
    table: 'table',
    fields: ['a', 'b', 'c'],
    values: {c: 'text', b: 5}
});

sql.query
// insert into "table" ("a", "b", "c") values (null, 5, $p1);

sql.values
// {p1: 'text'}

1
2
3
4
5
6
7
8
9
10
11
12

# modifier

Should be an object.

You can specify modifier operator. Available operators: $set, $inc, $dec, $mul, $div, $default.

Example:

var sql = jsonSql.build({
    type: 'update',
    table: 'table',
    modifier: {
        $set: {a: 5},
        $default: {b: true},
        $inc: {c: 10}
    }
});

sql.query
// update "table" set "a" = 5, "b" = default, "c" = "c" + 10;

1
2
3
4
5
6
7
8
9
10
11
12

If modifier operator is not specified it uses default operator $set.

Example:

var sql = jsonSql.build({
    type: 'update',
    table: 'table',
    modifier: {a: 5}
});

sql.query
// update "table" set "a" = 5;

1
2
3
4
5
6
7
8

# returning

Format is similar to fields block.

Example:

var sql = jsonSql.build({
    type: 'insert',
    table: 'table',
    values: {a: 5},
    returning: ['a']
});

sql.query
// insert into "table" ("a") values (5) returning "a";

1
2
3
4
5
6
7
8
9

# all

Should be a boolean.

Example:

var sql = jsonSql.build({
    type: 'union',
    all: true,
    queries: [
        {type: 'select', table: 'table1'},
        {type: 'select', table: 'table2'}
    ]
});

sql.query
// (select * from "table1") union all (select * from "table2");

1
2
3
4
5
6
7
8
9
10
11

# queries

Should be an array with minimum 2 items. Each item interprets as sub-query and process recursively with build(query) method.

Example:

var sql = jsonSql.build({
    type: 'union',
    queries: [
        {type: 'select', table: 'table1'},
        {type: 'select', table: 'table2'}
    ]
});

sql.query
// (select * from "table1") union (select * from "table2");

1
2
3
4
5
6
7
8
9
10

# Condition operators

TODO: write this section

#JSON #NodeJs #SQL

上次更新: 2024/12/23, 21:39:55

← Linux 系统下通过 Let‘s Encrypt 生成免费 https 证书的步骤 CentOS7安装与卸载anaconda3基础步骤→