zdp-sql-generator v1.3.0

ZDP SQL Generator

ZDP SQL Generator is a library for SQL query generator by parsing the structured JSON.

Installation

Use the npm package to install zdp-sql-generator library

  npm install zdp-sql-generator

Usage

Simply import the library functions

  const  {getSql, jsonStruct}  = require('zdp-sql-generator');

for to get the JSON struct use jsonStruct

for to the SQL from JSON use getSql function it takes the JSON object as parameter like

  let jsonObj = jsonStruct();
  jsonObj.mainTable = "table1";
  sql = getSql(jsonObj)
  console.log(sql)
  
  Output:
  SELECT * FROM table1

JSON Structure and Componments

The default JSON looks like

  {
    mainTable: '',
    selector: [],
    joinClauses: [ [], [], [] ],
    whereClauses: [ [], [], [], [] ],
    groupBy: { column: '', having: [ [], [], [], [] ] },
    orderBy: { column: '', by: '' },
    limit: { rows: '', offset: 0 }
  }

mainTable represent table name after FROM statement if you just set the mainTable value its output look this

    {   
        mainTable: 'table1'
        ...
    }
    
    Output:
    SELECT * FROM table1

selector component contains the columns that needs to select from table

    {   
        ...
        selector:[
            "table1.col1",
            "table1.col2"
        ]
        ...
    }
    
    Output:
    SELECT table1.col1,table1.col2 FROM table1

whereClauses component contains the condition chunks

    {   
        ...
        whereClauses:[
            ["table1.col1","table1.col1"],
            ["1","2"],
            ["!=","!="],
            ["AND"]
        ]
        ...
    }
    
    Output:
    WHERE table1.col1 != 1 AND table1.col1 != 2

orderBy component contains the sorting order detail

    {   
        ...
        orderBy: { column: 'table1.col1', by: 'DESC' }
        ...
    }
    
    Output:
    ORDER BY table1.col1 DESC

limit component contains the limit details of records

    {   
        ...
        limit: { rows: 5, offset: 2 }
    }
    
    Output:
    LIMIT 5 OFFSET 2

groupBy component contains the GROUP BY details

    {   
        ...
        groupBy: { column: 'table1.col1', having: [ ['table1.col1','table1.col1'], ['!=','!='], ['NULL','0'], ['AND'] ] }
        ...
    }
    
    Output:
    GROUP BY table1.col1 HAVING table1.col1 != NULL AND table1.col1 != 0

joinClauses component contains the joins details

    {   
        ...
        joinClauses: [ 
            ["INNER JOIN","LEFT JOIN"], 
            ["table2","table3"], 
            [
              [
                ["tabl1.col1","tabl2.col1"],
                ["tabl1.col2","tabl2.col2"],
                ["=","="],
                ["AND"]
              ],
              [
                ["tabl2.col1","tabl3.col1"],
                ["tabl2.col2","tabl3.col2"],
                ["=","="],
                ["AND"]
              ],  
            ]
        ]
        ...
    }
    
    Output:
    INNER JOIN table2 ON tabl1.col1 = tabl2.col1 AND tabl1.col2 = tabl2.col2 LEFT JOIN tabl2.col1 = tabl3.col1 AND tabl2.col2 = tabl3.col2