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<scriptsrc="https://unpkg.com/node-sql-parser/umd/index.umd.js"></script>// or you can import specified database parser only, it's about 150K<scriptsrc="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script><scriptsrc="https://unpkg.com/node-sql-parser/umd/postgresql.umd.js"></script>NodeSQLParserobject is onwindow
<!DOCTYPE html><htmllang="en" ><head><title>node-sql-parser</title><metacharset="utf-8" /></head><body><p><em>Check console to see the output</em></p><scriptsrc="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script><script>window.onload=function(){// Example parserconstparser=newNodeSQLParser.Parser()constast=parser.astify("select id, name from students where age < 18")console.log(ast)constsql=parser.sqlify(ast)console.log(sql)}</script></body></html>- BigQuery
- DB2
- Hive
- MariaDB
- MySQL
- PostgresQL
- Sqlite
- TransactSQL
- FlinkSQL
- Snowflake(alpha)
- Noql
- New issue could be made for other new database.
// import Parser for all databasesconst{ Parser }=require('node-sql-parser');constparser=newParser();constast=parser.astify('SELECT * FROM t');// mysql sql grammer parsed by defaultconsole.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
}constopt={database: 'MySQL'// MySQL is the default database}// import mysql parser onlyconst{ Parser }=require('node-sql-parser');constparser=newParser()// opt is optionalconstast=parser.astify('SELECT * FROM t',opt);constsql=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 onlyconst{ Parser }=require('node-sql-parser/build/transactsql')constparser=newParser()constsql=`SELECT id FROM test AS result`constast=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.
constopt={database: 'Postgresql'}// import all databases parserconst{ Parser }=require('node-sql-parser')constparser=newParser()// pass the opt config to the corresponding methodsconstast=parser.astify('SELECT * FROM t',opt)constsql=parser.sqlify(ast,opt)console.log(sql);// SELECT * FROM "t"constopt={database: 'MariaDB'// MySQL is the default database}const{ Parser }=require('node-sql-parser/build/mariadb');constparser=newParser()// opt is optionalconst{ 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
constopt={database: 'MySQL'}const{ Parser }=require('node-sql-parser/build/mysql');constparser=newParser();// opt is optionalconsttableList=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
constopt={database: 'MySQL'}const{ Parser }=require('node-sql-parser/build/mysql');constparser=newParser();// opt is optionalconstcolumnList=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');constparser=newParser();constsql='UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)'constwhiteTableList=['(select|update)::(.*)::(a|b)']// array that contain multiple authoritiesconstopt={database: 'MySQL',type: 'table',}// opt is optionalparser.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');constparser=newParser();constsql='UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)'constwhiteColumnList=['select::null::name','update::a::id']// array that contain multiple authoritiesconstopt={database: 'MySQL',type: 'column',}// opt is optionalparser.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.
A full list of third-party npm packages and their licenses is maintained in third_party_licenses/THIRD_PARTY_LICENSES.md. To regenerate it after dependency changes, run:
npx github:heavyai/js-license-listThis requires node_modules to be installed (npm install). The script is maintained in the heavyai/js-license-list repo.
Every third-party module from npm that gets includes in the final, distributed bundle has its license verified and license text (if provided) or license type shipped in licenses.txt with the bundle. Licenses must be in the pre-approved list of permissive open-source licenses. If it's necessary to override a license for a module because it's missing or improperly tagged in its package.json, add an entry in license-overrides.json.
License descriptions and public license URLs are maintained in licenses.json as well, but they are not verified and might not be up to date.
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.
Warning
Do not report security vulnerabilities through public GitHub issues!
NVIDIA takes security seriously. If you discover a vulnerability in node-sql-parser, DO NOT open a public issue. Use one of the private reporting channels described in SECURITY.md.
Join the HeavyAI GitHub Discussions to ask questions, share feedback, and report issues. HeavyAI maintainers review issues, discussions, and pull requests on a best effort basis without guaranteed response timelines.
Apache 2.0. See LICENSE.