SQL AST

[esatterwhite]

Subject of the feature

A new member to the unified family for parsing, constructing and transforming SQL queries / statements.

Problem

I'm constantly finding myself running into walls with sql builders / orms in the node space. The implementations tend to be very specific and as a result very limit. SQL is a rather mature standard, however the tools available really only implement specific use-cases for sql queries rather than a standard.

For example, most tools assume that I wont to select from a table, and as a result, that is my only option. But the standard allows for selecting from explicit values, a sub query, the result of a function, etc.

more over, functions can accept the result of other functions, or be variadic.

SELECT 
  name
, type
, (
    SELECT ARRAY_AGG(label)
    FROM foobar WHERE id IN ( SELECT id FROM other_table )
  ) as labels
FROM a (
  b()
, c()
, d(
    e(
       f(VARIADIC (SELECT id FROM table))
     )
  )
)

Additionally, different database engines provide slightly different syntaxes and features that can, generally speaking, be translated from one engine to the other.

Nothing in the ecosystem really does this, and it seems to me a standard, and tooling around a AST would go a long way to making working with sql less restrictive + painful

Expected behavior

• A standard structure for describing the structure of sql queries as a syntax tree
• Tools for paring, stringifying and transforming sql ast
• ultimately tools for building complex SQL queries using sql ast that are only restricted by the SQL standards

Alternatives

There are numerous attempts, as SQL is necessary skill for most developers. However, they all fall a bit short when you need to do something beyond the simple use cases.

knex is one of the more popular, but its API is restrictive and very difficult to work with if your use cases aren't supported by it directly.

Sqorn is newer, and very nice - but still very restrictive in a number of aspects.

SQL dialect is very expressive and can do a lot of this, but working with it is very clunky and adding functionality is difficult, and very specific to the engine implementation.

• https://github.com/sqorn/sqorn
• https://knexjs.org
• https://github.com/taozhi8833998/node-sql-parser
• https://www.npmjs.com/package/sql-dialect
• https://www.npmjs.com/package/sql

[wooorm]

@esatterwhite That’s really interesting, I definitely see the use cases for an SQL AST and processor.

Is this something you could work on and are seeking guidance for?
Or is this more of an idea that you’d like to exist?

[esatterwhite]

@wooorm All of the above I think. I'm pretty good with SQL, but a bit of a newbie when it comes to parsers / syntax trees.

I've built a few things w/ HAST, so I have some familiarity with the basics, but that is about it.
So I'm not sure where I can be of assistance or where to start.

[wooorm]

I think the first thing is getting a parser. Taking something that exists (and preferably supports positional information) and forking it to use unist internally, and to work as expected. That takes the most work as parsers are the hardest.
Together with building a parser would come creating a syntax tree format, that subsets unist (sqlst, sqlast?)

After that, the next thing would be figuring out how to get the syntax tree back to a string.

[ChristianMurphy]

Thanks for starting the discussion @esatterwhite!
We're in the process unifying ideas in with discussions unifiedjs/collective#44
If you'd like to continue this thread, or start a new one https://github.com/unifiedjs/unified/discussions will be the home for ideas going forward.