SQL Parser Mistic

1.2.3 verified Thu Apr 23 auth: no javascript

The `sql-parser-mistic` package provides a lexer and parser for SQL syntax, specifically designed for JavaScript environments. Currently at version 1.2.3, it primarily focuses on parsing basic `SELECT` queries, supporting features like `WHERE`, `GROUP BY`, `ORDER BY`, `LIMIT`, `CASE/WHEN`, `REGEXP`, `ILIKE`, and `BETWEEN` clauses, as well as sub-selects, functions, types, and named parameters. It processes a SQL query string into a stream of tokens using its lexer, then constructs an Abstract Syntax Tree (AST) via its parser, represented by a `Select` object. This package differentiates itself by its lightweight, JavaScript-centric approach to SQL parsing, allowing programmatic manipulation and reformatting of SQL queries. Release cadence appears to be irregular, with recent minor updates addressing parsing fixes and feature additions within the 1.x line.

Common errors

error Syntax Error: unexpected TOKEN at line X column Y ↓

cause The input SQL query contains syntax that is not recognized or supported by the parser, or it is malformed.

fix

Review the SQL query for typos or unsupported SQL features. Ensure it is a valid SELECT statement and uses only the clauses and functions known to be supported by sql-parser-mistic.

error TypeError: Cannot read properties of undefined (reading 'tokenize') ↓

cause The `lexer` object was not correctly imported or accessed. This commonly occurs when using CommonJS `require` syntax in an ESM module context, or vice-versa, or attempting to access an unexported property.

fix

If in an ES module environment, use import { lexer, parser } from 'sql-parser-mistic';. If in a CommonJS environment, ensure the library is correctly transpiled or imported, typically as const { lexer, parser } = require('sql-parser-mistic');.

Warnings

gotcha Limited SQL Dialect Support: The parser primarily supports `SELECT` statements and a subset of common clauses (WHERE, GROUP BY, ORDER BY, LIMIT, CASE/WHEN, REGEXP, BETWEEN, ILIKE). It does not support Data Definition Language (DDL) or Data Manipulation Language (DML) statements like INSERT, UPDATE, DELETE, CREATE TABLE, etc. ↓

fix Ensure that the SQL queries provided for parsing adhere strictly to the supported `SELECT` query syntax and features documented by the library. Avoid passing DDL/DML or highly complex, unsupported SQL structures.

gotcha Potential for Syntax Errors with Unsupported Features: While the library has added support for various features over time, complex or less common SQL constructs outside its tested scope may result in unexpected 'Syntax Error' messages. ↓

fix Thoroughly test parsing capabilities with your specific SQL queries. If an error occurs, simplify the query or break it down into smaller, more basic components to identify the unsupported syntax. Refer to the project's specifications for examples of currently supported queries.

Install

npm install sql-parser-mistic

yarn add sql-parser-mistic

pnpm add sql-parser-mistic

Imports

lexer

wrong

const lexer = require('sql-parser-mistic').lexer;

correct

import { lexer } from 'sql-parser-mistic';

The primary `lexer` object for tokenizing SQL queries.

parser
wrong
```
const parser = require('sql-parser-mistic').parser;
```
correct
```
import { parser } from 'sql-parser-mistic';
```
The primary `parser` object for converting tokens into an Abstract Syntax Tree (AST).

Quickstart

Demonstrates tokenizing a SQL query, parsing it into an AST, and then converting the AST back into a formatted SQL string using recent features like CASE/WHEN, BETWEEN, and ILIKE.

import { lexer, parser } from 'sql-parser-mistic';

const sqlQuery = `
  SELECT
    id,
    name,
    CASE
      WHEN status = 'active' THEN 'Enabled'
      WHEN status = 'inactive' THEN 'Disabled'
      ELSE 'Unknown'
    END AS display_status,
    created_at
  FROM
    users
  WHERE
    age BETWEEN 18 AND 65
    AND name ILIKE '%john%'
  ORDER BY
    created_at DESC
  LIMIT 10 OFFSET 0;
`;

try {
  // Step 1: Tokenize the SQL query into a stream of tokens
  const tokens = lexer.tokenize(sqlQuery);
  console.log("Tokens (first 5):", tokens.slice(0, 5), "...");

  // Step 2: Parse the tokens into an Abstract Syntax Tree (AST)
  const ast = parser.parse(tokens);
  console.log("\nParsed AST (simplified properties):", {
    type: ast.type,
    from: ast.from.value,
    where: ast.where ? 'present' : 'absent',
    limit: ast.limit ? ast.limit.value : 'absent'
  });

  // Step 3: Convert the AST back to a formatted SQL string
  const formattedSql = ast.toString();
  console.log("\nFormatted SQL from AST:\n", formattedSql);

} catch (error) {
  console.error("Error parsing SQL:", error.message);
}