node-sql-parser

You can target a specific database dialect in two ways:

1. Direct Import: Import the specific parser from node-sql-parser/build/{database}.
2. Options Object: Pass an options object containing the database key to the astify or sqlify methods.

// Method 1: Direct Import
const { Parser } = require('node-sql-parser/build/transactsql');
const parser = new Parser();
const ast = parser.astify('SELECT id FROM test AS result');
console.log(parser.sqlify(ast)); // SELECT [id] FROM [test] AS [result]

// Method 2: Options Object
const { Parser } = require('node-sql-parser');
const parser = new Parser();
const opt = { database: 'Postgresql' };
const ast = parser.astify('SELECT * FROM t', opt);

You can install the package via npm, yarn, or the GitHub Package Registry. For browser usage, you can include the UMD builds via unpkg.

# Using npm
npm install node-sql-parser --save

# Using yarn
yarn add node-sql-parser

# Using GitHub Package Registry
npm install @taozhi8833998/node-sql-parser --registry=https://npm.pkg.github.com/

To use the parser in a web page, import the UMD script. You can load the full parser (approx. 750K) or a specific database parser (approx. 150K) to save bandwidth. The NodeSQLParser object will be available on the window object.

<!-- Load a specific database parser (e.g., MySQL) -->
<script src="https://unpkg.com/node-sql-parser/umd/mysql.umd.js"></script>
<script>
  window.onload = function () {
    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>

The parse method returns an object containing the tableList, columnList, and the ast all at once.

const { Parser } = require('node-sql-parser/build/mariadb');
const parser = new Parser();
const opt = { database: 'MariaDB' };
const { tableList, columnList, ast } = parser.parse('SELECT * FROM t', opt);

Use the sqlify method to convert an AST back into a SQL string. You should pass an options object specifying the target database to ensure the correct syntax is used.

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const opt = { database: 'MySQL' };

const ast = parser.astify('SELECT * FROM t', opt);
const sql = parser.sqlify(ast, opt);
console.log(sql); // SELECT * FROM `t`

Use tableList and columnList to identify which tables and columns are referenced in a SQL statement.

Table List Format: {type}::{dbName}::{tableName}

• type is one of: select, update, delete, or insert.
• Supports database/schema prefixes (e.g., dbName::tableName or dbName.schemaName::tableName).

Column List Format: {type}::{tableName}::{columnName}

const { Parser } = require('node-sql-parser/build/mysql');
const parser = new Parser();
const opt = { database: 'MySQL' };

// Get tables
const tableList = parser.tableList('SELECT * FROM t', opt);
// Output: ["select::null::t"]

// Get columns
const columnList = parser.columnList('SELECT t.id FROM t', opt);
// Output: ["select::t::id"]

Use the astify method of the Parser class to convert a SQL string into an Abstract Syntax Tree (AST). By default, it uses MySQL grammar. You can also request node locations (line, column, offset) by passing includeLocations: true in parseOptions.

const { Parser } = require('node-sql-parser');
const parser = new Parser();

// Basic AST generation
const ast = parser.astify('SELECT * FROM t');

// AST generation with node locations
const astWithLoc = parser.astify('SELECT * FROM t', { 
  parseOptions: { includeLocations: true } 
});

The whiteListCheck method validates if the tables or columns used in a SQL statement match a provided list of allowed authorities (regex patterns). If the check fails, it throws an error.

• To check tables: set opt.type = 'table'.
• To check columns: set opt.type = 'column'.

const { Parser } = require('node-sql-parser');
const parser = new Parser();
const sql = 'UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)';

// Check table authority
const whiteTableList = ['(select|update)::(.*)::(a|b)'];
parser.whiteListCheck(sql, whiteTableList, { database: 'MySQL', type: 'table' });

// Check column authority
const whiteColumnList = ['select::null::name', 'update::a::id'];
parser.whiteListCheck(sql, whiteColumnList, { database: 'MySQL', type: 'column' });

These helper methods allow you to quickly extract specific metadata from a SQL string without manually traversing the AST.

• tableList(sql, opt): Returns the tableList from the parsed result.
• columnList(sql, opt): Returns the columnList from the parsed result.

Parameters:

• sql (string): The SQL statement.
• opt (object): Configuration options (e.g., { database: 'mysql' }).

import Parser from 'node-sql-parser';

const parser = new Parser();
const sql = 'SELECT name, age FROM users';

const tables = parser.tableList(sql, { database: 'mysql' });
const columns = parser.columnList(sql, { database: 'mysql' });

The sqlify method converts an Abstract Syntax Tree (AST) back into a formatted SQL string.

Parameters:

• ast (object): The AST object to convert.
• opt (object): Configuration options. Defaults to DEFAULT_OPT.

Returns:

• (string): The generated SQL string.

import Parser from 'node-sql-parser';

const parser = new Parser();
const ast = { /* ... your AST object ... */ };
const sql = parser.sqlify(ast, { database: 'mysql' });

The library automatically attaches itself to the global scope in browser-like environments to support direct script inclusion or Web Workers.

• In a Web Worker, the API is available via self.NodeSQLParser.
• In a Browser window, the API is available via window.NodeSQLParser.

// In a browser script or Web Worker
const parser = new self.NodeSQLParser.Parser();