CtrlK
BlogDocsLog inGet started
Tessl Logo

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

SecuritybySnyk

Pending

The risk profile of this skill

Overview
Eval results
Files

expressions.mddocs/

Expression Processing

Specialized functionality for handling SQL expressions, column references, and complex query components. This includes converting expression ASTs to SQL strings and processing column definitions.

Capabilities

Expression to SQL Conversion

Convert individual expression AST nodes back to SQL strings.

/**
 * Convert expression AST to SQL string
 * @param expr - Expression AST to convert
 * @param opt - Optional configuration object
 * @returns SQL string representation of the expression
 */
exprToSQL(expr: any, opt?: Option): string;

Usage Examples:

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

// Parse a query to get expression ASTs
const ast = parser.astify('SELECT name, age + 10 FROM users WHERE active = true');

// Convert individual expressions
const columnExpr = ast.columns[1].expr; // age + 10 expression
const exprSql = parser.exprToSQL(columnExpr);
console.log(exprSql); // "(`age` + 10)"

const whereExpr = ast.where; // active = true expression  
const whereSql = parser.exprToSQL(whereExpr);
console.log(whereSql); // "(`active` = TRUE)"

// Database-specific expression conversion
const pgExprSql = parser.exprToSQL(columnExpr, { database: 'PostgreSQL' });
console.log(pgExprSql); // "(\"age\" + 10)"

Column Processing

Convert column AST objects to SQL strings with table context awareness.

/**
 * Convert column AST objects to SQL strings
 * @param columns - Column objects to convert
 * @param tables - Table context for column resolution
 * @param opt - Optional configuration object
 * @returns Array of SQL column strings
 */
columnsToSQL(columns: any, tables: any, opt?: Option): string[];

Usage Examples:

// Parse query to get column and table information
const ast = parser.astify('SELECT u.name, u.email, p.title FROM users u JOIN posts p ON u.id = p.user_id');

// Convert columns with table context
const columns = ast.columns;
const tables = ast.from;
const columnSqls = parser.columnsToSQL(columns, tables);
console.log(columnSqls); 
// ["u.name", "u.email", "p.title"]

// Handle wildcard columns
const wildcardAst = parser.astify('SELECT * FROM users');
const wildcardColumns = parser.columnsToSQL(wildcardAst.columns, wildcardAst.from);
console.log(wildcardColumns); // []  (wildcard returns empty array)

// Handle computed columns
const computedAst = parser.astify('SELECT name, age * 2 as double_age FROM users');
const computedColumns = parser.columnsToSQL(computedAst.columns, computedAst.from);
console.log(computedColumns); // Returns array of SQL column strings

Expression Types

Binary Expressions

Represent operators between two operands (arithmetic, comparison, logical).

interface Binary {
  type: "binary_expr";
  operator: string;
  left: ExpressionValue | ExprList;
  right: ExpressionValue | ExprList;
  loc?: LocationRange;
  parentheses?: boolean;
}

Usage Examples:

// Parse binary expressions
const ast = parser.astify('SELECT * FROM users WHERE age >= 18 AND status = "active"');
const whereExpr = ast.where; // Binary expression with AND operator

console.log(whereExpr.type);     // "binary_expr"
console.log(whereExpr.operator); // "AND"
console.log(whereExpr.left);     // Binary expression for "age >= 18"
console.log(whereExpr.right);    // Binary expression for 'status = "active"'

// Convert back to SQL
const sql = parser.exprToSQL(whereExpr);
console.log(sql); // "((`age` >= 18) AND (`status` = 'active'))"

Function Expressions

Represent function calls in SQL expressions.

interface Function {
  type: "function";
  name: FunctionName;
  args?: ExprList;
  suffix?: any;
  loc?: LocationRange;
}

type FunctionName = {
  schema?: { value: string; type: string };
  name: ValueExpr<string>[];
};

Usage Examples:

// Parse function expressions
const ast = parser.astify('SELECT UPPER(name), COUNT(*) FROM users GROUP BY name');

const upperFunc = ast.columns[0].expr; // UPPER(name) function
console.log(upperFunc.type); // "function"
console.log(upperFunc.name); // Function name details

const countFunc = ast.columns[1].expr; // COUNT(*) function
console.log(countFunc.name); // COUNT function details
console.log(countFunc.args); // Arguments list

// Convert function expression to SQL
const funcSql = parser.exprToSQL(upperFunc);
console.log(funcSql); // "UPPER(`name`)"

Aggregate Functions

Specialized function expressions for SQL aggregate operations.

interface AggrFunc {
  type: "aggr_func";
  name: string;
  args: {
    expr: ExpressionValue;
    distinct: "DISTINCT" | null;
    orderby: OrderBy[] | null;
    parentheses?: boolean;
  };
  loc?: LocationRange;
}

Usage Examples:

// Parse aggregate functions
const ast = parser.astify('SELECT COUNT(DISTINCT user_id), SUM(amount) FROM orders');

const countDistinct = ast.columns[0].expr;
console.log(countDistinct.type); // "aggr_func"
console.log(countDistinct.name); // "COUNT"
console.log(countDistinct.args.distinct); // "DISTINCT"

