SQL Parser

Parse simple SQL statements into an abstract syntax tree (AST) with the vis...

Code ParserSQL
taozhi8833998/node-sql-parsertaozhi8833998.github.io/node-sql-parser/node-sql-parser

README

Nodejs SQL Parser


Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList, columnList and convert it back to SQL.

:star: Features


- support multiple sql statement seperate by semicolon
- support select, delete, update and insert type
- support drop, truncate and rename command
- output the table and column list that the sql visited with the corresponding authority
- support various databases engine

:tada: Install


From npmjs

  1. ```bash
  2. npm install node-sql-parser --save

  4. or

  6. yarn add node-sql-parser
  7. ```

From GitHub Package Registry

  1. ```bash
  2. npm install @taozhi8833998/node-sql-parser --registry=https://npm.pkg.github.com/
  3. ```

From Browser


Import the JS file in your page:

  1. ```javascript
  2. // support all database parser, but file size is about 750K
  3. <script src="https://unpkg.com/node-sql-parser/umd/index.umd.js"></script>

  5. // or you can import specified database parser only, it's about 150K

  7. <script src="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script>

  9. <script src="https://unpkg.com/node-sql-parser/umd/postgresql.umd.js"></script>
  10. ```
- NodeSQLParser object is on window

  1. ```html
  2. <!DOCTYPE html>
  3. <html lang="en" >
  4.   <head>
  5.     <title>node-sql-parser</title>
  6.     <meta charset="utf-8" />
  7.   </head>
  8.   <body>
  9.     <p><em>Check console to see the output</em></p>
  10.     <script src="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script>
  11.     <script>
  12.       window.onload = function () {
  13.         // Example parser
  14.         const parser = new NodeSQLParser.Parser()
  15.         const ast = parser.astify("select id, name from students where age < 18")
  16.         console.log(ast)
  17.         const sql = parser.sqlify(ast)
  18.         console.log(sql)
  19.       }
  20.     </script>
  21.   </body>
  22. </html>
  23. ```

:rocket: Usage


Supported Database SQL Syntax


- Athena
- BigQuery
- DB2
- Hive
- MariaDB
- MySQL
- PostgresQL
- Redshift
- Sqlite
- TransactSQL
- FlinkSQL
- Snowflake(alpha)
- Noql
- New issue could be made for other new database.

Create AST for SQL statement


  1. ```javascript
  2. // import Parser for all databases
  3. const { Parser } = require('node-sql-parser');
  4. const parser = new Parser();
  5. const ast = parser.astify('SELECT * FROM t'); // mysql sql grammer parsed by default

  7. console.log(ast);
  8. ```

- ast for SELECT * FROM t

  1. ```json
  2. {
  3.   "with": null,
  4.   "type": "select",
  5.   "options": null,
  6.   "distinct": null,
  7.   "columns": "*",
  8.   "from": [
  9.     {
  10.       "db": null,
  11.       "table": "t",
  12.       "as": null
  13.     }
  14.   ],
  15.   "where": null,
  16.   "groupby": null,
  17.   "having": null,
  18.   "orderby": null,
  19.   "limit": null
  20. }
  21. ```

Get node location in the AST

  1. ```javascript
  2. const { Parser } = require('node-sql-parser');
  3. const parser = new Parser();
  4. const ast = parser.astify('SELECT * FROM t', { parseOptions: { includeLocations: true } });

  6. console.log(ast);
  7. ```

- ast for SELECT * FROM t with the loc property indicating locations and ranges

  1. ```json
  2. {
  3.   "with": null,
  4.   "type": "select",
  5.   "options": null,
  6.   "distinct": null,
  7.   "columns": [
  8.     {
  9.       "expr": {
  10.         "type": "column_ref",
  11.         "table": null,
  12.         "column": "*"
  13.       },
  14.       "as": null,
  15.       "loc": {
  16.         "start": {
  17.           "offset": 7,
  18.           "line": 1,
  19.           "column": 8
  20.         },
  21.         "end": {
  22.           "offset": 8,
  23.           "line": 1,
  24.           "column": 9
  25.         }
  26.       }
  27.     }
  28.   ],
  29.   "into": {
  30.     "position": null
  31.   },
  32.   "from": [
  33.     {
  34.       "db": null,
  35.       "table": "t",
  36.       "as": null,
  37.       "loc": {
  38.         "start": {
  39.           "offset": 14,
  40.           "line": 1,
  41.           "column": 15
  42.         },
  43.         "end": {
  44.           "offset": 15,
  45.           "line": 1,
  46.           "column": 16
  47.         }
  48.       }
  49.     }
  50.   ],
  51.   "where": null,
  52.   "groupby": null,
  53.   "having": null,
  54.   "orderby": null,
  55.   "limit": null,
  56.   "locking_read": null,
  57.   "window": null,
  58.   "loc": {
  59.     "start": {
  60.       "offset": 0,
  61.       "line": 1,
  62.       "column": 1
  63.     },
  64.     "end": {
  65.       "offset": 15,
  66.       "line": 1,
  67.       "column": 16
  68.     }
  69.   }
  70. }
  71. ```