sql-json-generator

v0.15.1

Published

3 years ago

Module to create SQL queries from JSON objects

Downloads

383

0High
0Medium
0Low

manu2verdun

SQL JSON NODE

sql-json-generator

Generate SQL statements from JSON objects

Create reusable json objects to quickly build SQL statments

Initially designed for MySQL syntax, see Other Databases section

1. Introduction

This module was created because I wanted to create REST services for my existing projects, using node, building also Angular Clients. Even if it is easier to use noSQL database as Mongo with node, all my databases are running with mySQL and I do not want to change or migrate them.

Using the mySQL connector with node is quite easy, but the code for building the SQL statement from scratch could be painful and eventually becomes unreadable the more the query becomes complex.

The input JSON object can be build using other reusable JSON objects (for example a constant with nested JOIN statements, that are used in various queries.

It offers an easy way to build simple or complex queries without having to write the SQL syntax, just writing down the query logic.

The module syntax is loosely based on MongoDB querying syntax.

2. Table of Contents

1. Introduction
1. Table of Contents
1. Install
1. Wiki
1. Change log
1. API
- 6.1. SELECT
- 6.2. INSERT
- 6.3. UPDATE
- 6.4. DELETE
1. Formating queryData
- 7.1. SELECT
  - 7.1.1. $from, $fields, $field: basic SELECT FROM query
  - 7.1.2. $field
  - 7.1.3. $as
  - 7.1.4. $dateFormat
  - 7.1.5. $avg $count $min $max $sum
  - 7.1.6. $upper $lower
  - 7.1.7. $groupConcat
  - 7.1.8. $function
  - 7.1.9. $where
- 7.2. JOINS : $inner, $left, $right, $full
- 7.3. $where
  - 7.3.1. Simple Column comparison
  - 7.3.2. Logical Operators: $and and $or
  - 7.3.3. Complex Colums comparison
  - 7.3.4. Comparison Operators
  - 7.3.5. $in
- 7.4. $limit
- 7.5. $order
  - 7.5.1. $desc
- 7.6 $group
- 7.7 $having
- 7.8 $raw
1. mySQL Features
- 8.1. Escaping strings
- 8.2. $sqlCalcFoundRows
1. Other Databases
1. Debugging
1. Testing

3. Install

Install with npm install sql-json-generator

var SQLGenerator = require('sql-json-generator');
var sqlGenerator = new SQLGenerator();

4. Wiki

Find mode complex queries examples on the project wiki

5. Change log

Change Log

All notable changes to this project will be documented in this file.

6. API

6.1. SELECT

sqlGenerator.select( queryData )

The first parameter contains the data used to produce the SQL query. The function returns a string with the SQL. In case of error, will return null

example:

sqlParams = {
    $from : 'table1',
    $fields : [
        'column_a',
        'column_b',
        'column_c'
    ],
    $where : [{
        column_d: 1
    }]
}

sqlGenerator.select( sqlParams);

will return:

SELECT `column_a`, `column_b`, `column_c` FROM `table1` WHERE `table1`.`column_d` = '1'

6.2. INSERT

sqlGenerator.insert( queryData )

The first parameter contains the data used to produce the SQL query. The function returns a string with the SQL. In case of error, will return null

example:

sqlParams = {
    $insert: 'mytable',
    $values : {
        column_a: 1,
        column_b: 1
    }
}

sqlGenerator.insert( sqlParams);

will return:

INSERT INTO `mytable` (`column_a`,`column_b`) VALUES ('1','1')

6.3. UPDATE

sqlGenerator.update( queryData )

The first parameter contains the data used to produce the SQL query. The function returns a string with the SQL. In case of error, will return null

example:

sqlParams = {
    $update: 'mytable',
    $set : {
        column_b: 1
    },
    $where: [{
        column_a: 1
    }]
}

sqlGenerator.update( sqlParams);

will return:

UPDATE  `mytable`  SET `column_b` = '1' WHERE `column_a` = '1'

$where parameter is optional

6.4. DELETE

.delete( queryData )

The first parameter contains the data used to produce the SQL query. The function returns a string with the SQL. In case of error, will return null

example:

sqlParams = {
    $delete: 'mytable',
    $where: [{
        column_a: 1
    }]
}

sqlGenerator.delete( sqlParams);

will return:

UPDATE  `mytable`  SET `column_b` = '1' WHERE `column_a` = '1'

$where parameter is optional

7. Formating queryData

7.1. SELECT

7.1.1. $from, $fields, $field: basic SELECT FROM query

Columns to be displayed in a SELECT statement are elements of an array. It can be just an array of columns names

example:

{
    $from : 'table1',
    $fields : [
        'column_a',
        'column_b'
    ]
}

will return:

SELECT `table1`.`column_a`, `table1`.`column_b` FROM `table1`

To apply extra SQL formats to the colums (such as AS, SUM) the column must be wrapped in an object:

example:

{
    $from : 'table1',
    $fields : [
        {
            $field: 'column_a'
        },
        {
            $field: 'column_b'
        },
    ]
}

will return:

SELECT `table1`.`column_a`, `table1`.`column_b` FROM `table1`

7.1.2. $field

$field : column_name

$field must be used within an object.

table name is inherited from the parent table object ( $from, $inner ... )

7.1.3. $as

$as : alias name

$as must be used within an object, with a $field property.

example:

{
    $from : 'table1',
    $fields : [
        {
            $field: 'column_a',
            $as: 'column_a_as'
        },
        'column_b'
    ]
}

will return:

SELECT `table1`.`column_a` AS column_a_as, `table1`.`column_b` FROM `table1`

7.1.4. $dateFormat

$dateFormat : output date format (see SQL doc).

$dateFormat must be used within an object, with a $field property.

example:

{
    $from : 'table1',
    $fields : [
        {
            $field: 'column_a',
            $dateFormat : '%Y-%m-%d',
            $as: 'column_date'
        }
    ]
}

will return:

SELECT DATE_FORMAT(`table1`.`column_a`,'%Y-%m-%d') AS column_date FROM `table1`

7.1.5. $avg $count $min $max $sum

count : 1

example:

{
    $from : 'table1',
    $fields : [
        {
            $field: 'column_a',
            $count : 1,
            $as: 'column_date'
        }
    ]
}

will return:

SELECT COUNT(`table1`.`column_a`) AS column_date FROM `table1`

7.1.5. $avg $count $min $max $sum

$count : 1

example:

{
    $from : 'table1',
    $fields : [
        {
            $field: 'column_a',
            $count : 1,
            $as: 'column_date'
        }
    ]
}

will return:

SELECT COUNT(`table1`.`column_a`) AS column_date FROM `table1`

7.1.6. $upper $lower

$upper : 1

example:

{
    $from : 'table1',
    $fields : [
        {
            $field: 'column_a',
            $upper : 1,
            $as: 'column_date'
        }
    ]
}

will return:

SELECT UPPER(`table1`.`column_a`) AS column_date FROM `table1`

7.1.7. $groupConcat

$groupConcat : 1

example:

{
    $from : 'table1',
    $fields : [
        {
            $field: 'column_a',
            $groupConcat : 1,
            $as: 'column_date'
        }
    ]
}

will return:

SELECT GROUP_CONCAT(`table1`.`column_a`) AS column_date FROM `table1`

7.1.8. $function

$function : 'acos'

example:

{
    $from : 'table1',
    $fields : [
        {
            $field: 'column_a',
            $function : 'acos'
        }
    ]
}

will return:

SELECT ACOS(`table1`.`column_a`) FROM `table1`

7.1.9. $where

example:

{
    $from : 'table1',
    $fields : [
        'column_a',
        'column_b'
    ],
    $where : [{
        'column_c' : 1
    }]
}

will return:

SELECT `table1`.`column_a`, `table1`.`column_b` FROM `table1` WHERE `table1`.`column_c` = 1

7.2. JOINS : $inner, $left, $right, $full

JOIN tables are placed as an element inside the $fields array of the parent table.

JOIN tables can be nested

| JSON | SQL | |----------|:-------------:| | $inner | INNER JOIN | | $left | LEFT JOIN | | $right | RIGHT JOIN | | $full | LEFT JOIN |

JOIN object must have one $using or one $on parameter JOIN object must have one $fields parameter

example:

{
    $from : 'table1',
    $fields : [
        'column1a',
        'column1b',
        {
            $inner : 'table2',
            $using : 'column2a',
            $fields : [
                'column2a',
                'column2b',
            ]
        }
    ]
}

will return:

SELECT `table1`.`column1a`, `table1`.`column1b`, `table2`.`column2a`, `table2`.`column2b` FROM `table1` INNER JOIN `table2` USING(`column2a`)

example:

{
    $from : 'table1',
    $fields : [
        'column1a',
        'column1b',
        {
            $inner : 'table2',
            $on: {
                $parent : 'table2ForeignKey',
                $child : 'primaryKey'
            }
            $fields : [
                'column2a',
                'column2b',
            ]
        }
    ]
}

will return:

SELECT `table1`.`column1a`, `table1`.`column1b`, `table2`.`column2a`, `table2`.`column2b` FROM `table1` INNER JOIN `table2` ON `table1`.`table2ForeignKey` = `table2`.`primaryKey`

$on can be an array

example:

{
    $from : 'table1',
    $fields : [
        'column1a',
        'column1b',
        {
            $inner : 'table2',
            $on: [{
                $parent : 'table2ForeignKeyA',
                $child : 'primaryKeyA'
            },
            {
                $parent : 'table2ForeignKeyB',
                $child : 'primaryKeyB'
                        }]
            $fields : [
                'column2a',
                'column2b',
            ]
        }
    ]
}

will return:

SELECT `table1`.`column1a`, `table1`.`column1b`, `table2`.`column2a`, `table2`.`column2b` FROM `table1` INNER JOIN `table2` ON ( `table1`.`table2ForeignKeyA` = `table2`.`primaryKeyA` AND `table1`.`table2ForeignKeyB` = `table2`.`primaryKeyB` )

7.3. $where

$where: [{condition1}, {condition2}....]

7.3.1. Simple Column comparison

Simple column comparison, ie column equal value can be wrote using simple style

{ column_name : value }

In case of SELECT statement, the column name will be prefixed by the FROM table name

example:

[
    {column_a: 1}
]

will return:

column_a = '1'

or, for SELECT statement

table_a.column_a = '1'

7.3.2. Logical Operators: $and and $or

{ $and : [{condition1}, {condition2}... ]} , { $or : [{condition1}, {condition2}... ]}

example:

{
    $or : [
        {column_a: 1},
        {column_b: 1}
    ]
}

will return:

(column_a = '1' OR column_b = '1')

default behavior: $and

example:

[
    {column_a: 1},
    {column_b: 1},
    {column_c: 1}
]

will return:

column_a = '1' AND column_b = '1' AND column_c = '1'

7.3.3. Complex Colums comparison

{ $table: table_name, $field: field_name, comparison_operator : value}

If $table is not specified, will use the primary table name (FROM, UPDATE)

example:

[
    {
        $field: 'column_a',
        $eq : 1
    },
    {
        $table: 'table_b
        $field: 'column_b',
        $eq : 1
    }
]

will return:

table_a.column_a = '1' AND table_b.column_b = '1'

7.3.4. Comparison Operators

$gt : value

| JSON | SQL | |----------|:-------------:| | $gt | > | | $gte | >= | | $lt | < | | $lte | <= | | $eq | = | | $ne | <> | | $like | LIKE |

example:

[
    {
        $field: 'column_a',
        $gt : 1
    }
]

will return:

column_a > '1'

7.3.5. $in

example:

{
    {
        $field: 'column_a',
        $in: [ 1, 2 ]
    }
}

will return:

column_a IN ( 1 , 2 )

7.4. $limit

$limit: { $offset : offset_value , $rows : rows_to_return }

example:

{
    $limit : {
        $offset: 0,
        $rows: 10
    }
}

will return:

LIMIT 0,10

7.5. $order

$order: [ list of fields ]

example:

{
    $order : [
        'column1',
        'column2'
    ]
}

will return:

ORDER BY current_table.column1, current_table.column2

Elements of the $order array can be aliases names, previously defined, using the $as tag

example:

{
    $fields: [
        {
          $field: 'column1'
          $as : 'alias'
        }
    ]
    $order : [
        { $as : 'alias' }
    ]
}

will return:

ORDER BY current_table.column1, current_table.column2

7.5.1. $desc

to append DESC into one ORDER directive, the required field must be explicitly declared using $table and $field, or $as

example:

{
    $order : [
        {
            $table: 'table1',
            $field: 'column1',
            $desc : 1
        }
    ]
}

will return:

ORDER BY table1.column1 DESC

If not using a $table tag with $field, the current table will be used to build the command

example:

{
    $order : [
        {
            $field: 'column1',
            $desc : 1
        }
    ]
}

will return:

ORDER BY current_table.column1 DESC

7.6 $group

$group: [ list of fields ]

example:

{
    $group : [
        'column1',
        'column2'
    ]
}

will return:

GROUP BY current_table.column1, current_table.column2

$group uses the same syntaxt as $order

7.7 $having

$having: [ list of conditions ]

$having is resolved using the same rules as $where conditions

7.8 $raw

$raw: string

$raw argument is not interpreted and is put in the query without processing.

example:

{
    $having: [
        { 
            $raw: "`count` > 1"
        }
    ]
}

will return:

HAVING `count` > 1

8. mySQL Features

8.1. Escaping strings

var sqlGenerator = new SQLGenerator({escape: true});

will escape the strings values.

8.2. $sqlCalcFoundRows

$sqlCalcFoundRows: true

Will insert SQL_CALC_FOUND_ROWS just after SELECT keywords

If there is no $limit defined, the SQL_CALC_FOUND_ROWS is useless and will not be added after the SELECT.

example:

{
    $from: 'setores',
    $fields: ['id_setor', 'nome'],
    $where: [],
    $sqlCalcFoundRows: true,
    $limit: {
        $rows: 20,
        $offset: 0
    }
}

will return:

SELECT SQL_CALC_FOUND_ROWS `setores`.`id_setor`, `setores`.`nome` FROM `setores` LIMIT 0,20

otherdb-01

9. Other Databases

Using PostgreSQL syntax:

var sqlGenerator = new SQLGenerator({pgSQL: true});

LIMITED SUPORT, based on my basic pgSQL needs, feel free to report all issues

Using prestoDB syntax:

var sqlGenerator = new SQLGenerator({prestoDB: true});

BASIC SUPORT, based on @martin-kieliszek contributions, feel free to report all issues

10. Debugging

Display all generator steps in the console:

var sqlGenerator = new SQLGenerator({debug: true});

Display generated SQL in the console:

var sqlGenerator = new SQLGenerator({showSQL: true});

11. Testing

Using Mocha & Chai, test most common and uncommon queries

npm test

Published

Vulnerabilities

Links

Maintainers

Keywords

Readme

sql-json-generator

1. Introduction

2. Table of Contents

3. Install

4. Wiki

5. Change log

6. API

6.1. SELECT

6.2. INSERT

6.3. UPDATE

6.4. DELETE

7. Formating queryData

7.1. SELECT

7.1.1. $from, $fields, $field: basic SELECT FROM query

7.1.2. $field

7.1.3. $as

7.1.4. $dateFormat

7.1.5. $avg $count $min $max $sum

7.1.5. $avg $count $min $max $sum

7.1.6. $upper $lower

7.1.7. $groupConcat

7.1.8. $function

7.1.9. $where

7.2. JOINS : $inner, $left, $right, $full

7.3. $where

7.3.1. Simple Column comparison

7.3.2. Logical Operators: $and and $or

default behavior: $and

7.3.3. Complex Colums comparison

7.3.4. Comparison Operators

7.3.5. $in

7.4. $limit

7.5. $order

7.5.1. $desc

7.6 $group

7.7 $having

7.8 $raw

8. mySQL Features

8.1. Escaping strings

8.2. $sqlCalcFoundRows

9. Other Databases

10. Debugging

11. Testing