npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

node-sql-converter

v5.1.1

Published

node sql converter

Downloads

6

Readme

Nodejs SQL converter

Parse simple SQL statements into an abstract syntax tree (AST) with the visited tableList, columnList and convert it back to SQL.

:star: Features

  • support multiple sql statement seperate by semicolon
  • support select, delete, update and insert type
  • support drop, truncate and rename command
  • output the table and column list that the sql visited with the corresponding authority
  • support various databases engine

:tada: Install

From npmjs

npm install node-sql-converter --save

or

yarn add node-sql-converter

From Browser

Import the JS file in your page:

// support all database parser, but file size is about 750K
<script src="https://unpkg.com/node-sql-converter/umd/index.umd.js"></script>

// or you can import specified database parser only, it's about 150K

<script src="https://unpkg.com/node-sql-converter/umd/mysql.umd.js"></script>

<script src="https://unpkg.com/node-sql-converter/umd/postgresql.umd.js"></script>
  • NodeSQLParser object is on window
<!DOCTYPE html>
<html lang="en" >
  <head>
    <title>node-sql-converter</title>
    <meta charset="utf-8" />
  </head>
  <body>
    <p><em>Check console to see the output</em></p>
    <script src="https://unpkg.com/node-sql-converter/umd/mysql.umd.js"></script>
    <script>
      window.onload = function () {
        // Example parser
        const parser = new NodeSQLParser.Parser()
        const ast = parser.astify("select id, name from students where age < 18")
        console.log(ast)
        const sql = parser.sqlify(ast)
        console.log(sql)
      }
    </script>
  </body>
</html>

:rocket: Usage

Supported Database SQL Syntax

  • Athena
  • BigQuery
  • DB2
  • Hive
  • MariaDB
  • MySQL
  • PostgresQL
  • Redshift
  • Sqlite
  • TransactSQL
  • FlinkSQL
  • Snowflake(alpha)
  • Noql
  • New issue could be made for other new database.

Create AST for SQL statement

// import Parser for all databases
const { Parser } = require('node-sql-converter');
const parser = new Parser();
const ast = parser.astify('SELECT * FROM t'); // mysql sql grammer parsed by default

console.log(ast);
  • ast for SELECT * FROM t
{
  "with": null,
  "type": "select",
  "options": null,
  "distinct": null,
  "columns": "*",
  "from": [
    {
      "db": null,
      "table": "t",
      "as": null
    }
  ],
  "where": null,
  "groupby": null,
  "having": null,
  "orderby": null,
  "limit": null
}

Get node location in the AST

const { Parser } = require('node-sql-converter');
const parser = new Parser();
const ast = parser.astify('SELECT * FROM t', { parseOptions: { includeLocations: true } });

console.log(ast);
  • ast for SELECT * FROM t with the loc property indicating locations and ranges
{
  "with": null,
  "type": "select",
  "options": null,
  "distinct": null,
  "columns": [
    {
      "expr": {
        "type": "column_ref",
        "table": null,
        "column": "*"
      },
      "as": null,
      "loc": {
        "start": {
          "offset": 7,
          "line": 1,
          "column": 8
        },
        "end": {
          "offset": 8,
          "line": 1,
          "column": 9
        }
      }
    }
  ],
  "into": {
    "position": null
  },
  "from": [
    {
      "db": null,
      "table": "t",
      "as": null,
      "loc": {
        "start": {
          "offset": 14,
          "line": 1,
          "column": 15
        },
        "end": {
          "offset": 15,
          "line": 1,
          "column": 16
        }
      }
    }
  ],
  "where": null,
  "groupby": null,
  "having": null,
  "orderby": null,
  "limit": null,
  "locking_read": null,
  "window": null,
  "loc": {
    "start": {
      "offset": 0,
      "line": 1,
      "column": 1
    },
    "end": {
      "offset": 15,
      "line": 1,
      "column": 16
    }
  }
}

Convert AST back to SQL

const opt = {
  database: 'MySQL' // MySQL is the default database
}
// import mysql parser only
const { Parser } = require('node-sql-converter');
const parser = new Parser()
// opt is optional
const ast = parser.astify('SELECT * FROM t', opt);
const sql = parser.sqlify(ast, opt);

console.log(sql); // SELECT * FROM `t`

Parse specified Database

There two ways to parser the specified database.

import Parser from the specified database path node-sql-converter/build/{database}

// import transactsql parser only
const { Parser } = require('node-sql-converter/build/transactsql')
const parser = new Parser()
const sql = `SELECT id FROM test AS result`
const ast = parser.astify(sql)
console.log(parser.sqlify(ast)) // SELECT [id] FROM [test] AS [result]

OR you can pass a options object to the parser, and specify the database property.

const opt = {
  database: 'Postgresql'
}
// import all databases parser
const { Parser } = require('node-sql-converter')
const parser = new Parser()
// pass the opt config to the corresponding methods
const ast = parser.astify('SELECT * FROM t', opt)
const sql = parser.sqlify(ast, opt)
console.log(sql); // SELECT * FROM "t"

Get TableList, ColumnList, Ast by parse function

const opt = {
  database: 'MariaDB' // MySQL is the default database
}
const { Parser } = require('node-sql-converter/build/mariadb');
const parser = new Parser()
// opt is optional
const { tableList, columnList, ast } = parser.parse('SELECT * FROM t', opt);

Get the SQL visited tables

  • get the table list that the sql visited
  • the format is {type}::{dbName}::{tableName} // type could be select, update, delete or insert
const opt = {
  database: 'MySQL'
}
const { Parser } = require('node-sql-converter/build/mysql');
const parser = new Parser();
// opt is optional
const tableList = parser.tableList('SELECT * FROM t', opt);

console.log(tableList); // ["select::null::t"]

Get the SQL visited columns

  • get the column list that the sql visited
  • the format is {type}::{tableName}::{columnName} // type could be select, update, delete or insert
  • for select *, delete and insert into tableName values() without specified columns, the .* column authority regex is required
const opt = {
  database: 'MySQL'
}
const { Parser } = require('node-sql-converter/build/mysql');
const parser = new Parser();
// opt is optional
const columnList = parser.columnList('SELECT t.id FROM t', opt);

console.log(columnList); // ["select::t::id"]

Check the SQL with Authority List

  • check table authority
  • whiteListCheck function check on table mode and MySQL database by default
const { Parser } = require('node-sql-converter');
const parser = new Parser();
const sql = 'UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)'
const whiteTableList = ['(select|update)::(.*)::(a|b)'] // array that contain multiple authorities
const opt = {
  database: 'MySQL',
  type: 'table',
}
// opt is optional
parser.whiteListCheck(sql, whiteTableList, opt) // if check failed, an error would be thrown with relevant error message, if passed it would return undefined
  • check column authority
const { Parser } = require('node-sql-converter');
const parser = new Parser();
const sql = 'UPDATE a SET id = 1 WHERE name IN (SELECT name FROM b)'
const whiteColumnList = ['select::null::name', 'update::a::id'] // array that contain multiple authorities
const opt = {
  database: 'MySQL',
  type: 'column',
}
// opt is optional
parser.whiteListCheck(sql, whiteColumnList, opt) // if check failed, an error would be thrown with relevant error message, if passed it would return undefined

:kissing_heart: Acknowledgement

This project is inspired by the SQL parser flora-sql-parser module. Also base code is dereived from node-sql-parser.

License

Apache-2.0