forward/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

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

npm install
cake build

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'

Credits

A lot of the boilerplate and compilation code in this project is borrowed from the CoffeeScript project as it was the best example of a project using JISON that I could find. Thanks.

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

`Conflict in grammar` errors on build

Hi. I am trying to build this project from its source but the build ends with errors.

Steps

1. git clone https://github.com/forward/sql-parser.git
2. cd sql-parser
3. npm install
4. cake build

Conflict in grammar: multiple actions possible when lookahead token is BETWEEN in state 88
- reduce by rule: Expression -> Expression MATH Expression
- shift token (then go to state 62)
Conflict in grammar: multiple actions possible when lookahead token is BETWEEN in state 89
- reduce by rule: Expression -> Expression MATH_MULTI Expression
- shift token (then go to state 62)
Conflict in grammar: multiple actions possible when lookahead token is BETWEEN in state 90
- reduce by rule: Expression -> Expression OPERATOR Expression
- shift token (then go to state 62)
Conflict in grammar: multiple actions possible when lookahead token is BETWEEN in state 93
- reduce by rule: Expression -> Expression CONDITIONAL Expression
- shift token (then go to state 62)

...

States with conflicts:
State 88
  Expression -> Expression MATH Expression . #lookaheads= FROM AS MATH MATH_MULTI OPERATOR BETWEEN CONDITIONAL SEPARATOR EOF UNION ORDER GROUP LIMIT RIGHT_PAREN HAVING RIGHT LEFT JOIN WHERE
  Expression -> Expression .MATH Expression
  Expression -> Expression .MATH_MULTI Expression
  Expression -> Expression .OPERATOR Expression
  Expression -> Expression .BETWEEN BetweenExpression
  Expression -> Expression .CONDITIONAL Expression
State 89
  Expression -> Expression MATH_MULTI Expression . #lookaheads= FROM AS MATH MATH_MULTI OPERATOR BETWEEN CONDITIONAL SEPARATOR EOF UNION ORDER GROUP LIMIT RIGHT_PAREN HAVING RIGHT LEFT JOIN WHERE
  Expression -> Expression .MATH Expression
  Expression -> Expression .MATH_MULTI Expression
  Expression -> Expression .OPERATOR Expression
  Expression -> Expression .BETWEEN BetweenExpression
  Expression -> Expression .CONDITIONAL Expression
State 90
  Expression -> Expression OPERATOR Expression . #lookaheads= FROM AS MATH MATH_MULTI OPERATOR BETWEEN CONDITIONAL SEPARATOR EOF UNION ORDER GROUP LIMIT RIGHT_PAREN HAVING RIGHT LEFT JOIN WHERE
  Expression -> Expression .MATH Expression
  Expression -> Expression .MATH_MULTI Expression
  Expression -> Expression .OPERATOR Expression
  Expression -> Expression .BETWEEN BetweenExpression
  Expression -> Expression .CONDITIONAL Expression

...

events.js:160
      throw er; // Unhandled 'error' event
      ^

Error: spawn ./node_modules/.bin/coffee ENOENT
  at exports._errnoException (util.js:1022:11)
  at Process.ChildProcess._handle.onexit (internal/child_process.js:193:32)
  at onErrorNT (internal/child_process.js:359:16)
  at _combinedTickCallback (internal/process/next_tick.js:74:11)
  at process._tickCallback (internal/process/next_tick.js:98:9)
  at Module.runMain (module.js:606:11)
  at run (bootstrap_node.js:394:7)
  at startup (bootstrap_node.js:149:9)
  at bootstrap_node.js:509:3

Actual: The build ends with some errors.

Expected: The build should end successfully.

System Status

Operating System: Windows 8.1 Enterprise
Node version: v6.10.0

Support INSERT, UPDATE, DELETE

Hi Andy,

Thanks for this amazing piece of software!

How complicated would it be to implements UPDATE, INSERT AND DELETE ?

Regards

Error while compiling with jspm

err MultipleErrors: Error compiling cjs module "npm:[email protected]/lib/compiled_parser.js" at jspm_packages/npm/[email protected]/lib/compiled_parser.js
jspm_packages/npm/[email protected]/lib/compiled_parser.js:338:9: Unexpected reserved word function
jspm_packages/npm/[email protected]/lib/compiled_parser.js:338:24: Semi-colon expected

