# @kiyo5hi/node-sql-parser

> simple node sql parser

Latest version **3.6.6** (published 2021-09-29) · GPLv2 license · 0 weekly downloads

## Install

```sh
npm install @kiyo5hi/node-sql-parser
pnpm add @kiyo5hi/node-sql-parser
yarn add @kiyo5hi/node-sql-parser
bun add @kiyo5hi/node-sql-parser
```

## Health

**Score 25/100 (F)** — status: abandoned.

Positive: has types; no vulnerabilities; high quality score.

Warnings: low downloads; no esm support; large bundle.

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 3.6.6 |
| Published | 2021-09-29 |
| First published | 2021-08-24 |
| Weekly downloads | 0 |
| License | GPLv2 |
| TypeScript types | bundled |
| Module format | CommonJS |
| Node | >=8 |
| Dependencies | 1 |
| Unpacked size | 35.8 MB |
| Known vulnerabilities | 0 |
| Install scripts | no |
| Author | Kiyo5hi |
| Maintainers | kiyo5hi |
| Keywords | sql, sql-parser, parser, node, nodejs, node-parser, node-sql-parser, ast, sql-ast |

## Links

- npm: https://www.npmjs.com/package/@kiyo5hi/node-sql-parser
- Repository: https://github.com/Kiyo5hi/node-sql-parser
- Homepage: https://github.com/Kiyo5hi/node-sql-parser#readme
- Issues: https://github.com/Kiyo5hi/node-sql-parser/issues
- npm.io page: https://npm.io/package/@kiyo5hi/node-sql-parser

## Dependencies (1)

- [big-integer](https://npm.io/package/big-integer.md) ^1.6.48

## Alternatives

- [babylon](https://npm.io/package/babylon.md) — 5.1M weekly downloads
- [csscolorparser](https://npm.io/package/csscolorparser.md) — 3.7M weekly downloads
- [expr-eval-fork](https://npm.io/package/expr-eval-fork.md) — 1.5M weekly downloads
- [@leeoniya/ufuzzy](https://npm.io/package/@leeoniya/ufuzzy.md) — 247.7K weekly downloads
- [xml-parser](https://npm.io/package/xml-parser.md) — 78.4K weekly downloads

## Recent versions

- 3.6.6 (latest) — 2021-09-29
- 3.6.5 — 2021-08-25
- 3.6.4 — 2021-08-24

## README

# Nodejs SQL Parser

Forked from [Repo](https://github.com/taozhi8833998/node-sql-parser)

## :tada: Install

### From [npmjs](https://www.npmjs.org/)

```bash
npm install node-sql-parser --save

or

yarn add node-sql-parser

### From Browser

Import the JS file in your page:

```javascript
// 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`

```html
<!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

- BigQuery
- DB2
- Hive
- MariaDB
- Mysql
- PostgresQL
- Sqlite(developing)
- TransactSQL
- [FlinkSQL](https://ci.apache.org/projects/flink/flink-docs-stable/dev/table/sql/)
- New issue could be made for other new database.

### Create AST for SQL statement

```javascript
// 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`

```json
{
  "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

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

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

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

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

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

```javascript
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](https://github.com/godmodelabs/flora-sql-parser) module.

## License

[GPLv2](LICENSE)

## [Donation list](https://github.com/taozhi8833998/node-sql-parser/blob/master/DONATIONLIST.md)

---
_Source: https://npm.io/package/@kiyo5hi/node-sql-parser · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._