9 ms·

Open Source SQL Parsers

125 points by EntICOnc 4y ago

bogomipz 4y ago
>"Parsing SQL queries provides superpowers for monitoring data health" Can someone say how this works exactly? What does parsing offer that things like logs and other metrics don't in terms of monitoring?
eatonphil 4y ago
I wrote a similar post recently and not all the libraries overlap interestingly. My post includes sample code for each library and evaluates a few various complexity SELECT queries for correctness. I may have to write a second post now to include some from this one that I missed! https://datastation.multiprocess.io/blog/2022-04-11-sql-parsers.html https://datastation.multiprocess.io/blog/2022-04-11-sql-pars...
1egg0myegg0 4y ago
Since DuckDB is so portable, it can be a good choice for adding a SQL parser to a new system. Have a look here for an example that uses the parser separately from the execution engine: https://github.com/duckdb/duckdb/blob/master/examples/standalone-plan/main.cpp https://github.com/duckdb/duckdb/blob/master/examples/standa...
- eatonphil 4y ago
  Duckdb itself uses a variation of pganalyze's library-ification of postgres's parser so this seems a little indirect. That said, I've heard the duckdb folks have improved on the postgres parser a bit.
tehlike 4y ago
Not related to parsers, but i find sql syntax so backwards. Listing columns first Then table Then join Then filter Then group bys Then limit The order of operations are out of whack and makes pipeline ing a little hard. I found this to be closer to LINQ way https://github.com/prql/prql https://github.com/prql/prql I hope in near future databases will come with better query languages...
- minxomat 4y ago
  KQL is a more LINQy language.
  - tehlike 4y ago
    Looks interesting.
- nickcox 4y ago
  > The order of operations are out of whack and makes pipeline ing a little hard It makes it really hard to offer good autocompletions too.
- tfigment 4y ago
  This comes up for me in autocompleting editors. Have to start with "select * from table t;" then go back and then remove * and t.<ctrl-space> to get the hints to populate and then continue with joins which may require going back to select. Update and delete at least start with tables and joins.
- userbinator 4y ago
  It was probably influenced by COBOL, i.e. designed to read like English.
  - manigandham 4y ago
    In fact, SQL (structured query language) was originally called SEQUEL (structured English query language).
    - tehlike 4y ago
      I find prql more English for some reason...
  - ErwinSmout 4y ago
    It was probably also influenced by Codd's own preference for the data language in calculus-based style (Codd's roots as a mathematician are to "blame" here), as opposed to algebra-based (the style typically preferred by developers).
anitil 4y ago
I'd be interested to hear people's use cases for parsing SQL. The link talks about exploring sql history, but has anyone else got some interesting uses? A couple of times where I've needed to parse SQL I would typically write a module for sqlite3 and get it to do the parsing for me. But annoyingly I can't remember _why_ I did this or what I was trying to achieve.
- eatonphil 4y ago
  Building a SQL IDE and want to do introspection on queries. Building a SQL frontend for some API or CLI. Building a SQL-aware proxy. Just a few ideas. :)
  - anitil 4y ago
    Interesting. Out of interest, what would an SQL-aware proxy give you? On-the-fly rewrites? Automatic caching?
    - eatonphil 4y ago
      Have you heard of Vitess? Or the company Planet Scale? Vitess is a SQL-aware proxy that helps you scale MySQL. https://vitess.io/docs/13.0/overview/whatisvitess/ https://vitess.io/docs/13.0/overview/whatisvitess/
  - achanda358 4y ago
    Some more: SQL linter, alternate DB implementations (Cockroach DB, TiDB etc), SQL optimisers
- cgio 4y ago
  Harvesting lineage information could be a good use case.
  - anitil 4y ago
    This would be so useful to me right now. So often I'm flying blind making edits and just praying I don't ruin someone else's day
