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