The workbench uses node-sql-parser extensively for both parsing and building SQL queries. We use the parsing functionality primarily for extracting individual values like table or column names from SQL statements and distinguishing between read and write queries, and we use the building functionality to generate queries based on user interactions. Currently, it's really difficult to stay up-to-date on this package for the following reasons:
- There are frequent breaking changes to the structure of the AST.
- It is unsafe to assume that the structure of the AST will be the same across SQL dialects.
- The package is not intended to be used as a SQL builder in the way that we are using it. Currently, we build SQL by constructing the AST and then converting it back to SQL. This results in a lot of contructs like this:
if (whereVals) {
sel.where = {
type: "function",
name: { name: [{ type: "default", value: "NOT" }] },
args: {
type: "expr_list",
value: [whereVals],
},
};
}
return convertToSqlSelect(sel);
This means that any issues that come up due to (1) or (2) will also break SQL building functionality. Additionally, we are frequently injecting dialect-specific quirks into the existing SQL builder operations. Snippets like these are common:
const escapedName = isPostgres ? `"${name}"` : `\`${name}\``;
Here are some ideas on how we can improve this:
- Develop an interface-like abstraction based on SQL dialect. If there are changes to how Postgres is parsed in node-sql-parser, we should be able to handle it without breaking the MySQL logic, and we shouldn't have to pass around an
isPostgres flag everywhere. This is more extensible and would make it much easier to support more SQL dialects in the future.
- Migrate off of node-sql-parser for SQL building purposes and use a library more specifically geared towards SQL building like Knex.js. This would decouple parsing from building and would also allow us to not worry about things like quotation marks and other dialect differences.
Both of these approaches are fairly significant refactors, but I think they pay for themselves in the long run. Any feedback is appreciated.
The workbench uses node-sql-parser extensively for both parsing and building SQL queries. We use the parsing functionality primarily for extracting individual values like table or column names from SQL statements and distinguishing between read and write queries, and we use the building functionality to generate queries based on user interactions. Currently, it's really difficult to stay up-to-date on this package for the following reasons:
Here are some ideas on how we can improve this:
isPostgresflag everywhere. This is more extensible and would make it much easier to support more SQL dialects in the future.Both of these approaches are fairly significant refactors, but I think they pay for themselves in the long run. Any feedback is appreciated.