forked fromtaozhi8833998/node-sql-parser
- Notifications
You must be signed in to change notification settings - Fork0
Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList and convert it back to SQL
License
NotificationsYou must be signed in to change notification settings
heavyai/node-sql-parser
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
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
Fromnpmjs
npm install node-sql-parser --saveoryarn add node-sql-parser
npm 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 pathnode-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 undefined
This project is inspired by the SQL parserflora-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 bypaypal to my accounttaozhi8833998@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.
About
Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList and convert it back to SQL
Resources
License
Uh oh!
There was an error while loading.Please reload this page.
Stars
Watchers
Forks
Packages0
No packages published
Languages
- PEG.js68.7%
- JavaScript28.5%
- TypeScript2.8%