Bringing Together Containers & SQL

Having read about it in theory, I used Docker and understood how containerisation actually works in one of my recent mini projects. This mini project is a combination of some tools and technologies that are new to my knowledge (Docker and Flask) and tools such as SQL and Python that I have used in the past. The application builds visualisations based on the queries it asks the users to input on the dashboard; a dashboard for data stored on SQL databases.

Architecture

Flask API & connecting to SQL

First, I established the connection to the SQL database using the mysql.connector written within a function. Depending upon your database, user and host, the credentials need to be updated.

def get_connection():
    try:
        logger.debug("Connecting to MySQL database...")
        connection = mysql.connector.connect(
            host='db', user='root', password='yourpassword', database='hotel'
        )
        logger.debug("Successfully connected to MySQL database")
        return connection
    except mysql.connector.Error as err:
        logger.error(f"Error connecting to database: {err}")
        return None

The heart of the application is built using Flask. The Flask API works with the endpoint /run_query which is programmed to handle both GET and POST requests. The GET request is in charge of retrieving the input from the users and POST is in charge of sending the requests to the server.

@app.route('/run_query', methods=['GET', 'POST'])

The functionality defined at the API endpoint is a simple retrieval and visualisation process. Query validation is done to check if the query is empty or not and is then executed on the databases. Flask uses the cursor to access the results, which are then stored as a list of rows, along with which column names are extracted, for plotting purposes.

def run_query():
    chart_image = None 
    if request.method == 'POST':
        query = request.form.get('sql_query') 

        if not query:
            logger.debug("No query entered by the user")
            return render_template('index.html', chart_image=None)

        connection = get_connection()
        cursor = connection.cursor()

        try:
            logger.debug(f"Executing query: {query}")
            cursor.execute(query) 
            result = cursor.fetchall() 
            columns = [desc[0] for desc in cursor.description] 
            logger.debug(f"Query Result: {result}")
            logger.debug(f"Columns: {columns}")

            if not result:
                logger.debug("No results returned from the query")
            x = [row[0] for row in result]
            y = [row[1] for row in result]

            if not x or not y:
                logger.debug("X or Y values are empty, no chart will be generated") 

A key feature I have integrated is converting the outputs as graphs. The application is good for scenarios when more than one variable is being fetched, which means the variables can be plotted against one another. The charts are created using Matplotlib, and the choice of chart can vary as per the data being fetched. For the purpose of demonstration, I have implemented a bar chart. The chart is then saved as an in-memory image, encoded into base64 format, and sent back to the frontend. This ensures the chart can be displayed on the dashboard without saving any files locally.

            fig, ax = plt.subplots()
            ax.bar(x, y)
            ax.set_xlabel(columns[0])
            ax.set_ylabel(columns[1])
            ax.set_title('Query Result')

            img_io = BytesIO()
            fig.savefig(img_io, format='png')
            img_io.seek(0) 
            img_data = base64.b64encode(img_io.getvalue()).decode('utf-8') 
            chart_image = img_data

        except mysql.connector.Error as err:
            chart_image = None 
            logger.error(f"Error executing query: {err}")
        finally:
            connection.close() 
    return render_template('index.html', chart_image=chart_image)

Frontend

The frontend is rather basic for this application but does the job. The form (input of SQL query) collects the queries via a POST request. The frontend also displays a section for the chart, depending upon whether the query was executed or not. If a query is successfully executed, the chart is displayed in the form of an embedded image encoded in Base64 format. This ensures seamless integration without the need to store image files locally, making the application lightweight and efficient.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Data Dashboard</title>
</head>
<body>
    <h1>SQL Query Data Dashboard</h1>
    <form method="POST" action="/run_query">
        <textarea name="sql_query" rows="4" cols="50" placeholder="Enter your SQL query here..."></textarea><br><br>
        <button type="submit">Submit Query</button>
    </form>
    {% if chart_image %}
        <h3>Query Result Chart</h3>
        <img src="data:image/png;base64,{{ chart_image }}" alt="Chart">
    {% endif %}
</body>
</html>

Docker Components

First, the Dockerfile - A set of instructions that serves as an image for the Flask app. It includes specifications and commands that need to be run in the container. It automates and takes away the hurdle of manually having to install each and every dependancy. An important part of this file is to set the working directory (/app in this case), which is where all subsequent commands that come later in the file, will be run.

FROM python:3.9-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install -r requirements.txt
COPY . .
CMD ["python", "app.py"]

Next, the compose file - Essentially specifying the services that contain the multiple containers present in the application. In this case, I have specified two containers - Flask and SQL. Other than configuration details such as ports and environments, the containers are linked for the application to run. Here, the flask container depends on the SQL container for the data.

services:
  db:
    image: mysql:8.0
    environment:
        - MYSQL_ROOT_PASSWORD=${DB_PASSWORD}
        - MYSQL_DATABASE=${DB_NAME}
    ports:
        - "3307:3306"
    volumes:
        - db_data:/var/lib/mysql

  web:

    build: .
    ports:
        - "5002:5002"
    depends_on:
        - db
    environment:
        - FLASK_ENV=${FLASK_ENV}
        - DB_HOST=${DB_HOST}
        - DB_USER=${DB_USER}
        - DB_PASSWORD=${DB_PASSWORD}
        - DB_NAME=${DB_NAME}
    volumes:
        - .:/app
volumes:
  db_data:

Data population - A Learning

During the development of this app, I faced the error of queries not being executed due to 'data not existing'. This kept me wondering, because the connection with the SQL database was successfully established. Here is when I realised that using SQL while containerising the application was slightly different and needed some more setups.

A container is nothing but a virtual machine which works only when it has all its dependancies and data. While integrating containers and SQL, a separate container is created for the SQL database, which is where data will be extracted from. Something to note here is that, even though a container is created, does not mean the data has been copied into it. This is something that has to be done manually. Data from the local device can be copied into the container by creating a .sql file, which is a portable file and can be pasted by accessing the container in bash mode. Now that the container has been populated, we can get results as per queries.

Conclusion

Throughout the development, challenges such as managing SQL data within containers offered me valuable learning experiences, further solidifying my understanding of containerisation and database interactions. The project lays a foundation for building more robust and feature-rich applications in the future, with opportunities for scalability, improved frontend design, and enhanced user engagement.