sqlite-parser

    1.0.1 • Public • Published

    sqlite-parser

    NPM Version Image dependencies Status Image devDependencies Status Image License Type Image

    This JavaScript library parses SQLite queries to generate abstract syntax tree (AST) representations of the parsed statements.

    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.

    Install

    npm i sqlite-parser

    DownloadsWeekly Downloads

    4,088

    Version

    1.0.1

    License

    MIT

    Last publish

    Collaborators

    • nwronski