Show HN: GitHub Copilot for SQL
1 comments
I have worked on this idea for almost 2 years now and the problem I have found is; like APL, numpy and others, sql is a language of thought. Once understood even a bit, english is vastly less efficient. How are your experiences with this?
I agree. I see two use cases here: 1) an audience of the people that havent' touched SQL in a while (like me) or less (even non) technical folks. You can get quite complex queries without help from someone that knows SQL. 2) I asked for a P99 query that used some Postgres built-in functions I never heard of. I would take me some googling to find out even if I were used to SQL. Or I would take more time to come up with a more complex solution.
Here's my prompt and the query:
-- distribution of byte sizes of sessions without review with avg, median, p90, p95, and p99 -- ERROR: column "blob_stream" does not exist``` SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY octet_length(blob_stream_id::text)) AS median, percentile_cont(0.9) WITHIN GROUP (ORDER BY octet_length(blob_stream_id::text)) AS p90, percentile_cont(0.95) WITHIN GROUP (ORDER BY octet_length(blob_stream_id::text)) AS p95, percentile_cont(0.99) WITHIN GROUP (ORDER BY octet_length(blob_stream_id::text)) AS p99, avg(octet_length(blob_stream_id::text)) AS avg FROM public.sessions s LEFT JOIN public.reviews r ON s.id = r.session_id WHERE r.session_id IS NULL AND s.blob_stream_id IS NOT NULL;
Here's my prompt and the query:
-- distribution of byte sizes of sessions without review with avg, median, p90, p95, and p99 -- ERROR: column "blob_stream" does not exist``` SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY octet_length(blob_stream_id::text)) AS median, percentile_cont(0.9) WITHIN GROUP (ORDER BY octet_length(blob_stream_id::text)) AS p90, percentile_cont(0.95) WITHIN GROUP (ORDER BY octet_length(blob_stream_id::text)) AS p95, percentile_cont(0.99) WITHIN GROUP (ORDER BY octet_length(blob_stream_id::text)) AS p99, avg(octet_length(blob_stream_id::text)) AS avg FROM public.sessions s LEFT JOIN public.reviews r ON s.id = r.session_id WHERE r.session_id IS NULL AND s.blob_stream_id IS NOT NULL;
Yes, English is a terrible choice for expressing anything non-ambiguously.
Programming is a precision art.
But, there are orders of magnitude more people who know english way better than sql. That was me, 15 years ago when i started with SQL.
Programming is a precision art.
But, there are orders of magnitude more people who know english way better than sql. That was me, 15 years ago when i started with SQL.
The simpler the task I'm working on, the higher the likelihood it gets it right. This is where I get the most benefit from Copilot, as I already understand what I'm coding, why I'm coding it, and how it should appear. Copilot sometimes saves me the 5-30 seconds it would take to write it manually. Over the course of a day, these saved moments add up.
But I always found myself turning to ChatGPT for help writing SQL. I would 1) run a query to get the schema. 2) Feed schema to ChatGPT. 3) Ask GPT to write my query. 4) Paste query into client. Solid results, but the workflow was not great with lots of copy and paste and multiple apps.
I work at Hoopdev, an infrastructure access gateway and we happen to have a build-in web client. Therefore our language server has a REST API that we could hook to GPT4. But our first attempt failed. We tried the Ask AI box UX, which only solved half the problem: providing the schema as context. Not getting the way was still missing. The bad suggestions required another prompt to get fixed.
Therefore we went on to build the copilot experience [0], but the opposite of Github copilot happened: most suggestions are good. This is because the only context for writing SQL is the database schema and what you want to do, all the model has to do is translate your english query to another language (SQL) using a small context (schema), as opposed to full codebases.
Hoopdev's client is web-based, because of that you can try it in 15 seconds. After signing up you get a demo Postgres ready to be queried in the web [1]. If you don't wanna sign up, run it yourself and use your own OpenAI keys [2]. Please let me know what you think. any feedback is welcome. Is this helpful? Do you know/use a better alternative for SQL?
0. https://hoop.dev/blog/introducing-an-ai-powered-query-builde... 1. https://hoop.dev/start 2. https://hoop.dev/docs/deploy/AWS