Snowflake support/parameter syntax issue

[bglusman]

Hey! I was excited to see you just added DuckDB support a few days, I'm considering using AyeSQL for managing some snowflake queries, TBD whether with Avalanche or Snowflex as the adapter... I have a pair of minimally functional adapters I'm playing with locally to evaluate, but thought since you're adding support beyond ecto and postgres now, you might be interested in having the adapter here also? I had just opened an issue in Avalanche earlier today to ask if it made sense to host it there, but, I hadn't seeen DuckDB PR yet, so if you're expanding the built in support here, it probably makes more sense here?

At the moment everything works on simple queries, but for unclear reasons both libraries seem to support both the $1 and the ? syntax for query params, BUT, in both snowflex and avalanche (only verified in avalanche but seems like same issue in both), the $1 syntax works fine for ordinary queries, but breaks as soon as the params are used in a list via the {:in, list} syntax, though it works fine with the ? syntax... Unclear if this is a snowflake issue, library issue, or what, but, would you be open to a change that made which syntax to use when generating the SQL configurable or something/do you have any sense how difficult a change that would be?

For the moment I can just run String.replace(statement, ~r"\$\d+", "?") I suppose before executing the query, just feels a bit awkward 🤷‍♂️

[alexdesousa]

@bglusman Hey! Thank you for your interest in my library. At the moment, I'm not actively adding more SQL runners. The reason I added DuckDB was because it fits AyeSQL use case in my opinion. I saw myself writing queries for several CSV files in the near future, so it seemed to be a good use case (a bit different than Ecto and PostgreSQL use cases).

Either way, I've gotten similar requests in the past to allow chaning $_ to ?. Additionally, the code from the linked issue was similar to the runner I was using at some point for MS SQL Server which uses ? for parameters.

So, I'd like to know more about your use case and see what I can do to help 😁

[bglusman]

Ahh, that's fair... depending where we land I can revisit the possibility in the future here or in Avalanche, or as its own tiny hex library, but it may be trivial enough that it's not worthwhile. The use case is work ETL jobs out of snowflake across many teams, and just trying to figure out the most maintainable/composable way to have a pretty fair quantity of raw SQL... I'm curious about your use cases too, we're early on our journey and just seeing SQL complexity grow, and having only ever worked before with Ecto and other adapter libraries, I've never really deal with scaling and maintaining a lot of raw SQL before, especially across a decent size team, so trying to learn what I can about best practices and tooling/approaches that can help.

[alexdesousa]

@bglusman I see. I was checking the code today, and it could be potentially easy to make parameters names configurable e.g. $_ or ? via module configuration.

How are you planning to use AyeSQL and Avalanche together? Are you using an SQL runner? Or just avoid running the query and just use the AyeSQL query struct?

I have never used Avalanche, so I have no clue how you would start a connection and send a query to the database.

[bglusman]

Yeah, I'm already using it and for now just using String.replace which works well enough, but might be interesting to support this? I built a small runner for Avalanche, and a second one for Snowflex, both need the same String.replace trick though. There's no real "connection" for avalanche I don't think as its a REST API, though snowflex has a connection via odbc.