Chat with MySQL

Meet MySQL Assistant: Access database info seamlessly with natural language. No SQL expertise required. Simply ask questions, receive clear responses.

Comment

Inspiration

In an ideal world, there should be no barriers or gatekeepers when it comes to accessing information. A person, irrespective of their technical prowess or familiarity with SQL or databases, should be able to independently extract data to accomplish their tasks. They should be able to interact with databases as effortlessly as they converse with fellow humans - in a natural language. Imagine a world where one could simply ask the database in English, instead of SQL, and receive the desired information in a comprehensible, natural language. This is the future we envision - a future where data is accessible to all, not just the technically inclined.

What it does

The MySQL Assistant revolutionizes data interaction by providing information in a user-friendly, natural language format. It accepts queries in everyday language, eliminating the need for SQL knowledge. For instance, if you ask, “How many countries have Spanish as an official language?”, the Assistant responds in plain English with the precise count. Additionally, it offers the corresponding SQL query as a reference. This innovative approach simplifies data extraction, making it accessible to all, regardless of their technical expertise. No more gatekeeping - just straightforward, effective data interaction.

How I built it

Langchain: The mysql-connector plays a crucial role in Langchain by establishing a connection with the MySQL database, extracting the database schema, and executing database queries. The features of AI and Human Messages contribute to the creation of the app's chat-like interface.

Streamlit: Streamlit is a user-friendly tool that facilitates the creation of web apps and the showcasing of our work. It's a time-saver and was employed to construct the entire web app for this project.

Gemini and Gemini API: The seamless conversion from user-query to SQL-query and back from SQL-query response to natural language is made possible by the freeflow prompt functionality of Gemini and Gemini API.

1) get_sql Function: This function takes in the Database schema, chat history, and user query. It utilizes a well-curated template to convert the user query into an SQL query.

2) get_response Function: This function takes in the Database schema, chat history, user query, and the generated SQL query from the get_sql function. It runs the generated SQL query on the database and feeds the result into a well-curated prompt which also takes in the schema, chat history, and user query.

The process can be summarized as follows: User query + Data Schema -> LLM -> SQL query -> Run Query -> LLM -> Natural Language Answer. This flow represents the core functionality of Langchain.

Challenges I ran into

1) Template Creation: Crafting a well-curated template was a significant challenge. This was accomplished through a process of trial and error, requiring both patience and precision.

2) Langchain Support: At present, Langchain does not provide support for Gemini in the same manner as it does for ChatGPT models. This posed a challenge as using chat prompt templates and chaining them was not an option. To overcome this, f-strings were employed to appropriately structure the prompt and function class.

3) Exception Handling: Designing the model to effectively manage any exceptions that arose during its operation was another hurdle. This was crucial to ensure the system's robustness and reliability. Despite the challenges, this was successfully implemented, enhancing the overall performance of the system.

Accomplishments that I am proud of

1) Successfully developing this system without the assistance of Langchain's chaining prompts, templates, and runnable passthroughs is a significant achievement.

2) I'm proud to have developed this system to a level where anyone can extract basic, beginner-friendly information from databases using natural language. This makes the system accessible and user-friendly.

3) Lastly, learning and utilizing Gemini and Gemini API to build a fully functional app has been a rewarding experience. This accomplishment has not only enhanced my technical skills but also contributed to the successful completion of the project.

What I learned

Learning how to utilize Gemini for custom purposes and developing AI applications without the aid of major Langchain features were significant aspects of this project. It was enlightening to discover that similar use cases could be achieved through traditional programming. This experience has enhanced my coding skills and encouraged me to think of alternative uses.

Moreover, this project has been instrumental in training my mind towards system design and architecture design.

What's next for Chat with MySQL

1) Support other types of databases.

2) Optimization: Scanning Database at every user request is not optimal. So, need to find a better and optimal solution.

3) Databases on cloud: Right now, this app only works locally. The next step would be to upgrade this app so that it supports databases on cloud with appropriate security measures. This will help me to deploy this app on cloud. But for now, this only works locally.

Built With

Share this project:

Updates