SQL - Brings an extensible SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries.

Very interesting work! It’s on my list of things to try out with EctoFDB.

My use case: FDB has no SQL so I’m thinking I could make a cli tool to query EctoFDB (similar to psql for Postgres) that uses the Ecto adapter and your SQL library.

Pure serendipity, I’ve been looking at a very similar challenge with nimble parsec yesterday.

Namely, splitting a file full of AWS Athena DDL/SQL statements (it only accepts one statement at a time) while interpolating some variables in the DDL statements.

I’m eager to read your implementation. There are some challenging edge cases such as things like user ‘O\’Malley’ and the ubiquitous’Little Bobby;drop tables’.

I would love to see that file, although I did start out with a version in nimble parsec, but I was pushing the limit of how I wanted to write the MVP and I didn’t wanna try to retro fit the grammar to make it composable.

https://github.com/dashbitco/nimble_parsec/issues/144
https://github.com/dashbitco/nimble_parsec/issues/140

So I ended up hand writing it. (this could change in the future though)

If I understand parser terminology right, then what I have written is a recursive decent parser / single pass as I read the string the AST is being built. And nimble parsec does not fit that well, but it would be great for spitting out tokens and then you could write some logic to analyze and build your AST.

The edge cases you have listed are those interpolated values or strings?

If the latter, then I’m not sure if my parser could handle those cases, as I only built it with the sigil in mind, not to migrate files of exotic SQL statements. Furthermore I have yet to implement DDL keywords in the parser and any keyword not tokenized by the parser would end up as ident tokens and passed through.

Did you try with Erlang’s yecc and leex?

Yes, I’ve used that in the past, and I might use it for this in the future. But generated parsers does not come without it flaws.

For this project I wanted to see if we could get all the benefits of Ecto.Query and Elixir with its composability but with less abstraction and more control.

I looked closely at this, and my parser does not support backticks yet. But it was pretty simple to add:

iex(18)> ~SQL[select `Little Bobby;drop tables`].query
[
  {:select, [line: 0, column: 6, end_line: 0, end_column: 32],
   [
     {:"``", [line: 0, column: 8, end_line: 0, end_column: 32],
      [
        {:ident, [line: 0, column: 8, end_line: 0, end_column: 32],
         [~c"Little Bobby;drop tables"]}
      ]}
   ]}
]

For what it’s worth, it looks like your examples got botched. I believe that only quotes and backticks are valid SQL syntax, but with that in mind, my parser would have trouble with O\Malley, but that also smells like invalid SQL, and should be: 'OMalley’:

iex(26)> ~SQL[select  'O`Malley'].query
[
  {:select, [line: 0, column: 6, end_line: 0, end_column: 17],
   [
     {:"''", [line: 0, column: 9, end_line: 0, end_column: 17],
      [
        {:ident, [line: 0, column: 9, end_line: 0, end_column: 17],
         [~c"O`Malley"]}
      ]}
   ]}
]

Awesome library, thank you for sharing this!

After a cursory look, my 2c:

Currently we have:

iex> ~SQL"SELECT 42"
select 42

I’d consider using ~SQL[...] or #SQL[...] in the inspect implementation:

iex> ~SQL"SELECT 42"
~SQL[select 42]

Currently we have:

x = 1 ; ~SQL"SELECT #{x}"

By convention we use interpolations with lowercase sigils but (to my dismay) we can’t have lowercase multi letter ~sql so another idea would be to have slightly different interpolation syntax, say:

x = 1 ; ~SQL"SELECT {x}"

Besides following convention I think doing this would actually be a security feature, never ever allow users to use string interpolations anywhere near SQL. Once users feel comfortable using this library that does the right thing they will inevitably (ok, I’m projecting, I definitely will) call sql = "select #{x}" ; Repo.query!(sql) and that can turn into a bug pretty fast. Using a distinct syntax like {...} prevents this. In hindsight, Repo.query! accepting sanitized strings (akin to, say, Phoenix.HTML.Safe) might have been a better default.

Thanks for the feedback, I’m currently working my way through the SQL grammar, once I can generate a parser from the BNF file, then we can make a decision based on our options, from what I can tell then braces are valid SQL syntax, but I couldn’t find any #.

As I’m polishing off the next version which currently is scheduled to be released in the coming weeks, and I got two exciting sneak peaks, we’re now more or less conformant with SQL 2016 with over 900 generated tests and SQL generation is 400-650x faster than Ecto.

This is amazing!