This package is a fork of https://github.com/taozhi8833998/node-sql-parser.
Original author: taozhi License: Apache 2.0
This fork exists to patch issues with the SQLite implementation while they're working their way to the upstream.
As updates are made, this package will be published using versions like 5.4.0-fork.0, 5.4.0-fork.1, 5.4.1-fork.0 etc. to indicate the tag of the upstream they were forked from.
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"]- 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 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.