node-sql-parser

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

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

:rocket: Usage

Supported Database SQL Syntax

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);
{
  "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
}

Get node location in the AST

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const ast = parser.astify('SELECT * FROM t', { parseOptions: { includeLocations: true } });

console.log(ast);
{
  "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
    }
  }
}

Convert AST back to SQL

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`

Parse specified Database

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"

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

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

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

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
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 inspired by the SQL parser flora-sql-parser module.

License

Apache-2.0

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 taozhi8833998@163.com

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

Star History

Star History Chart

</a>