tessl/npm-node-sql-parser

Simple SQL parser library that converts SQL statements to AST and back to SQL with support for multiple database engines

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Securityby

Pending

The risk profile of this skill

Overview

Eval results

Files

SQL Parsing

Name: tessl/npm-node-sql-parser
Author: tessl

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

Capabilities

Parse Method

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

/**
 * Parse SQL string and return AST with table/column lists
 * @param sql - SQL string to parse
 * @param opt - Optional configuration object
 * @returns Object containing tableList, columnList, and ast
 */
parse(sql: string, opt?: Option): TableColumnAst;

interface TableColumnAst {
  tableList: string[];
  columnList: string[];
  ast: AST[] | AST;
  loc?: LocationRange;
}

interface Option {
  database?: string;
  type?: string;
  trimQuery?: boolean;
  parseOptions?: ParseOptions;
}

interface ParseOptions {
  includeLocations?: boolean;
}

Usage Examples:

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

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

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

// Parse with specific database
const pgResult = parser.parse(
  'SELECT * FROM "user_table"',
  { database: 'PostgreSQL' }
);

AST Generation

Convert SQL strings into Abstract Syntax Trees for programmatic manipulation.

/**
 * Parse SQL string into Abstract Syntax Tree
 * @param sql - SQL string to parse
 * @param opt - Optional configuration object
 * @returns AST object or array of AST objects for multiple statements
 */
astify(sql: string, opt?: Option): AST[] | AST;

Usage Examples:

// Single statement
const ast = parser.astify('SELECT * FROM users');
console.log(ast.type);     // "select"
console.log(ast.columns);  // "*"
console.log(ast.from);     // [{ db: null, table: "users", as: null }]

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

// Complex query with joins
const complexAst = parser.astify(`
  SELECT u.name, p.title 
  FROM users u 
  LEFT JOIN posts p ON u.id = p.user_id 
  WHERE u.active = 1
  ORDER BY u.name
`);

SQL Generation

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

/**
 * Convert AST back to SQL string
 * @param ast - AST object or array to convert
 * @param opt - Optional configuration object
 * @returns SQL string representation
 */
sqlify(ast: AST[] | AST, opt?: Option): string;

Usage Examples:

// Basic conversion
const ast = parser.astify('SELECT * FROM users');
const sql = parser.sqlify(ast);
console.log(sql); // "SELECT * FROM `users`" (MySQL format)

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

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

// Multiple statements
const multipleAsts = parser.astify('SELECT * FROM users; SELECT * FROM products;');
const multipleSql = parser.sqlify(multipleAsts);
console.log(multipleSql); // Combined SQL string

Query Trimming

Control automatic trimming of whitespace from SQL queries.

interface Option {
  trimQuery?: boolean; // Default: true
}

Usage Examples:

// Default behavior (trimQuery: true)
const ast1 = parser.astify('  SELECT * FROM users  ');

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

Location Tracking

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

interface ParseOptions {
  includeLocations?: boolean;
}

interface LocationRange {
  start: Location;
  end: Location;
}

interface Location {
  offset: number;
  line: number;
  column: number;
}

Usage Examples:

const ast = parser.astify(
  'SELECT name FROM users', 
  { parseOptions: { includeLocations: true } }
);

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

AST Structure

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

SELECT Statement AST

interface Select {
  with: With[] | null;
  type: "select";
  options: any[] | null;
  distinct: "DISTINCT" | null;
  columns: any[] | Column[];
  from: From[] | TableExpr | null;
  where: Binary | Function | null;
  groupby: { columns: ColumnRef[] | null, modifiers: ValueExpr<string>[] };
  having: any[] | null;
  orderby: OrderBy[] | null;
  limit: Limit | null;
  window?: WindowExpr;
  qualify?: any[] | null;
  _orderby?: OrderBy[] | null;
  _limit?: Limit | null;
  parentheses_symbol?: boolean;
  _parentheses?: boolean;
  loc?: LocationRange;
  _next?: Select;
  set_op?: string;
}

INSERT/REPLACE Statement AST

interface Insert_Replace {
  type: "replace" | "insert";
  table: any;
  columns: string[] | null;
  values: InsertReplaceValue[] | Select;
  partition: any[];
  prefix: string;
  on_duplicate_update: {
    keyword: "on duplicate key update";
    set: SetList[];
  };
  loc?: LocationRange;
  returning?: Returning;
}

UPDATE Statement AST

interface Update {
  type: "update";
  db: string | null;
  table: Array<From | Dual> | null;
  set: SetList[];
  where: Binary | Function | null;
  loc?: LocationRange;
  returning?: Returning;
}

DELETE Statement AST

interface Delete {
  type: "delete";
  table: any;
  from: Array<From | Dual>;
  where: Binary | Function | null;
  loc?: LocationRange;
  returning?: Returning;
}

Error Handling

The parser throws errors for invalid SQL syntax:

try {
  const ast = parser.astify('INVALID SQL SYNTAX');
} catch (error) {
  console.error('Parse error:', error.message);
}

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