node-sql-parser
Advanced tools
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.
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));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.
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 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.
Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList, columnList and convert it back to SQL.
npm install node-sql-parser --save
or
yarn add node-sql-parser
npm 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>
NodeSQLParser object is on window<!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>
// 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{
"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 opt = {
database: 'MySQL' // MySQL is the default database
}
// import mysql parser only
const { Parser } = require('node-sql-parser/build/mysql');
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`
parse functionconst 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);
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"]
select *, delete and insert into tableName values() without specified columns, the .* column authority regex is requiredconst 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"]
whiteListCheck function check on table mode and MySQL database by defaultconst { 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
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
This project is based on the SQL parser extracted from 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.
FAQs
simple node sql parser
The npm package node-sql-parser receives a total of 70,637 weekly downloads. As such, node-sql-parser popularity was classified as popular.
We found that node-sql-parser demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 0 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Socket CEO Feross Aboukhadijeh discusses open source security challenges, including zero-day attacks and supply chain risks, on the Cyber Security Council podcast.
Security News
Research
Socket researchers uncover how threat actors weaponize Out-of-Band Application Security Testing (OAST) techniques across the npm, PyPI, and RubyGems ecosystems to exfiltrate sensitive data.
Security News
Research
A malicious npm campaign is targeting Ethereum developers by impersonating Hardhat plugins and the Nomic Foundation, stealing sensitive data like private keys.