SQLParser

SQLParser is a JavaScript library that converts SQL WHERE clauses into a structured Abstract Syntax Tree (AST) and transforms them into DevExpress filter format. It removes inline parameters while preserving them as dynamic variables for flexible query processing.

Features

• AST-Based Query Processing: Parses WHERE clauses and generates a structured AST.
• Supports Dynamic Parameters: Identifies and extracts placeholders ({param}) for dynamic resolution.
• Parameter Cleanup: Removes inline parameters while maintaining their structure.
• DevExpress-Compatible Output: Converts parsed SQL conditions into the DevExpress filter format.
• Short-Circuit Optimization: By default, eliminates value = value expressions for DevExpress compatibility (can be disabled for performance optimization).
• Separation of Concerns: Generate AST once, then use it for multiple state updates.

Installation

npm install sqlparser-devexpress

Example Workflow

Step 1: Input SQL

WHERE OrderID = {CustomerOrders.OrderID} AND Status IN (1, 3)

Step 2: Generate AST

import { convertSQLToAst } from "sqlparser-devexpress";

const sqlQuery = "OrderID = {CustomerOrders.OrderID} AND Status IN (1, 3)";
const { ast, variables } = convertSQLToAst(sqlQuery, true); // Enable logs

AST Output

{
  "type": "logical",
  "operator": "AND",
  "left": {
    "type": "comparison",
    "field": "OrderID",
    "operator": "=",
    "value": {
      "type": "placeholder",
      "value": "CustomerOrders.OrderID"
    }
  },
  "right": {
    "type": "comparison",
    "field": "Status",
    "operator": "in",
    "value": {
      "type": "value",
      "value": [1, 3]
    }
  }
}

Extracted Variables

The parser identifies placeholders within the SQL query and extracts them for dynamic value resolution.

Example Output:

[
  "CustomerOrders.OrderID"
]

These extracted variables can be used to fetch the corresponding state values in the application. You can store them in a Record<string, any>, where the key is the placeholder name, and the value is the resolved data from the application's state.

Step 3: Convert AST to DevExpress Format

import { convertAstToDevextreme } from "sqlparser-devexpress";

const sampleState = {
    "CustomerOrders.OrderID": 76548
};

const devexpressFilter = convertAstToDevextreme(ast, sampleState, true); // Short-circuit enabled (default)

console.log("DevExpress Filter:", JSON.stringify(devexpressFilter, null, 2));

DevExpress Filter Output

[
  ["OrderID", "=", 76548],
  "and",
  [
    ["Status", "=", 1],
    "or",
    ["Status", "=", 3]
  ]
]

API Reference

convertSQLToAst(filterString, enableConsoleLogs = false)

• Input: SQL WHERE clause as a string.
• Output: An object { ast } where:
  • ast: The parsed Abstract Syntax Tree.
• Example:

  const { ast } = convertSQLToAst("OrderID = {CustomerOrders.OrderID} AND Status IN (1, 3)");

convertAstToDevextreme(ast, state, shortCircuit = true)

• Input:
  • ast: The AST generated from convertSQLToAst().
  • state: An object containing values for placeholders.
  • shortCircuit: (Optional, default true) Enables short-circuiting of value = value expressions for DevExpress compatibility.
• Output: DevExpress filter array.
• Example:

  const devexpressFilter = convertAstToDevextreme(ast, sampleState, false); // Disables short-circuiting

DevExtreme with React Example

First, install the necessary DevExtreme packages:

npm install devextreme devextreme-react

Now, create a simple React component that integrates DevExtreme with the sqlparser-devexpress library:

import React, { useState } from 'react';
import DataGrid, { Column, FilterRow, HeaderFilter } from 'devextreme-react/data-grid';
import 'devextreme/dist/css/dx.light.css';
import { convertSQLToAst, convertAstToDevextreme } from 'sqlparser-devexpress';

const App = () => {
  const [data, setData] = useState([
    { OrderID: 76548, Status: 1 },
    { OrderID: 76549, Status: 3 },
    { OrderID: 76550, Status: 2 },
  ]);

  const sqlQuery = "OrderID = {CustomerOrders.OrderID} OR Status IN (1, 3)";
  const { ast, variables } = convertSQLToAst(sqlQuery);

  // Extracted state for dynamic variables (usually fetched from your app state)
  const sampleState = { "CustomerOrders.OrderID": 76548 };

  const devexpressFilter = convertAstToDevextreme(ast, sampleState);

  return (
    <div>
      <DataGrid dataSource={data} filterValue={devexpressFilter}>
        <Column dataField="OrderID" />
        <Column dataField="Status" />
        <FilterRow visible={true} />
        <HeaderFilter visible={true} />
      </DataGrid>
    </div>
  );
};

export default App;

Explanation:

1. DataGrid: Displays the data with filtering.
2. convertSQLToAst: Converts the SQL WHERE clause into an AST.
3. convertAstToDevextreme: Converts the AST to the DevExtreme filter format.
4. filterValue: Applies the filter to the DataGrid.

This is a simple integration where the filter from SQLParser is used to filter the grid data in a React app using DevExtreme's DataGrid.

Roadmap

• Support for additional SQL operators and functions.
• Improved error handling and validation.

Contributing

Contributions are welcome! Feel free to open issues or submit pull requests.

License

MIT