node package manager
Stop writing boring code. Discover, share, and reuse within your team. Create a free org ¬Ľ

sql92-json

SQL92-JSON

can stringify a JSON into an SQL and viceversa parse an SQL and serialize it into a JSON

ūüöß STATUS: Right now the test suite contains many SELECT statements (and few CREATEs) that are serialized into JSON and viceversa parsed back into SQL successfully. Adding INSERT and other DMLs statements as well as DDLs is on the roadmap. The following keywords are already implemented and tested:

SELECT COUNT FROM GROUP BY ORDER BY UNION UNION ALL AND OR JOIN AS HAVING LEFT JOIN WHERE BETWEEN MIN MAX AVG SUM

Installation | API | Examples | Recipes | References | License

NPM version Build Status Badge size Dependency Status Coverage Status JavaScript Style Guide

Installation

Using npm

With npm do

npm install sql92-json

Using a CDN

Adding this to your HTML page

<script src="https://unpkg.com/sql92-json/dist/sql92-json.min.js"></script>

API

stringify

Convert a JSON to SQL

var json2sql = require('sql92-json').stringify
 
console.log(json2sql({ SELECT: ['*'], FROM: ['revenue'] }))
//
// SELECT *
// FROM revenue
//

parse

Convert an SQL to JSON

var sql2json = require('sql92-json').parse
 
console.log(sql2json('SELECT * FROM revenue')
// {
//   SELECT: ['*'],
//   FROM: ['revenue']
// }

Recipes

Examples

See examples folder where every .json file has its homonym .sql.

See for example the following example JSON and its corresponding SQL.

{
  "SELECT": [
    { "COUNT": "*", "AS": "num" }
  ],
  "FROM": [
    {
      "SELECT": ["*"],
      "FROM": ["mytable"],
      "WHERE": [
        "yyyymmdd", { "=": 20170101 },
        { "AND": [ "country", { "IN": ["IT", "US"] } ] },
        { "AND": [
          "categoryid", { "BETWEEN": [100, 200] },
          { "OR": [ "productname", { "!=": "'icecream'" } ] }
        ] }
      ]
    }
  ]
}
SELECT COUNT(*) AS num
FROM (
    SELECT *
    FROM mytable
    WHERE yyyymmdd = 20170101
        AND country IN ( 'IT', 'US' )
        AND (
            categoryid BETWEEN 100 AND 200
            OR productname != 'icecream'
        )
)

References

sql1992.txt was downloaded from here.

License

MIT