Conversational SQL: Enabling User-friendly Database Interactions with Gemini-Pro

I got the idea to build this application after watching the Developer’s Keynote at the Google Next ’24 event. I saw how the CTO of honeycomb used it in her demo for querying API log data and decided to recreate that with something more relatable.

Introduction

In today’s data-driven world, accessing and querying databases is a crucial task for businesses of all sizes. However, for non-technical users, crafting SQL queries can be intimidating and challenging. That’s where this application comes in!

Powered by Python, Streamlit, and the innovative Gemini API, I built an application that aims to bridge the gap between non-technical users and databases. By leveraging natural language processing (NLP) capabilities, this app transforms everyday language into SQL queries, simplifying the database querying process for users with varying levels of technical expertise.

In this article, we’ll learn how to use Python and Streamlit to create an intuitive user interface and how the Gemini API enables us to generate SQL queries from user input. The aim of this project is to empower non-technical users to seamlessly interact with databases and unlock valuable insights from their data.

Requirements

1. A code editor of choice.
2. Basic understanding of Python.
3. Basic understanding of relational databases and how to query them.
4. Gemini API key.

Building the Application

In this section, I have broken down building the application into the following steps:

1. Setting up the development environment.
2. Integrating the Gemini API for natural language processing.
3. Implementing the SQL querying logic.
4. Setting up the user interface.
5. Testing the application

I will go ahead to explain each of these steps in great detail now.

Setting up the development environment

In this section, I have broken down setting up the development environment into two areas:

1. python environment setup.
2. database setup.

In order to setup the python environment, you can use any IDE you prefer and create a new project. After doing this, you should create a virtual environment so there is no need to download the dependencies globally. To set up the virtual environment, you need to create the virtual environment and then activate it. You can do this using the commands:

python3 -m venv venv # create the virtual environment

source venv/bin/activate # activate the virtual environment

The next thing to do after setting up the virtual environment is to install the dependencies in the activated virtual environment. To do this you need to create a requirements.txt file, and add the following lines to it:

streamlit
google-generativeai
python-dotenv

These are the required dependencies to run the app, to add them, you need to run the following command:

pip install -r requirements.txt

That’s all for the python environment setup. Next is the database setup.

For the database, ensure you have MySql server installed and running on your machine, then you can create a database or use an existing database to build the application. For my case, I already have an existing database and that is what I will use in this project. You can get the database schema structure here.

Once you have done this, you need to create a .env file and configure your database credentials so the application can have access to the database. You can do this by copying the following to your code, and updating them appropriately:

DB_HOST=localhost
DB_USER=<DB_USER_HERE>
DB_PASSWORD=<DB_PASSWORD_HERE>
DB_NAME=<DB_NAME_HERE>

Integrating the Gemini API for natural language processing

Congratulations for reading this far!!! At this stage, we will be integrating Gemini-Pro into our application with the help of their python SDK that we previously installed along with other dependencies in the requirements.txt file.

To integrate Gemini, we need to carry out the following steps:

1. Get the Gemini API key and add the value to the .env file.
2. Configure Gemini using the Gemini python SDK and the API key.
3. Write a prompt that will give Gemini the instructions to generate SQL queries.
4. Create a function that will take in user inputs and return an SQL query.

You can get the Gemini API key from Google AI studio. After successfully completing this, add it to the .env file. Your .env file should look like this at this stage:

DB_HOST=localhost
DB_USER=<DB_USER_HERE>
DB_PASSWORD=<DB_PASSWORD_HERE>
DB_NAME=<DB_NAME_HERE>
GOOGLE_GEMINI_API_KEY=<GEMINI_API_KEY_HERE>

To configure Gemini into our application, we first create an app.py file in the root directory (we will write the core logic here) and add the following code:

from dotenv import load_dotenv
import os
import google.generativeai as genai

load_dotenv()  # load all the environment variables

genai.configure(api_key=os.getenv("GOOGLE_GEMINI_API_KEY"))  # Configure Gemini api key

In the code above, we load our API key from the .env file and configure Gemini in our application with the help of their SDK.

Creating the prompt is a crucial part of our app setup. Here, we tell Gemini what kind of SQL queries to make by outlining the tables and columns in our database. This helps Gemini generate queries that fit our app perfectly.

I have created a prompt below, tailored to the tables and columns in the database which you can see here:

