@getclarify/node-sql-parser
Advanced tools
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 { 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{
"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
}
}
}
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`
There two ways to parser the specified database.
import Parser from the specified database path node-sql-parser/build/{database}
// 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.
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"
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 inspired by the SQL parser 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
We found that @getclarify/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
CISOs are racing to adopt AI for cybersecurity, but hurdles in budgets and governance may leave some falling behind in the fight against cyber threats.
Research
Security News
Socket researchers uncovered a backdoored typosquat of BoltDB in the Go ecosystem, exploiting Go Module Proxy caching to persist undetected for years.
Security News
Company News
Socket is joining TC54 to help develop standards for software supply chain security, contributing to the evolution of SBOMs, CycloneDX, and Package URL specifications.