sqlite-parser

This JavaScript library parses SQLite queries to generate abstract syntax tree (AST) representations of the parsed statements. It also parses some non-SQLite constructs, like PostgreSQL custom operators, although non-SQLite parsing can't be 100% accurate due to slight syntax differences (eg. different operator precedences) so corner cases might be parsed wrong.

Try out the interactive demo to see it in action.

This parser is written against the SQLite 3 spec.

Install

npm install sqlite-parser

Install as a global module (since v1.0.0)

Use the command-line interface of the parser by installing it as a global module. The sqlite-parser command is then available to use to parse input SQL files and write the results to stdout or a JSON file. Additional usage instructions and options available through sqlite-parser --help.

npm i -g sqlite-parser

Basic Usage

The library exposes a function that accepts two arguments: a string containing SQL to parse and a callback function. If an AST cannot be generated from the input string then a descriptive error is generated.

If invoked without a callback function the parser will runs synchronously and return the resulting AST or throw an error if one occurs.

var sqliteParser = require('sqlite-parser');
var query = 'select pants from laundry;';
// sync
var ast = sqliteParser(query);
console.log(ast);

// async
sqliteParser(query, function (err, ast) {
  if (err) {
    console.error(err);
    return;
  }
  console.log(ast);
});

Use parser on Node streams (experimental) (since v1.0.0)

This library also includes experimental support as a stream transform that can accept a readable stream of SQL statements and produce a JSON string, representing the AST of each statement, as it is read and transformed. Using this method, the parser can handle files containing hundreds or thousands of queries at once without running into memory limitations. The AST for each statement is pushed down the stream as soon as it is read and parsed instead of reading the entire file into memory before parsing begins.

var parserTransform = require('sqlite-parser').createParser();
var readStream = require('fs').createReadStream('./large-input-file.sql');

readStream.pipe(parserTransform);
parserTransform.pipe(process.stdout);

parserTransform.on('error', function (err) {
  console.error(err);
  process.exit(1);
});

parserTransform.on('finish', function () {
  process.exit(0);
});

To pipe the output into a file that contains a single valid JSON structure, the output of the parser steam transform needs to be wrapped in statement list node where every statement is separated by a comma.

var fs = require('fs');
var sqliteParser = require('sqlite-parser');
var parserTransform = sqliteParser.createParser();
var singleNodeTransform = sqliteParser.createStitcher();
var readStream = fs.createReadStream('./large-input-file.sql');
var writeStream = fs.createWriteStream('./large-output-file.json');

readStream.pipe(parserTransform);
parserTransform.pipe(singleNodeTransform);
singleNodeTransform.pipe(writeStream);

parserTransform.on('error', function (err) {
  console.error(err);
  process.exit(1);
});

writeStream.on('finish', function () {
  process.exit(0);
});

AST

The AST is stable as of release 1.0.0. However, if changes need to be made to improve consistency between node types, they will be explicitly listed in the CHANGELOG.

Example

You can provide one or more SQL statements at a time. The resulting AST object has, at the highest level, a statement list node that contains an array of statements.

Input SQL

SELECT
 MAX(honey) AS "Max Honey"
FROM
 BeeHive

Result AST

{
  "type": "statement",
  "variant": "list",
  "statement": [
    {
      "type": "statement",
      "variant": "select",
      "result": [
        {
          "type": "function",
          "name": {
            "type": "identifier",
            "variant": "function",
            "name": "max"
          },
          "args": {
            "type": "expression",
            "variant": "list",
            "expression": [
              {
                "type": "identifier",
                "variant": "column",
                "name": "honey"
              }
            ]
          },
          "alias": "Max Honey"
        }
      ],
      "from": {
        "type": "identifier",
        "variant": "table",
        "name": "beehive"
      }
    }
  ]
}

Syntax Errors

This parser will try to create descriptive error messages when it cannot parse some input SQL. In addition to an approximate location for the syntax error, the parser will attempt to describe the area of concern (e.g.: Syntax error found near Column Identifier (WHERE Clause)).

Contributing

Contributions are welcome! You can get started by checking out the contributing guidelines.