How to generate SQL statements with ChatGPT
1–10 of 48 posts
Re: How to generate SQL statements with ChatGPT
#2the difficulty at my job is the many tables have no documentation, the transform code is inaccessible, and they were created by different teams by people who arent here any more.
by the time i figure out what to write in the prompt most of my work is done.
Re: How to generate SQL statements with ChatGPT
#3Yes! You can also instruct the LLM to constrain the generated statement with specific clauses, so that the notion of "my data" in the english query comes with implicit constraints. This can be made safe using a validation scheme, which is what HeimdaLLM[1] does. It guarantees that generated queries are only joining to allowed tables, that they have required constraints, that they only select allowable columns, and that they only execute allowed functions (and more).
The end result is that you can produce safe SQL queries on behalf of your users, allowing your users to use natural language to query their data in your database.
For example, in this demo[2], I ask it:
query("how much have i spent renting movies, broken down by month")
1. https://github.com/amoffat/HeimdaLLM 2. https://github.com/amoffat/HeimdaLLM/blob/dev/notebooks/demo...
Re: How to generate SQL statements with ChatGPT
#4wow, blast from the past. Ben Forta's books taught me ColdFusion WAY back in the late 90's. Very cool to see him still active in this space.
Re: How to generate SQL statements with ChatGPT
#5Yesterday I had the case where I needed to create analytics tables in Clickhouse based on typescript types for around 150 types.
Thought I'd give it a try and gave chatGPT 2 examples of existing tables and the corresponding TS types and asked it if it understood what it should do next. It explained the existing logic and also 2 edge cases hidden (e.g. if a type was boolean the column was UInt8 (to save 0/1))
Then I pasted all types in batches of 15 in and it generated with only 2-3 corrections needed, probably saving me around 1-2h of manual checking and/or creating. It understood that certain fields are non LowCardinality and therefore not used it.
Re: How to generate SQL statements with ChatGPT
#6If you can export your data to SQLite first (my https://datasette.io/tools/db-to-sqlite tool can help with that) you can upload the SQLite database directly to ChatGPT Code Interpreter and have it run the queries for you, since sqlite3 is in the Python standard library
If you do this, it will automatically retry with a new SQL query if it gets any error messages.
Re: How to generate SQL statements with ChatGPT
#7If you know SQL why is this better than just writing SQL? If you don’t know SQL how do you know it’s correct?
I had to forbid my coworkers from using chatgpt to generate SQL because it kept doing things slightly wrong - like missing parens when dealing with AND and OR in a where statement.
Re: How to generate SQL statements with ChatGPT
#8the difficulty at my job is the many tables have no documentation, the transform code is inaccessible, and they were created by different teams by people who arent here any more. by the time i figure out what to write in the prompt most of my work is done.
lol, a lot of time I just copy paste the CREATE syntax into chatgpt and ask for help with how things are connected, it understands it pretty damn well.
At some point some startup is going to come up with a backend-in-a-box where you just describe the tables, rules and it will spit out all Graphql mutations and queries, react components, an auth layer, etc...
ChatGPT out of the box can do all of that right now, but it needs a coordinating tool, perhaps to even make unit tests and alter statements (for changes) and mostly, so that we don't blow out the context. That's the key issue - context. If context was unlimited, then we can just emit everything in one go. So initial structure and some prompting should lead to everything else.
Re: How to generate SQL statements with ChatGPT
#9This isn't limited to SQL either; I'm working on chaining together natural language queries to create Elasticsearch queries.
Fortunately, the queries themselves are small, so the rest of tokens are spent on context that include index names and schema.
This doesn't necessarily scale well in other contexts and can get pricy quick!
Re: How to generate SQL statements with ChatGPT
#10If you know SQL why is this better than just writing SQL? If you don’t know SQL how do you know it’s correct? I had to forbid my coworkers from using chatgpt to generate SQL because it kept doing things slightly wrong - like missing parens when dealing with AND and OR in a where statement.
You send the produced query through a parser and a validator. The parser ensures it is syntactically correct, the validator ensures it is only accessing allowed data.