Needless Patchouli Manufacture

    node-sql-parser
    TypeScript icon, indicating that this package has built-in type declarations

    4.0.1 • Public • Published

    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.

    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

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

    🚀 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

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

    Install

    npm i node-sql-parser

    DownloadsWeekly Downloads

    12,903

    Version

    4.0.1

    License

    GPLv2

    Unpacked Size

    39.2 MB

    Total Files

    94

    Last publish

    Collaborators

    • taozhi8833998