node-sql-parser

What is node-sql-parser?

The node-sql-parser package is a powerful tool for parsing, formatting, and analyzing SQL queries in Node.js. It supports various SQL dialects and provides a range of functionalities to work with SQL queries programmatically.

What are node-sql-parser's main functionalities?

SQL Parsing

This feature allows you to parse SQL queries into an Abstract Syntax Tree (AST). The AST can be used for further analysis or transformation of the SQL query.

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const sql = 'SELECT * FROM users WHERE age > 30';
const ast = parser.astify(sql);
console.log(JSON.stringify(ast, null, 2));

SQL Formatting

This feature allows you to format SQL queries. You can convert an AST back into a SQL string, which can be useful for standardizing the format of SQL queries.

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const sql = 'SELECT * FROM users WHERE age > 30';
const formattedSQL = parser.sqlify(parser.astify(sql));
console.log(formattedSQL);

SQL Dialect Support

This feature allows you to specify the SQL dialect (e.g., MySQL, PostgreSQL) when parsing SQL queries. This ensures that the parser correctly interprets the syntax and semantics of the specified dialect.

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const sql = 'SELECT * FROM users WHERE age > 30';
const ast = parser.astify(sql, { database: 'mysql' });
console.log(JSON.stringify(ast, null, 2));

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.

README

GanJiang SQL Parser

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

:star: Features

• support multiple sql statement seperate by semicolon
• support select, delete, update and insert type
• output the table list that the sql visited with the corresponding authority

:rocket: 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"]

Check the SQL with Authority List

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 whiteList = ['(select|update)::(.*)::(a|b)'] // array that contain multiple authorities
parser.whiteListCheck(sql, whiteList) // 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`

:kissing_heart: Acknowledgement

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

License

MIT