node-sql-parser

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 typescript

:tada: Install

npm install node-sql-parser --save

or

yarn add node-sql-parser

Install the following type module for typescript usage

npm install @types/node-sql-parser --save-dev

or

yarn add @types/node-sql-parser --dev

:rocket: Usage

Create AST for SQL statement

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

console.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
}

Convert AST back to SQL

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

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

Get TableList, ColumnList, Ast by parse function

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

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

:kissing_heart: Acknowledgement

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

License

GPLv2

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 donation to Author.

Paypal

Donate money by paypal to my account [e-mail]

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

Similar packages

Other packages similar to node-sql-parser

sql-parser

The sql-parser package is a simple SQL parser for Node.js. It provides basic parsing capabilities but lacks the advanced features and dialect support of node-sql-parser.

sql-formatter

The sql-formatter package focuses on formatting SQL queries. It provides a range of formatting options but does not offer parsing or AST generation capabilities like node-sql-parser.

sequelize

Sequelize is an ORM for Node.js that supports various SQL dialects. While it provides some query parsing and generation capabilities, its primary focus is on database interaction and ORM functionalities, making it more comprehensive but also more complex than node-sql-parser.