Skip to content

README.md

Latest commit

 

History

History
258 lines (162 loc) · 8.19 KB

README.md

File metadata and controls

258 lines (162 loc) · 8.19 KB

Multi-Container Database Application

Purpose

This project is designed for educational purposes. It aims to help you learn how to:

  • Set up and configure Docker containers.
  • Create and manage a MariaDB database.
  • Perform CRUD (Create, Read, Update, Delete) operations using SQL and Python from within the localhost
  • Connect a simple Python application in one container to a MariaDB database in another

Citation Instructions

If you use this repository in your work, please cite it as follows:

GitHub Repository: multi-container-crud-app

Suggested Citation:

Please ensure proper attribution when using or modifying this work.

Project Roadmap

1. Define the Database Schema

📌 Milestone: Create a SQL script to define the database structure (tables, relationships, constraints).

📄 File: schema.sql

🔹 This file will contain CREATE TABLE statements for your three tables.

2. Set Up Sample Data

📌 Milestone: Create a SQL script to insert test data into the tables.

📄 File: seed.sql

🔹 This file will contain INSERT INTO statements with dummy data to test queries.

3. Configure Docker for MariaDB

📌 Milestone: Create a Docker environment to run MariaDB.

📄 Files:

  • docker-compose.yaml (to manage services like MariaDB).
  • .env (to store environment variables). Use .env-template as a reference. Don't use quotes or spaces.

🔹 This ensures MariaDB runs in a container and is easy to start/stop.

🔹 Data persistence is achieved using a named Docker volume (mariadb_data_volume).

See section "How to Instantiate the MariaDB Container" to run the project at this stage.

Note, when running docker compose up:

  • If you get a unable to get image error, it may be that docker engine is not running
  • If port 3306 is unexpectedly already in use, it may be mysql. If so kill the process in task manager.

4. Connect Python to MariaDB

📌 Milestone: Write a Python script to run on the host machine to connect to the database, run queries, and retrieve data.

📄 File: src/db_connect.py

🔹 This script will use a MariaDB library (mariadb) to interact with the database. I chose mariadb over sqlalchemy because I will use SQL scripts instead of using ORM functionality so mariadb will suffice.

🔹 Running the Script:

  1. Install Dependencies:

    pip install -r requirements.txt

  2. Run the Script:

    Ensure that the MariaDB container is running before executing the script.

    python src/db_connect.py

    This will connect to the MariaDB instance running in the Docker container and print a success message if the connection is established.

5. Explicit Database Creation (Optional)

📌 Milestone: Automate database setup with Python.

So far, docker-compose.yaml configuration already ensures that init.sql runs when the MariaDB container is started. Recall, that this was achieved by mounting the /sql directory to /docker-entrypoint-initdb.d in the container, which is a special directory that MariaDB uses to initialize the database.

Now we will create a script, setup_db.py, that handles the database creation directly.

While docker-compose.yaml handles the initial setup of the database, there are some scenarios where having a setup_db.py script can be beneficial:

  • Reinitialization: If you need to reinitialize the database without restarting the container, a Python script can be run independently to reset the database state
  • Environment Flexibility: The script can be used in environments where Docker is not available or not preferred, such as local development without containers
  • Additional Logic: The script can include additional logic, such as conditional checks, logging, or more complex initialization steps that are not easily handled by SQL scripts alone
  • CI/CD Integration: The script can be integrated into CI/CD pipelines to ensure the database is correctly set up before running tests or deploying the application

📄 File: setup_db.py

🔹 This script will:

✅ Create the database (if it doesn’t exist).

6. Initialize Database Schema and Data

📌 Milestone: Create tables and populate with initial data.

📄 File: initialize_db.py

🔹 This script will:

✅ Connect to the database created by setup_db.py

✅ Execute schema.sql to create the tables

✅ Execute seed.sql to populate tables with test data

🔹 Running the Script:

python src/initialize_db.py

7. Implement CRUD Operations

📌 Milestone: Create a comprehensive CRUD interface that can be used from localhost.

📄 File: crud_operations.py

🔹 This script will implement:

  • Create: Insert new records
  • Read: Retrieve existing records
  • Update: Modify existing records
  • Delete: Remove records

8. Containerize the Python App

📌 Milestone: Run your Python scripts inside a Docker container.

📄 File: Dockerfile (for Python container).

🔹 This allows your Python app to run in a container alongside MariaDB.

  • Added python-app service to docker-compose.yaml
  • Created dedicated python-app directory for application files
    • Dockerfile - Container configuration for Python app
    • requirements.txt - Python dependencies
    • run.sh - Application startup script
    • Python source files (*.py)

9. Return logging back to the host machine

📌 Milestone: Add logging to the python-app that saves logs to a file. A Docker volume will persist the logs into the localhost.

The logs will show:

  • Timestamp for each log entry
  • Log level (DEBUG, INFO, ERROR, etc.)
  • Component name
  • Detailed message

The logs will be available within the logs directory in the localhost and will be overwritten each time the container runs.

This setup will help you:

  • Debug database connection issues
  • Track SQL query execution
  • Monitor application state
  • Troubleshoot errors in real-time

How to Instantiate the MariaDB Container

  1. Clone the Repository:

    git clone https://github.com/francisco-camargo/multi-container-crud-app.git
cd multi-container-crud-app

  2. Create the .env File: Copy the .env-template file to .env and fill in the required environment variables.

    cp .env-template .env

  3. Build and Start the MariaDB Container: Use Docker Compose to build and start the MariaDB container.

    docker compose up --build -d

  4. Initialize the Database: Run the setup script to create and initialize the database:

    python src/setup_db.py

  5. Stop the MariaDB Container: To stop the MariaDB container and clean up, use one of the following approaches:

    # Just stop the container but keep the database volume
docker compose down

# Stop the container and remove the declared volume
docker compose down -v

# To delete just the database but keep the container running:
python src/delete_db.py

  6. Access the MariaDB Container: To access the MariaDB container, use the following command:

    docker exec -it mariadb_container bash

    To enter MariaDB run

    mariadb -u user -puserpassword

    Or, alternatively, run

    docker exec -it mariadb_container mariadb -u user -puserpassword

    Either way, once you are in the MariaDB program, you can verify that the database is up by running

    SHOW DATABASES;

>>>
+--------------------+
| Database           |
+--------------------+
| crud_db            |
| information_schema |
+--------------------+
  • information_schema: A system database that contains metadata about the database server and its objects. It is automatically created and managed by MariaDB.
  • crud_db: A user-defined database created for your application to store your application's data. The name is defined in .env