0
# SQL Parsing
1

2
Core functionality for parsing SQL strings into structured Abstract Syntax Trees and converting AST back to SQL strings with support for multiple database engines.
3

4
## Capabilities
5

6
### Parse Method
7

8
Complete parsing functionality that returns AST along with extracted table and column lists.
9

10
```javascript { .api }
11
/**
12
 * Parse SQL string and return AST with table/column lists
13
 * @param sql - SQL string to parse
14
 * @param opt - Optional configuration object
15
 * @returns Object containing tableList, columnList, and ast
16
 */
17
parse(sql: string, opt?: Option): TableColumnAst;
18

19
interface TableColumnAst {
20
  tableList: string[];
21
  columnList: string[];
22
  ast: AST[] | AST;
23
  loc?: LocationRange;
24
}
25

26
interface Option {
27
  database?: string;
28
  type?: string;
29
  trimQuery?: boolean;
30
  parseOptions?: ParseOptions;
31
}
32

33
interface ParseOptions {
34
  includeLocations?: boolean;
35
}
36
```
37

38
**Usage Examples:**
39

40
```javascript
41
const { Parser } = require('node-sql-parser');
42
const parser = new Parser();
43

44
// Basic parsing
45
const result = parser.parse('SELECT id, name FROM users WHERE age > 18');
46
console.log(result.tableList);   // ["select::null::users"]
47
console.log(result.columnList);  // ["select::users::id", "select::users::name", "select::users::age"]
48
console.log(result.ast);         // AST object
49

50
// Parse with location information
51
const resultWithLoc = parser.parse(
52
  'SELECT * FROM products', 
53
  { parseOptions: { includeLocations: true } }
54
);
55

56
// Parse with specific database
57
const pgResult = parser.parse(
58
  'SELECT * FROM "user_table"',
59
  { database: 'PostgreSQL' }
60
);
61
```
62

63
### AST Generation
64

65
Convert SQL strings into Abstract Syntax Trees for programmatic manipulation.
66

67
```javascript { .api }
68
/**
69
 * Parse SQL string into Abstract Syntax Tree
70
 * @param sql - SQL string to parse
71
 * @param opt - Optional configuration object
72
 * @returns AST object or array of AST objects for multiple statements
73
 */
74
astify(sql: string, opt?: Option): AST[] | AST;
75
```
76

77
**Usage Examples:**
78

79
```javascript
80
// Single statement
81
const ast = parser.astify('SELECT * FROM users');
82
console.log(ast.type);     // "select"
83
console.log(ast.columns);  // "*"
84
console.log(ast.from);     // [{ db: null, table: "users", as: null }]
85

86
// Multiple statements separated by semicolon
87
const multipleAsts = parser.astify('SELECT * FROM users; DELETE FROM logs;');
88
console.log(multipleAsts.length); // 2
89
console.log(multipleAsts[0].type); // "select"
90
console.log(multipleAsts[1].type); // "delete"
91

92
// Complex query with joins
93
const complexAst = parser.astify(`
94
  SELECT u.name, p.title 
95
  FROM users u 
96
  LEFT JOIN posts p ON u.id = p.user_id 
97
  WHERE u.active = 1
98
  ORDER BY u.name
99
`);
100
```
101

102
### SQL Generation
103

104
Convert Abstract Syntax Trees back into SQL strings with database-specific formatting.
105

106
```javascript { .api }
107
/**
108
 * Convert AST back to SQL string
109
 * @param ast - AST object or array to convert
110
 * @param opt - Optional configuration object
111
 * @returns SQL string representation
112
 */
113
sqlify(ast: AST[] | AST, opt?: Option): string;
114
```
115

116
**Usage Examples:**
117

118
```javascript
119
// Basic conversion
120
const ast = parser.astify('SELECT * FROM users');
121
const sql = parser.sqlify(ast);
122
console.log(sql); // "SELECT * FROM `users`" (MySQL format)
123

124
// Database-specific formatting
125
const postgresAst = parser.astify('SELECT * FROM users', { database: 'PostgreSQL' });
126
const postgresSql = parser.sqlify(postgresAst, { database: 'PostgreSQL' });
127
console.log(postgresSql); // "SELECT * FROM \"users\"" (PostgreSQL format)
128

129
// TransactSQL formatting
130
const tsqlAst = parser.astify('SELECT * FROM users', { database: 'TransactSQL' });
131
const tsqlSql = parser.sqlify(tsqlAst, { database: 'TransactSQL' });
132
console.log(tsqlSql); // "SELECT * FROM [users]" (TransactSQL format)
133

134
// Multiple statements
135
const multipleAsts = parser.astify('SELECT * FROM users; SELECT * FROM products;');
136
const multipleSql = parser.sqlify(multipleAsts);
137
console.log(multipleSql); // Combined SQL string
138
```
139