prompt = [
    """
    You are an expert in SQL queries!

    The SQL database contains the following tables:

    Table 1: user
    Columns: id (BINARY(16) NOT NULL UNIQUE), password_hash (VARCHAR(64)), first_name (VARCHAR(64)), last_name (VARCHAR(64)), email (VARCHAR(30) UNIQUE), phone_number (VARCHAR(15) UNIQUE), image_url (VARCHAR(256)), activated (BIT NOT NULL), user_type (VARCHAR(11) NOT NULL), created_by (VARCHAR(255)), creation_date (DATETIME), last_modified_by (VARCHAR(255)), last_modified_date (DATETIME)

    Table 2: authority
    Columns: id (BINARY(16) NOT NULL UNIQUE), authority_name (VARCHAR(64) NOT NULL), created_by (VARCHAR(255)), creation_date (DATETIME), last_modified_by (VARCHAR(255)), last_modified_date (DATETIME)

    Table 3: user_authority
    Columns: user_id (BINARY(16) NOT NULL), authority_id (BINARY(16) NOT NULL)

    Table 4: currency
    Columns: id (BINARY(16) NOT NULL UNIQUE), name (VARCHAR(64) NOT NULL), symbol (VARCHAR(30) NOT NULL UNIQUE), enabled (BIT NOT NULL), created_by (VARCHAR(255)), creation_date (DATETIME), last_modified_by (VARCHAR(255)), last_modified_date (DATETIME)

    Table 5: transactions
    Columns: id (BINARY(16) NOT NULL UNIQUE), amount (DECIMAL(64) NOT NULL), type (VARCHAR(15) NOT NULL), purpose (VARCHAR(35) NOT NULL), account_id (BINARY(16) NOT NULL), reference (BINARY(16) NOT NULL UNIQUE), status (VARCHAR(30) NOT NULL), description (VARCHAR(255)), sender_account (VARCHAR(20) NOT NULL), receiver_account (VARCHAR(20) NOT NULL), created_by (VARCHAR(255)), creation_date (DATETIME), last_modified_by (VARCHAR(255)), last_modified_date (DATETIME)

    Table 6: account
    Columns: id (BINARY(16) NOT NULL UNIQUE), available_balance (DECIMAL(64) NOT NULL), reserved_balance (VARCHAR(30) NOT NULL), locked (BIT NOT NULL), status (VARCHAR(20) NOT NULL), type (VARCHAR(20) NOT NULL), currency_id (BINARY(16) NOT NULL), user_id (BINARY(16) NOT NULL), account_number (VARCHAR(20) NOT NULL UNIQUE), created_by (VARCHAR(255)), creation_date (DATETIME), last_modified_by (VARCHAR(255)), last_modified_date (DATETIME)
    Please provide an English question related to these tables, and I'll help you generate the corresponding SQL query.
    also the sql code should not have ``` in beginning or end and sql word in output
    """

]

Note: to avoid runtime errors we add the `sql code should not have ``` in beginning or end and sql word in output` so that Gemini creates clean SQL queries that contain no illegal characters, hence making the queries run smoothly.

After all this is done, the next thing is to create a function that will take in a user’s text input and return SQL queries generated by Gemini. This is shown in the code below:

def get_gemini_response(user_text, ai_prompt):

    """
    :param user_text: user question from the frontend in the form of text
    :param ai_prompt: text we use to prompt Gemini
    :return: gemini SQL query
    """

    model = genai.GenerativeModel('gemini-pro')
    ai_response = model.generate_content([ai_prompt[0], user_text])
    return ai_response.text

Implementing the SQL querying logic

Anytime we query the database, we always get results that includes rows retrieved or affected. At this stage, we are going to write logic that connects to and queries the MySQL database then returns the results (rows). The database is queried using the SQL script generated by Gemini. This is done using the logic below:

import mysql.connector

def read_sql_query(sql_query):

    """
    :param sql_query: SQL query to query the db, generated by Gemini
    :return: queried data rows
    """

    # connect to the db
    conn = mysql.connector.connect(
        host=os.getenv("DB_HOST"),
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASSWORD"),
        database=os.getenv("DB_NAME")
    )

    # execute SQL queries
    cur = conn.cursor()
    cur.execute(sql_query)
    rows = cur.fetchall()
    conn.commit()
    conn.close()

    # return query results
    return rows

Setting up the user interface

Now that we have built the core logic of our application, the next thing on our list is to create a friendly user interface that people can use to write their texts. I have done this using streamlit, an open-source tool for creating simple UIs.

# Creating a streamlit app
st.set_page_config(page_title="Query Databases with Gemini Pro")
st.header("Gemini App To Retrieve Data With Normal Text")

question = st.text_input("Enter your text:", key="input", placeholder="Type your text here")

submit = st.button("Ask the question")

# if submit is clicked
try:
    if submit:

        if question is None or question == "":
            raise Exception("question cannot be null")

        print("user input: " + question)
        response = get_gemini_response(question, prompt)

        print("gemini query: " + response)
        response = read_sql_query(response)

        st.subheader("The Response is")
        for row in response:
            print(row)
            st.header(row)

except Exception as exception:
    print(exception)
    st.header("could not generate query from your input")

The logic also includes the instructions to be executed when you enter text and click on the submit button. It ensures the user input is not null or empty, then it uses that input and the prompt we created earlier to generate a response which is an SQL query. This is then used to query the database which will return the results. This is what the UI looks like:

Testing the application

In this section, I will test the application and ensure it works correctly. To run the application, we use the following command:

streamlit run app.py #app.py is the name of my file, it should be streamlit run <file_name.py>

The application is running perfectly, let us now ask our questions. I will ask questions, then show you the SQL queries under the hood.

Q1: how many users exist?

In the pictures above, we can see the text that the user passes into our application, and the result. We can also see the gemini query that was generated based on the text entered.

I will do this for 2 more complex examples and display the results.

Q2: What user has the most money in his accounts?

what happens under the hood

Q3: What is the most common account type:

what happens under the hood

That’s it! You have successfully created an application that queries a database using normal text. You can access the full code for this application here.

Applications of this technology

This technology simplifies accessing data from databases, especially for non-technical folks like managers. By allowing queries in everyday language, it speeds up the process and reduces the need for developers, helping teams make decisions faster.

Conclusion

Our application, built with Python, Streamlit, and the Gemini API, simplifies database querying for non-technical users. By automating SQL query generation from natural language input, we’ve made data exploration more accessible and intuitive. I urge you to explore the application and experience firsthand how it can streamline your database querying process. I also encourage you to try it with your own datasets.