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