Nodejs SQL Parser

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
• output the table and column list that the sql visited with the corresponding authority

🎉 Install

npm install node-sql-parser --save

🚀 Usage

Create AST for SQL statement

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const ast = parser.sqlToAst('SELECT * FROM t');

console.log(ast);

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

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const tableList = parser.tableList('SELECT * FROM t');

console.log(tableList); // ["select::null::t"]

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

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const columnList = parser.columnList('SELECT t.id FROM t');

console.log(columnList); // ["select::t::id"]

Check the SQL with Authority List

• check table authority
• whiteListCheck function check on table mode 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
parser.whiteListCheck(sql, whiteTableList, 'table') // 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
parser.whiteListCheck(sql, whiteColumnList, 'column') // if check failed, an error would be thrown with relevant error message, if passed it would return undefined

Convert AST back to SQL

const { Parser, util } = require('node-sql-parser');
const parser = new Parser()
const ast = parser.sqlToAst('SELECT * FROM t');
const sql = util.astToSQL(ast);

console.log(sql); // SELECT * FROM `t`

😘 Acknowledgement

This project is based on the SQL parser extracted from flora-sql-parser module.

License

MIT