140
### Query Trimming
141

142
Control automatic trimming of whitespace from SQL queries.
143

144
```javascript { .api }
145
interface Option {
146
  trimQuery?: boolean; // Default: true
147
}
148
```
149

150
**Usage Examples:**
151

152
```javascript
153
// Default behavior (trimQuery: true)
154
const ast1 = parser.astify('  SELECT * FROM users  ');
155

156
// Preserve whitespace
157
const ast2 = parser.astify('  SELECT * FROM users  ', { trimQuery: false });
158
```
159

160
### Location Tracking
161

162
Include source code location information in AST nodes for debugging and error reporting.
163

164
```javascript { .api }
165
interface ParseOptions {
166
  includeLocations?: boolean;
167
}
168

169
interface LocationRange {
170
  start: Location;
171
  end: Location;
172
}
173

174
interface Location {
175
  offset: number;
176
  line: number;
177
  column: number;
178
}
179
```
180

181
**Usage Examples:**
182

183
```javascript
184
const ast = parser.astify(
185
  'SELECT name FROM users', 
186
  { parseOptions: { includeLocations: true } }
187
);
188

189
// Each AST node will have a 'loc' property
190
console.log(ast.loc); // { start: { offset: 0, line: 1, column: 1 }, end: { ... } }
191
console.log(ast.columns[0].loc); // Location info for the 'name' column
192
```
193

194
## AST Structure
195

196
The AST follows a consistent structure for different SQL statement types:
197

198
### SELECT Statement AST
199

200
```javascript { .api }
201
interface Select {
202
  with: With[] | null;
203
  type: "select";
204
  options: any[] | null;
205
  distinct: "DISTINCT" | null;
206
  columns: any[] | Column[];
207
  from: From[] | TableExpr | null;
208
  where: Binary | Function | null;
209
  groupby: { columns: ColumnRef[] | null, modifiers: ValueExpr<string>[] };
210
  having: any[] | null;
211
  orderby: OrderBy[] | null;
212
  limit: Limit | null;
213
  window?: WindowExpr;
214
  qualify?: any[] | null;
215
  _orderby?: OrderBy[] | null;
216
  _limit?: Limit | null;
217
  parentheses_symbol?: boolean;
218
  _parentheses?: boolean;
219
  loc?: LocationRange;
220
  _next?: Select;
221
  set_op?: string;
222
}
223
```
224

225
### INSERT/REPLACE Statement AST
226

227
```javascript { .api }
228
interface Insert_Replace {
229
  type: "replace" | "insert";
230
  table: any;
231
  columns: string[] | null;
232
  values: InsertReplaceValue[] | Select;
233
  partition: any[];
234
  prefix: string;
235
  on_duplicate_update: {
236
    keyword: "on duplicate key update";
237
    set: SetList[];
238
  };
239
  loc?: LocationRange;
240
  returning?: Returning;
241
}
242
```
243

244
### UPDATE Statement AST
245

246
```javascript { .api }
247
interface Update {
248
  type: "update";
249
  db: string | null;
250
  table: Array<From | Dual> | null;
251
  set: SetList[];
252
  where: Binary | Function | null;
253
  loc?: LocationRange;
254
  returning?: Returning;
255
}
256
```
257

258
### DELETE Statement AST
259

260
```javascript { .api }
261
interface Delete {
262
  type: "delete";
263
  table: any;
264
  from: Array<From | Dual>;
265
  where: Binary | Function | null;
266
  loc?: LocationRange;
267
  returning?: Returning;
268
}
269
```
270

271
## Error Handling
272

273
The parser throws errors for invalid SQL syntax:
274

275
```javascript
276
try {
277
  const ast = parser.astify('INVALID SQL SYNTAX');
278
} catch (error) {
279
  console.error('Parse error:', error.message);
280
}
281

282
// Database-specific errors
283
try {
284
  const ast = parser.astify('SELECT * FROM users', { database: 'UnsupportedDB' });
285
} catch (error) {
286
  console.error('Database error:', error.message); // "UnsupportedDB is not supported currently"
287
}
288
```