const sumFunc = ast.columns[1].expr;
console.log(sumFunc.name); // "SUM"
console.log(sumFunc.args.expr); // Expression for 'amount'

// Convert aggregate to SQL
const aggrSql = parser.exprToSQL(countDistinct);
console.log(aggrSql); // "COUNT(DISTINCT `user_id`)"

Case Expressions

Represent CASE WHEN conditional expressions.

interface Case {
  type: "case";
  expr: null;
  args: Array<
    | {
        cond: Binary;
        result: ExpressionValue;
        type: "when";
      }
    | {
        result: ExpressionValue;
        type: "else";
      }
  >;
}

Usage Examples:

// Parse CASE expressions
const ast = parser.astify(`
  SELECT 
    name,
    CASE 
      WHEN age < 18 THEN 'Minor'
      WHEN age >= 65 THEN 'Senior'
      ELSE 'Adult'
    END as category
  FROM users
`);

const caseExpr = ast.columns[1].expr;
console.log(caseExpr.type); // "case"
console.log(caseExpr.args.length); // 3 (two WHEN conditions + ELSE)
console.log(caseExpr.args[0].type); // "when"
console.log(caseExpr.args[2].type); // "else"

// Convert CASE to SQL
const caseSql = parser.exprToSQL(caseExpr);
console.log(caseSql); // Full CASE expression SQL

Cast Expressions

Represent type casting operations.

interface Cast {
  type: "cast";
  keyword: "cast";
  expr: ExpressionValue;
  symbol: "as";
  target: {
    dataType: string;
    quoted?: string;
  }[];
}

Usage Examples:

// Parse CAST expressions
const ast = parser.astify('SELECT CAST(price AS DECIMAL(10,2)) FROM products');

const castExpr = ast.columns[0].expr;
console.log(castExpr.type); // "cast"
console.log(castExpr.keyword); // "cast"
console.log(castExpr.expr); // Expression being cast (price)
console.log(castExpr.target); // Target data type info

// Convert CAST to SQL
const castSql = parser.exprToSQL(castExpr);
console.log(castSql); // "CAST(`price` AS DECIMAL(10, 2))"

Column References

Represent references to table columns with optional table qualification.

interface ColumnRefItem {
  type: "column_ref";
  table: string | null;
  column: string | { expr: ValueExpr };
  options?: ExprList;
  loc?: LocationRange;
  collate?: { collate: CollateExpr } | null;
  order_by?: SortDirection | null;
}

interface ColumnRefExpr {
  type: "expr";
  expr: ColumnRefItem;
  as: string | null;
}

type ColumnRef = ColumnRefItem | ColumnRefExpr;

Usage Examples:

// Parse column references
const ast = parser.astify('SELECT u.name, email FROM users u');

const qualifiedCol = ast.columns[0].expr; // u.name
console.log(qualifiedCol.type); // "column_ref"
console.log(qualifiedCol.table); // "u"
console.log(qualifiedCol.column); // "name"

const unqualifiedCol = ast.columns[1].expr; // email
console.log(unqualifiedCol.table); // null
console.log(unqualifiedCol.column); // "email"

// Convert column reference to SQL
const colSql = parser.exprToSQL(qualifiedCol);
console.log(colSql); // "`u`.`name`"

Value Expressions

Represent literal values in SQL expressions.

interface ValueExpr<T = string | number | boolean> {
  type: "backticks_quote_string" | "string" | "regex_string" | "hex_string" | 
        "full_hex_string" | "natural_string" | "bit_string" | "double_quote_string" | 
        "single_quote_string" | "boolean" | "bool" | "null" | "star" | "param" | 
        "origin" | "date" | "datetime" | "default" | "time" | "timestamp" | "var_string";
  value: T;
}

Usage Examples:

// Parse various value types
const ast = parser.astify("SELECT 'hello', 42, true, null FROM dual");

const stringVal = ast.columns[0].expr;
console.log(stringVal.type); // "single_quote_string"
console.log(stringVal.value); // "hello"

const numberVal = ast.columns[1].expr;
console.log(numberVal.type); // "number"
console.log(numberVal.value); // 42

const boolVal = ast.columns[2].expr;
console.log(boolVal.type); // "bool"
console.log(boolVal.value); // true

const nullVal = ast.columns[3].expr;
console.log(nullVal.type); // "null"
console.log(nullVal.value); // null

Expression Lists

Handle arrays of expressions in contexts like function arguments or IN clauses.

interface ExprList {
  type: "expr_list";
  value: ExpressionValue[];
  loc?: LocationRange;
  parentheses?: boolean;
  separator?: string;
}

Usage Examples:

// Parse expression lists
const ast = parser.astify('SELECT * FROM users WHERE id IN (1, 2, 3)');

const inExpr = ast.where.right; // Expression list for IN clause
console.log(inExpr.type); // "expr_list"
console.log(inExpr.value.length); // 3
console.log(inExpr.parentheses); // true

// Function with multiple arguments
const funcAst = parser.astify('SELECT SUBSTRING(name, 1, 5) FROM users');
const funcArgs = funcAst.columns[0].expr.args;
console.log(funcArgs.type); // "expr_list"
console.log(funcArgs.value.length); // 3 arguments

docs

expressions.md

index.md

security.md

sql-parsing.md

utilities.md

tile.json