mistic100 / sql-parser

A SQL parser written in pure JS

SQL Parser

SQL Parser is a lexer, grammar and parser for SQL written in JS. Currently it is only capable of parsing fairly basic SELECT queries but full SQL support will hopefully come in time. See the specs for examples of currently supported queries.

Installation

The package is distributed on NPM and can be installed with...

npm install sql-parser-mistic

To build from source you'll need to run the following from the root of the project...

npm install
npm run compile

Tests are written using Mocha and can be run with...

npm test

Lexer

The lexer takes a SQL query string as input and returns a stream of tokens in the format

['NAME', 'value', lineNumber]

Here is a simple example...

lexer.tokenize('select * from my_table')

[
  ['SELECT','select',1],
  ['STAR','*',1],
  ['FROM','from',1],
  ['LITERAL','my_table',1]
]

The tokenized output is in a format compatible with JISON.

Parser

The parser only currently supports SELECT queries but is able to produce a Select object with properties for where, group, order, limit. See lib/nodes.coffee for more info of the returned object structure. Calling .toString() on a Select object should give you back a well formatted version of the original SQL input.

tokens = lexer.tokenize("select * from my_table where foo = 'bar'")
parser.parse(tokens).toString()

SELECT *
  FROM `my_table`
  WHERE `foo` = 'bar'

Contributions

Contributions in the form of pull requests that add syntax support are very welcome but should be supported by both Lexer and Parser level tests.

sql-parser's Issues

Module not found: Error: Can't resolve 'fs' in 'C:\Dev\client-apps\react-query-builder\node_modules\sql-parser-mistic\lib'

I use it with react-app and I've got an error:

Module not found: Error: Can't resolve 'fs' in '\node_modules\sql-parser-mistic\lib' .
How can I use it within browser application?

Dont Support Chinese Character

Suppor for ILIKE operator

Loosing all groups and bracket after calling `setRulesFromSQL`

Having following query:

product_class.classification_code IN('EC001959') 
AND ( feature.classification_code IN('EF000042') AND feature_value.classification_code IN('EV000098') )  
AND ( feature.classification_code IN('EF011586') AND product_feature.value_min >= 15 AND product_feature.value_max <= 15 )

this selector looks like

after save/reload it looks like

it's loosing all groups and bracket after calling setRulesFromSQL

---

If we looks at tokens after Lexer

we see that brackets are there.

Backtick around column names messes the parser around

im using something like

`is_staff` = 1 OR `is_team` = 1 OR `is_department` = 1 OR `is_category` = 1

im passing the backticks in the id for the rules already

{
    "id":"`is_team`",
    "...."
}

the sql parser doesnt seem to be able to pick up the columns from the given sql vs the "rule" items. so it comes up blank.

im guessing theres a strip strange marks on the field names?

right now its an either or. either i pass the sql as above and leave the field names (id) without the backticks and it works but outputs sql without the backticks around column names.. or i break the loading up of sql (pre populating) with backticks and fieldnames with backticks but it then "outputs" sql with backticks.

so either, given that the stored sql is:

`is_team` = 1

using:

{
    "id":"is_team",
    "...."
}

the querybuilder rules works fine but it outputs the sql as:

is_team = 1

OR

i use

{
    "id":"`is_team`",
    "...."
}

and the query builder (sql parser) breaks cause it cant find the fields in the rules

but it outputs - when adding a new rule -

`is_team` = 1

which is what i want

---

might be useful for it to 1 to 1 match on the id value without stripping anything out if you use different tables n stuff.

{
    "id":"`table`.`column`",
    "...."
}

Needs ne build

@mistic100 this needs a new release since I cant get the current distribution with the negative number fix.

"WHERE clause" involving ARRAY comparison of values - Postgresql

Hi,

Am using Postgresql database. I need to generate SQL Where clause which involves comparison of Array of values with an Array column in the database and below will be the syntax for the same in Postgresql -

ARRAY['A','B']::text[] && testColumn

In above syntax, am comparing if values A, B are available in the "text[]" array column with name - "testColumn".

In the js query builder component, there is no operator to handle this scenario to create the SQL where clause from the component. So, i have created an operator - "in_string_array" to handle this, please refer js fiddle - https://jsfiddle.net/rpqvgLj0/14/

Am able to generate the SQL where clause successfully from the querybuilder component.

However, am not able to do the reverse, that is, i have a SQL Where clause with the syntax -
ARRAY['A','B']::text[] && testColumn
Am not able to paint the query builder component on browser for the above SQL Where clause syntax using setRulesFromSQL function. Please refer jsfiddle - https://jsfiddle.net/rpqvgLj0/14/

Am getting error in console as below -
Uncaught Error: NOTHING CONSUMED: Stopped at - '['A','B']::text[] && testArray'
at new Lexer (sql-parser.js:32)
at Object.exports.tokenize (sql-parser.js:276)
at Object.exports.parse (sql-parser.js:1327)
at QueryBuilder.getRulesFromSQL (query-builder.standalone.js:5871)
at QueryBuilder.setRulesFromSQL (query-builder.standalone.js:6041)
at jQuery.fn.init.$.fn.queryBuilder (query-builder.standalone.js:4046)
at (index):79
at dispatch (jquery-2.1.3.js:4430)
at elemData.handle (jquery-2.1.3.js:4116)

I tried making modifications in SQL parser js by including the words -"ARRAY", "[", "]" etc. as part of SQL parser js keywords and symbols. However, they were not working and i was getting error in console - Uncaught Error: Parse error on line 2: Unexpected 'ARRAY['

I have been trying this for more than 2 days now. Any pointers would be of great help. An understanding how the parsing works here would also help so that i can extend and customize the SqlParser to make it work.

I had created this issue in the jquery-querybuilder repo. However, since my issue is in the SQLParser side, am creating the issue here. Please help.

Thanks and regards,
V. Srinivasan

Where condition with negative number value(s) cause "Unexpected 'MATH'" error.

Where condition with negative numbers cause "Unexpected 'MATH'" error. To reproduce change 1 to -1 in your example page: select * from test where a = -1 and foo = "bar"

There is a logical or regex problem in your Lexer:
MATH = ['+', '-', '||', '&&'];
and
NUMBER = /^[0-9]+(.[0-9]+)?/;

I was trying to add sign to your number regex but it did not help: NUMBER = /^[-+]?[0-9]+(.[0-9]+)?/;

Can you fix the problem as it make component much less useful?

Thanks,

Dmitry

JSON_OVERLAPS

how to add parsing capability json_overlaps(name, "['1','2']")?