I added AI Text-to-MySQL to my analytics app
docs.uxwizz.com
docs.uxwizz.com
I have been building (for 13 years) a self-hosted website analytics[0] application, where the database runs on MySQL.
I decided to jump on the AI-hype wagon and add some AI integrations in my app. The obvious integration was "ask the AI questions about the stats stored in the database". Questions like "How many visitors came from Google in last 30 days?" or "which product is sold the most on Sundays?".
Being self-hosted, I didn't want to send any of your data to the OpenAI API, so my solution, as described in the docs was:
1. You type your question.
e.g. "How many visitors came from Google versus directly?"
2. The question, together with the database schema and some instructions (system prompt) are sent to ChatGPT.
3. The AI generates a MySQL query that answers your question.
4. The MySQL query is displayed to you. You can then send the query for execution to your local database to get the results.
5. The output is displayed as JSON in the chat.
The AI (even GPT 4) sometimes struggles to generate valid SQL queries, but most of the times it works as expected. It is really fun to play with.
I could give more SQL examples/instructions to the AI, but that considerably increases the price (tokens)/query. If anyone knows if I can somehow make the system prompt "global" so it doesn't use tokens for each completion, please let me know.
One example question that fails (that a client mentioned): "what two pages did users visit before visiting the /pricing page?". This is actually a quite tricky MySQL query, and it has several edge cases. If any SQL guru knows a good/efficient SQL answer to this question, write it below. I linked the db structure[1].
So far the reception has been great, got some nice emails from users.
ChatGPT gets a bit expensive, so I am planning to add other different APIs (anyscale?) and even local Ollama support.