caused by
compiled_parser.js:338 _token_stack: function lex() { var token; ...

Without _token_stack: works well.

No support for column names with brackets '('

Hi,
I am trying to use the query-builder for my project. I have database table with columns like 'Result(1/0)'. When I pass this to query-builder as query, it is not able to parse it and gives error.
I tried [Result(1/0)] = 0and Result(1/0) = 0.
Can you please suggest how to add this in the lexer to support this or add it.
Thanks.

Count(*) Not working

Hi Dear,

Thank you for building such an awesome library.
I am running into issues when using count statements and could do with a little help.

This piece of code doesnt execute the SQL command namely select count(*) from data

$(function(){
    var validQuery = "select count(*) from data";

    try {    
        SQLParser.parse(validQuery);
        //validQuery.addClass('green');  
        alert("Valid SQL")
    }
    catch (err) { 
        alert("Invalid SQL")
    } 
});

I am also not able to do something like select count column_name from table_name.
Could you please advise on this?

Unexpected reserved word function

Hi.
I got error:
err MultipleErrors: jspm_packages/npm/[email protected]/lib/compiled_parser.js:338:9: Unexpected reserved word function
I guess "_token_stack:" needed to remove

plain inner and outer joins

I've been trying to get the grammar right for inner/outer joins,

o 'INNER JOIN Table ON Expression',                   -> new Join($3, $5, 'INNER')
o 'OUTER JOIN Table ON Expression',                   -> new Join($3, $5, 'OUTER')

but it is still not picking up my query. I'm guessing there is a conflict with RIGHT INNER JOIN, etc. Also, string literals a la 'Part A 2' lack symbols. Should it take ` ' " all? Not sure on that one yet.

Add support for 'CREATE TABLE' SQL statements

Would love this support. I'm not familiar with CoffeeScript syntax, so I'll need to figure my way around the repo if I were to make a PR.

Can not parse concatenated columns

Hi,
Sql that involves concatenating a string to a column or two columns together can not be parsed.

e.g.
var sql = "select FIELD1 || FIELD2 from MYTABLE";
require('sql-parser').lexer.tokenize(sql);

results in:

Error: NOTHING CONSUMED: Stopped at - '|| FIELD2 from MYTABLE'
at new Lexer (C:\MyProject\node_modules\sql-parser\lib\lexer.js:21:17)

I use Firebird SQL, so I'm not sure if this is valid in other SQL dialects.

Uncaught Error: Parse error on line 2: Unexpected 'EOF'

I am using sql-parser.js with Jquery SQL Query builder. When i load the sql-parser.js file i am getting the error:

Uncaught Error: Parse error on line 2: Unexpected 'EOF'

Any ideas why?

support for WHERE foo IN ('foo', 'bar')

Nice little script. It is useful to me. But, would you be able to support the IN operator? This kind of syntax does not work: WHERE foo IN ('foo', 'bar') => Parse error on line 2: Unexpected 'LITERAL'

True source for this project?

Hey, quick question - you mention "the coffeescript project" and also JISON. So is there a source language file that you used to generate the coffeescript/javascript? Interested in maybe continuing this project, but the code looks generated, so I'm not sure where to start.

Add support "table_name AS alias" syntax

According mysql docs: tbl_name [[AS] alias]

JSON_OVERLAPS

how to add pasing capability json_overloaps(name, "['1','2']")

Add parsing logic to consume semicolon

Parameterless function call

Parameterless function calls generate an error.
`"SELECT X(Y(Z())) AS TOTAL FROM STJ TBL"``

Dies in Unexpected 'RIGHT_PAREN', while

`"SELECT X(Y(Z)) AS TOTAL FROM STJ TBL"``

Works fine.

I'll try to fix it in the Jison grammar and submit a pull-request.

Bower ?

would it be possible to push it on Bower ?

basic bower.json :

{
  "name": "sql-parser",
  "version": "0.5.0",
  "authors": [{
    "name": "Andy Kent",
    "email": "[email protected]"
  }],
  "description": "Lexer and Parser for SQL Syntax",
  "main": "browser/sql-parser.js",
  "keywords": [
    "sql",
    "lexer",
    "parser",
    "ast"
  ],
  "license": "MIT",
  "homepage": "https://github.com/forward/sql-parser",
  "repository": {
    "type": "git",
    "url": "git://github.com/forward/sql-parser.git"
  },
  "ignore": [
    "**/.*",
    "node_modules",
    "bower_components",
    "src",
    "test",
    "package.json",
    "Cakefile"
  ]
}

without forgetting to register it and create tags for next releases 😄

Licence?

Hi @andykent, I'm using an adapted version of sql-parser in project of mine at https://github.com/bjornharrtell/postgresql-http-server which is MIT licensed.

It does not seem sql-parser have a license... any chance of making it available with a permissive license like MIT?

Support for variables in sql code

It would be nice to have the ability to pop variables in the SQL and then have them tokenized and parsed

PR coming

Add support for NOT LIKE

Parsing select * from my_table where x not like "%foo%" throws Unexpected 'LITERAL' due to the use of NOT operator.

STAR and multiplication

If I try to parse conditions or assignments with '*', the system gives the following error:

SQLParser.parse('UPDATE t1 SET a=b*100')

Uncaught Error: Parse error on line 2: Unexpected 'STAR'

Could you fix it, please.

Problem in Postgres CAST shortcut

Hi.

If I use cast function, it passes, but if I use the ::TYPE shortcut, it gives me an error.
Ex:

SELECT CAST(now() AS DATE)

OK

SELECT now()::DATE

ERROR