Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList, columnList and convert it back to SQL.
- 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
From npmjs
npm install node-sql-parser --save or yarn add node-sql-parsernpm install @taozhi8833998/node-sql-parser --registry=https://npm.pkg.github.com/Import the JS file in your page:
// support all database parser, but file size is about 750K <script src="https://unpkg.com/node-sql-parser/umd/index.umd.js"></script> // or you can import specified database parser only, it's about 150K <script src="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script> <script src="https://unpkg.com/node-sql-parser/umd/postgresql.umd.js"></script>NodeSQLParserobject is onwindow
<!DOCTYPE html> <html lang="en" > <head> <title>node-sql-parser</title> <meta charset="utf-8" /> </head> <body> <p><em>Check console to see the output</em></p> <script src="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script> <script> window.onload = function () { // Example parser const parser = new NodeSQLParser.Parser() const ast = parser.astify("select id, name from students where age < 18") console.log(ast) const sql = parser.sqlify(ast) console.log(sql) } </script> </body> </html>- Athena
- BigQuery
- DB2
- Hive
- MariaDB
- MySQL
- PostgresQL
- Redshift
- Sqlite
- TransactSQL
- FlinkSQL
- Snowflake(alpha)
- Noql
- New issue could be made for other new database.
// 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);astforSELECT * FROM t
{ "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 }const { Parser } = require('node-sql-parser'); const parser = new Parser(); const ast = parser.astify('SELECT * FROM t', { parseOptions: { includeLocations: true } }); console.log(ast);astforSELECT * FROM twith thelocproperty indicating locations and ranges
{ "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 } } }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`There two ways to parser the specified database.
import Parser from the specified database path node-sql-parser/build/{database}
// 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.
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"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 table list that the sql visited
- the format is {type}::{dbName}::{tableName} // type could be select, update, delete or insert
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"]- get the column list that the sql visited
- the format is {type}::{tableName}::{columnName} // type could be select, update, delete or insert
- for
select *,deleteandinsert into tableName values()without specified columns, the.*column authority regex is required
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 table authority
whiteListCheckfunction check ontablemode andMySQLdatabase by default
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
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 undefinedThis project is inspired by the SQL parser flora-sql-parser module.
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.
Donate money by paypal to my account taozhi8833998@163.com
If you have made a donation, you can leave your name and email in the issue, your name will be written to the donation list.