Skip to content

README.md

Latest commit

History

History
397 lines (321 loc) · 11.4 KB

README.md

File metadata and controls

397 lines (321 loc) · 11.4 KB
Raw

Nodejs SQL Parser

Build StatusCodacy BadgeCoverage StatusDependenciesKnown Vulnerabilities

npm versionNPM downloads

issues

TypeScript definitions on DefinitelyTypedlicense

Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList, columnList and convert it back to SQL.

⭐ Features

  • 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

🎉 Install

From npmjs

npm install node-sql-parser --save
or
yarn add node-sql-parser

From GitHub Package Registry

npm install @taozhi8833998/node-sql-parser --registry=https://npm.pkg.github.com/

From Browser

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>
  • NodeSQLParser object is on window
<!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>

🚀 Usage

Supported Database SQL Syntax

  • Athena
  • BigQuery
  • DB2
  • Hive
  • MariaDB
  • MySQL
  • PostgresQL
  • Redshift
  • Sqlite
  • TransactSQL
  • FlinkSQL
  • Snowflake(alpha)
  • Noql
  • New issue could be made for other new database.

Create AST for SQL statement

// 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);
  • ast for SELECT * 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
}

Get node location in the AST

const{ Parser }=require('node-sql-parser');constparser=newParser();constast=parser.astify('SELECT * FROM t',{parseOptions: {includeLocations: true}});console.log(ast);
  • ast for SELECT * FROM t with the loc property 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
}
}
}

Convert AST back to SQL

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`

Parse specified Database

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"

Get TableList, ColumnList, Ast by parse function

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 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
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"]
  • 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
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 the SQL with Authority List

  • check table authority
  • whiteListCheck function check on table mode and MySQL database 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

😘 Acknowledgement

This project is inspired by the SQL parser flora-sql-parser module.

License

Apache-2.0

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 to my account taozhi8833998@163.com

AliPay(支付宝)

Wechat(微信)

Explain

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.

Donation list

Star History

Star History Chart