SQL Parser for Node.js

5.4.0 · active · verified Sun Apr 19

Node.js SQL Parser is a versatile library designed to parse various SQL statements into a structured Abstract Syntax Tree (AST) and then reconstruct the SQL from that AST. It supports a comprehensive range of SQL dialects, including MySQL, PostgreSQL, SQLite, Athena, BigQuery, Snowflake, and TransactSQL, among others. Currently at version 5.4.0, the package maintains an active development pace with frequent minor and patch updates, consistently adding support for new SQL syntax features and improving existing dialect compatibility. Key differentiators include its extensive multi-dialect support, the ability to extract visited table and column lists with associated authority, and its dual functionality for both SQL parsing and generation. It offers both Node.js module and browser UMD bundles, providing flexibility across different environments.

Common errors

```
TypeError: Parser is not a constructor
```
cause This typically occurs when trying to `require` the module in a CommonJS context while expecting a default export, or attempting to use browser global `NodeSQLParser.Parser` in a Node.js environment, or an incorrect named import in ESM.

fix For CommonJS, use `const { Parser } = require('node-sql-parser');`. For ESM/TypeScript, use `import { Parser } from 'node-sql-parser';`. In a browser environment using UMD, ensure the correct script is loaded and access `new NodeSQLParser.Parser()`.
```
Error: Unknown dialect: 'xyz'
```
cause The `Parser` constructor or `astify`/`sqlify` methods were called with a `dialect` option that is not recognized or supported by the library.

fix Review the documentation for a list of supported SQL dialects and ensure the dialect string passed to the options object (e.g., `{ dialect: 'postgresql' }`) is spelled correctly and is indeed supported.
```
Syntax error near '...' at line X column Y
```
cause The provided SQL query is either malformed, contains syntax unsupported by the chosen SQL dialect, or contains syntax that the parser currently does not support.

fix First, verify the SQL query for correctness against the specified database's syntax. Second, ensure you have correctly set the `dialect` option in the `Parser` constructor to match your SQL. If the SQL is valid for the dialect but still errors, it might be a limitation of the parser itself; check the GitHub issues for similar reports.

Warnings

gotcha The `Parser` constructor defaults to the MySQL dialect if no `dialect` option is explicitly provided. Parsing SQL from other database systems (e.g., PostgreSQL, SQLite) without specifying the correct dialect will likely result in syntax errors or incorrect AST generation.
Fix: Always pass the appropriate dialect option to the `Parser` constructor, e.g., `new Parser({ dialect: 'postgresql' })`. Refer to the documentation for a list of supported dialects.
gotcha When using `node-sql-parser` in a browser environment via UMD bundles, importing the full `index.umd.js` can result in a large bundle size (approximately 750KB). If you only need support for a specific database, specialized UMD bundles like `mysql.umd.js` or `postgresql.umd.js` are available and significantly smaller (around 150KB).
Fix: For browser-based applications, consider using dialect-specific UMD bundles if your application only requires a subset of SQL parsing capabilities to reduce overall page load size.
gotcha The `astify` method returns an array of AST nodes if multiple SQL statements are separated by semicolons in the input string. If you expect a single statement, always consider that the output might be an array, or ensure your input SQL contains only one statement.
Fix: When processing the output of `parser.astify()`, be prepared to handle an array of AST objects, even if you anticipate a single statement. Iterate through the array or access the first element if you are certain only one statement is present.

Install

npm install node-sql-parser npm
yarn add node-sql-parser yarn
pnpm add node-sql-parser pnpm

Imports

Parser
wrong:
```
const Parser = require('node-sql-parser');
```
correct:
```
import { Parser } from 'node-sql-parser';
```
For ESM environments or TypeScript projects. The `Parser` class is the primary entry point for parsing and sqlifying operations.
Parser (CommonJS)
wrong:
```
import { Parser } from 'node-sql-parser';
```
correct:
```
const { Parser } = require('node-sql-parser');
```
For CommonJS environments. Destructure the `Parser` class from the module export.
NodeSQLParser (Browser Global)
```
const parser = new NodeSQLParser.Parser();
```
When using the UMD bundle directly in a browser via a `<script>` tag. The `NodeSQLParser` object is exposed globally on `window`.

Quickstart

This quickstart demonstrates how to initialize the SQL parser, parse a sample SQL query into an AST, extract table and column lists, and then reconstruct the SQL from the AST. It highlights the basic workflow for SQL analysis and manipulation.

import { Parser, AST } from 'node-sql-parser';

const sqlQuery = "SELECT id, name FROM users WHERE age > 25 AND city = 'New York';";

// Initialize the parser, defaulting to MySQL dialect. For other dialects, pass { dialect: 'postgresql' } as an option.
const parser = new Parser();

try {
  // Parse the SQL query into an Abstract Syntax Tree (AST).
  const ast: AST[] = parser.astify(sqlQuery) as AST[];
  console.log('Parsed AST:', JSON.stringify(ast, null, 2));

  // Get the list of tables visited in the SQL statement.
  const tables = parser.tableList(sqlQuery);
  console.log('Visited Tables:', tables);

  // Get the list of columns visited in the SQL statement.
  const columns = parser.columnList(sqlQuery);
  console.log('Visited Columns:', columns);

  // Convert the AST back into a SQL string.
  const reconstructedSql = parser.sqlify(ast);
  console.log('Reconstructed SQL:', reconstructedSql);
} catch (error: any) {
  console.error('SQL Parsing Error:', error.message);
}

view raw JSON →