0
# Node SQL Parser
1

2
Node SQL Parser is a comprehensive SQL parsing library that converts SQL statements into Abstract Syntax Trees (AST) and back to SQL strings. It supports multiple database engines including MySQL, PostgreSQL, SQLite, BigQuery, MariaDB, DB2, Hive, Redshift, TransactSQL, FlinkSQL, Snowflake, and others. The library provides table and column extraction capabilities with authority information, making it ideal for SQL analysis, security checks, and query validation.
3

4
## Package Information
5

6
- **Package Name**: node-sql-parser
7
- **Package Type**: npm
8
- **Language**: JavaScript with TypeScript definitions
9
- **Installation**: `npm install node-sql-parser`
10

11
## Core Imports
12

13
```javascript
14
const { Parser } = require('node-sql-parser');
15
```
16

17
For ES modules:
18

19
```javascript
20
import { Parser, util } from 'node-sql-parser';
21
```
22

23
For database-specific parsers:
24

25
```javascript
26
const { Parser } = require('node-sql-parser/build/mysql');
27
const { Parser } = require('node-sql-parser/build/postgresql');
28
```
29

30
## Basic Usage
31

32
```javascript
33
const { Parser } = require('node-sql-parser');
34
const parser = new Parser();
35

36
// Parse SQL to AST
37
const ast = parser.astify('SELECT * FROM users WHERE age > 18');
38

39
// Convert AST back to SQL
40
const sql = parser.sqlify(ast);
41

42
// Get table and column lists with parse()
43
const { tableList, columnList, ast: parsedAst } = parser.parse(
44
  'SELECT name, email FROM users WHERE active = 1'
45
);
46

47
console.log(tableList);   // ["select::null::users"] 
48
console.log(columnList);  // ["select::users::name", "select::users::email", "select::users::active"]
49
```
50

51
## Browser Usage
52

53
For browser environments, you can use the UMD builds:
54

55
```html
56
<!-- Full parser (supports all databases, ~750KB) -->
57
<script src="https://unpkg.com/node-sql-parser/umd/index.umd.js"></script>
58

59
<!-- Database-specific parsers (~150KB each) -->
60
<script src="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script>
61
<script src="https://unpkg.com/node-sql-parser/umd/postgresql.umd.js"></script>
62
```
63

64
The `NodeSQLParser` object is available on the global `window`:
65

66
```javascript
67
// Use in browser
68
const parser = new NodeSQLParser.Parser();
69
const ast = parser.astify("SELECT id, name FROM students WHERE age < 18");
70
console.log(ast);
71
const sql = parser.sqlify(ast);
72
console.log(sql);
73
```
74

75
## Architecture
76

77
Node SQL Parser is built around several key components:
78

79
- **Parser Class**: Main interface providing parsing, AST generation, and SQL conversion
80
- **Database Engines**: Pluggable parsers for different SQL dialects (MySQL, PostgreSQL, etc.)
81
- **AST Types**: Comprehensive type system for representing SQL structures
82
- **Utility Functions**: Helper functions for SQL string manipulation and AST processing
83
- **Security Features**: Whitelist checking for table and column access authorization
84

85
## Capabilities
86

87
### SQL Parsing and AST Generation
88

89
Core functionality for parsing SQL strings into structured Abstract Syntax Trees and converting AST back to SQL.
90

91
```javascript { .api }
92
class Parser {
93
  constructor();
94
  
95
  parse(sql: string, opt?: Option): TableColumnAst;
96
  astify(sql: string, opt?: Option): AST[] | AST;
97
  sqlify(ast: AST[] | AST, opt?: Option): string;
98
}
99

100
interface TableColumnAst {
101
  tableList: string[];
102
  columnList: string[];
103
  ast: AST[] | AST;
104
  loc?: LocationRange;
105
}
106

107
interface Option {
108
  database?: string;
109
  type?: string;
110
  trimQuery?: boolean;
111
  parseOptions?: ParseOptions;
112
}
113

114
interface ParseOptions {
115
  includeLocations?: boolean;
116
}
117
```
118

119
[SQL Parsing](./sql-parsing.md)
120

121
### Expression Processing
122

123
Specialized functionality for handling SQL expressions, column references, and complex query components.
124

125
```javascript { .api }
126
class Parser {
127
  exprToSQL(expr: any, opt?: Option): string;
128
  columnsToSQL(columns: any, tables: any, opt?: Option): string[];
129
}
130
```
131

132
[Expression Processing](./expressions.md)
133

134
### Authority and Security
135

136
Table and column access analysis with whitelist validation for security and authorization checking.
137

138
```javascript { .api }
139
class Parser {
140
  tableList(sql: string, opt?: Option): string[];
141
  columnList(sql: string, opt?: Option): string[];
142
  whiteListCheck(sql: string, whiteList: string[], opt?: Option): Error | undefined;
143
}
144

145
type WhilteListCheckMode = "table" | "column";
146
```
147

148
[Authority and Security](./security.md)
149

150
### Utility Functions
151

152
Helper functions for SQL string manipulation, AST processing, and database-specific formatting.
153

