Check console to see the output
``` ## :rocket: Usage ### Supported Database SQL Syntax - Athena - BigQuery - DB2 - Hive - MariaDB - MySQL - PostgresQL - Redshift - Sqlite - TransactSQL - [FlinkSQL](https://ci.apache.org/projects/flink/flink-docs-stable/dev/table/sql/) - Snowflake(alpha) - [Noql](https://noql.synatic.dev/) - New issue could be made for other new database. ### Create AST for SQL statement ```javascript // import Parser for all databases const { Parser } = require('node-sql-parser'); const parser = new Parser(); const ast = parser.astify('SELECT * FROM t'); // mysql sql grammer parsed by default console.log(ast); ``` - `ast` for `SELECT * FROM t` ```json { "with": null, "type": "select", "options": null, "distinct": null, "columns": "*", "from": [ { "db": null, "table": "t", "as": null } ], "where": null, "groupby": null, "having": null, "orderby": null, "limit": null } ``` #### Get node location in the AST ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); const ast = parser.astify('SELECT * FROM t', { parseOptions: { includeLocations: true } }); console.log(ast); ``` - `ast` for `SELECT * FROM t` with the `loc` property indicating locations and ranges ```json { "with": null, "type": "select", "options": null, "distinct": null, "columns": [ { "expr": { "type": "column_ref", "table": null, "column": "*" }, "as": null, "loc": { "start": { "offset": 7, "line": 1, "column": 8 }, "end": { "offset": 8, "line": 1, "column": 9 } } } ], "into": { "position": null }, "from": [ { "db": null, "table": "t", "as": null, "loc": { "start": { "offset": 14, "line": 1, "column": 15 }, "end": { "offset": 15, "line": 1, "column": 16 } } } ], "where": null, "groupby": null, "having": null, "orderby": null, "limit": null, "locking_read": null, "window": null, "loc": { "start": { "offset": 0, "line": 1, "column": 1 }, "end": { "offset": 15, "line": 1, "column": 16 } } } ``` ### Convert AST back to SQL ```javascript const opt = { database: 'MySQL' // MySQL is the default database } // import mysql parser only const { Parser } = require('node-sql-parser'); const parser = new Parser() // opt is optional const ast = parser.astify('SELECT * FROM t', opt); const sql = parser.sqlify(ast, opt); console.log(sql); // SELECT * FROM `t` ``` ### Parse specified Database There two ways to parser the specified database. import Parser from the specified database path `node-sql-parser/build/{database}` ```javascript // import transactsql parser only const { Parser } = require('node-sql-parser/build/transactsql') const parser = new Parser() const sql = `SELECT id FROM test AS result` const ast = parser.astify(sql) console.log(parser.sqlify(ast)) // SELECT [id] FROM [test] AS [result] ``` OR you can pass a options object to the parser, and specify the database property. ```javascript const opt = { database: 'Postgresql' } // import all databases parser const { Parser } = require('node-sql-parser') const parser = new Parser() // pass the opt config to the corresponding methods const ast = parser.astify('SELECT * FROM t', opt) const sql = parser.sqlify(ast, opt) console.log(sql); // SELECT * FROM "t" ``` ### Get TableList, ColumnList, Ast by `parse` function ```javascript const opt = { database: 'MariaDB' // MySQL is the default database } const { Parser } = require('node-sql-parser/build/mariadb'); const parser = new Parser() // opt is optional const { tableList, columnList, ast } = parser.parse('SELECT * FROM t', opt); ``` ### Get the SQL visited tables - get the table list that the sql visited - the format is **{type}::{dbName}::{tableName}** // type could be select, update, delete or insert ```javascript const opt = { database: 'MySQL' } const { Parser } = require('node-sql-parser/build/mysql'); const parser = new Parser(); // opt is optional const tableList = parser.tableList('SELECT * FROM t', opt); console.log(tableList); // ["select::null::t"] ``` - if the table name is prefixed with database name, the table name will be parsed as **dbName::tableName** - if the table name is prefixed with database and schema name, the table name will be parsed as **dbName.schemaName::tableName** - if the table name is prefixed with server name in TransactSQL, the table name will be parsed as **serverName.dbName.schemaName::tableName** ### Get the SQL visited columns - get the column list that the sql visited - the format is **{type}::{tableName}::{columnName}** // type could be select, update, delete or insert - for `select *`, `delete` and `insert into tableName values()` without specified columns, the `.*` column authority regex is required ```javascript const opt = { database: 'MySQL' } const { Parser } = require('node-sql-parser/build/mysql'); const parser = new Parser(); // opt is optional const columnList = parser.columnList('SELECT t.id FROM t', opt); console.log(columnList); // ["select::t::id"] ``` ### Check the SQL with Authority List - check table authority - `whiteListCheck` function check on `table` mode and `MySQL` database by default ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); const sql = 'UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)' const whiteTableList = ['(select|update)::(.*)::(a|b)'] // array that contain multiple authorities const opt = { database: 'MySQL', type: 'table', } // opt is optional parser.whiteListCheck(sql, whiteTableList, opt) // if check failed, an error would be thrown with relevant error message, if passed it would return undefined ``` - check column authority ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); const sql = 'UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)' const whiteColumnList = ['select::null::name', 'update::a::id'] // array that contain multiple authorities const opt = { database: 'MySQL', type: 'column', } // opt is optional parser.whiteListCheck(sql, whiteColumnList, opt) // if check failed, an error would be thrown with relevant error message, if passed it would return undefined ``` ## :kissing_heart: Acknowledgement This project is inspired by the SQL parser [flora-sql-parser](https://github.com/godmodelabs/flora-sql-parser) module. ## License [Apache-2.0](LICENSE) ## Buy me a Coffee If you like my project, **Star** in the corresponding project right corner. Your support is my biggest encouragement! ^_^ You can also scan the qr code below or open paypal link to donate to Author. ### Paypal Donate money by [paypal](https://www.paypal.me/taozhi8833998/5) to my account [taozhi8833998@163.com](taozhi8833998@163.com) ### AliPay(支付宝)