userbinator 4y ago
Much like with other things, recursive-descent is probably the best way to write a SQL parser as it's easy to debug, modify, and extend. There's some great hyperlinked grammars at https://ronsavage.github.io/SQL/ https://ronsavage.github.io/SQL/ for those who want to try writing one. Also, is it just me or does the text of this article have an almost SEO-spam-like "texture" to it? It reads very unnaturally.
- lmwnshn 4y ago
  On the other hand, you're probably writing your SQL parser to parse SQL queries that are being fed into some DBMS. I found pg_query's argument convincing: "Our conclusion: The only way to correctly parse all valid SQL queries that PostgreSQL understands, now and in the future, is to use PostgreSQL itself." [0] [0] https://pganalyze.com/blog/parse-postgresql-queries-in-ruby https://pganalyze.com/blog/parse-postgresql-queries-in-ruby
throwaway858 4y ago
There is also a Haskell port of the original PostgreSQL parser, implemented using megaparsec, which makes it highly flexible and hackable: https://github.com/nikita-volkov/hasql-th#implementation https://github.com/nikita-volkov/hasql-th#implementation
cm2187 4y ago
I believe antlr has grammars for other sql syntaxes (sqlite, tsql, etc): https://github.com/antlr/grammars-v4/tree/master/sql https://github.com/antlr/grammars-v4/tree/master/sql
m-novikov 4y ago
There are also multiple ongoing projects attempting to implement incremental parsers(1, 2, 3) e.g. for tree-sitter. To make editing SQL code more convenient. [1] https://github.com/m-novikov/tree-sitter-sql https://github.com/m-novikov/tree-sitter-sql [2] https://github.com/DerekStride/tree-sitter-sql https://github.com/DerekStride/tree-sitter-sql [3] https://github.com/Snowflake-Labs/lezer-snowsql https://github.com/Snowflake-Labs/lezer-snowsql
conaclos 4y ago
SQL is a mess. It is difficult to find a parser that fulfills your needs. A month ago, I needed a parser to parse SQLite schemas. SQLite has some edge case and I needed a lossless parsing to detect these edge cases. I finally wrote my own parser [1] to fullfill my needs and to have some fun. This is for a POC project. [1] https://github.com/coast-team/sqlschm https://github.com/coast-team/sqlschm
- cryptonector 4y ago
  If I needed to parse SQLite3 schemas, I might just fork the Lemon grammar for SQLite3 from SQLite3 and make it produce an AST.

125 points by EntICOnc 4y ago

>"Parsing SQL queries provides superpowers for monitoring data health" Can someone say how this works exactly? What does parsing offer that things like logs and other metrics don't in terms of monitoring?

eatonphil 4y ago

I wrote a similar post recently and not all the libraries overlap interestingly. My post includes sample code for each library and evaluates a few various complexity SELECT queries for correctness. I may have to write a second post now to include some from this one that I missed! https://datastation.multiprocess.io/blog/2022-04-11-sql-parsers.html https://datastation.multiprocess.io/blog/2022-04-11-sql-pars...

1egg0myegg0 4y ago

Since DuckDB is so portable, it can be a good choice for adding a SQL parser to a new system. Have a look here for an example that uses the parser separately from the execution engine: https://github.com/duckdb/duckdb/blob/master/examples/standalone-plan/main.cpp https://github.com/duckdb/duckdb/blob/master/examples/standa...

eatonphil 4y ago
Duckdb itself uses a variation of pganalyze's library-ification of postgres's parser so this seems a little indirect. That said, I've heard the duckdb folks have improved on the postgres parser a bit.

tehlike 4y ago

Not related to parsers, but i find sql syntax so backwards. Listing columns first Then table Then join Then filter Then group bys Then limit The order of operations are out of whack and makes pipeline ing a little hard. I found this to be closer to LINQ way https://github.com/prql/prql https://github.com/prql/prql I hope in near future databases will come with better query languages...

minxomat 4y ago
KQL is a more LINQy language.
- tehlike 4y ago
  Looks interesting.
nickcox 4y ago
> The order of operations are out of whack and makes pipeline ing a little hard It makes it really hard to offer good autocompletions too.
tfigment 4y ago
This comes up for me in autocompleting editors. Have to start with "select * from table t;" then go back and then remove * and t.<ctrl-space> to get the hints to populate and then continue with joins which may require going back to select. Update and delete at least start with tables and joins.
userbinator 4y ago
It was probably influenced by COBOL, i.e. designed to read like English.
- manigandham 4y ago
  In fact, SQL (structured query language) was originally called SEQUEL (structured English query language).
  - tehlike 4y ago
    I find prql more English for some reason...
