Need help with node-sql-parser?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.

About the developer

taozhi8833998
261 Stars 67 Forks GNU General Public License v2.0 1.1K Commits 20 Opened issues

Description

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

Services available

!
?

Need anything else?

Contributors list

Nodejs SQL Parser

Build Status Codacy Badge Coverage Status Dependencies Known Vulnerabilities

npm version NPM downloads

issues

TypeScript definitions on DefinitelyTyped license

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 various databases engine

:tada: Install

From npmjs

npm install node-sql-parser --save

or

yarn add node-sql-parser

From GitHub Package Registry

npm install @taozhi8833998/node-sql-parser --registry=https://npm.pkg.github.com/

From Browser

Import the JS file in your page:

// support all database parser, but file size is about 750K


// or you can import specified database parser only, it's about 150K

  • NodeSQLParser
    object is on
    window
<title>node-sql-parser</title>
<meta charset="utf-8">


<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>

:rocket: Usage

Supported Database SQL Syntax

  • BigQuery
  • DB2
  • Hive
  • MariaDB
  • Mysql
  • PostgresQL
  • Sqlite(developing)
  • TransactSQL
  • FlinkSQL
  • New issue could be made for other new database.

Create AST for SQL statement

// 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
}

Convert AST back to SQL

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 = parse.sqlify(ast, opt);

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

Get TableList, ColumnList, Ast by
parse
function

const 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);

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

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

Check the SQL with Authority List

  • check table authority
  • whiteListCheck
    function check on
    table
    mode and
    MySQL
    database 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
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
  • 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
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

: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 donate to Author.

Paypal

Donate money by paypal to my account [email protected]

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

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.