SQL Parser which produces a Concrete Syntax Tree (CST).
Unlike a more usual parser which produces an Abstract Syntax Tree (AST),
this parser preserves all the syntax elements present in the parsed source code,
with the goal of being able to re-create the exact original source code.
MySQL - experimental (version 8)
(see #7 for implementation progress).
MariaDB - experimental (version 10)
(see #32 for implementation progress).
PostgreSQL - experimental (version 16)
(see #40 for implementation progress).
PL/pgSQL - experimental (version 16)
(see #130 for implementation progress).
Note: This software is in very active development.
The syntax tree structure is mostly stable now,
though there are bound to be changes as new SQL dialects
are added and they contain features that need to be accommodated
to the syntax tree.
Install
npm install sql-parser-cst
Usage
import { parse, show, cstVisitor } from "sql-parser-cst";const cst = parse("select * from my_table", { dialect: "sqlite", // These are optional: includeSpaces: true, // Adds spaces/tabs includeNewlines: true, // Adds newlines includeComments: true, // Adds comments includeRange: true, // Adds source code location data});// convert all keywords to uppercaseconst toUpper = cstVisitor({ keyword: (kw) => { kw.text = kw.text.toUpperCase(); },});toUpper(cst);// Serialize back to SQLshow(cst); // --> SELECT * FROM my_table
AST versus CST-parsers
For example, given the following SQL:
/* My query */SELECT ("first_name" || ' jr.') as fname-- use important tableFROM persons;
An AST-parser might parse this to the following abstract syntax tree:
All keywords are preserved in type: keyword nodes, which are usually
stored in fields named like someNameKw.
Parenthesis is represented by separate type: paren_expr node.
Comma-separated lists are represented by separate type: list_expr node.
Trailing semicolon is represented by type: empty node in the end.
The original source code representation of strings, identifiers, keywords, etc
is preserved in text fields.
Each node can have leading and trailing fields,
which store comments and newlines immediately before or after that node.
These fields will also contain information about regular spaces/tabs
(e.g. {"type": "space", "text": " \t"}). This has been left out from this
example for the sake of simplicity.
API
parse(sql: string, options: ParserOptions): Program
Parses SQL string and returns the CST tree. Takes the following options:
dialect: 'sqlite' | 'bigquery' | 'mysql' | 'mariadb' | 'postgresql' | 'plpgsql' The SQL dialect to parse (required).
includeRange: boolean When enabled adds range: [number, number] field to all CST nodes,
which contains the start and end locations of the node.
includeComments: boolean When enabled adds leading: Whitespace[] and/or trailing: Whitespace[]
to nodes which are preceded or followed by comments.
includeNewlines: boolean Like includeComments, but includes newlines info to the same fields.
includeSpaces: boolean Like includeComments, but includes horizontal whitespace info to the same fields.
paramTypes: ("?" | "?nr" | "$nr" | ":name" | "$name" | "@name" | "@`name`")[]
Determines the types of bound parameters supported by the parser.
By default a query like SELECT * FROM tbl WHERE id = ? will result in parse error.
To fix it, use paramTypes: ["?"] config option.
filename: string Name of the SQL file. This is only used for error-reporting.
When parsing fails with syntax error, it throws FormattedSyntaxError which contains a message like:
Syntax Error: Unexpected "WHERE"Was expecting to see: "!", "$", "(", "-", ":", "?", "@", "CASE", ...--> my_db.sql:2:33 |2 | SELECT * FROM my_table ORDER BY WHERE | ^
Because not all SQL dialects are 100% supported and even the ones that are can have bugs,
you might encounter SQL that the parser fails to parse. As a workaround you can use conditional
comments to disable the parser for the problematic part of SQL:
SELECT * FROM products;/* sql-parser-cst-disable */UNSUPPORTED SQL IN HERE;/* sql-parser-cst-enable */UPDATE products SET sold = true WHERE id = 2;
show(cst: Node): string
Converts CST back to string.
Important caveat: the CST has to contain whitespace data, meaning,
it was generated with includeComments, includeNewlines and includeSpaces options enabled.
For any valid SQL the following assertion will always hold:
Generates a function that walks through the whole CST tree and calls
a function in map whenever it encounters a node with that type.
For example the following code checks that all table and column aliases
use the explicit AS keyword:
const checkAliases = cstVisitor({ alias: (node) => { if (!node.asKw) { throw new Error("All alias definitions must use AS keyword!"); } },});checkAliases(cst);
You can return VisitorAction.SKIP to avoid visiting all child nodes of a specific node:
cstTransformer<T>(map: TransformMap<T>): (node: Node) => T
Transforms the whole CST into some other type T. The map object
should contain an entry for each of the CST node types it expects to
encounter (this generally means all of them).
For example, the following implements a toString() function that
serializes very basic SQL queries like SELECT 1, 2, 3 + 4:
The builtin show() function is implemented as such a transform.
xKeywords: Record<string, boolean>
Additionally the parser exports lists of reserved keywords for each supported SQL dialect:
sqliteKeywords, bigqueryKeywords, mysqlKeywords, mariadbKeywords, postgresqlKeywords.
These are simple JavaScript objects, useful for doing lookups:
When running the testsuite one always needs to pick a dialect.
For example npm run test:sqlite or npm run test:mysql.
Running one of these commands will run the testsuite against the parser
of that dialect. It will execute all the generic tests plus tests
applicable for that dialect.
npm test will execute the testsuite for each supported dialect,
covering all the possible combinations.
During development
Start the parser-generator watch process in one terminal:
npm run watch:generate
and the tests watch process in another terminal:
npm run test:sqlite -- --watch
Note that npm run test --watch doesn't work.
A separate watch process needs to be started manually for each dialect.
Release
Generate new release with:
npm version <patch|minor>npm publish
To generate a changelog use the npm run changelog command: