Nodejs SQL Parser





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

:star: 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

:tada: Install

$3

``bash
npm install node-sql-parser --save

or

yarn add node-sql-parser
`

$3

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

$3

Import the JS file in your page:

`javascript
// support all database parser, but file size is about 750K


// or you can import specified database parser only, it's about 150K


`
- NodeSQLParser object is on window

`html









Check console to see the output






`

:rocket: Usage

$3

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

$3

`javascript
// 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);
`

- ast for SELECT * FROM t

`json
{
"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
`javascript
const { Parser } = require('node-sql-parser');
const parser = new Parser();
const ast = parser.astify('SELECT * FROM t', { parseOptions: { includeLocations: true } });

console.log(ast);
`

- ast for SELECT * FROM t with the loc property indicating locations and ranges

`json
{
"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
}
}
}
`




$3

`javascript
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
`

$3

There two ways to parser the specified database.

import Parser from the specified database path node-sql-parser/build/{database}

`javascript
// 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.

`javascript
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"
`

`javascript
const opt = {
database: 'ClickHouse'
}
// import all databases parser
const { Parser } = require('node-sql-parser')
const parser = new Parser()
// parse ClickHouse specific SQL with String data type
const ast = parser.astify('CREATE TABLE users (id UInt64, name String, tags Array(String))', opt)
const sql = parser.sqlify(ast, opt)
console.log(sql); // CREATE TABLE users (id UInt64, name String, tags Array(String))
`

`javascript
const opt = {
database: 'DuckDB'
}
// import all databases parser
const { Parser } = require('node-sql-parser')
const parser = new Parser()
// parse DuckDB specific SQL with advanced data types
const ast = parser.astify('CREATE TABLE users (id INTEGER, data STRUCT(name VARCHAR, tags LIST(VARCHAR)), metadata MAP(VARCHAR, VARCHAR))', opt)
const sql = parser.sqlify(ast, opt)
console.log(sql); // CREATE TABLE "users" ("id" INTEGER, "data" STRUCT("name" VARCHAR, "tags" LIST(VARCHAR)), "metadata" MAP(VARCHAR, VARCHAR))
`

$3

`javascript
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);
`

$3

- get the table list that the sql visited
- the format is {type}::{dbName}::{tableName} // type could be select, update, delete or insert

`javascript
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

$3

- 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

`javascript
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"]
`

$3

- check table authority
- whiteListCheck function check on table mode and MySQL database by default

`javascript
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

`javascript
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 undefined
``

:kissing_heart: 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.

$3

Donate money by paypal to my account taozhi8833998@163.com

$3

$3

$3

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