Huge News!Announcing our $40M Series B led by Abstract Ventures.Learn More →

node-sql-parser

Package Overview

Dependencies

Advanced tools

Install Socket

Detect and block malicious and high-risk dependencies

Install

node-sql-parser

simple node sql parser

5.3.5
latest
Source
npm

Version published: 2 weeks ago

Weekly downloads: 155K; increased by2.31%

Maintainers: 0

Weekly downloads

Created: 9 years ago

What is node-sql-parser?

The node-sql-parser package is a powerful tool for parsing, formatting, and analyzing SQL queries in Node.js. It supports various SQL dialects and provides a range of functionalities to work with SQL queries programmatically.

What are node-sql-parser's main functionalities?

SQL Parsing

This feature allows you to parse SQL queries into an Abstract Syntax Tree (AST). The AST can be used for further analysis or transformation of the SQL query.

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const sql = 'SELECT * FROM users WHERE age > 30';
const ast = parser.astify(sql);
console.log(JSON.stringify(ast, null, 2));

SQL Formatting

This feature allows you to format SQL queries. You can convert an AST back into a SQL string, which can be useful for standardizing the format of SQL queries.

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const sql = 'SELECT * FROM users WHERE age > 30';
const formattedSQL = parser.sqlify(parser.astify(sql));
console.log(formattedSQL);

SQL Dialect Support

This feature allows you to specify the SQL dialect (e.g., MySQL, PostgreSQL) when parsing SQL queries. This ensures that the parser correctly interprets the syntax and semantics of the specified dialect.

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const sql = 'SELECT * FROM users WHERE age > 30';
const ast = parser.astify(sql, { database: 'mysql' });
console.log(JSON.stringify(ast, null, 2));

Other packages similar to node-sql-parser

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

:rocket: Usage

Supported Database SQL Syntax

Athena
BigQuery
DB2
Hive
MariaDB
MySQL
PostgresQL
Redshift
Sqlite
TransactSQL
FlinkSQL
Snowflake(alpha)
Noql
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
}

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

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

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

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

if the table name is prefixed with database name, the table name will be parsed as dbName::tableName
if the table name is prefixed with database and schema name, the table name will be parsed as dbName.schemaName::tableName
if the table name is prefixed with server name in TransactSQL, the table name will be parsed as serverName.dbName.schemaName::tableName

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

Keywords

FAQs

What is node-sql-parser?

Is node-sql-parser popular?

Is node-sql-parser well maintained?

Package last updated on 02 Dec 2024

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.

Install

node-sql-parser

What is node-sql-parser?

What are node-sql-parser's main functionalities?

Other packages similar to node-sql-parser

sql-parser

sql-formatter

sequelize

Nodejs SQL Parser

:star: Features

:tada: Install

From npmjs

From GitHub Package Registry

From Browser

:rocket: Usage

Supported Database SQL Syntax

Create AST for SQL statement

Get node location in the AST

Convert AST back to SQL

Parse specified Database

Get TableList, ColumnList, Ast by parse function

Get the SQL visited tables

Get the SQL visited columns

Check the SQL with Authority List

:kissing_heart: Acknowledgement

License

Buy me a Coffee

Paypal

AliPay(支付宝)

Wechat(微信)

Explain

Donation list

Star History

Keywords

Related posts

PyPI on Ultralytics Supply Chain Attack: Poor CI/CD Practices to Blame, No Security Flaws in PyPI Exploited

Data Theft Repackaged: A Case Study in Malicious Wrapper Packages on npm

Malicious npm Package Typosquats Popular TypeScript ESLint Plugin, Exfiltrates Data and Enables Remote Exploitation

Get TableList, ColumnList, Ast by `parse` function