154
```javascript { .api }
155
import { util } from 'node-sql-parser';
156

157
// Note: Utility functions are not exported from the main module
158
// Import directly from the util module for access to these functions
159
// import { createValueExpr, createBinaryExpr, escape, literalToSQL } from 'node-sql-parser/lib/util';
160

161
createValueExpr(value: any): ValueExpr;
162
createBinaryExpr(operator: string, left: any, right: any): Binary;
163
escape(str: string): string;
164
literalToSQL(literal: any): string;
165
identifierToSql(identifier: string): string;
166
```
167

168
[Utility Functions](./utilities.md)
169

170
## Types
171

172
```javascript { .api }
173
// Core AST Types
174
type AST = Use | Select | Insert_Replace | Update | Delete | Alter | Create | Drop;
175

176
interface Select {
177
  with: With[] | null;
178
  type: "select";
179
  options: any[] | null;
180
  distinct: "DISTINCT" | null;
181
  columns: any[] | Column[];
182
  from: From[] | TableExpr | null;
183
  where: Binary | Function | null;
184
  groupby: { columns: ColumnRef[] | null, modifiers: ValueExpr<string>[] };
185
  having: any[] | null;
186
  orderby: OrderBy[] | null;
187
  limit: Limit | null;
188
  window?: WindowExpr;
189
  qualify?: any[] | null;
190
  loc?: LocationRange;
191
}
192

193
interface Insert_Replace {
194
  type: "replace" | "insert";
195
  table: any;
196
  columns: string[] | null;
197
  values: InsertReplaceValue[] | Select;
198
  partition: any[];
199
  prefix: string;
200
  on_duplicate_update: {
201
    keyword: "on duplicate key update";
202
    set: SetList[];
203
  };
204
  loc?: LocationRange;
205
  returning?: Returning;
206
}
207

208
interface Update {
209
  type: "update";
210
  db: string | null;
211
  table: Array<From | Dual> | null;
212
  set: SetList[];
213
  where: Binary | Function | null;
214
  loc?: LocationRange;
215
  returning?: Returning;
216
}
217

218
interface Delete {
219
  type: "delete";
220
  table: any;
221
  from: Array<From | Dual>;
222
  where: Binary | Function | null;
223
  loc?: LocationRange;
224
  returning?: Returning;
225
}
226

227
// Expression Types
228
type ExpressionValue = ColumnRef | Param | Function | Case | AggrFunc | Value | Binary | Cast | Interval;
229

230
interface Binary {
231
  type: "binary_expr";
232
  operator: string;
233
  left: ExpressionValue | ExprList;
234
  right: ExpressionValue | ExprList;
235
  loc?: LocationRange;
236
  parentheses?: boolean;
237
}
238

239
interface Function {
240
  type: "function";
241
  name: FunctionName;
242
  args?: ExprList;
243
  suffix?: any;
244
  loc?: LocationRange;
245
}
246

247
interface AggrFunc {
248
  type: "aggr_func";
249
  name: string;
250
  args: {
251
    expr: ExpressionValue;
252
    distinct: "DISTINCT" | null;
253
    orderby: OrderBy[] | null;
254
    parentheses?: boolean;
255
  };
256
  loc?: LocationRange;
257
}
258

259
// Table and Column Types
260
type From = BaseFrom | Join | TableExpr | Dual;
261

262
interface BaseFrom {
263
  db: string | null;
264
  table: string;
265
  as: string | null;
266
  schema?: string;
267
  loc?: LocationRange;
268
}
269

270
interface Join extends BaseFrom {
271
  join: "INNER JOIN" | "LEFT JOIN" | "RIGHT JOIN";
272
  using?: string[];
273
  on?: Binary;
274
}
275

276
interface ColumnRef {
277
  type: "column_ref";
278
  table: string | null;
279
  column: string | { expr: ValueExpr };
280
  options?: ExprList;
281
  loc?: LocationRange;
282
  collate?: { collate: CollateExpr } | null;
283
  order_by?: SortDirection | null;
284
}
285

286
// Utility Types
287
interface ValueExpr<T = string | number | boolean> {
288
  type: "backticks_quote_string" | "string" | "regex_string" | "hex_string" | 
289
        "full_hex_string" | "natural_string" | "bit_string" | "double_quote_string" | 
290
        "single_quote_string" | "boolean" | "bool" | "null" | "star" | "param" | 
291
        "origin" | "date" | "datetime" | "default" | "time" | "timestamp" | "var_string";
292
  value: T;
293
}
294

295
interface OrderBy {
296
  type: "ASC" | "DESC";
297
  expr: any;
298
  loc?: LocationRange;
299
}
300

301
interface Limit {
302
  seperator: string;
303
  value: LimitValue[];
304
  loc?: LocationRange;
305
}
306

307
interface LimitValue {
308
  type: string;
309
  value: number;
310
  loc?: LocationRange;
311
}
312
```
313

314
## Supported Database Engines
315

316
- **MySQL** (default)
317
- **PostgreSQL** 
318
- **SQLite**
319
- **MariaDB**
320
- **BigQuery**
321
- **DB2**
322
- **Hive**
323
- **Redshift**
324
- **TransactSQL**
325
- **FlinkSQL**
326
- **Snowflake** (alpha)
327
- **Athena**
328
- **Noql**
329
- **Trino**
330

331
Database selection can be specified via options:
332

333
```javascript
334
const opt = { database: 'PostgreSQL' };
335
const ast = parser.astify('SELECT * FROM users', opt);
336
const sql = parser.sqlify(ast, opt);
337
```