4 ms·

If you aren't already (1) telling Claude Code which flavor of SQL you want (there are several major dialects and many more minor ones) and (2) giving it access

If you aren't already (1) telling Claude Code which flavor of SQL you want (there are several major dialects and many more minor ones) and (2) giving it access to up-to-date documentation via MCP (e.g. https://github.com/arabold/docs-mcp-server https://github.com/arabold/docs-mcp-server) so it has direct access to canonical docs for authoritative grounding and syntax references, you'll find that you get much better results by doing one or both of those things.

impure-aqua 1y ago
Documentation on features your SQL dialect supports and key requirements for your query are very important for incentivizing it to generate the output you want. As a recent example, I am working on a Rust app with integrated DuckDB, and asked it to implement a scoring algorithm query (after chatting with it to generate a Markdown file "RFC" describing how the algorithm works.) It started the implementation with an absolute minimal SQL query that pulled all metrics for a given time window. I questioned this rather than accepting the change, and it said its plan was to implement the more complex aggregation logic in Rust because 1) it's easier to interpret Rust branching logic than SQL statements (true) and 2) because not all SQL dialects include EXP(), STDDEV(), VAR() support which would be necessary to compute the metrics. The former point actually seems like quite a reasonable bias to me, personally I find it harder to review complex aggregations in SQL than mentally traversing the path of data through a bunch of branches. But if you are familiar with DuckDB you know that 1) it does support these features and 2) the OLAP efficiency of DuckDB makes it a better choice for doing these aggregations in a performant way than iterating through the results in Rust, so the initial generated output is suboptimal. I informed it of DuckDB's support for these operations and pointed out the performance consideration and it gladly generated the (long and certainly harder to interpret) SQL query, so it is clearly quite capable, just needs some prodding to go in the right direction.
msikora 1y ago
Haven't heard of docs-mcp-server, but there is the very popular Context7 with 23k Github stars and more active development: https://github.com/upstash/context7 https://github.com/upstash/context7
- CharlesW 1y ago
  Great suggestion, thank you!

impure-aqua 1y ago

Documentation on features your SQL dialect supports and key requirements for your query are very important for incentivizing it to generate the output you want. As a recent example, I am working on a Rust app with integrated DuckDB, and asked it to implement a scoring algorithm query (after chatting with it to generate a Markdown file "RFC" describing how the algorithm works.) It started the implementation with an absolute minimal SQL query that pulled all metrics for a given time window. I questioned this rather than accepting the change, and it said its plan was to implement the more complex aggregation logic in Rust because 1) it's easier to interpret Rust branching logic than SQL statements (true) and 2) because not all SQL dialects include EXP(), STDDEV(), VAR() support which would be necessary to compute the metrics. The former point actually seems like quite a reasonable bias to me, personally I find it harder to review complex aggregations in SQL than mentally traversing the path of data through a bunch of branches. But if you are familiar with DuckDB you know that 1) it does support these features and 2) the OLAP efficiency of DuckDB makes it a better choice for doing these aggregations in a performant way than iterating through the results in Rust, so the initial generated output is suboptimal. I informed it of DuckDB's support for these operations and pointed out the performance consideration and it gladly generated the (long and certainly harder to interpret) SQL query, so it is clearly quite capable, just needs some prodding to go in the right direction.

msikora 1y ago

Haven't heard of docs-mcp-server, but there is the very popular Context7 with 23k Github stars and more active development: https://github.com/upstash/context7 https://github.com/upstash/context7

CharlesW 1y ago
Great suggestion, thank you!