Check console to see the output
``` -------------------------------- ### Create AST for SQL statement using require Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/README.md Import the Parser class using require and create an AST from a SQL string. This example uses the default MySQL grammar. ```javascript // import Parser for all databases const { Parser } = require('node-sql-parser'); const parser = new Parser(); const ast = parser.astify('SELECT * FROM t'); // mysql sql grammer parsed by default console.log(ast); ``` -------------------------------- ### Get Parser Options Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Retrieves the current global parser configuration. Useful for inspecting the active database dialect or other settings. ```javascript const { util } = require('node-sql-parser'); const opts = util.getParserOpt(); console.log(opts.database); // 'mysql' ``` -------------------------------- ### SQL to AST Conversion Example Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md Demonstrates parsing a SQL SELECT statement into its Abstract Syntax Tree (AST) representation. The AST shows the hierarchical structure of the SQL query, including statement type, columns, FROM clause, and WHERE clause. ```sql SELECT id, name FROM users WHERE age > 18 ``` ```json { type: 'select', columns: [ { expr: { type: 'column_ref', column: 'id' } }, { expr: { type: 'column_ref', column: 'name' } } ], from: [{ table: 'users', db: null }], where: { type: 'binary_expr', operator: '>', left: { type: 'column_ref', column: 'age' }, right: { type: 'number', value: 18 } } } ``` -------------------------------- ### SQL Access Control Whitelist Check Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md Shows how to perform access control checks on a SQL query against a defined whitelist of allowed operations. This example checks for table-level access. ```javascript const whitelist = [ '(select|update)::(.*)::(users|orders)', 'delete::(.*)::(logs)' ]; try { parser.whiteListCheck(sql, whitelist, { type: 'table' }); // Access allowed } catch (err) { // Access denied console.error(err.message); } ``` -------------------------------- ### Utility Functions for Parser State Management Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md These functions allow you to get and set the configuration options for the SQL parser. Use them to customize parsing behavior. ```javascript getParserOpt(): Option setParserOpt(opt: Option): void ``` -------------------------------- ### Parse SQL to Get TableList, ColumnList, and AST Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/README.md Use the `parse` function to simultaneously obtain the list of tables, columns, and the AST for a given SQL query. Database-specific options can be provided. ```javascript const opt = { database: 'MariaDB' // MySQL is the default database } const { Parser } = require('node-sql-parser/build/mariadb'); const parser = new Parser() // opt is optional const { tableList, columnList, ast } = parser.parse('SELECT * FROM t', opt); ``` -------------------------------- ### Parse SQL to AST, Table List, and Column List Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/parser.md Use the `parse` function to get a comprehensive analysis of a SQL statement, including its Abstract Syntax Tree (AST), a list of tables, and a list of columns. This is useful for understanding the structure and components of a query. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); 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'] console.log(result.ast.type); // 'select' ``` -------------------------------- ### Handling Invalid Value Type for Expression Creation Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/errors.md Demonstrates how to catch errors when `createValueExpr` is called with unsupported data types like objects. It shows the correct way to handle such errors and provides examples of supported types. ```javascript const { util } = require('node-sql-parser'); try { // Trying to convert an object (not supported) util.createValueExpr({ nested: 'object' }); } catch (error) { if (error.message.includes('Cannot convert value')) { console.error('Unsupported value type for expression'); } } ``` ```javascript // Instead of object, pass individual values util.createValueExpr('string value'); // Works util.createValueExpr(42); // Works util.createValueExpr([1, 2, 3]); // Works ``` -------------------------------- ### Catching Column Authority Check Failure Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/errors.md This example shows how to catch errors when a column is not permitted by the whitelist in column mode authority checks. It includes extracting the specific denied column authority. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); const sql = 'SELECT id, email, password FROM users'; // Only allow id and email const whitelist = [ 'select::users::id', 'select::users::email' ]; try { parser.whiteListCheck(sql, whitelist, { type: 'column' }); } catch (error) { if (error.message.includes('is required in column whiteList')) { const match = error.message.match(/authority = '([^']+)'/); const deniedColumn = match ? match[1] : 'unknown'; console.error(`Column access denied: ${deniedColumn}`); // Example: "Column access denied: select::users::password" } } ``` -------------------------------- ### Basic SQL Parsing and Formatting Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md Shows how to initialize the Parser, convert a SQL string into an AST, and then convert the AST back into SQL, with specific formatting for PostgreSQL. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); // Parse SQL to AST const ast = parser.astify('SELECT * FROM users WHERE id = 1'); // Convert AST back to SQL (with database-specific formatting) const sql = parser.sqlify(ast, { database: 'PostgreSQL' }); ``` -------------------------------- ### Import and Log Default Options Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/configuration.md Demonstrates how to import the DEFAULT_OPT constant from the util module and log its value. ```javascript const { util } = require('node-sql-parser'); console.log(util.DEFAULT_OPT); // { // database: 'mysql', // type: 'table', // trimQuery: true, // parseOptions: { includeLocations: false } // } ``` -------------------------------- ### Location Range Type Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/types.md Represents position information for AST nodes, including start and end locations within the source SQL. ```typescript interface LocationRange { source?: string; // Optional source identifier start: Location; end: Location; } interface Location { line: number; // Line number (1-based) column: number; // Column number (1-based) offset: number; // Byte offset from start } ``` -------------------------------- ### Column-Level Access Control Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/configuration.md Checks if specified columns in a SQL query are allowed based on a whitelist. Includes an example of handling access denied errors. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); const sql = 'SELECT id, email FROM users WHERE id = 1'; const allowedColumns = [ 'select::users::id', 'select::users::email' ]; parser.whiteListCheck(sql, allowedColumns, { type: 'column' }); // Passes: all accessed columns are allowed const restrictedColumns = ['select::users::id']; try { parser.whiteListCheck(sql, restrictedColumns, { type: 'column' }); } catch (e) { console.log(e.message); // Error: authority = 'select::users::email' is required in column whiteList } ``` -------------------------------- ### Combining and Reusing Options Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/configuration.md Shows how to define and reuse a configuration object for multiple SQL parsing operations. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); // Define reusable config const pgConfig = { database: 'PostgreSQL', parseOptions: { includeLocations: true } }; const ast1 = parser.astify('SELECT * FROM users', pgConfig); const ast2 = parser.astify('SELECT * FROM orders', pgConfig); // Both use the same configuration ``` -------------------------------- ### Create a New Parser Instance Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/parser.md Initializes a new instance of the SQL parser. Configuration options are provided during method calls, not at instantiation. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); ``` -------------------------------- ### Get Node Location in AST Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/README.md Generate an AST with location information for each node. This is useful for understanding the structure and precise positioning of elements within the SQL query. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); const ast = parser.astify('SELECT * FROM t', { parseOptions: { includeLocations: true } }); console.log(ast); ``` ```json { "with": null, "type": "select", "options": null, "distinct": null, "columns": [ { "expr": { "type": "column_ref", "table": null, "column": "*" }, "as": null, "loc": { "start": { "offset": 7, "line": 1, "column": 8 }, "end": { "offset": 8, "line": 1, "column": 9 } } } ], "into": { "position": null }, "from": [ { "db": null, "table": "t", "as": null, "loc": { "start": { "offset": 14, "line": 1, "column": 15 }, "end": { "offset": 15, "line": 1, "column": 16 } } } ], "where": null, "groupby": null, "having": null, "orderby": null, "limit": null, "locking_read": null, "window": null, "loc": { "start": { "offset": 0, "line": 1, "column": 1 }, "end": { "offset": 15, "line": 1, "column": 16 } } } ``` -------------------------------- ### Parse SQL with Different Dialects Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/configuration.md Demonstrates how to parse SQL strings using different database dialects and how to convert an AST to a specific dialect's SQL string. Ensure the correct dialect is specified for accurate parsing and conversion. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); // Parse MySQL syntax parser.astify('SELECT * FROM users', { database: 'MySQL' }); // Parse PostgreSQL syntax parser.astify('SELECT * FROM "users"', { database: 'PostgreSQL' }); // Convert to different dialect const ast = parser.astify('SELECT * FROM users'); const mysqlSql = parser.sqlify(ast, { database: 'MySQL' }); const pgSql = parser.sqlify(ast, { database: 'PostgreSQL' }); ``` -------------------------------- ### Multi-Database SQL Formatting Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md Illustrates converting an AST into SQL strings formatted for different database systems, specifically MySQL and PostgreSQL, from a common AST. ```javascript // Parse with one database, convert to another const ast = parser.astify('SELECT * FROM users'); const mysqlSql = parser.sqlify(ast, { database: 'MySQL' }); // SELECT * FROM `users` const pgSql = parser.sqlify(ast, { database: 'PostgreSQL' }); // SELECT FROM "users" ``` -------------------------------- ### Importing Parser Class Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md Illustrates different ways to import the Parser class from the node-sql-parser library, including main exports and database-specific bundles. ```javascript // Main exports const { Parser } = require('node-sql-parser'); const { Parser, util } = require('node-sql-parser'); // Database-specific (smaller bundles) const { Parser } = require('node-sql-parser/build/postgresql'); const { Parser } = require('node-sql-parser/build/mysql'); // Browser (UMD) ``` -------------------------------- ### Get SQL Visited Columns Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/README.md Extract a list of columns referenced in a SQL query. The format is 'type::tableName::columnName'. Special handling is needed for wildcard selections like `SELECT *`. ```javascript const opt = { database: 'MySQL' } const { Parser } = require('node-sql-parser/build/mysql'); const parser = new Parser(); // opt is optional const columnList = parser.columnList('SELECT t.id FROM t', opt); console.log(columnList); // ["select::t::id"] ``` -------------------------------- ### Validate Configuration Before Parsing Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/errors.md This pattern shows how to validate database configuration options before attempting to parse SQL. It throws a custom error if an unsupported database type is provided. ```javascript const { Parser } = require('node-sql-parser'); const SUPPORTED_DATABASES = [ 'mysql', 'postgresql', 'sqlite', 'transactsql' ]; function parseSQL(sql, options = {}) { if (options.database && !SUPPORTED_DATABASES.includes(options.database.toLowerCase())) { throw new Error(`Database ${options.database} is not supported`); } const parser = new Parser(); return parser.astify(sql, options); } ``` -------------------------------- ### Parameterized Query Replacement Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md Demonstrates parsing a SQL query with named parameters, replacing those parameters with actual values, and then converting the modified AST back into a SQL string. ```javascript const ast = parser.astify('SELECT * FROM users WHERE id = :id AND name = :name'); // Replace parameters const newAst = util.replaceParams(ast, { id: 123, name: 'John' }); // Convert back to SQL const finalSql = parser.sqlify(newAst); ``` -------------------------------- ### Get SQL Visited Tables Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/README.md Retrieve a list of tables accessed by a SQL query. The format is 'type::dbName::tableName', indicating the operation type and the table's fully qualified name. ```javascript const opt = { database: 'MySQL' } const { Parser } = require('node-sql-parser/build/mysql'); const parser = new Parser(); // opt is optional const tableList = parser.tableList('SELECT * FROM t', opt); console.log(tableList); // ["select::null::t"] ``` -------------------------------- ### Multi-Database SQL Conversion Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/configuration.md Demonstrates parsing a SQL query and then converting the resulting AST to different SQL dialects like MySQL, PostgreSQL, and TransactSQL. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); const sql = 'SELECT * FROM users'; // Parse as MySQL (works for all dialects) const ast = parser.astify(sql); // Convert to different dialects const mysqlSql = parser.sqlify(ast, { database: 'MySQL' }); // SELECT * FROM `users` const pgSql = parser.sqlify(ast, { database: 'PostgreSQL' }); // SELECT * FROM "users" const tsSql = parser.sqlify(ast, { database: 'TransactSQL' }); // SELECT * FROM [users] ``` -------------------------------- ### sqlify(ast, opt) Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/parser.md Converts an Abstract Syntax Tree (AST) back into a SQL string. It supports different database dialects for formatting identifiers. ```APIDOC ## sqlify(ast, opt) ### Description Converts an Abstract Syntax Tree (AST) back into a SQL string. It supports different database dialects for formatting identifiers. ### Method `sqlify(ast: AST[] | AST, opt?: Option): string` ### Parameters #### Path Parameters - **ast** (AST[] | AST) - Required - The AST object or array of AST objects to convert - **opt** (Option) - Optional - Database dialect configuration ### Request Example ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); const ast = parser.astify('SELECT * FROM users'); // Generate MySQL syntax const mysqlSql = parser.sqlify(ast, { database: 'MySQL' }); // Output: SELECT * FROM `users` // Generate PostgreSQL syntax const pgSql = parser.sqlify(ast, { database: 'PostgreSQL' }); // Output: SELECT * FROM "users" // Generate TransactSQL syntax const tsSql = parser.sqlify(ast, { database: 'TransactSQL' }); // Output: SELECT * FROM [users] ``` ### Response #### Success Response - **string** - SQL statement with database-specific syntax ``` -------------------------------- ### Configuration Options Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md Options that can be passed to parser methods to customize behavior, such as specifying the target database or parsing preferences. ```APIDOC ## Configuration Options ### `Option` Interface ```typescript interface Option { database?: string; // Specifies the target database (e.g., 'mysql', 'PostgreSQL'). Defaults to 'mysql'. type?: string; // Specifies the type for access control checks (e.g., 'table'). Defaults to 'table'. trimQuery?: boolean; // If true, trims whitespace from the query. Defaults to true. parseOptions?: { includeLocations?: boolean }; // Options for the underlying parser, such as including source code locations. Defaults to { includeLocations: false }. } ``` ``` -------------------------------- ### Parse SQL for a Specified Database (Import) Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/README.md Import a parser specifically for a database dialect to handle its unique syntax. This ensures accurate parsing and SQL generation for that database. ```javascript // import transactsql parser only const { Parser } = require('node-sql-parser/build/transactsql') const parser = new Parser() const sql = `SELECT id FROM test AS result` const ast = parser.astify(sql) console.log(parser.sqlify(ast)) // SELECT [id] FROM [test] AS [result] ``` -------------------------------- ### Parse SQL for a Specified Database (Options) Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/README.md Specify the target database using an options object passed to parsing methods. This allows using the general parser while ensuring dialect-specific behavior. ```javascript const opt = { database: 'Postgresql' } // import all databases parser const { Parser } = require('node-sql-parser') const parser = new Parser() // pass the opt config to the corresponding methods const ast = parser.astify('SELECT * FROM t', opt) const sql = parser.sqlify(ast, opt) console.log(sql); // SELECT * FROM "t" ``` -------------------------------- ### astify(sql, opt) Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/parser.md Parses a SQL string into an Abstract Syntax Tree (AST). It can handle single or multiple SQL statements and supports various database dialects. Options can be provided to include location information or specify the database dialect. ```APIDOC ## astify(sql, opt) ### Description Parses a SQL string into an Abstract Syntax Tree (AST). It can handle single or multiple SQL statements and supports various database dialects. Options can be provided to include location information or specify the database dialect. ### Method `astify(sql: string, opt?: Option): AST[] | AST` ### Parameters #### Path Parameters - **sql** (string) - Required - SQL statement or multiple statements separated by semicolons - **opt** (Option) - Optional - Configuration object with database, trimQuery, and parseOptions ### Request Example ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); // Parse a simple SELECT const ast = parser.astify('SELECT id, name FROM users WHERE age > 18'); // Parse multiple statements const asts = parser.astify( 'SELECT * FROM users; SELECT * FROM orders;' ); // Parse with location information const astWithLoc = parser.astify( 'SELECT * FROM products', { parseOptions: { includeLocations: true } } ); // Parse PostgreSQL-specific syntax const pgAst = parser.astify( 'SELECT * FROM users', { database: 'PostgreSQL' } ); ``` ### Response #### Success Response - **AST[] | AST** - Array of AST objects if multiple statements, single AST object for single statement ``` -------------------------------- ### getParserOpt() Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Retrieves the current global parser options. This function is useful for inspecting the active configuration of the SQL parser. ```APIDOC ## getParserOpt() ### Description Get the current parser options. ### Method ```javascript getParserOpt(): Option ``` ### Return Type - `Option` - Current parser configuration ``` -------------------------------- ### PostgreSQL Parsing with Location Tracking Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/configuration.md Parses a SQL query with PostgreSQL dialect and enables location tracking for AST nodes. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); const ast = parser.astify( 'SELECT id, name FROM users WHERE age > 18', { database: 'PostgreSQL', parseOptions: { includeLocations: true } } ); // Can now access loc property for each node ``` -------------------------------- ### Create Interface Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/types.md Represents an SQL CREATE statement for various objects like tables, indexes, or views. Use this for defining new database objects. ```typescript interface Create { type: "create"; keyword: "aggregate" | "table" | "trigger" | "extension" | "function" | "index" | "database" | "schema" | "view" | "domain" | "type" | "user"; temporary?: "temporary" | null; table?: { db: string; table: string }[] | { db: string | null; table: string }; if_not_exists?: "if not exists" | null; like?: { type: "like"; table: string; parentheses?: boolean } | null; ignore_replace?: "ignore" | "replace" | null; as?: string | null; query_expr?: any | null; // SELECT for CREATE TABLE AS create_definitions?: CreateDefinition[] | null; table_options?: any[] | null; index_using?: { keyword: "using"; type: "btree" | "hash" } | null; index?: string | null | { schema: string | null; name: string }; on_kw?: "on" | null; index_columns?: ColumnRefItem[] | null; index_type?: "unique" | "fulltext" | "spatial" | null; index_options?: any[] | null; algorithm_option?: { type: "alter"; keyword: "algorithm"; resource: "algorithm"; symbol: "=" | null; algorithm: "default" | "instant" | "inplace" | "copy"; } | null; lock_option?: { type: "alter"; keyword: "lock"; resource: "lock"; symbol: "=" | null; lock: "default" | "none" | "shared" | "exclusive"; } | null; database?: string; loc?: LocationRange; where?: Binary | Function | null; } ``` -------------------------------- ### Use Interface Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/types.md Represents an SQL USE statement. Use this to specify the database to be used for subsequent operations. ```typescript interface Use { type: "use"; db: string; loc?: LocationRange; } ``` -------------------------------- ### Accessing Default Parser Options Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Logs the default parser configuration object to the console. This object defines default settings for database type, query trimming, and parsing options. ```javascript const { Parser, util } = require('node-sql-parser'); console.log(util.DEFAULT_OPT); // { // database: 'mysql', // type: 'table', // trimQuery: true, // parseOptions: { includeLocations: false } // } ``` -------------------------------- ### Creating a Logical AND Binary Expression Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Constructs a binary expression AST node for a logical AND operation. Requires two expression AST nodes as operands. ```javascript const { util } = require('node-sql-parser'); // Logical AND util.createBinaryExpr('AND', expr1, expr2); ``` -------------------------------- ### Creating a Simple Binary Expression Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Constructs a binary expression AST node for a simple comparison. Primitive values are automatically wrapped into value expressions. ```javascript const { util } = require('node-sql-parser'); // Simple comparison util.createBinaryExpr('=', { type: 'column_ref', column: 'age' }, 18); // { // type: 'binary_expr', // operator: '=', // left: { type: 'column_ref', column: 'age' }, // right: { type: 'number', value: 18 } // } ``` -------------------------------- ### Catching Missing Parameter Value Error Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/errors.md Demonstrates how to catch errors when a required parameter is missing during SQL parameter replacement. It shows how to identify and log the name of the missing parameter. ```javascript const { Parser, util } = require('node-sql-parser'); const parser = new Parser(); const ast = parser.astify('SELECT * FROM users WHERE id = :id AND name = :name'); try { const newAst = util.replaceParams(ast, { id: 123 }); // Missing 'name' parameter } catch (error) { if (error.message.includes('no value for parameter')) { const match = error.message.match(/:(\w+)/); const missingParam = match ? match[1] : 'unknown'; console.error(`Missing parameter: ${missingParam}`); } } ``` -------------------------------- ### Include node-sql-parser in HTML (all parsers) Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/README.md Include the full parser library in an HTML page using a script tag from a CDN. This makes the NodeSQLParser object available globally. ```html ``` -------------------------------- ### parse(sql, opt) Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/parser.md Parses a given SQL statement and returns a comprehensive result including the Abstract Syntax Tree (AST), a list of accessed tables, and a list of accessed columns. ```APIDOC ## parse(sql, opt) ### Description Parses a SQL statement and returns a combined result with AST, table list, and column list. ### Method Signature ```javascript parse(sql: string, opt?: Option): TableColumnAst ``` ### Parameters #### Parameters - **sql** (string) - Required - SQL statement to parse - **opt** (Option) - Optional - Configuration object, defaults to `DEFAULT_OPT` ### Return Type #### TableColumnAst - **tableList** (string[]) - List of accessed tables - **columnList** (string[]) - List of accessed columns - **ast** (AST[] | AST) - Parsed AST - **loc** (LocationRange) - Optional location info (if includeLocations is true) ### Example ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); 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'] console.log(result.ast.type); // 'select' ``` ``` -------------------------------- ### connector(keyword, str) Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Combines a keyword and a string with a space in between, ensuring the keyword is in uppercase. Useful for constructing SQL clauses. ```APIDOC ## connector(keyword, str) ### Description Combine a keyword and string with space and uppercase keyword. ### Method ```javascript connector(keyword: string, str: string): string ``` ``` -------------------------------- ### Perform Whitelist Validation Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/configuration.md Illustrates how to use the `whiteListCheck` method for access control, specifying either table-level or column-level validation. The `type` option determines the granularity of the check. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); // Check table-level access const tableWhitelist = ['(select|update)::(.*)::(users|orders)']; parser.whiteListCheck(sql, tableWhitelist, { type: 'table' }); // Check column-level access const columnWhitelist = ['select::users::id', 'select::users::email']; parser.whiteListCheck(sql, columnWhitelist, { type: 'column' }); ``` -------------------------------- ### Utility Functions Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md Helper functions for creating SQL expressions, replacing parameters, and converting data types to SQL format. ```APIDOC ## Utility Functions ### Description Provides utility functions for expression creation, parameter replacement, and SQL conversion. ### Functions - `createValueExpr(value: any): ExpressionValue` - `createBinaryExpr(expr: BinaryExpression): ExpressionValue` - `replaceParams(sql: string, params: any[], options?: Option): string` - `literalToSQL(literal: any, options?: Option): string` - `dataTypeToSQL(dataType: DataType, options?: Option): string` - `quoteIdent(identifier: string, options?: Option): string` ### Parameters - **value**: The value to create an expression from. - **expr**: A BinaryExpression object. - **sql**: The SQL string with parameters. - **params**: An array of parameters to replace. - **literal**: The literal value to convert to SQL. - **dataType**: The DataType object to convert to SQL. - **identifier**: The identifier to quote. - **options** (Option): Optional configuration object. ### Returns - Various types depending on the function, including `ExpressionValue`, `string`, and `void`. ``` -------------------------------- ### commonOptionConnector(keyword, action, opt) Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Conditionally applies a given action function and prefixes the result with a specified keyword. This is helpful for building dynamic SQL query parts. ```APIDOC ## commonOptionConnector(keyword, action, opt) ### Description Conditionally apply a function and add keyword prefix. ### Method ```javascript commonOptionConnector(keyword: string, action: Function, opt: any): string ``` ``` -------------------------------- ### Extracting Table and Column Metadata Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md Demonstrates how to parse a SQL query and extract a list of all tables and columns accessed within it. The output format for tables and columns is specific to the library's access control system. ```javascript // Get all tables and columns accessed const { tableList, columnList } = parser.parse('SELECT id, name FROM users'); // tableList: ['select::null::users'] // columnList: ['select::users::id', 'select::users::name'] ``` -------------------------------- ### Graceful Error Recovery with Fallback Database Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/errors.md Provides a mechanism to attempt SQL parsing with a primary database dialect and fall back to a secondary one if the primary is not supported. It logs a warning when the fallback is used. ```javascript const { Parser } = require('node-sql-parser'); function parseWithFallback(sql, primaryDb, fallbackDb) { const parser = new Parser(); try { return parser.astify(sql, { database: primaryDb }); } catch (error) { if (error.message.includes('not supported currently')) { console.warn(`${primaryDb} failed, trying ${fallbackDb}`); return parser.astify(sql, { database: fallbackDb }); } throw error; // Re-throw other errors } } ``` -------------------------------- ### DEFAULT_OPT Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Provides the default configuration object used throughout the parser for various settings like database type, query trimming, and parsing options. ```APIDOC ## DEFAULT_OPT ### Description Provides the default configuration object used throughout the parser for various settings like database type, query trimming, and parsing options. ### Type ```typescript interface Option { database: string type: string trimQuery: boolean parseOptions: { includeLocations: boolean } } ``` ### Value ```javascript const DEFAULT_OPT = { database: PARSER_NAME || 'mysql', type: 'table', trimQuery: true, parseOptions: { includeLocations: false, }, } ``` ### Example ```javascript const { Parser, util } = require('node-sql-parser'); console.log(util.DEFAULT_OPT); ``` ``` -------------------------------- ### returningToSQL Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Converts a RETURNING clause to SQL, used in databases like PostgreSQL and SQLite. It takes an array of column references. ```APIDOC ## returningToSQL(returning) ### Description Convert RETURNING clause to SQL (PostgreSQL, SQLite). ### Parameters #### Path Parameters - **returning.columns** (ColumnRef[]) - Required - Columns to return ### Request Example ```javascript const { util } = require('node-sql-parser'); util.returningToSQL({ columns: [{ type: 'column_ref', table: null, column: 'id' }] }); // 'RETURNING `id`' ``` ``` -------------------------------- ### Convert AST back to SQL Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/README.md Convert an Abstract Syntax Tree (AST) back into a SQL string. The `database` option can be specified to ensure correct SQL dialect formatting. ```javascript const opt = { database: 'MySQL' // MySQL is the default database } // import mysql parser only const { Parser } = require('node-sql-parser'); const parser = new Parser() // opt is optional const ast = parser.astify('SELECT * FROM t', opt); const sql = parser.sqlify(ast, opt); console.log(sql); // SELECT * FROM `t` ``` -------------------------------- ### Parser Class Methods Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md The main Parser class provides methods for SQL parsing and manipulation. These include converting SQL to AST, AST to SQL, and extracting specific information like table and column names. ```APIDOC ## Parser Class Methods ### Description Provides core functionality for SQL parsing and transformation. ### Methods - `Parser.astify(sql: string, options?: Option): AST` - `Parser.sqlify(ast: AST, options?: Option): string` - `Parser.parse(sql: string, options?: Option): { ast: AST, tableList: string[], columnList: string[] }` - `Parser.tableList(sql: string, options?: Option): string[]` - `Parser.columnList(sql: string, options?: Option): string[]` - `Parser.whiteListCheck(sql: string, options?: Option): void` ### Parameters - **sql** (string) - The SQL statement to process. - **options** (Option) - Optional configuration object for the parser. ### Returns - `astify`: Returns the Abstract Syntax Tree (AST) representation of the SQL. - `sqlify`: Returns the SQL string generated from the AST. - `parse`: Returns an object containing the AST, a list of table names, and a list of column names. - `tableList`: Returns an array of table names found in the SQL. - `columnList`: Returns an array of column names found in the SQL. - `whiteListCheck`: Returns void if the access control check passes, throws an error otherwise. ``` -------------------------------- ### topToSQL Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Converts TOP clause options into an SQL string, primarily for TransactSQL. It handles row counts, percentages, and WITH TIES. ```APIDOC ## topToSQL(opt) ### Description Convert TOP clause options to SQL (used in TransactSQL). ### Parameters #### Path Parameters - **opt.value** (number) - Required - Number of rows - **opt.percent** (string) - Optional - Optional PERCENT keyword - **opt.parentheses** (boolean) - Optional - If true, wraps value in parens - **opt.with_ties** (boolean) - Optional - If true, adds WITH TIES ### Request Example ```javascript const { util } = require('node-sql-parser'); util.topToSQL({ value: 10 }); // 'TOP 10' util.topToSQL({ value: 10, percent: 'PERCENT', with_ties: true }); // 'TOP 10 PERCENT WITH TIES' ``` ``` -------------------------------- ### Parse Options Configuration Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/types.md Configure parsing behavior with ParseOptions, such as enabling location information for AST nodes. ```typescript interface ParseOptions { includeLocations?: boolean; // Include location info in AST nodes } ``` -------------------------------- ### Parse SQL to AST Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/parser.md Use the astify method to convert SQL strings into Abstract Syntax Trees. Supports single or multiple statements and options for location information or specific database dialects. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); // Parse a simple SELECT const ast = parser.astify('SELECT id, name FROM users WHERE age > 18'); // Parse multiple statements const asts = parser.astify( 'SELECT * FROM users; SELECT * FROM orders;' ); // Parse with location information const astWithLoc = parser.astify( 'SELECT * FROM products', { parseOptions: { includeLocations: true } } ); // Parse PostgreSQL-specific syntax const pgAst = parser.astify( 'SELECT * FROM users', { database: 'PostgreSQL' } ); ``` -------------------------------- ### setParserOpt(opt) Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Sets the global parser options. This function allows you to modify the parser's behavior by providing a new configuration object. Use with caution as it affects the global state. ```APIDOC ## setParserOpt(opt) ### Description Set the parser options globally. ### Method ```javascript setParserOpt(opt: Option): void ``` ### Parameters #### Parameters - **opt** (Option) - Required - Configuration to apply ### Note This affects global parser state. Used internally by Parser methods. ``` -------------------------------- ### Conditionally Apply Function with Keyword Prefix Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Applies a given function to an option and prefixes the result with a specified keyword. This is useful for building SQL statements where a clause might be optional. ```javascript const { util } = require('node-sql-parser'); util.commonOptionConnector('WHERE', exprToSQL, whereExpr); ``` -------------------------------- ### Utility Functions for SQL Conversion Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md These functions convert Abstract Syntax Tree (AST) nodes or specific SQL components into their string representations. They are useful for generating SQL queries from parsed structures. ```javascript literalToSQL(literal: Object): string identifierToSql(ident: string): string dataTypeToSQL(expr: DataType): string commentToSQL(comment: Object): string ``` -------------------------------- ### Utility Functions Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md The `util` object contains helper functions for manipulating ASTs, such as replacing parameters. ```APIDOC ## Utility Functions ### `util.replaceParams(ast: AST, params: object): AST` **Description**: Replaces placeholder parameters within an AST with provided values and returns the modified AST. ``` -------------------------------- ### Include node-sql-parser in HTML (PostgreSQL parser only) Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/README.md Include only the PostgreSQL specific parser in an HTML page from a CDN. This is useful for reducing the size of your application's JavaScript. ```html ``` -------------------------------- ### Utility Functions for Expression Creation Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/INDEX.md These functions are used to programmatically create expression objects for SQL parsing. They help in building Abstract Syntax Tree (AST) nodes. ```javascript createValueExpr(value: any): Object createBinaryExpr(operator: string, left: any, right: any): Object replaceParams(ast: Object, params: Object): Object ``` -------------------------------- ### columnsToSQL Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/parser.md Converts a list of column definitions into SQL strings. It can handle both explicit column definitions and wildcard selections, with optional table context and database-specific formatting. ```APIDOC ## columnsToSQL ### Description Converts column list to SQL string. ### Method `columnsToSQL(columns, tables, opt)` ### Parameters #### Path Parameters None #### Query Parameters None #### Request Body None ### Parameters - **columns** (Column[] | string) - Required - Column objects or '*' for all columns - **tables** (From[] | null) - Optional - Table references for context - **opt** (Option) - Optional - Database configuration ### Request Example ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); const columns = [ { expr: { type: 'column_ref', column: 'id' }, as: null }, { expr: { type: 'column_ref', column: 'name' }, as: 'user_name' } ]; parser.columnsToSQL(columns, null, { database: 'MySQL' }); // Output: ['`id`', '`name` AS user_name'] // With wildcard parser.columnsToSQL('*', null); // Output: [] ``` ### Response #### Success Response (200) - `string[]` - Array of SQL column strings ``` -------------------------------- ### Convert Columns to SQL String Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/parser.md Converts column definitions into SQL string format. Supports basic column references and aliased columns. Use '*' to represent all columns, which results in an empty array. ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); const columns = [ { expr: { type: 'column_ref', column: 'id' }, as: null }, { expr: { type: 'column_ref', column: 'name' }, as: 'user_name' } ]; parser.columnsToSQL(columns, null, { database: 'MySQL' }); // Output: ['`id`', '`name` AS user_name'] // With wildcard parser.columnsToSQL('*', null); // Output: [] ``` -------------------------------- ### exprToSQL(expr, opt) Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/parser.md Converts a single expression object into its SQL string representation. ```APIDOC ## exprToSQL(expr, opt) ### Description Convert a single expression to SQL string. ### Method Signature ```javascript exprToSQL(expr: ExpressionValue, opt?: Option): string ``` ### Parameters #### Parameters - **expr** (ExpressionValue) - Required - Expression object to convert - **opt** (Option) - Optional - Database configuration, defaults to `DEFAULT_OPT` ### Return Type - `string` - SQL representation of the expression ### Supported Expression Types - `binary_expr` - Binary operations (=, !=, <, >, AND, OR, BETWEEN, etc.) - `function` - Function calls - `column_ref` - Column references - `case` - CASE expressions - `cast` - CAST expressions - `aggr_func` - Aggregate functions - `interval` - INTERVAL expressions - `unary_expr` - Unary operations ### Example ```javascript const { Parser } = require('node-sql-parser'); const parser = new Parser(); // Convert a binary expression const binaryExpr = { type: 'binary_expr', operator: '=', left: { type: 'column_ref', table: null, column: 'age' }, right: { type: 'number', value: 18 } }; parser.exprToSQL(binaryExpr, { database: 'MySQL' }); // Output: `age` = 18 ``` ``` -------------------------------- ### replaceParams(ast, params) Source: https://github.com/taozhi8833998/node-sql-parser/blob/master/_autodocs/api-reference/util.md Replaces parameter placeholders within an Abstract Syntax Tree (AST) with provided values. It takes an AST and a parameters object, returning a new AST with the replacements. It throws an error if a parameter is missing a value. ```APIDOC ## replaceParams(ast, params) ### Description Replaces parameter placeholders in an AST with actual values. ### Method `replaceParams(ast: Object, params: Object): Object` ### Parameters #### Path Parameters - **ast** (Object) - Required - The AST to process (cloned internally) - **params** (Object) - Required - Object with parameter names as keys and values to substitute ### Request Example ```javascript const { Parser, util } = require('node-sql-parser'); const parser = new Parser(); const ast = parser.astify('SELECT * FROM users WHERE id = :id AND name = :name'); const newAst = util.replaceParams(ast, { id: 123, name: 'John' }); ``` ### Response #### Success Response (200) - **Object** - New AST with parameters replaced (original is not modified) ### Throws - `Error` - "no value for parameter :{paramName} found" if a parameter lacks a value ```