- ErwinSmout 4y ago
  It was probably also influenced by Codd's own preference for the data language in calculus-based style (Codd's roots as a mathematician are to "blame" here), as opposed to algebra-based (the style typically preferred by developers).

anitil 4y ago

I'd be interested to hear people's use cases for parsing SQL. The link talks about exploring sql history, but has anyone else got some interesting uses? A couple of times where I've needed to parse SQL I would typically write a module for sqlite3 and get it to do the parsing for me. But annoyingly I can't remember _why_ I did this or what I was trying to achieve.

eatonphil 4y ago
Building a SQL IDE and want to do introspection on queries. Building a SQL frontend for some API or CLI. Building a SQL-aware proxy. Just a few ideas. :)
- anitil 4y ago
  Interesting. Out of interest, what would an SQL-aware proxy give you? On-the-fly rewrites? Automatic caching?
  - eatonphil 4y ago
    Have you heard of Vitess? Or the company Planet Scale? Vitess is a SQL-aware proxy that helps you scale MySQL. https://vitess.io/docs/13.0/overview/whatisvitess/ https://vitess.io/docs/13.0/overview/whatisvitess/
- achanda358 4y ago
  Some more: SQL linter, alternate DB implementations (Cockroach DB, TiDB etc), SQL optimisers
cgio 4y ago
Harvesting lineage information could be a good use case.
- anitil 4y ago
  This would be so useful to me right now. So often I'm flying blind making edits and just praying I don't ruin someone else's day

userbinator 4y ago

Much like with other things, recursive-descent is probably the best way to write a SQL parser as it's easy to debug, modify, and extend. There's some great hyperlinked grammars at https://ronsavage.github.io/SQL/ https://ronsavage.github.io/SQL/ for those who want to try writing one. Also, is it just me or does the text of this article have an almost SEO-spam-like "texture" to it? It reads very unnaturally.

lmwnshn 4y ago
On the other hand, you're probably writing your SQL parser to parse SQL queries that are being fed into some DBMS. I found pg_query's argument convincing: "Our conclusion: The only way to correctly parse all valid SQL queries that PostgreSQL understands, now and in the future, is to use PostgreSQL itself." [0] [0] https://pganalyze.com/blog/parse-postgresql-queries-in-ruby https://pganalyze.com/blog/parse-postgresql-queries-in-ruby

throwaway858 4y ago

There is also a Haskell port of the original PostgreSQL parser, implemented using megaparsec, which makes it highly flexible and hackable: https://github.com/nikita-volkov/hasql-th#implementation https://github.com/nikita-volkov/hasql-th#implementation

cm2187 4y ago

I believe antlr has grammars for other sql syntaxes (sqlite, tsql, etc): https://github.com/antlr/grammars-v4/tree/master/sql https://github.com/antlr/grammars-v4/tree/master/sql

m-novikov 4y ago

There are also multiple ongoing projects attempting to implement incremental parsers(1, 2, 3) e.g. for tree-sitter. To make editing SQL code more convenient. [1] https://github.com/m-novikov/tree-sitter-sql https://github.com/m-novikov/tree-sitter-sql [2] https://github.com/DerekStride/tree-sitter-sql https://github.com/DerekStride/tree-sitter-sql [3] https://github.com/Snowflake-Labs/lezer-snowsql https://github.com/Snowflake-Labs/lezer-snowsql

conaclos 4y ago

SQL is a mess. It is difficult to find a parser that fulfills your needs. A month ago, I needed a parser to parse SQLite schemas. SQLite has some edge case and I needed a lossless parsing to detect these edge cases. I finally wrote my own parser [1] to fullfill my needs and to have some fun. This is for a POC project. [1] https://github.com/coast-team/sqlschm https://github.com/coast-team/sqlschm

cryptonector 4y ago
If I needed to parse SQLite3 schemas, I might just fork the Lemon grammar for SQLite3 from SQLite3 and make it produce an AST.