Forem: Florian Zeba

#37 Running a Docker Container in a Docker Container

Florian Zeba — Fri, 02 May 2025 09:11:00 +0000

0. High-Level Introduction (Why Run Docker in Docker?)

Imagine you're using Docker to run your applications or build processes. Now, what if one of those processes, running inside a Docker container, needs to build another Docker image or start other Docker containers? This is the core idea behind "Docker-in-Docker".

While it sounds a bit like inception, this capability is surprisingly useful, especially in automated environments like Continuous Integration/Continuous Deployment (CI/CD) pipelines (e.g., Jenkins, GitLab CI) where build jobs run in containers but need to produce Docker images as output. It's also used for complex testing scenarios or specialized development environments.

However, allowing one container to control Docker operations introduces significant security considerations and technical nuances. This manual provides a detailed guide for technical users on how to achieve this, covering the common methods, their trade-offs, security implications, and practical examples. If you need a container to interact with the Docker API, this guide explains how to do it correctly and cautiously.

1. Technical Introduction

1.1 What is Docker-in-Docker?

Docker-in-Docker refers to the practice of running Docker commands and managing Docker containers from within another Docker container. This allows a containerized environment to interact with the Docker API, build images, and run sibling or child containers.

1.2 Common Use Cases

CI/CD Pipelines: Jenkins, GitLab CI, GitHub Actions, etc., often run build jobs inside containers. These jobs might need to build Docker images or run services using Docker Compose.
Testing Frameworks: Integration tests that require spinning up multiple containerized services (databases, APIs) managed by the test runner itself.
Development Environments: Providing developers with a consistent, containerized environment that includes the ability to build and run other containers.
Container Orchestration Development/Testing: Experimenting with tools that interact with the Docker API.

1.3 Key Approaches & Terminology (DinD vs. DooD)

While often used interchangeably, there's a distinction:

Docker-out-of-Docker (DooD): This involves mounting the host machine's Docker control socket (/var/run/docker.sock) into the container. The Docker client inside the container communicates directly with the Docker daemon running on the host. Containers launched this way are siblings to the container running the client, not children nested within it. This is the most common and often simpler method.
True Docker-in-Docker (DinD): This involves running a completely separate, isolated Docker daemon inside the container. This requires special privileges and configuration (like using the official docker:dind image). Containers launched this way are children of the inner Docker daemon.

This guide covers both approaches.

2. Prerequisites

Host Machine: A system (Linux, macOS, Windows with WSL2) with Docker Engine installed and running.
Docker CLI: Familiarity with basic Docker commands (docker run, docker build, docker ps, docker exec, etc.).
Understanding of Docker Concepts: Images, containers, volumes, networking, Docker socket.
(Optional but Recommended): Understanding of Linux permissions and security implications of privileged operations.

3. Method 1: Mounting the Host's Docker Socket (DooD)

This method allows a container to control the host's Docker daemon.

3.1 Concept

The Docker daemon listens for API requests on a Unix socket, typically located at /var/run/docker.sock on Linux. By mounting this socket file into a container using a volume (-v), the Docker client installed inside that container can connect to and control the host's Docker daemon.

3.2 Pros & Cons

Pros:

Simplicity: Relatively easy to set up with a simple volume mount.
Resource Efficiency: No overhead of running a second Docker daemon.
Shared Resources: Layers are shared with the host daemon, potentially speeding up builds and pulls if images already exist on the host.

Cons:

Security Risk: A container with access to the host's Docker socket effectively has root-equivalent privileges on the host system. It can start privileged containers, mount sensitive host directories, and interfere with other containers. This is the primary drawback.
Version Skew: Potential issues if the Docker client version inside the container is incompatible with the Docker daemon version on the host.
Environment Bleed: The container interacts directly with the host's Docker environment, which might not be desired for isolation purposes.

3.3 Implementation Steps & `docker exec` Access

Prepare a Dockerfile: Create an image that includes the Docker client CLI. Ensure the CMD or ENTRYPOINT keeps the container running (e.g., CMD ["sleep", "infinity"]).
Build the Image: Use docker build.
Run the Container: Use docker run with the -v /var/run/docker.sock:/var/run/docker.sock flag. Run it detached (-d) and give it a name (--name) for easy access (e.g., dood-controller).
Access the Container: Use docker exec -it dood-controller bash (or sh) to get an interactive terminal inside the running container.
Run Docker Commands: From the exec session, execute standard Docker commands (e.g., docker ps, docker run hello-world, docker build .). These commands will interact with the host's Docker daemon via the mounted socket. Containers started this way are siblings to dood-controller.

3.4 Code Example (Including `docker exec` usage)

Dockerfile (Installs Docker client on Debian)

# Use a base image
FROM debian:bullseye-slim

# Avoid prompts during installation
ENV DEBIAN_FRONTEND=noninteractive

# Install prerequisites and Docker client
RUN apt-get update && \
    apt-get install -y --no-install-recommends \
    apt-transport-https \
    ca-certificates \
    curl \
    gnupg \
    lsb-release && \
    mkdir -p /etc/apt/keyrings && \
    curl -fsSL https://download.docker.com/linux/debian/gpg | gpg --dearmor -o /etc/apt/keyrings/docker.gpg && \
    echo \
      "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.gpg] https://download.docker.com/linux/debian \
      $(lsb_release -cs) stable" | tee /etc/apt/sources.list.d/docker.list > /dev/null && \
    apt-get update && \
    apt-get install -y --no-install-recommends docker-ce-cli && \
    apt-get clean && \
    rm -rf /var/lib/apt/lists/*

# Keep the container running indefinitely
CMD ["sleep", "infinity"]

Build Command (on Host):

docker build -t my-docker-client .

Run Command (on Host):

# Ensure the user running this command has permissions for the host's docker.sock
# Run detached (-d) and give it a name
docker run -d --name dood-controller \
  -v /var/run/docker.sock:/var/run/docker.sock \
  my-docker-client

# Verify the container is running
docker ps

Access and Run Commands Inside (on Host):

# Get an interactive shell inside the running container
docker exec -it dood-controller bash

# Now, inside the 'dood-controller' container's bash session:
# These commands interact with the HOST Docker daemon

# List containers running on the HOST (will include 'dood-controller' itself)
echo "Running 'docker ps' inside the container:"
docker ps

# Run a new container (sibling to 'dood-controller') on the HOST
echo "Running 'hello-world' inside the container:"
docker run --rm hello-world

# List images available on the HOST
docker images

# Exit the container's shell
exit

Cleanup (on Host):

docker stop dood-controller
docker rm dood-controller

3.5 Security Considerations (DooD)

Never run untrusted images with the Docker socket mounted. This grants the image potential control over your host.
Permissions: The user inside the container needs permission to write to the socket. Often, the socket on the host is owned by root and group docker. You might need to:
- Run the container as root (less secure).
- Create a docker group inside the container with the same GID as the docker group on the host, and run the container process as a user belonging to that group. This requires knowing the host's GID beforehand.
Consider read-only mounts (-v /var/run/docker.sock:/var/run/docker.sock:ro) if the container only needs to query the Docker API.

4. Method 2: Running a Dedicated Docker Daemon Inside (True DinD)

This method runs an independent dockerd process inside your container.

4.1 Concept

You run a container based on an image specifically designed for DinD (like the official docker:dind image). This container starts its own Docker daemon process. To interact with this inner daemon, you typically run a second container (the "client") that connects to the inner daemon, often via TCP or by sharing a volume for the inner daemon's socket. This requires running the DinD container in --privileged mode due to the low-level system operations dockerd needs to perform.

4.2 Pros & Cons

Pros:

Better Isolation (Theoretically): The inner Docker daemon is separate from the host daemon. Actions inside don't directly affect the host's Docker environment (though --privileged bypasses many host protections).
Clean Environment: Useful for tests requiring a pristine Docker environment without interference from the host's images or containers.
Version Control: You control the exact version of the inner Docker daemon, independent of the host.

Cons:

Complexity: Requires running the DinD container and linking/networking a client container to it.
--privileged Requirement: Running containers in privileged mode is highly insecure. It disables most container isolation mechanisms, giving the container near-root access to the host kernel and devices. This is a major security risk.
Resource Overhead: Running a full Docker daemon inside a container consumes more RAM and CPU.
Storage Driver Issues: The inner dockerd needs a suitable storage driver. This often works with --privileged but can be problematic.
Networking Complexity: Managing network connections between the host, the DinD container, and the containers started by the inner daemon can be complex.

4.3 Implementation Steps & `docker exec` Access

Start the DinD Daemon Container: Run the docker:dind image with the --privileged flag, detached (-d), and a name (e.g., my-dind-daemon). Use Docker networking (create a network, assign an alias like docker) for reliable connection.
Start a Client Container: Run another container (e.g., using the docker base image which contains the client CLI) on the same Docker network. Set the DOCKER_HOST environment variable in the client to point to the DinD daemon's network alias and port (e.g., tcp://docker:2375). Give this client container a name (e.g., dind-client) and run it detached (-d) with a command to keep it alive (e.g., sleep infinity).
Access the Client Container: Use docker exec -it dind-client sh (or bash) to get an interactive terminal inside the client container.
Run Docker Commands: From the exec session within the client container, execute standard Docker commands. These commands will interact with the inner Docker daemon running in the my-dind-daemon container. Containers started here will be children of the my-dind-daemon container and isolated from the host's Docker environment.

4.4 Code Example (Including `docker exec` usage)

Step 1: Create Network and Run DinD Daemon Container (on Host)

# Create a dedicated network
docker network create dind-network

# Run the privileged DinD daemon container on the network
# Give it a network alias 'docker' for easy reference by the client
docker run -d --name my-dind-daemon --network dind-network --network-alias docker \
  --privileged \
  -e DOCKER_TLS_CERTDIR="" \
  docker:dind

# Verify the daemon container is running
docker ps

Step 2: Run a Client Container Connected to the DinD Daemon (on Host)

# Run the client container on the same network, pointing DOCKER_HOST to the daemon
# Run detached (-d) and give it a name, keep it alive with sleep
docker run -d --name dind-client --network dind-network \
  -e DOCKER_HOST=tcp://docker:2375 \
  docker sleep infinity # Use 'docker' image which has the client CLI

# Verify the client container is running
docker ps

Step 3: Access Client Container and Run Commands Inside (on Host)

# Get an interactive shell inside the running CLIENT container
docker exec -it dind-client sh # 'docker' image uses sh by default

# Now, inside the 'dind-client' container's sh session:
# These commands interact with the INNER Docker daemon ('my-dind-daemon')

# List containers managed by the INNER daemon (should be empty initially)
echo "Running 'docker ps' inside the client (against inner daemon):"
docker ps

# Run a new container managed by the INNER daemon
echo "Running 'hello-world' inside the client (against inner daemon):"
docker run --rm hello-world

# Verify the hello-world container ran by checking the inner daemon's container list again
docker ps # Should show no running containers as hello-world exited

# List images known to the INNER daemon (will now include hello-world)
docker images

# Exit the client container's shell
exit

Cleanup (on Host):

docker stop dind-client my-dind-daemon
docker rm dind-client my-dind-daemon
docker network rm dind-network

4.4.1 Security Considerations (True DinD)

--privileged is Dangerous: This is the biggest concern. It essentially breaks container isolation. Avoid it if at all possible. If you must use it, only run trusted images and be fully aware of the risks.
Resource Exhaustion: The inner daemon could potentially consume excessive host resources.
Kernel Exploits: Any kernel vulnerability exploitable from within a container becomes much easier to leverage when running as --privileged.

4.5 Advanced Example: Controlling Docker via Python/Jupyter (using DooD)

This example demonstrates setting up a primary container running Jupyter Notebook. From within the notebook, we will use the docker Python library to interactively manage containers via the host's Docker daemon (using the mounted socket - DooD method). This avoids calling shell commands directly from Python.

Concept:

A "main" container is built with Python, the Docker client CLI, the docker Python library, and Jupyter Notebook.
This main container is run using the DooD method, mounting /var/run/docker.sock.
Jupyter Notebook is started inside the main container, exposing its port (8888).
The user connects to Jupyter via a web browser.
Python code within a Jupyter cell uses the docker library to connect to the Docker daemon (via the mounted socket) and execute operations like listing or running containers.

Security Warning: This setup inherits all the security risks of the DooD method. The container (and thus the Jupyter Notebook environment and the docker library running within it) has significant control over the host's Docker daemon. The example runs Jupyter without token authentication for simplicity; in any real-world scenario, you MUST enable authentication.

4.5.1 Implementation Steps

Create Dockerfile: Define a Dockerfile installing Python, Docker CLI, docker library, and Jupyter.
Build Image: Build the Docker image using docker build.
Run Container: Run the container, mounting the Docker socket and publishing the Jupyter port.
Access Jupyter: Open a web browser to http://localhost:8888 (or the host's IP).
Execute Code: Create a new Jupyter Notebook and run the provided Python code snippet using the docker library.

4.5.2 Code Example

Dockerfile.jupyter_dockerpy_dood

# Start from a Python base image
FROM python:3.10-slim

# Set working directory
WORKDIR /app

# Avoid prompts during installation
ENV DEBIAN_FRONTEND=noninteractive

# Install prerequisites (curl, gpg, etc.) and Docker client CLI
# (CLI is still useful for potential debugging inside the container)
RUN apt-get update && \
    apt-get install -y --no-install-recommends \
    apt-transport-https \
    ca-certificates \
    curl \
    gnupg \
    lsb-release && \
    mkdir -p /etc/apt/keyrings && \
    curl -fsSL https://download.docker.com/linux/debian/gpg | gpg --dearmor -o /etc/apt/keyrings/docker.gpg && \
    echo \
      "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.gpg] https://download.docker.com/linux/debian \
      $(lsb_release -cs) stable" | tee /etc/apt/sources.list.d/docker.list > /dev/null && \
    apt-get update && \
    apt-get install -y --no-install-recommends docker-ce-cli && \
    apt-get clean && \
    rm -rf /var/lib/apt/lists/*

# Install Jupyter Notebook and the Docker Python library
RUN pip install --no-cache-dir notebook docker

# Expose Jupyter default port
EXPOSE 8888

# Start Jupyter Notebook on container startup
# WARNING: Disables token authentication for simplicity. SECURE THIS IN PRODUCTION.
CMD ["jupyter", "notebook", "--ip=0.0.0.0", "--port=8888", "--allow-root", "--NotebookApp.token=''", "--NotebookApp.password=''"]

Build Command (on Host):

docker build -t jupyter-dockerpy-dood -f Dockerfile.jupyter_dockerpy_dood .

Run Command (on Host):

# Ensure the user running this command has permissions for the host's docker.sock
# Run detached, named, mount socket, publish port to localhost only
docker run -d --name jupyter-dockerpy \
  -v /var/run/docker.sock:/var/run/docker.sock \
  -p 127.0.0.1:8888:8888 \
  jupyter-dockerpy-dood

# Verify the container is running
docker ps

Access Jupyter Notebook:

Open your web browser and navigate to: http://localhost:8888

Jupyter Notebook Code Cell (Python):

Create a new Python 3 notebook and enter the following code into a cell:

import docker
import sys

print(f"Using docker library version: {docker.__version__}")
print(f"Python version: {sys.version}")

try:
    # Connect to the Docker daemon via the mounted socket
    # Uses DOCKER_HOST environment variable if set, otherwise defaults
    # to standard socket paths like /var/run/docker.sock
    client = docker.from_env()

    # Verify connection by pinging the daemon
    print("\nPinging Docker daemon...")
    if client.ping():
        print("Successfully connected to Docker daemon.")
    else:
        print("Error: Could not connect to Docker daemon.")
        # Stop execution if connection fails
        raise ConnectionError("Failed to ping Docker daemon")

    #  Example Usage

    # 1. List all containers (running and stopped) visible to the host daemon
    print("\nListing all containers (via host daemon)...")
    containers = client.containers.list(all=True)
    if containers:
        for container in containers:
            print(f"  - ID: {container.short_id}, Name: {container.name}, Status: {container.status}, Image: {container.image.tags}")
    else:
        print("  No containers found.")

    print("\n" + "="*40 + "\n")

    # 2. Run a simple Alpine container using the host Docker daemon
    print("Starting an Alpine container (via host daemon)...")
    alpine_image = "alpine:latest"
    alpine_command = "echo 'Hello from inner Alpine container!'"

    try:
        print(f"Running image '{alpine_image}' with command: '{alpine_command}'")
        # client.containers.run() streams logs by default if attach=True (default)
        # It returns the logs as bytes.
        # remove=True cleans up the container afterwards, similar to --rm
        logs = client.containers.run(
            alpine_image,
            command=alpine_command,
            remove=True,  # Equivalent to --rm
            stdout=True,
            stderr=True
        )
        print("\n Alpine Container Logs ")
        print(logs.decode('utf-8').strip()) # Decode bytes to string
        print(" End Alpine Container Logs ")
        print("Alpine container ran and was removed successfully.")

    except docker.errors.ImageNotFound:
        print(f"Error: Image '{alpine_image}' not found. Pulling image...")
        try:
            client.images.pull(alpine_image)
            print("Image pulled successfully. Please re-run the cell.")
        except docker.errors.APIError as e:
            print(f"Error pulling image: {e}")
    except docker.errors.APIError as e:
        print(f"Error running container: {e}")

except ConnectionError as e:
    print(f"Connection Error: {e}")
    print("Ensure the Docker socket is mounted correctly and the host daemon is running.")
except Exception as e:
    print(f"An unexpected error occurred: {e}")


print("\n" + "="*40 + "\n")
print("Script finished.")

Execution:

Run the cell in Jupyter Notebook. You should see:

Confirmation of connection to the Docker daemon.
A list of containers visible to the host daemon, including their IDs, names, and statuses (including the jupyter-dockerpy container itself).
Logs indicating the Alpine container is being run.
The output from the Alpine container ("Hello from inner Alpine container!").
Confirmation that the Alpine container completed and was removed.

Cleanup (on Host):

docker stop jupyter-dockerpy
docker rm jupyter-dockerpy

This revised example uses the docker Python library for cleaner, more idiomatic interaction with the Docker daemon from within the Jupyter environment, while still relying on the DooD socket-mounting technique. The security considerations remain paramount.

5. Security Best Practices (General)

Prefer DooD (Socket Mounting) over True DinD (--privileged) if possible, despite its own risks, as --privileged is generally considered worse.
Understand the Risks: Fully grasp the security implications of whichever method you choose.
Use Trusted Images: Only run well-known, verified base images.
Least Privilege (DooD): Explore running the container process as a non-root user mapped to the host's docker group GID.
Network Segmentation: Use Docker networks to isolate components.
Resource Limits: Apply resource constraints (CPU, memory) to the controlling container.
Consider Alternatives: Evaluate if tools like Buildah, Kaniko, Podman, Testcontainers, or Sysbox meet your needs without requiring full DinD/DooD.
Keep Host and Docker Updated: Regularly patch the host OS and the Docker Engine.

6. Troubleshooting Common Issues

permission denied accessing /var/run/docker.sock (DooD): Check host socket permissions and container user/group GID matching.
Cannot connect to the Docker daemon (DooD/DinD): Verify socket mount (DooD), daemon container running status (DinD), network connectivity/DOCKER_HOST variable (DinD), and host daemon status.
Storage Driver Errors (True DinD): Check DinD container logs (docker logs my-dind-daemon). May need --privileged or specific storage driver flags (e.g., --storage-driver=vfs, though inefficient).
Networking Issues (True DinD): Ensure proper Docker network setup for communication between the client, the DinD daemon, and any inner containers.

7. Alternatives

Kaniko: Daemonless image builds in containers/Kubernetes. Ideal for CI/CD.
Buildah: Daemonless OCI image building.
Podman: Daemonless Docker-compatible engine, often better for rootless containers-in-containers.
Testcontainers: Library for managing containerized dependencies (including DinD/DooD) in tests.
Sysbox: Container runtime designed for secure system-level workloads like DinD without --privileged.

8. Conclusion

Running Docker inside Docker, whether via socket mounting (DooD) or a dedicated inner daemon (True DinD), enables powerful workflows but introduces significant security considerations. DooD is simpler but grants host daemon control; True DinD offers theoretical isolation but requires the dangerous --privileged flag. Carefully evaluate the risks, prefer DooD if manageable, explore alternatives, and always prioritize security.

9. TL;DR

Why? Needed for CI/CD pipelines, complex tests, or dev environments where a container needs to build/run other containers.
Method 1: DooD (Docker-out-of-Docker):
- How: Mount host socket: docker run -v /var/run/docker.sock:/var/run/docker.sock ...
- Effect: Container talks to host's Docker daemon. New containers are siblings.
- Pros: Simple, efficient, shared layers.
- Cons: Major Security Risk: Container effectively gets root on host via the socket. Potential version conflicts.
Method 2: True DinD (Docker-in-Docker):
- How: Run docker:dind image with docker run --privileged .... Connect a client container to it (usually via Docker network and DOCKER_HOST=tcp://...).
- Effect: Container runs its own isolated Docker daemon. New containers are children.
- Pros: Better isolation (in theory), clean environment, controlled daemon version.
- Cons: Major Security Risk: Requires --privileged, breaking container isolation. Complex, resource-heavy.
Accessing/Using: Use docker exec -it <container_name> bash to get a shell inside the controlling container, then run standard docker commands (docker run, docker build, etc.).
Security: Both methods are risky. Avoid --privileged (DinD) if possible. Prefer DooD with caution, or use alternatives like Kaniko, Buildah, Podman, or Sysbox if they fit your use case.

#36 Connecting Alteryx to Snowflake: A Comprehensive Guide

Florian Zeba — Thu, 01 May 2025 07:14:00 +0000

Introduction

Alteryx is a powerful data analytics and automation platform that enables users to blend, prepare, and analyze data efficiently. Snowflake, on the other hand, is a cloud-based data warehousing solution known for its scalability, performance, and ease of use. Integrating Alteryx with Snowflake allows organizations to leverage the strengths of both platforms—Alteryx’s data preparation and analytics capabilities with Snowflake’s cloud-native storage and compute power.

This article explores the various methods of connecting Alteryx to Snowflake, their advantages, and implementation steps.

Methods to Connect Alteryx to Snowflake

There are several ways to establish a connection between Alteryx and Snowflake, each suited for different use cases:

Using the Snowflake ODBC Driver
Using the Snowflake Connector in Alteryx (In-Database Tools)
Using Alteryx’s Snowflake Bulk Loader
Using Python or R Scripts in Alteryx

Let’s explore each method in detail.

1. Connecting via Snowflake ODBC Driver

Overview

The Open Database Connectivity (ODBC) driver is a standard method for connecting applications to databases. Alteryx supports ODBC connections, making it straightforward to query and load data from Snowflake.

Steps to Configure

Install the Snowflake ODBC Driver

- Download the latest Snowflake ODBC driver from [Snowflake’s official site](https://docs.snowflake.com/en/user-guide/odbc.html).
- Install it on the machine where Alteryx is running.

Configure the ODBC Data Source

- Open **ODBC Data Source Administrator** (64-bit).
- Navigate to the **System DSN** tab and click **Add**.
- Select **Snowflake ODBC Driver** and configure:
    - **Data Source Name**: A friendly name (e.g., `Snowflake_Prod`).
    - **Server**: Your Snowflake account URL (e.g., `account_name.snowflakecomputing.com`).
    - **User**: Your Snowflake username.
    - **Password**: Your Snowflake password.
    - **Database/Schema/Warehouse**: Specify default values if needed.

Connect in Alteryx
- In Alteryx Designer, drag an Input Data or Output Data tool.
- Select ODBC as the connection type.
- Choose the configured DSN and authenticate.

Pros & Cons

✅ Pros:

Simple setup.
Works with all Alteryx versions.

❌ Cons:

Requires driver installation.
Performance may be slower than native connectors.

2. Using Alteryx’s In-Database Tools (Snowflake Connector)

Overview

Alteryx provides In-Database tools that push processing directly to Snowflake, improving performance by minimizing data movement.

Steps to Configure

Enable In-Database Processing

- Ensure you have Alteryx Designer with **In-Database** capabilities.

Configure the Connection

- Open **Alteryx Designer** → **Options** → **Advanced Options** → **In-DB Connections**.
- Click **Add** and select **Snowflake**.
- Enter:
    - **Server**: `account_name.snowflakecomputing.com`
    - **Username/Password**: Snowflake credentials.
    - **Database/Schema/Warehouse**: Default settings.

Use In-Database Tools
- Drag In-DB Connect and select the configured connection.
- Use tools like In-DB Select, In-DB Join, etc.

Pros & Cons

✅ Pros:

Faster processing (pushes logic to Snowflake).
Reduces data transfer overhead.

❌ Cons:

Requires Alteryx Designer with In-DB support.
Some Alteryx functions may not translate to Snowflake SQL.

3. Using Alteryx’s Snowflake Bulk Loader

Overview

For large datasets, Alteryx provides a Snowflake Bulk Loader tool that efficiently loads data using Snowflake’s COPY INTO command.

Steps to Configure

Set Up Snowflake Stage

- Create an internal or external stage in Snowflake:

    ```sql
    CREATE STAGE my_stage;
    ```

Use the Bulk Loader in Alteryx

- Drag the **Snowflake Bulk Loader** tool (available in some Alteryx versions).
- Configure:
    - **Connection**: Snowflake ODBC or In-DB connection.
    - **Target Table**: Schema and table name.
    - **Stage Name**: The Snowflake stage.

Execute the Workflow
- The tool will stage files and load them via COPY INTO.

Pros & Cons

✅ Pros:

Optimized for large data loads.
Uses Snowflake’s high-speed ingestion.

❌ Cons:

Requires additional setup (staging).
Not available in all Alteryx versions.

4. Using Python or R Scripts in Alteryx

Overview

For advanced users, Alteryx allows Python/R scripts to interact with Snowflake using libraries like snowflake-connector-python.

Example Python Script

import snowflake.connector
from ayx import Alteryx

# Connect to Snowflake
conn = snowflake.connector.connect(
    user="USER",
    password="PASSWORD",
    account="ACCOUNT_NAME",
    warehouse="WAREHOUSE",
    database="DATABASE",
    schema="SCHEMA"
)

# Query data
cursor = conn.cursor()
cursor.execute("SELECT * FROM MY_TABLE")
data = cursor.fetchall()

# Output to Alteryx
Alteryx.write(data, 1)

Pros & Cons

✅ Pros:

Full flexibility with custom logic.
Can handle complex transformations.

❌ Cons:

Requires coding knowledge.
Slower than native connectors.

Best Practices for Alteryx-Snowflake Integration

Optimize Query Performance

- Use **In-Database** tools to push down processing.
- Limit data pulled into Alteryx with `WHERE` clauses.

Manage Credentials Securely

- Use **Alteryx Credentials Manager** or Snowflake key-pair authentication.

Monitor Costs

- Snowflake charges by compute usage—optimize queries to reduce costs.

Schedule Workflows
- Use Alteryx Server/Scheduler to automate Snowflake data refreshes.

Conclusion

Connecting Alteryx to Snowflake unlocks powerful analytics capabilities by combining Alteryx’s data preparation with Snowflake’s cloud scalability. Whether using ODBC, In-Database tools, bulk loading, or scripting, each method has its strengths depending on the use case.

For most users, In-Database tools offer the best balance of performance and ease of use, while Python/R scripts provide flexibility for advanced scenarios.

#35 Python & Alteryx Integration: Unlocking Advanced Analytics

Florian Zeba — Wed, 30 Apr 2025 10:12:00 +0000

Introduction

Alteryx is a powerful data analytics platform known for its intuitive workflow-based approach to data preparation, blending, and advanced analytics. While Alteryx provides a rich set of built-in tools, integrating Python into Alteryx workflows unlocks even greater flexibility, allowing users to leverage Python’s extensive libraries for statistical analysis, machine learning, and custom data transformations.

This article explores the possibilities of using Python within Alteryx, covering:

Why Use Python in Alteryx?
Setting Up Python in Alteryx
Key Python Libraries for Data Analysis
Common Use Cases
Best Practices and Limitations

1. Why Use Python in Alteryx?

Alteryx excels at drag-and-drop data processing, but Python integration enhances its capabilities by:

Extending Functionality: Access advanced statistical, machine learning, and visualization libraries (e.g., Pandas, Scikit-learn, Matplotlib).
Custom Scripting: Perform complex transformations not natively supported in Alteryx.
Automation: Seamlessly integrate Python scripts into Alteryx workflows for batch processing.
Open-Source Ecosystem: Leverage thousands of Python packages for specialized tasks (e.g., NLP, time-series forecasting).

2. Setting Up Python in Alteryx

To use Python in Alteryx, follow these steps:

Prerequisites

Alteryx Designer installed.
Python (preferably Anaconda or a standalone installation).

Configuration

Enable Python in Alteryx:

- Go to **Options** > **User Settings** > **Edit User Settings**.
- Under **Python**, specify the Python executable path (e.g., `C:\Python\python.exe`).

Install Required Libraries:

Use pip to install necessary packages:
```
pip install pandas numpy scikit-learn matplotlib
```
Use the Python Tool in Workflows:

Drag the Python Tool from the Developer tab into your workflow.

3. Key Python Libraries for Data Analysis

Python’s rich ecosystem enhances Alteryx workflows. Key libraries include:

Library	Use Case	Example Alteryx Integration
Pandas	Data manipulation & cleaning	Replace Alteryx data preparation steps
NumPy	Numerical computing	Advanced mathematical operations
Scikit-learn	Machine learning models	Predictive modeling in workflows
Matplotlib/Seaborn	Data visualization	Custom charts beyond Alteryx tools
Statsmodels	Statistical analysis	Regression, hypothesis testing

4. Common Use Cases

A. Advanced Data Wrangling

Pandas can handle complex joins, filtering, and aggregations:

import pandas as pd

# Read input from Alteryx
df = pd.read_csv(r"{{input_file}}")

# Clean and transform data
df['Sales'] = df['Sales'].fillna(0)
df['Profit_Ratio'] = df['Profit'] / df['Sales']

# Output to Alteryx
df.to_csv(r"{{output_file}}", index=False)

B. Machine Learning Integration

Train models using Scikit-learn:

from sklearn.linear_model import LinearRegression

# Prepare data
X = df[['Feature1', 'Feature2']]
y = df['Target']

# Train model
model = LinearRegression()
model.fit(X, y)

# Predict and output
df['Prediction'] = model.predict(X)
df.to_csv(r"{{output_file}}", index=False)

C. Custom Visualizations

Generate plots with Matplotlib:

import matplotlib.pyplot as plt

plt.scatter(df['Sales'], df['Profit'])
plt.xlabel('Sales')
plt.ylabel('Profit')
plt.savefig(r"{{output_image_path}}")

D. Text & NLP Processing

Use NLTK or SpaCy for text analysis:

import nltk
from nltk.tokenize import word_tokenize

df['Tokenized_Text'] = df['Text_Column'].apply(word_tokenize)

5. Best Practices & Limitations

Best Practices

✔ Modularize Code: Write reusable Python functions.

✔ Error Handling: Use try-except blocks for robustness.

✔ Optimize Performance: Avoid loops; use vectorized Pandas operations.

✔ Document Dependencies: List required libraries in workflow notes.

Limitations

⚠ Performance Overhead: Large datasets may slow down Python execution.

⚠ Version Conflicts: Ensure Python versions align between Alteryx and scripts.

⚠ Debugging Challenges: Errors may require external Python IDEs for troubleshooting.

Conclusion

Integrating Python with Alteryx bridges the gap between no-code analytics and advanced data science. By leveraging Python’s libraries, users can perform sophisticated analyses while maintaining Alteryx’s workflow efficiency. Whether for predictive modeling, custom visualizations, or text mining, Python empowers Alteryx users to push the boundaries of data analytics.

Next Steps:

Experiment with small Python scripts in Alteryx.
Explore Alteryx’s Python SDK for deeper integration.
Combine Alteryx’s ETL strengths with Python’s ML capabilities for end-to-end solutions.

#34 50 Advanced SQL Queries Every Developer Should Know

Florian Zeba — Tue, 29 Apr 2025 15:14:06 +0000

SQL is a powerful language for managing and querying relational databases. While basic queries like SELECT, INSERT, UPDATE, and DELETE are essential, mastering advanced SQL techniques can significantly enhance your ability to analyze data, optimize performance, and solve complex problems.

In this article, we’ll explore 50 advanced SQL queries that cover window functions, recursive CTEs, pivoting, performance optimization, and more.

1. Window Functions (Analytical Queries)

Window functions allow computations across a set of table rows related to the current row.

1.1. ROW_NUMBER() – Assign a Unique Row Number

SELECT
    employee_id,
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

1.2. RANK() – Rank with Gaps for Ties

SELECT
    employee_id,
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

1.3. DENSE_RANK() – Rank Without Gaps

SELECT
    employee_id,
    name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

1.4. NTILE() – Divide Rows into Buckets

SELECT
    employee_id,
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

1.5. LEAD() – Access Next Row’s Value

SELECT
    employee_id,
    name,
    salary,
    LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary
FROM employees;

1.6. LAG() – Access Previous Row’s Value

SELECT
    employee_id,
    name,
    salary,
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary
FROM employees;

1.7. FIRST_VALUE() – Get First Value in a Window

SELECT
    employee_id,
    name,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_in_dept
FROM employees;

1.8. LAST_VALUE() – Get Last Value in a Window

SELECT
    employee_id,
    name,
    salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY department
        ORDER BY salary DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_in_dept
FROM employees;

1.9. Running Total with SUM() OVER

SELECT
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) AS running_total
FROM sales;

1.10. Moving Average

SELECT
    date,
    revenue,
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

2. Common Table Expressions (CTEs) and Recursive Queries

CTEs improve readability and allow recursive operations.

2.1. Basic CTE

WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_earners;

2.2. Recursive CTE (Hierarchical Data)

WITH RECURSIVE employee_hierarchy AS (
    -- Base case: CEO (no manager)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: Employees with managers
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

2.3. Multiple CTEs in a Single Query

WITH
    dept_stats AS (
        SELECT department, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department
    ),
    high_paying_depts AS (
        SELECT department
        FROM dept_stats
        WHERE avg_salary > 80000
    )
SELECT e.*
FROM employees e
JOIN high_paying_depts hpd ON e.department = hpd.department;

3. Pivoting and Unpivoting Data

3.1. Pivot with CASE

SELECT
    product_id,
    SUM(CASE WHEN region = 'North' THEN sales ELSE 0 END) AS north_sales,
    SUM(CASE WHEN region = 'South' THEN sales ELSE 0 END) AS south_sales,
    SUM(CASE WHEN region = 'East' THEN sales ELSE 0 END) AS east_sales,
    SUM(CASE WHEN region = 'West' THEN sales ELSE 0 END) AS west_sales
FROM sales
GROUP BY product_id;

3.2. Pivot with PIVOT (SQL Server, Oracle)

SELECT *
FROM (
    SELECT product_id, region, sales
    FROM sales
) AS src
PIVOT (
    SUM(sales) FOR region IN ([North], [South], [East], [West])
) AS pvt;

3.3. Unpivot Data

SELECT product_id, region, sales
FROM (
    SELECT product_id, north_sales, south_sales, east_sales, west_sales
    FROM pivoted_sales
) AS src
UNPIVOT (
    sales FOR region IN (north_sales, south_sales, east_sales, west_sales)
) AS unpvt;

4. Advanced Joins and Subqueries

4.1. Self-Join (Find Employees with Same Manager)

SELECT
    e1.name AS employee1,
    e2.name AS employee2,
    e1.manager_id
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id AND e1.id < e2.id;

4.2. Lateral Join (PostgreSQL)

SELECT
    d.department_name,
    e.name,
    e.salary
FROM departments d
CROSS JOIN LATERAL (
    SELECT name, salary
    FROM employees
    WHERE department_id = d.id
    ORDER BY salary DESC
    LIMIT 3
) e;

4.3. Correlated Subquery (Find Employees Earning Above Avg in Dept)

SELECT
    e1.name,
    e1.salary,
    e1.department
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

5. Performance Optimization

5.1. Index Hinting (Force Index Usage)

SELECT * FROM employees WITH (INDEX(idx_salary)) WHERE salary > 50000;

5.2. Query Plan Analysis (EXPLAIN)

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Engineering';

5.3. Materialized Views (Precompute Expensive Queries)

CREATE MATERIALIZED VIEW mv_high_earners AS
SELECT * FROM employees WHERE salary > 100000;

REFRESH MATERIALIZED VIEW mv_high_earners;

6. Advanced Aggregations

6.1. ROLLUP (Hierarchical Grouping)

SELECT
    department,
    job_title,
    SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department, job_title);

6.2. CUBE (All Possible Groupings)

SELECT
    department,
    job_title,
    SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE(department, job_title);

6.3. GROUPING SETS (Custom Groupings)

SELECT
    department,
    job_title,
    SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS (
    (department, job_title),
    (department),
    (job_title),
    ()
);

7. JSON and XML Handling

7.1. Extract JSON Fields

SELECT
    id,
    json_data->>'name' AS name,
    json_data->>'age' AS age
FROM users;

7.2. Query Nested JSON Arrays

SELECT
    id,
    json_array_elements(json_data->'skills') AS skill
FROM users;

7.3. XML Parsing

SELECT
    id,
    xpath('//name/text()', xml_data) AS name,
    xpath('//age/text()', xml_data) AS age
FROM users;

8. Dynamic SQL

8.1. Execute Dynamic Query (SQL Injection Safe)

EXECUTE format('SELECT * FROM %I WHERE salary > %L', 'employees', 50000);

8.2. Generate and Run SQL in a Loop

DO $$
DECLARE
    query TEXT;
BEGIN
    FOR i IN 1..10 LOOP
        query := format('INSERT INTO logs (message) VALUES (%L)', 'Log ' || i);
        EXECUTE query;
    END LOOP;
END $$;

9. Advanced Joins and Set Operations

9.1. FULL OUTER JOIN (Find All Matches and Non-Matches)

SELECT
    e.employee_id,
    e.name,
    d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

9.2. NATURAL JOIN (Join on Columns with Same Name)

SELECT * FROM employees NATURAL JOIN departments;

9.3. INTERSECT (Find Common Records Between Two Queries)

SELECT employee_id FROM full_time_employees
INTERSECT
SELECT employee_id FROM high_performers;

9.4. EXCEPT (Find Records in First Query but Not Second)

SELECT employee_id FROM all_employees
EXCEPT
SELECT employee_id FROM terminated_employees;

9.5. UNION ALL (Combine Results with Duplicates)

SELECT name, salary FROM current_employees
UNION ALL
SELECT name, salary FROM former_employees;

10. Advanced Subqueries

10.1. EXISTS (Check for Related Records)

SELECT e.name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM sales s
    WHERE s.employee_id = e.employee_id AND s.amount > 10000
);

10.2. NOT EXISTS (Find Records Without Related Data)

SELECT d.department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.department_id
);

10.3. IN with Subquery (Filter Based on Another Query)

SELECT name, salary
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'New York'
);

10.4. ALL (Compare Against All Values in Subquery)

SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department = 'Intern'
);

10.5. ANY/SOME (Compare Against Any Value in Subquery)

SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department = 'Management'
);

11. Advanced Data Modification

11.1. UPSERT (INSERT or UPDATE on Conflict)

INSERT INTO employees (id, name, salary)
VALUES (101, 'John Doe', 75000)
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, salary = EXCLUDED.salary;

11.2. MERGE (Conditional INSERT/UPDATE/DELETE)

MERGE INTO employees e
USING updated_employees ue
ON e.id = ue.id
WHEN MATCHED THEN
    UPDATE SET e.name = ue.name, e.salary = ue.salary
WHEN NOT MATCHED THEN
    INSERT (id, name, salary) VALUES (ue.id, ue.name, ue.salary);

11.3. DELETE with JOIN

DELETE FROM employees
USING departments
WHERE employees.department_id = departments.department_id
AND departments.location = 'Remote';

11.4. UPDATE from Another Table

UPDATE employees e
SET salary = e.salary * 1.1
FROM departments d
WHERE e.department_id = d.department_id
AND d.budget > 1000000;

12. Database Administration & Meta-Queries

12.1. List All Tables in a Database

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';

12.2. Find Column Names in a Table

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'employees';

12.3. Check Table Size (PostgreSQL)

SELECT
    table_name,
    pg_size_pretty(pg_total_relation_size(table_name)) AS size
FROM information_schema.tables
WHERE table_schema = 'public';

12.4. Find Long-Running Queries

SELECT
    pid,
    query,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

12.5. Kill a Running Query

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE query LIKE '%long_running_query%';

13. Advanced Date & Time Operations

13.1. Generate Date Series

SELECT generate_series(
    '2023-01-01'::date,
    '2023-12-31'::date,
    '1 day'::interval
) AS date;

13.2. Calculate Business Days Between Dates

SELECT
    date1,
    date2,
    COUNT(*) FILTER (WHERE EXTRACT(DOW FROM day) BETWEEN 1 AND 5) AS business_days
FROM (
    SELECT
        '2023-01-01'::date AS date1,
        '2023-01-31'::date AS date2,
        generate_series(
            '2023-01-01'::date,
            '2023-01-31'::date,
            '1 day'::interval
        ) AS day
) t;

13.3. Find Last Day of Month

SELECT
    date_trunc('month', current_date) + INTERVAL '1 month - 1 day' AS last_day_of_month;

14. Advanced String Manipulation

14.1. Regex Extract

SELECT
    regexp_matches(email, '([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+)\.([A-Za-z]{2,})')
FROM users;

14.2. Split String into Rows

SELECT
    id,
    unnest(string_to_array(tags, ',')) AS tag
FROM products;

14.3. Concatenate Rows into String

SELECT
    department_id,
    string_agg(name, ', ') AS employees
FROM employees
GROUP BY department_id;

15. Advanced Security & Permissions

15.1. Grant Column-Level Permissions

GRANT SELECT (name, email) ON employees TO analyst_role;

15.2. Create a Read-Only User

CREATE USER readonly WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Conclusion

With these 20 additional advanced SQL queries, we now have a complete list of 50 essential SQL techniques covering:

✅ Window Functions

✅ CTEs & Recursive Queries

✅ Pivoting & Unpivoting

✅ Advanced Joins & Subqueries

✅ Performance Optimization

✅ JSON/XML Handling

✅ Dynamic SQL

✅ Database Administration

#33 Stripe Integration Guide for Next.js 15 with Supabase

Florian Zeba — Wed, 02 Apr 2025 16:12:36 +0000

This guide provides a step-by-step process to integrate Stripe payments into your Next.js 15 application with Supabase authentication.

Prerequisites

Before starting, ensure you have:

A Next.js 15 application set up
Supabase integration for authentication and storage
Node.js v18.17.0 or later
npm or yarn package manager

Setting Up Stripe Account

Create a Stripe account at stripe.com
Navigate to the Stripe Dashboard
Get your API keys from Developers > API keys
Note both your Publishable Key and Secret Key
Enable test mode for development

Installing Required Packages

Install the necessary packages:

npm install stripe @stripe/stripe-js @stripe/react-stripe-js
# or
yarn add stripe @stripe/stripe-js @stripe/react-stripe-js

Environment Configuration

Create or update your .env.local file with Stripe configuration:

# Stripe API Keys
NEXT_PUBLIC_STRIPE_PUBLISHABLE_KEY=pk_test_your_publishable_key
STRIPE_SECRET_KEY=sk_test_your_secret_key

# Stripe Webhook Secret (you'll get this later)
STRIPE_WEBHOOK_SECRET=whsec_your_webhook_secret

# Your domain for Stripe redirects
NEXT_PUBLIC_SITE_URL=http://localhost:3000

Stripe Client Integration

1. Create a Stripe context provider

Create a file at lib/stripe/stripe-client.js:

import { loadStripe } from '@stripe/stripe-js';

let stripePromise;

export const getStripe = () => {
    if (!stripePromise) {
        stripePromise = loadStripe(
            process.env.NEXT_PUBLIC_STRIPE_PUBLISHABLE_KEY
        );
    }
    return stripePromise;
};

2. Create a Stripe Elements provider component

Create a file at components/StripeElementsProvider.jsx:

'use client';

import { Elements } from '@stripe/react-stripe-js';
import { getStripe } from '@/lib/stripe/stripe-client';

export default function StripeElementsProvider({ children, options }) {
    const stripePromise = getStripe();

    return (
        <Elements stripe={stripePromise} options={options}>
            {children}
        </Elements>
    );
}

Stripe API Routes

1. Set up Stripe server-side instance

Create a file at lib/stripe/stripe-server.js:

import Stripe from 'stripe';

let stripe;

export const getStripe = () => {
    if (!stripe) {
        stripe = new Stripe(process.env.STRIPE_SECRET_KEY, {
            apiVersion: '2023-10-16', // Use the latest API version
        });
    }
    return stripe;
};

2. Create API route for creating payment intents

Create a file at app/api/stripe/payment-intents/route.js:

import { NextResponse } from 'next/server';
import { getStripe } from '@/lib/stripe/stripe-server';
import { createClient } from '@supabase/supabase-js';

export async function POST(request) {
    try {
        const {
            amount,
            currency = 'usd',
            paymentMethodType = 'card',
            metadata = {},
        } = await request.json();

        // Validate amount
        if (!amount || isNaN(amount) || amount <= 0) {
            return NextResponse.json(
                { error: 'Invalid amount' },
                { status: 400 }
            );
        }

        // Initialize Supabase client
        const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL;
        const supabaseServiceKey = process.env.SUPABASE_SERVICE_ROLE_KEY;
        const supabase = createClient(supabaseUrl, supabaseServiceKey);

        // Get user from cookie (this assumes you're using Supabase Auth)
        const cookieStore = request.cookies;
        const supabaseAuthToken = cookieStore.get('sb-access-token')?.value;

        if (!supabaseAuthToken) {
            return NextResponse.json(
                { error: 'User not authenticated' },
                { status: 401 }
            );
        }

        // Get user from Supabase
        const {
            data: { user },
            error,
        } = await supabase.auth.getUser(supabaseAuthToken);

        if (error || !user) {
            return NextResponse.json(
                { error: 'User not found' },
                { status: 401 }
            );
        }

        // Add user ID to metadata
        const enhancedMetadata = {
            ...metadata,
            userId: user.id,
        };

        // Create a PaymentIntent with the order amount and currency
        const stripe = getStripe();
        const paymentIntent = await stripe.paymentIntents.create({
            amount: Math.round(amount * 100), // Stripe expects amount in cents
            currency,
            payment_method_types: [paymentMethodType],
            metadata: enhancedMetadata,
        });

        return NextResponse.json({
            clientSecret: paymentIntent.client_secret,
        });
    } catch (error) {
        console.error('Error creating payment intent:', error);
        return NextResponse.json({ error: error.message }, { status: 500 });
    }
}

3. Create API route for creating checkout sessions

Create a file at app/api/stripe/checkout-sessions/route.js:

import { NextResponse } from 'next/server';
import { getStripe } from '@/lib/stripe/stripe-server';
import { createClient } from '@supabase/supabase-js';

export async function POST(request) {
    try {
        const {
            priceId,
            mode = 'payment',
            successUrl = `${process.env.NEXT_PUBLIC_SITE_URL}/success`,
            cancelUrl = `${process.env.NEXT_PUBLIC_SITE_URL}/cancel`,
            metadata = {},
        } = await request.json();

        if (!priceId) {
            return NextResponse.json(
                { error: 'Price ID is required' },
                { status: 400 }
            );
        }

        // Initialize Supabase client
        const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL;
        const supabaseServiceKey = process.env.SUPABASE_SERVICE_ROLE_KEY;
        const supabase = createClient(supabaseUrl, supabaseServiceKey);

        // Get user from cookie
        const cookieStore = request.cookies;
        const supabaseAuthToken = cookieStore.get('sb-access-token')?.value;

        if (!supabaseAuthToken) {
            return NextResponse.json(
                { error: 'User not authenticated' },
                { status: 401 }
            );
        }

        // Get user from Supabase
        const {
            data: { user },
            error,
        } = await supabase.auth.getUser(supabaseAuthToken);

        if (error || !user) {
            return NextResponse.json(
                { error: 'User not found' },
                { status: 401 }
            );
        }

        // Add user ID to metadata
        const enhancedMetadata = {
            ...metadata,
            userId: user.id,
        };

        // Create Checkout Session
        const stripe = getStripe();
        const session = await stripe.checkout.sessions.create({
            mode,
            payment_method_types: ['card'],
            line_items: [
                {
                    price: priceId,
                    quantity: 1,
                },
            ],
            success_url: `${successUrl}?session_id={CHECKOUT_SESSION_ID}`,
            cancel_url: cancelUrl,
            metadata: enhancedMetadata,
            customer_email: user.email, // Pre-fill customer email
        });

        return NextResponse.json({
            sessionId: session.id,
            url: session.url,
        });
    } catch (error) {
        console.error('Error creating checkout session:', error);
        return NextResponse.json({ error: error.message }, { status: 500 });
    }
}

4. Create API route for subscriptions

Create a file at app/api/stripe/subscriptions/route.js:

import { NextResponse } from 'next/server';
import { getStripe } from '@/lib/stripe/stripe-server';
import { createClient } from '@supabase/supabase-js';

export async function POST(request) {
    try {
        const {
            priceId,
            successUrl = `${process.env.NEXT_PUBLIC_SITE_URL}/subscription/success`,
            cancelUrl = `${process.env.NEXT_PUBLIC_SITE_URL}/subscription/cancel`,
            metadata = {},
        } = await request.json();

        if (!priceId) {
            return NextResponse.json(
                { error: 'Price ID is required' },
                { status: 400 }
            );
        }

        // Initialize Supabase client
        const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL;
        const supabaseServiceKey = process.env.SUPABASE_SERVICE_ROLE_KEY;
        const supabase = createClient(supabaseUrl, supabaseServiceKey);

        // Get user from cookie
        const cookieStore = request.cookies;
        const supabaseAuthToken = cookieStore.get('sb-access-token')?.value;

        if (!supabaseAuthToken) {
            return NextResponse.json(
                { error: 'User not authenticated' },
                { status: 401 }
            );
        }

        // Get user from Supabase
        const {
            data: { user },
            error,
        } = await supabase.auth.getUser(supabaseAuthToken);

        if (error || !user) {
            return NextResponse.json(
                { error: 'User not found' },
                { status: 401 }
            );
        }

        // Check if user already has a Stripe customer ID
        const { data: customerData } = await supabase
            .from('customers')
            .select('stripe_customer_id')
            .eq('user_id', user.id)
            .single();

        const stripe = getStripe();
        let customerId;

        // If no customer ID exists, create one
        if (!customerData?.stripe_customer_id) {
            const customer = await stripe.customers.create({
                email: user.email,
                metadata: {
                    userId: user.id,
                },
            });

            customerId = customer.id;

            // Save Stripe customer ID to Supabase
            await supabase.from('customers').insert({
                user_id: user.id,
                stripe_customer_id: customerId,
            });
        } else {
            customerId = customerData.stripe_customer_id;
        }

        // Add user ID to metadata
        const enhancedMetadata = {
            ...metadata,
            userId: user.id,
        };

        // Create subscription checkout session
        const session = await stripe.checkout.sessions.create({
            customer: customerId,
            mode: 'subscription',
            payment_method_types: ['card'],
            line_items: [
                {
                    price: priceId,
                    quantity: 1,
                },
            ],
            success_url: `${successUrl}?session_id={CHECKOUT_SESSION_ID}`,
            cancel_url: cancelUrl,
            metadata: enhancedMetadata,
        });

        return NextResponse.json({
            sessionId: session.id,
            url: session.url,
        });
    } catch (error) {
        console.error('Error creating subscription:', error);
        return NextResponse.json({ error: error.message }, { status: 500 });
    }
}

// GET route to fetch user subscriptions
export async function GET(request) {
    try {
        // Initialize Supabase client
        const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL;
        const supabaseServiceKey = process.env.SUPABASE_SERVICE_ROLE_KEY;
        const supabase = createClient(supabaseUrl, supabaseServiceKey);

        // Get user from cookie
        const cookieStore = request.cookies;
        const supabaseAuthToken = cookieStore.get('sb-access-token')?.value;

        if (!supabaseAuthToken) {
            return NextResponse.json(
                { error: 'User not authenticated' },
                { status: 401 }
            );
        }

        // Get user from Supabase
        const {
            data: { user },
            error,
        } = await supabase.auth.getUser(supabaseAuthToken);

        if (error || !user) {
            return NextResponse.json(
                { error: 'User not found' },
                { status: 401 }
            );
        }

        // Get customer ID from Supabase
        const { data: customerData } = await supabase
            .from('customers')
            .select('stripe_customer_id')
            .eq('user_id', user.id)
            .single();

        if (!customerData?.stripe_customer_id) {
            return NextResponse.json({
                subscriptions: [],
            });
        }

        // Get subscriptions from Stripe
        const stripe = getStripe();
        const subscriptions = await stripe.subscriptions.list({
            customer: customerData.stripe_customer_id,
            status: 'active',
            expand: [
                'data.default_payment_method',
                'data.items.data.price.product',
            ],
        });

        return NextResponse.json({
            subscriptions: subscriptions.data,
        });
    } catch (error) {
        console.error('Error fetching subscriptions:', error);
        return NextResponse.json({ error: error.message }, { status: 500 });
    }
}

Payment Flows

One-time Payments

1. Create a payment component using Elements

Create a file at components/CheckoutForm.jsx:

'use client';

import { useState } from 'react';
import { CardElement, useStripe, useElements } from '@stripe/react-stripe-js';
import StripeElementsProvider from './StripeElementsProvider';

const CheckoutFormInner = ({ amount, onSuccess, onError }) => {
    const stripe = useStripe();
    const elements = useElements();
    const [isLoading, setIsLoading] = useState(false);
    const [errorMessage, setErrorMessage] = useState(null);

    const handleSubmit = async (e) => {
        e.preventDefault();

        if (!stripe || !elements) {
            return;
        }

        setIsLoading(true);
        setErrorMessage(null);

        try {
            // Create a payment intent on the server
            const response = await fetch('/api/stripe/payment-intents', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json',
                },
                body: JSON.stringify({
                    amount,
                }),
            });

            const data = await response.json();

            if (!response.ok) {
                throw new Error(data.error || 'Something went wrong');
            }

            // Confirm the payment on the client
            const { error, paymentIntent } = await stripe.confirmCardPayment(
                data.clientSecret,
                {
                    payment_method: {
                        card: elements.getElement(CardElement),
                    },
                }
            );

            if (error) {
                throw new Error(error.message);
            }

            if (paymentIntent.status === 'succeeded') {
                if (onSuccess) {
                    onSuccess(paymentIntent);
                }
            }
        } catch (error) {
            setErrorMessage(error.message);
            if (onError) {
                onError(error);
            }
        } finally {
            setIsLoading(false);
        }
    };

    return (
        <form onSubmit={handleSubmit} className='space-y-4'>
            <div className='p-4 border rounded-md'>
                <CardElement
                    options={{
                        style: {
                            base: {
                                fontSize: '16px',
                                color: '#424770',
                                '::placeholder': {
                                    color: '#aab7c4',
                                },
                            },
                            invalid: {
                                color: '#9e2146',
                            },
                        },
                    }}
                />
            </div>

            {errorMessage && (
                <div className='text-red-500 text-sm'>{errorMessage}</div>
            )}

            <button
                type='submit'
                disabled={!stripe || isLoading}
                className='px-4 py-2 bg-blue-600 text-white rounded-md disabled:opacity-50'
            >
                {isLoading ? 'Processing...' : `Pay $${amount.toFixed(2)}`}
            </button>
        </form>
    );
};

export default function CheckoutForm({ amount, onSuccess, onError }) {
    return (
        <StripeElementsProvider>
            <CheckoutFormInner
                amount={amount}
                onSuccess={onSuccess}
                onError={onError}
            />
        </StripeElementsProvider>
    );
}

2. Create a checkout page

Create a file at app/checkout/page.jsx:

'use client';

import { useState } from 'react';
import { useRouter } from 'next/navigation';
import CheckoutForm from '@/components/CheckoutForm';

export default function CheckoutPage() {
    const router = useRouter();
    const [isSuccess, setIsSuccess] = useState(false);

    // Sample product
    const product = {
        name: 'Sample Product',
        price: 19.99,
        description: 'This is a sample product for testing Stripe integration',
    };

    const handleSuccess = (paymentIntent) => {
        setIsSuccess(true);
        // Navigate to success page after a short delay
        setTimeout(() => {
            router.push(`/success?payment_intent=${paymentIntent.id}`);
        }, 1500);
    };

    const handleError = (error) => {
        console.error('Payment error:', error);
    };

    return (
        <div className='max-w-md mx-auto my-8 p-6 bg-white rounded-lg shadow-md'>
            <h1 className='text-2xl font-bold mb-4'>Checkout</h1>

            {isSuccess ? (
                <div className='text-green-600 font-semibold mb-4'>
                    Payment successful! Redirecting...
                </div>
            ) : (
                <>
                    <div className='mb-6'>
                        <h2 className='text-xl font-semibold'>
                            {product.name}
                        </h2>
                        <p className='text-gray-600'>{product.description}</p>
                        <div className='text-xl font-bold mt-2'>
                            ${product.price.toFixed(2)}
                        </div>
                    </div>

                    <CheckoutForm
                        amount={product.price}
                        onSuccess={handleSuccess}
                        onError={handleError}
                    />
                </>
            )}
        </div>
    );
}

3. Create a success page

Create a file at app/success/page.jsx:

'use client';

import { useEffect, useState } from 'react';
import { useSearchParams } from 'next/navigation';
import Link from 'next/link';

export default function SuccessPage() {
    const searchParams = useSearchParams();
    const sessionId = searchParams.get('session_id');
    const paymentIntentId = searchParams.get('payment_intent');
    const [paymentDetails, setPaymentDetails] = useState(null);
    const [loading, setLoading] = useState(true);

    useEffect(() => {
        const getPaymentDetails = async () => {
            try {
                if (sessionId) {
                    // If we have a checkout session ID, fetch session details
                    const response = await fetch(
                        `/api/stripe/checkout-sessions/${sessionId}`
                    );
                    if (response.ok) {
                        const data = await response.json();
                        setPaymentDetails(data.session);
                    }
                } else if (paymentIntentId) {
                    // If we have a payment intent ID, fetch payment intent details
                    const response = await fetch(
                        `/api/stripe/payment-intents/${paymentIntentId}`
                    );
                    if (response.ok) {
                        const data = await response.json();
                        setPaymentDetails(data.paymentIntent);
                    }
                }
            } catch (error) {
                console.error('Error fetching payment details:', error);
            } finally {
                setLoading(false);
            }
        };

        getPaymentDetails();
    }, [sessionId, paymentIntentId]);

    return (
        <div className='max-w-md mx-auto my-8 p-6 bg-white rounded-lg shadow-md'>
            <div className='text-center mb-6'>
                <div className='inline-flex items-center justify-center w-16 h-16 bg-green-100 rounded-full mb-4'>
                    <svg
                        xmlns='http://www.w3.org/2000/svg'
                        className='h-8 w-8 text-green-600'
                        fill='none'
                        viewBox='0 0 24 24'
                        stroke='currentColor'
                    >
                        <path
                            strokeLinecap='round'
                            strokeLinejoin='round'
                            strokeWidth={2}
                            d='M5 13l4 4L19 7'
                        />
                    </svg>
                </div>
                <h1 className='text-2xl font-bold text-green-600'>
                    Payment Successful!
                </h1>
                <p className='text-gray-600 mt-2'>
                    Thank you for your purchase. Your payment has been processed
                    successfully.
                </p>
            </div>

            {loading ? (
                <p className='text-center text-gray-500'>
                    Loading payment details...
                </p>
            ) : paymentDetails ? (
                <div className='border-t border-gray-200 pt-4'>
                    <h2 className='text-lg font-semibold mb-2'>
                        Payment Details
                    </h2>
                    <p className='text-gray-700'>
                        Amount: ${(paymentDetails.amount / 100).toFixed(2)}
                    </p>
                    <p className='text-gray-700'>
                        Date:{' '}
                        {new Date(
                            paymentDetails.created * 1000
                        ).toLocaleDateString()}
                    </p>
                    <p className='text-gray-700'>
                        Payment ID: {paymentDetails.id}
                    </p>
                </div>
            ) : null}

            <div className='mt-6 text-center'>
                <Link href='/' className='text-blue-600 hover:text-blue-800'>
                    Return to Home
                </Link>
            </div>
        </div>
    );
}

Subscriptions

1. Create a subscription checkout button component

Create a file at components/SubscribeButton.jsx:

'use client';

import { useState } from 'react';
import { getStripe } from '@/lib/stripe/stripe-client';

export default function SubscribeButton({ priceId, buttonText = 'Subscribe' }) {
    const [isLoading, setIsLoading] = useState(false);
    const [error, setError] = useState(null);

    const handleSubscribe = async () => {
        setIsLoading(true);
        setError(null);

        try {
            const response = await fetch('/api/stripe/subscriptions', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json',
                },
                body: JSON.stringify({
                    priceId,
                }),
            });

            const data = await response.json();

            if (!response.ok) {
                throw new Error(data.error || 'Something went wrong');
            }

            // Redirect to Stripe Checkout
            if (data.url) {
                window.location.href = data.url;
            } else {
                // If no URL is provided, redirect using the session ID
                const stripe = await getStripe();
                const { error } = await stripe.redirectToCheckout({
                    sessionId: data.sessionId,
                });

                if (error) throw error;
            }
        } catch (error) {
            setError(error.message);
            console.error('Error subscribing:', error);
        } finally {
            setIsLoading(false);
        }
    };

    return (
        <div>
            <button
                onClick={handleSubscribe}
                disabled={isLoading}
                className='px-4 py-2 bg-blue-600 text-white rounded-md hover:bg-blue-700 disabled:opacity-50'
            >
                {isLoading ? 'Processing...' : buttonText}
            </button>

            {error && <div className='text-red-500 text-sm mt-2'>{error}</div>}
        </div>
    );
}

2. Create a pricing page with subscription options

Create a file at app/pricing/page.jsx:

'use client';

import { useState, useEffect } from 'react';
import SubscribeButton from '@/components/SubscribeButton';
import { useRouter } from 'next/navigation';
import { useSupabase } from '@/lib/supabase/client'; // Assuming you have this hook

export default function PricingPage() {
    const router = useRouter();
    const { supabase, user } = useSupabase();
    const [subscription, setSubscription] = useState(null);
    const [loading, setLoading] = useState(true);

    // Pricing plans - in a real app, you would fetch these from Stripe
    const pricingPlans = [
        {
            name: 'Basic',
            description: 'For individuals and small projects',
            price: '$9.99',
            interval: 'month',
            features: ['Feature 1', 'Feature 2', 'Feature 3'],
            priceId: 'price_1NxYzABCDEFGHIJK', // Your actual Stripe Price ID
        },
        {
            name: 'Pro',
            description: 'For professionals and teams',
            price: '$19.99',
            interval: 'month',
            features: [
                'All Basic features',
                'Feature 4',
                'Feature 5',
                'Feature 6',
            ],
            priceId: 'price_2OyZaBCDEFGHIJK', // Your actual Stripe Price ID
            popular: true,
        },
        {
            name: 'Enterprise',
            description: 'For large organizations',
            price: '$49.99',
            interval: 'month',
            features: [
                'All Pro features',
                'Feature 7',
                'Feature 8',
                'Feature 9',
                'Priority support',
            ],
            priceId: 'price_3PzZbBCDEFGHIJK', // Your actual Stripe Price ID
        },
    ];

    useEffect(() => {
        const checkUser = async () => {
            if (!user) {
                router.push('/login?redirect=/pricing');
                return;
            }

            try {
                // Fetch current subscription
                const response = await fetch('/api/stripe/subscriptions');
                const data = await response.json();

                if (response.ok && data.subscriptions.length > 0) {
                    setSubscription(data.subscriptions[0]);
                }
            } catch (error) {
                console.error('Error fetching subscription:', error);
            } finally {
                setLoading(false);
            }
        };

        checkUser();
    }, [user, router]);

    // Helper function to check if user has the current plan
    const hasCurrentPlan = (priceId) => {
        if (!subscription) return false;

        return subscription.items.data.some(
            (item) => item.price.id === priceId
        );
    };

    // Handle subscription cancellation
    const handleCancelSubscription = async () => {
        if (!subscription) return;

        try {
            setLoading(true);

            const response = await fetch(
                `/api/stripe/subscriptions/${subscription.id}`,
                {
                    method: 'DELETE',
                }
            );

            if (response.ok) {
                setSubscription(null);
                alert('Subscription cancelled successfully');
            } else {
                const data = await response.json();
                throw new Error(data.error || 'Failed to cancel subscription');
            }
        } catch (error) {
            console.error('Error cancelling subscription:', error);
            alert(error.message);
        } finally {
            setLoading(false);
        }
    };

    if (!user) {
        return (
            <div className='flex justify-center items-center h-64'>
                <p>Please login to view pricing...</p>
            </div>
        );
    }

    if (loading) {
        return (
            <div className='flex justify-center items-center h-64'>
                <p>Loading pricing options...</p>
            </div>
        );
    }

    return (
        <div className='max-w-6xl mx-auto py-12 px-4 sm:px-6 lg:px-8'>
            <div className='text-center mb-12'>
                <h1 className='text-3xl font-extrabold text-gray-900 sm:text-4xl'>
                    Pricing Plans
                </h1>
                <p className='mt-4 text-xl text-gray-600'>
                    Choose the perfect plan for your needs
                </p>
            </div>

            {subscription && (
                <div className='mb-12 max-w-md mx-auto bg-green-50 rounded-lg p-6 border border-green-200'>
                    <h2 className='text-xl font-semibold text-gray-900'>
                        Your Current Subscription
                    </h2>
                    <p className='text-gray-600 mt-2'>
                        You are currently subscribed to the{' '}
                        {subscription.items.data[0].price.product.name} plan.
                    </p>
                    <p className='text-gray-600 mt-2'>
                        Next billing date:{' '}
                        {new Date(
                            subscription.current_period_end * 1000
                        ).toLocaleDateString()}
                    </p>
                    <button
                        onClick={handleCancelSubscription}
                        className='mt-4 px-4 py-2 bg-red-600 text-white rounded-md hover:bg-red-700'
                    >
                        Cancel Subscription
                    </button>
                </div>
            )}

            <div className='grid gap-6 lg:grid-cols-3 lg:gap-8'>
                {pricingPlans.map((plan) => (
                    <div
                        key={plan.name}
                        className={`bg-white rounded-lg shadow-lg divide-y divide-gray-200 ${
                            plan.popular
                                ? 'border-2 border-blue-500 relative'
                                : ''
                        }`}
                    >
                        {plan.popular && (
                            <div className='absolute top-0 right-0 transform translate-x-2 -translate-y-2'>
                                <span className='bg-blue-500 text-white text-xs font-semibold px-3 py-1 rounded-full'>
                                    Popular
                                </span>
                            </div>
                        )}

                        <div className='p-6'>
                            <h2 className='text-xl font-semibold text-gray-900'>
                                {plan.name}
                            </h2>
                            <p className='mt-2 text-gray-600'>
                                {plan.description}
                            </p>
                            <p className='mt-4'>
                                <span className='text-3xl font-extrabold text-gray-900'>
                                    {plan.price}
                                </span>
                                <span className='text-base font-medium text-gray-500'>
                                    /{plan.interval}
                                </span>
                            </p>
                        </div>

                        <div className='px-6 pt-6 pb-4'>
                            <h3 className='text-sm font-semibold text-gray-900 uppercase tracking-wide'>
                                What's included
                            </h3>
                            <ul className='mt-4 space-y-3'>
                                {plan.features.map((feature) => (
                                    <li key={feature} className='flex'>
                                        <svg
                                            className='h-5 w-5 text-green-500'
                                            fill='none'
                                            viewBox='0 0 24 24'
                                            stroke='currentColor'
                                        >
                                            <path
                                                strokeLinecap='round'
                                                strokeLinejoin='round'
                                                strokeWidth={2}
                                                d='M5 13l4 4L19 7'
                                            />
                                        </svg>
                                        <span className='ml-3 text-gray-700'>
                                            {feature}
                                        </span>
                                    </li>
                                ))}
                            </ul>
                        </div>

                        <div className='px-6 py-4'>
                            {hasCurrentPlan(plan.priceId) ? (
                                <div className='inline-flex items-center px-4 py-2 border border-transparent text-sm font-medium rounded-md shadow-sm text-white bg-green-600'>
                                    Current Plan
                                </div>
                            ) : (
                                <SubscribeButton
                                    priceId={plan.priceId}
                                    buttonText={
                                        subscription
                                            ? 'Change Plan'
                                            : 'Subscribe'
                                    }
                                />
                            )}
                        </div>
                    </div>
                ))}
            </div>
        </div>
    );
}

Webhook Handler

Create a file at app/api/stripe/webhook/route.js:

import { headers } from 'next/headers';
import { NextResponse } from 'next/server';
import { getStripe } from '@/lib/stripe/stripe-server';
import { createClient } from '@supabase/supabase-js';

// Buffer to string for webhook signature verification
const buffer = async (readable) => {
    const chunks = [];
    for await (const chunk of readable) {
        chunks.push(typeof chunk === 'string' ? Buffer.from(chunk) : chunk);
    }
    return Buffer.concat(chunks);
};

export async function POST(request) {
    try {
        const body = await request.text();
        const signature = headers().get('stripe-signature');

        if (!signature) {
            return NextResponse.json(
                { error: 'Missing Stripe signature' },
                { status: 401 }
            );
        }

        // Initialize Stripe
        const stripe = getStripe();

        // Verify webhook signature
        let event;
        try {
            event = stripe.webhooks.constructEvent(
                body,
                signature,
                process.env.STRIPE_WEBHOOK_SECRET
            );
        } catch (err) {
            console.error(
                `Webhook signature verification failed: ${err.message}`
            );
            return NextResponse.json(
                {
                    error: `Webhook signature verification failed: ${err.message}`,
                },
                { status: 400 }
            );
        }

        // Initialize Supabase
        const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL;
        const supabaseServiceKey = process.env.SUPABASE_SERVICE_ROLE_KEY;
        const supabase = createClient(supabaseUrl, supabaseServiceKey);

        // Handle specific Stripe events
        switch (event.type) {
            case 'checkout.session.completed':
                const session = event.data.object;

                // Extract user ID from metadata
                const userId = session.metadata?.userId;

                if (userId) {
                    if (session.mode === 'subscription') {
                        // Handle subscription payment
                        await handleSuccessfulSubscription(
                            session,
                            userId,
                            supabase
                        );
                    } else {
                        // Handle one-time payment
                        await handleSuccessfulPayment(
                            session,
                            userId,
                            supabase
                        );
                    }
                }
                break;

            case 'invoice.paid':
                // Handle successful invoice payment
                await handleSuccessfulInvoice(event.data.object, supabase);
                break;

            case 'invoice.payment_failed':
                // Handle failed invoice payment
                await handleFailedInvoice(event.data.object, supabase);
                break;

            case 'customer.subscription.deleted':
                // Handle subscription cancellation
                await handleSubscriptionCancelled(event.data.object, supabase);
                break;

            // Add more event handlers as needed
        }

        return NextResponse.json({ received: true });
    } catch (error) {
        console.error('Webhook error:', error);
        return NextResponse.json({ error: error.message }, { status: 500 });
    }
}

// Helper functions for handling webhook events

async function handleSuccessfulPayment(session, userId, supabase) {
    // Record the payment in your database
    await supabase.from('payments').insert({
        user_id: userId,
        stripe_checkout_id: session.id,
        amount: session.amount_total,
        currency: session.currency,
        status: 'completed',
        payment_intent: session.payment_intent,
        payment_method: session.payment_method_types?.[0] || 'unknown',
        created_at: new Date().toISOString(),
    });

    // Update user's access level or entitlements if needed
    // This depends on your specific business logic
}

async function handleSuccessfulSubscription(session, userId, supabase) {
    // Get the customer and subscription IDs
    const subscriptionId = session.subscription;
    const customerId = session.customer;

    // Verify subscription details
    const stripe = getStripe();
    const subscription = await stripe.subscriptions.retrieve(subscriptionId);
    const priceId = subscription.items.data[0].price.id;

    // Record the subscription in your database
    await supabase.from('subscriptions').insert({
        user_id: userId,
        stripe_customer_id: customerId,
        stripe_subscription_id: subscriptionId,
        stripe_price_id: priceId,
        status: subscription.status,
        current_period_start: new Date(
            subscription.current_period_start * 1000
        ).toISOString(),
        current_period_end: new Date(
            subscription.current_period_end * 1000
        ).toISOString(),
        created_at: new Date().toISOString(),
    });

    // Update user's access level based on the subscription
    await supabase
        .from('profiles')
        .update({
            subscription_status: subscription.status,
            subscription_plan: priceId,
        })
        .eq('user_id', userId);
}

async function handleSuccessfulInvoice(invoice, supabase) {
    const customerId = invoice.customer;
    const subscriptionId = invoice.subscription;

    if (!subscriptionId) {
        // This is not a subscription invoice
        return;
    }

    // Find the customer in your database
    const { data: customerData } = await supabase
        .from('customers')
        .select('user_id')
        .eq('stripe_customer_id', customerId)
        .single();

    if (!customerData?.user_id) {
        console.error('Customer not found:', customerId);
        return;
    }

    // Update subscription status
    await supabase
        .from('subscriptions')
        .update({
            status: 'active',
            current_period_end: new Date(
                invoice.lines.data[0].period.end * 1000
            ).toISOString(),
        })
        .eq('stripe_subscription_id', subscriptionId);

    // Update user's subscription status
    await supabase
        .from('profiles')
        .update({
            subscription_status: 'active',
        })
        .eq('user_id', customerData.user_id);
}

async function handleFailedInvoice(invoice, supabase) {
    const customerId = invoice.customer;
    const subscriptionId = invoice.subscription;

    if (!subscriptionId) {
        // This is not a subscription invoice
        return;
    }

    // Find the customer in your database
    const { data: customerData } = await supabase
        .from('customers')
        .select('user_id')
        .eq('stripe_customer_id', customerId)
        .single();

    if (!customerData?.user_id) {
        console.error('Customer not found:', customerId);
        return;
    }

    // Update subscription status
    await supabase
        .from('subscriptions')
        .update({
            status: 'past_due',
        })
        .eq('stripe_subscription_id', subscriptionId);

    // Update user's subscription status
    await supabase
        .from('profiles')
        .update({
            subscription_status: 'past_due',
        })
        .eq('user_id', customerData.user_id);
}

async function handleSubscriptionCancelled(subscription, supabase) {
    const subscriptionId = subscription.id;

    // Update subscription in your database
    await supabase
        .from('subscriptions')
        .update({
            status: 'cancelled',
            cancelled_at: new Date().toISOString(),
        })
        .eq('stripe_subscription_id', subscriptionId);

    // Find the user associated with this subscription
    const { data: subscriptionData } = await supabase
        .from('subscriptions')
        .select('user_id')
        .eq('stripe_subscription_id', subscriptionId)
        .single();

    if (subscriptionData?.user_id) {
        // Update user's subscription status
        await supabase
            .from('profiles')
            .update({
                subscription_status: 'cancelled',
                subscription_plan: null,
            })
            .eq('user_id', subscriptionData.user_id);
    }
}

// This is needed to parse the body as a stream for the webhook signature verification
export const config = {
    api: {
        bodyParser: false,
    },
};

Supabase Integration

1. Add Stripe Customer ID to Supabase User Table

Create a Supabase migration to add a stripe_customer_id column to your users table:

-- Create customers table to store Stripe customer IDs
CREATE TABLE IF NOT EXISTS customers (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  stripe_customer_id TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()),
  UNIQUE(user_id),
  UNIQUE(stripe_customer_id)
);

-- Create payments table to track one-time payments
CREATE TABLE IF NOT EXISTS payments (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  stripe_checkout_id TEXT,
  stripe_payment_intent_id TEXT,
  amount INTEGER NOT NULL,
  currency TEXT NOT NULL,
  status TEXT NOT NULL,
  payment_method TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW())
);

-- Create subscriptions table to track user subscriptions
CREATE TABLE IF NOT EXISTS subscriptions (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  stripe_customer_id TEXT NOT NULL,
  stripe_subscription_id TEXT NOT NULL,
  stripe_price_id TEXT NOT NULL,
  status TEXT NOT NULL,
  current_period_start TIMESTAMP WITH TIME ZONE NOT NULL,
  current_period_end TIMESTAMP WITH TIME ZONE NOT NULL,
  cancelled_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()),
  UNIQUE(stripe_subscription_id)
);

-- Add subscription fields to profiles table if it exists
-- If you don't have a profiles table, you should create one
ALTER TABLE IF EXISTS profiles
ADD COLUMN IF NOT EXISTS subscription_status TEXT,
ADD COLUMN IF NOT EXISTS subscription_plan TEXT;

2. Create a Supabase client hook (if you haven't already)

Create a file at lib/supabase/client.js:

'use client';

import { createContext, useContext, useEffect, useState } from 'react';
import { createClient } from '@supabase/supabase-js';

// Create a Supabase client
const createBrowserClient = () => {
    return createClient(
        process.env.NEXT_PUBLIC_SUPABASE_URL,
        process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY
    );
};

// Create a context for the Supabase client
const SupabaseContext = createContext(null);

// Provider component to wrap your app
export function SupabaseProvider({ children }) {
    const [supabase] = useState(() => createBrowserClient());
    const [user, setUser] = useState(null);
    const [loading, setLoading] = useState(true);

    useEffect(() => {
        const {
            data: { subscription },
        } = supabase.auth.onAuthStateChange(async (event, session) => {
            setUser(session?.user || null);
            setLoading(false);
        });

        // Initial check
        const checkUser = async () => {
            const {
                data: { session },
            } = await supabase.auth.getSession();
            setUser(session?.user || null);
            setLoading(false);
        };

        checkUser();

        return () => {
            subscription?.unsubscribe();
        };
    }, [supabase]);

    return (
        <SupabaseContext.Provider value={{ supabase, user, loading }}>
            {children}
        </SupabaseContext.Provider>
    );
}

// Hook to use the Supabase client
export function useSupabase() {
    const context = useContext(SupabaseContext);
    if (!context) {
        throw new Error('useSupabase must be used within a SupabaseProvider');
    }
    return context;
}

3. Update your app layout to include the Supabase provider

Update file at app/layout.jsx:

import { SupabaseProvider } from '@/lib/supabase/client';
import './globals.css';

export const metadata = {
    title: 'My Next.js App with Stripe and Supabase',
    description:
        'A Next.js app with Stripe payments and Supabase authentication',
};

export default function RootLayout({ children }) {
    return (
        <html lang='en'>
            <body>
                <SupabaseProvider>{children}</SupabaseProvider>
            </body>
        </html>
    );
}

Testing

1. Set up Stripe CLI for local testing

Download and install the Stripe CLI
Login to your Stripe account:
```
stripe login
```

Start the webhook forwarding:

stripe listen --forward-to http://localhost:3000/api/stripe/webhook

The CLI will output a webhook signing secret. Add this to your .env.local file:
```
STRIPE_WEBHOOK_SECRET=whsec_your_webhook_secret_from_cli
```

2. Use Stripe test cards for testing

For testing payments, use Stripe's test card numbers:

Successful payment: 4242 4242 4242 4242
Payment requires authentication: 4000 0025 0000 3155
Payment declined: 4000 0000 0000 0002

3. Testing checklist

Ensure the Stripe dashboard is set to test mode
Test one-time payments
Test subscription creation
Test subscription cancellation
Test webhook handling
Verify Supabase data is updated correctly

Going to Production

1. Update environment variables

For production, update your environment variables with production API keys:

# Production Stripe API Keys
NEXT_PUBLIC_STRIPE_PUBLISHABLE_KEY=pk_live_your_publishable_key
STRIPE_SECRET_KEY=sk_live_your_secret_key

# Production Webhook Secret (from Stripe Dashboard)
STRIPE_WEBHOOK_SECRET=whsec_your_live_webhook_secret

# Production URL
NEXT_PUBLIC_SITE_URL=https://your-production-domain.com

2. Set up production webhooks

Go to the Stripe Dashboard > Developers > Webhooks
Add an endpoint with your production URL (e.g., https://your-production-domain.com/api/stripe/webhook)
Select the events you want to listen for (at minimum: checkout.session.completed, invoice.paid, invoice.payment_failed, customer.subscription.deleted)
Copy the webhook signing secret and add it to your environment variables

3. Final checklist before going live

Ensure your Stripe account is fully verified for production
Test the integration in production mode with a small real payment
Verify that webhooks are being received correctly
Implement proper error handling and monitoring
Set up Stripe email receipts
Configure tax settings if applicable
Ensure compliance with local payment regulations

Conclusion

You now have a complete Stripe integration for your Next.js 15 application with Supabase authentication. This implementation supports:

One-time payments via Elements and Checkout Sessions
Subscription management
Webhook handling
Integration with user accounts

Remember to keep your API keys secure and never expose your Stripe secret key to the client-side code. All sensitive operations should be handled on the server-side through API routes.

Read this article and more on fzeba.com.

#32 PHP Server Tools for your file management needs on the server

Florian Zeba — Sat, 08 Mar 2025 11:00:00 +0000

Introduction

Server administrators and developers frequently need to perform file management operations such as extracting ZIP archives and deleting files or directories. These operations, traditionally performed via command-line interfaces or FTP clients, can be streamlined through browser-based PHP utilities. This article presents a technical analysis of three PHP scripts designed to handle common file management tasks:

extract-zip.php - For extracting ZIP archives
delete-folder.php - For selectively removing directories
delete-script.php - For unified management of both files and folders

We'll examine the architecture, security implementations, and technical advantages of each tool, while providing code analysis and potential implementation scenarios.

Accessing the Tools

These PHP scripts function as standalone web applications that can be accessed through any browser. After uploading the scripts to your server:

http://example-domain.com/extract-zip.php
http://example-domain.com/delete-folder.php
http://example-domain.com/delete-script.php

For local development environments, you can access them via:

http://localhost/extract-zip.php
http://localhost/delete-folder.php
http://localhost/delete-script.php

The scripts are entirely self-contained, requiring no additional setup or configuration files to function. Simply access the URL, and the web interface will load, providing immediate access to the file management tools.

Technical Overview

All three scripts follow a similar architectural pattern:

Configuration Layer: Setting execution parameters and defining base directories
Functional Layer: Core file operation functions with error handling
Request Processing Layer: Capturing and validating user input
Response Layer: Generating human-readable feedback and UI elements

The scripts also implement critical protection mechanisms including path traversal prevention, execution time management, and confirmation steps to prevent accidental data loss.

Script 1: ZIP File Extraction (`extract-zip.php`)

Technical Architecture

extract-zip.php implements a web interface for the ZipArchive PHP class. The script:

Sets an extended execution time limit to handle large archives
Scans the current directory for ZIP files
Provides a selection interface for users
Extracts the selected archive with detailed error handling
Provides feedback on the extraction process

Code Analysis

1. Execution Configuration

// Set max execution time to 5 minutes (300 seconds)
set_time_limit(300);

This configuration prevents script timeout during extraction of large archives. For production environments with significant ZIP processing requirements, this parameter may need adjustment based on server capabilities and expected file sizes.

2. ZIP File Discovery

function getZipFiles() {
    $zipFiles = [];
    $dir = dirname(__FILE__);
    if ($handle = opendir($dir)) {
        while (false !== ($entry = readdir($handle))) {
            if ($entry != "." && $entry != ".." && pathinfo($entry, PATHINFO_EXTENSION) === 'zip') {
                $zipFiles[] = $entry;
            }
        }
        closedir($handle);
    }
    return $zipFiles;
}

Technical notes:

Uses dirname(__FILE__) to ensure consistent path resolution regardless of execution context
Leverages pathinfo() with PATHINFO_EXTENSION parameter for reliable file type identification
Explicitly excludes directory entries (. and ..)
Properly closes directory handles to prevent resource leaks
Returns an indexed array for direct iteration in the presentation layer

3. Archive Extraction Engine

function extractZip($zipFile) {
    // Create a ZIP archive object
    $zip = new ZipArchive();

    // Open the ZIP file
    $result = $zip->open($zipFile);

    $extractedFiles = [];

    if ($result === TRUE) {
        // Get current directory (where this script and ZIP file are located)
        $extractPath = dirname(__FILE__);

        // Extract the contents of the ZIP file
        $zip->extractTo($extractPath);

        // Get list of extracted files
        for ($i = 0; $i < $zip->numFiles; $i++) {
            $extractedFiles[] = $zip->getNameIndex($i);
        }

        // Close the ZIP archive
        $zip->close();

        return [
            'success' => true,
            'files' => $extractedFiles
        ];
    } else {
        // Extraction failed, get error message
        switch ($result) {
            case ZipArchive::ER_INCONS:
                $error = 'Inconsistent ZIP archive.';
                break;
            case ZipArchive::ER_MEMORY:
                $error = 'Memory allocation failure.';
                break;
            case ZipArchive::ER_NOENT:
                $error = 'ZIP file not found.';
                break;
            case ZipArchive::ER_NOZIP:
                $error = 'Not a ZIP archive.';
                break;
            case ZipArchive::ER_OPEN:
                $error = 'Could not open file.';
                break;
            case ZipArchive::ER_READ:
                $error = 'Read error.';
                break;
            case ZipArchive::ER_SEEK:
                $error = 'Seek error.';
                break;
            default:
                $error = 'Unknown error (code: ' . $result . ').';
                break;
        }

        return [
            'success' => false,
            'error' => $error
        ];
    }
}

Technical implementation features:

Uses the native PHP ZipArchive class for optimal performance
Implements granular error handling with specific error codes from the ZipArchive class
Builds a comprehensive list of extracted files for verification
Follows proper resource management by explicitly closing the ZIP archive
Returns a structured array with consistent keys for result processing

4. Security Implementation

// Handle extraction request
$extractionResult = null;
if (isset($_POST['extract']) && isset($_POST['zipFile'])) {
    $zipFile = $_POST['zipFile'];

    // Basic security check to prevent directory traversal
    if (strpos($zipFile, '../') !== false || strpos($zipFile, '..\\') !== false) {
        $extractionResult = [
            'success' => false,
            'error' => 'Invalid file path.'
        ];
    } else {
        $extractionResult = extractZip($zipFile);
    }
}

Security mechanisms:

Validates required parameters before processing
Implements directory traversal protection by checking for path manipulation sequences
Accounts for both Unix (../) and Windows (..\) path separators
Returns structured error response on security violations
Does not expose system paths in error messages

Technical Limitations

The script extracts all files to the current directory without a configurable destination
Directory traversal protection only checks for direct path manipulation, not encoded variants
Lacks authentication mechanisms for restricting access to authorized users
Does not implement file type verification beyond the .zip extension

Script 2: Folder Deletion Tool (`delete-folder.php`)

Technical Architecture

delete-folder.php provides a specialized interface for recursively deleting directory structures. The script implements:

Base directory restriction for security
Directory scanning and listing
Multi-step confirmation process
Recursive directory deletion with error handling
User feedback mechanisms

Code Analysis

1. Base Directory Configuration

// Base directory - set this to limit which directories can be accessed
// For safety, default to the current directory
$baseDir = realpath(dirname(__FILE__));

Technical implementation notes:

Uses realpath() to resolve the absolute canonical path
Prevents symbolic link attacks by resolving to the real filesystem path
Sets a restrictive default (current directory) for maximum security

2. Directory Recursion Algorithm

function deleteDirectory($dir) {
    if (!file_exists($dir)) {
        return true;
    }

    if (!is_dir($dir)) {
        return unlink($dir);
    }

    $files = array_diff(scandir($dir), ['.', '..']);

    foreach ($files as $file) {
        $path = $dir . DIRECTORY_SEPARATOR . $file;

        if (is_dir($path)) {
            deleteDirectory($path);
        } else {
            unlink($path);
        }
    }

    return rmdir($dir);
}

Algorithm characteristics:

Implements a depth-first traversal of the directory tree
Uses recursive function calls for subdirectory processing
Handles edge cases (non-existent directories, files misidentified as directories)
Uses DIRECTORY_SEPARATOR constant for cross-platform compatibility
Returns boolean success indicators for error tracking

3. Path Security Validation

// Security check: Make sure the folder is within the base directory
$realSelectedPath = realpath($selectedFolder);

if ($realSelectedPath === false) {
    $message = "Error: The selected folder does not exist.";
    $messageType = "error";
} elseif (strpos($realSelectedPath, $baseDir) !== 0) {
    $message = "Error: You can only delete folders within the allowed directory.";
    $messageType = "error";
} else {
    // Deletion code here
}

Security mechanisms:

Uses realpath() to resolve symbolic links and normalize paths
Validates that the resolved path exists in the filesystem
Ensures the target path is a subdirectory of the base directory
Uses string position comparison (strpos() === 0) to verify directory hierarchy
Provides informative error messages without exposing system paths

4. Multi-step Confirmation

// Confirmation step
if (!isset($_POST['confirm'])) {
    $message = "Please confirm deletion of the folder: " . htmlspecialchars($folderDeleted);
    $messageType = "warning";
} else {
    // Attempt deletion
    try {
        if (deleteDirectory($realSelectedPath)) {
            $message = "Successfully deleted folder: " . htmlspecialchars($folderDeleted);
            $messageType = "success";
        } else {
            $message = "Failed to delete folder: " . htmlspecialchars($folderDeleted);
            $messageType = "error";
        }
    } catch (Exception $e) {
        $message = "Error: " . $e->getMessage();
        $messageType = "error";
    }
}

Implementation features:

Two-step confirmation process for destructive operations
Exception handling for unexpected errors during deletion
Consistent messaging format for different result types
Uses htmlspecialchars() to prevent XSS vulnerabilities in output

Script 3: Comprehensive File and Folder Management (`delete-script.php`)

Technical Architecture

delete-script.php provides a unified interface for managing both files and directories. The script:

Scans directories for all content (files and folders)
Presents a table-based interface with type indicators and file sizes
Provides individual delete buttons for each item
Implements the same security features as the folder deletion tool
Adds file type and size metadata for improved user experience

Code Analysis

1. Enhanced File and Folder Discovery

function getFilesAndFolders($directory) {
    $items = [];

    if ($handle = opendir($directory)) {
        while (false !== ($entry = readdir($handle))) {
            if ($entry != "." && $entry != "..") {
                $fullPath = $directory . DIRECTORY_SEPARATOR . $entry;

                // Store relative path to make it easier to display
                $relativePath = str_replace($GLOBALS['baseDir'] . DIRECTORY_SEPARATOR, '', $fullPath);

                // Add type indicator
                $type = is_dir($fullPath) ? 'folder' : 'file';

                // For files, get extension and size
                $extension = '';
                $size = '';
                if ($type === 'file') {
                    $extension = strtolower(pathinfo($entry, PATHINFO_EXTENSION));
                    $size = filesize($fullPath);
                    // Format file size
                    if ($size < 1024) {
                        $size = $size . ' B';
                    } elseif ($size < 1048576) {
                        $size = round($size / 1024, 2) . ' KB';
                    } else {
                        $size = round($size / 1048576, 2) . ' MB';
                    }
                }

                $items[] = [
                    'path' => $fullPath,
                    'name' => $relativePath ?: $entry,
                    'type' => $type,
                    'extension' => $extension,
                    'size' => $size
                ];
            }
        }
        closedir($handle);
    }

    // Sort items: folders first, then files, both alphabetically
    usort($items, function($a, $b) {
        if ($a['type'] === $b['type']) {
            return strcasecmp($a['name'], $b['name']);
        }
        return ($a['type'] === 'folder') ? -1 : 1;
    });

    return $items;
}

Technical implementation features:

Collects comprehensive metadata for each filesystem item
Implements human-readable file size formatting with appropriate units
Uses case-insensitive string comparison for alphabetical sorting
Prioritizes directories over files in the listing order
Stores both absolute paths (for operations) and relative paths (for display)
Uses global variable access for configuration consistency

2. Unified Deletion Handler

// Attempt deletion
try {
    if ($itemType === "folder") {
        $result = deleteDirectory($realSelectedPath);
    } else {
        $result = unlink($realSelectedPath);
    }

    if ($result) {
        $message = "Successfully deleted " . $itemType . ": " . htmlspecialchars($itemDeleted);
        $messageType = "success";
    } else {
        $message = "Failed to delete " . $itemType . ": " . htmlspecialchars($itemDeleted);
        $messageType = "error";
    }
} catch (Exception $e) {
    $message = "Error: " . $e->getMessage();
    $messageType = "error";
}

Implementation characteristics:

Conditional logic based on item type
Uses native unlink() function for file deletion
Reuses the recursive deleteDirectory() function for directories
Consistent error handling for both operation types
Maintains type-specific messaging for user feedback

3. Enhanced User Interface Elements

<table class="file-list">
    <thead>
        <tr>
            <th>Type</th>
            <th>Name</th>
            <th>Size</th>
            <th>Action</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($items as $item): ?>
            <tr>
                <td>
                    <?php if ($item['type'] === 'folder'): ?>
                        <span class="folder-icon">ðŸ“</span> Folder
                    <?php elseif ($item['extension'] === 'zip'): ?>
                        <span class="zip-icon">ðŸ—œï¸</span> ZIP File
                    <?php else: ?>
                        <span class="file-icon">ðŸ“„</span> File
                    <?php endif; ?>
                </td>
                <td><?php echo htmlspecialchars($item['name']); ?></td>
                <td><?php echo $item['size']; ?></td>
                <td>
                    <form method="post" onsubmit="return confirm('Are you sure you want to delete this <?php echo $item['type']; ?>?');">
                        <input type="hidden" name="item" value="<?php echo htmlspecialchars($item['path']); ?>">
                        <button type="submit" name="delete" class="delete-btn">Delete</button>
                    </form>
                </td>
            </tr>
        <?php endforeach; ?>
    </tbody>
</table>

UI technical features:

Table-based layout for structured data presentation
Visual icon differentiation for different file types
Client-side JavaScript confirmation for deletion operations
Form-based action buttons for each item
Hidden input fields for maintaining state between requests
XSS protection on all dynamically generated content

Technical Applications and Use Cases

1. Web Application Deployment

These tools are valuable in web application deployment scenarios:

Package Extraction: extract-zip.php can unpack deployment packages directly on the server
Cleanup Operations: delete-folder.php can remove outdated releases or temporary installation files
Version Management: delete-script.php provides a unified interface for managing deployment artifacts

Example workflow:

Upload deployment package: deployment-v1.2.3.zip to your server
Access http://example-domain.com/extract-zip.php in your browser
Select deployment-v1.2.3.zip from the dropdown and click "Extract ZIP File"
Verify the extracted files in the success screen
If needed, use http://example-domain.com/delete-script.php to clean up the original ZIP file

Implementation approach:

// Example addition to extract-zip.php for deployment versioning
$deploymentDirectory = 'releases/v' . date('Y.m.d-His');
mkdir($deploymentDirectory, 0755, true);
$zip->extractTo($deploymentDirectory);

2. Content Management Systems

For CMS applications, these scripts provide:

Plugin/Theme Installation: Extract plugin or theme ZIP packages to the appropriate directories
Media Management: Clean up unused media directories
Bulk Content Operations: Delete generated files and directories for expired content

Example workflow:

Download a new theme or plugin as a ZIP file from the developer's site
Upload the ZIP file to your server (via FTP or other means)
Navigate to http://your-cms-site.com/extract-zip.php in your browser
Extract the theme directly to your web root
Access http://your-cms-site.com/delete-folder.php to remove any deprecated themes

Implementation approach:

// Example modification for CMS plugin installation
function extractPlugin($zipFile, $pluginDirectory) {
    $zip = new ZipArchive();
    $result = $zip->open($zipFile);

    if ($result === TRUE) {
        // Create target directory if it doesn't exist
        if (!file_exists($pluginDirectory)) {
            mkdir($pluginDirectory, 0755, true);
        }

        $zip->extractTo($pluginDirectory);
        $zip->close();
        return true;
    }
    return false;
}

3. Server Administration

For server administrators, these tools provide:

Remote File Management: Browser-based alternative to FTP or SSH access
Disk Space Management: Identify and remove large directories consuming storage
Maintenance Operations: Extract updates or clean up log directories

Example workflow:

Upload system updates as a ZIP file to your server
Browse to http://server-ip/admin/extract-zip.php
Extract the updates package to apply changes
Navigate to http://server-ip/admin/delete-script.php to view all files and directories
Clean up temporary directories and outdated backup files

4. Secure File Hosting

For file hosting applications, these tools can be adapted to:

User Upload Processing: Extract user-uploaded ZIP files to their storage areas
Storage Quota Management: Delete user files to maintain storage quotas
Content Organization: Provide structured file management capabilities to end users

Implementation approach:

// Example modification for multi-tenant file hosting
$userDirectory = 'users/' . $userId . '/files/';
$baseDir = realpath($userDirectory); // Restrict operations to user's directory

Technical Enhancements for Production Use

For production deployment, these scripts should be enhanced with:

1. Authentication & Authorization

Note: As currently implemented, these scripts are immediately accessible to anyone who knows the URL. For production environments, it's critical to add authentication before deployment.

// Example basic authentication implementation
session_start();
if (!isset($_SESSION['authenticated']) || $_SESSION['authenticated'] !== true) {
    // Redirect to login page or show authentication form
    header('Location: login.php');
    exit;
}

2. Enhanced Security Validation

// More robust path traversal prevention
function isPathSafe($path) {
    $realPath = realpath($path);
    $basePath = realpath($GLOBALS['baseDir']);

    // Check if path exists and is within base directory
    if ($realPath === false || strpos($realPath, $basePath) !== 0) {
        return false;
    }

    // Check for encoded path traversal attempts
    $decodedPath = urldecode($path);
    if ($path !== $decodedPath && strpos($decodedPath, '..') !== false) {
        return false;
    }

    return true;
}

3. Logging and Audit Trails

// Example logging implementation
function logOperation($operation, $path, $result, $userId) {
    $logEntry = [
        'timestamp' => date('Y-m-d H:i:s'),
        'operation' => $operation,
        'path' => $path,
        'result' => $result ? 'success' : 'failure',
        'user' => $userId,
        'ip' => $_SERVER['REMOTE_ADDR']
    ];

    file_put_contents(
        'file_operations.log',
        json_encode($logEntry) . PHP_EOL,
        FILE_APPEND
    );
}

4. Configurable Base Directories

// Configuration file approach
$config = include 'config.php';
$baseDir = realpath($config['base_directory']);

// Or environment variable approach
$baseDir = realpath(getenv('FILE_MANAGER_BASE_DIR') ?: dirname(__FILE__));

Installation Instructions

To implement these tools on your server:

Download or create the three PHP files with the provided code:

- `extract-zip.php`
- `delete-folder.php`
- `delete-script.php`

Upload the files to your web server using FTP or your preferred file transfer method

Set appropriate permissions:

chmod 644 extract-zip.php delete-folder.php delete-script.php

Access the tools through your web browser:

http://your-domain.com/extract-zip.php
http://your-domain.com/delete-folder.php
http://your-domain.com/delete-script.php

For local development with PHP's built-in server:
```
php -S localhost:8000
```
Then access: http://localhost:8000/extract-zip.php

Conclusion

These PHP file management tools demonstrate effective implementation of server-side file operations. The key technical strengths include:

Security-conscious design: Path validation, input sanitization, and restricted operations
Error handling: Comprehensive error detection and user-friendly messaging
Resource management: Proper handling of file handles and memory-intensive operations
Cross-platform compatibility: Using PHP constants and functions that work across operating systems
Browser-based accessibility: Immediate access via standard HTTP URLs without additional configuration

While suitable for administrative use in protected environments, production deployment should include the enhancements outlined above, particularly authentication mechanisms and more robust security validation.

These utilities serve as both practical tools for server management and educational examples of handling filesystem operations securely in PHP applications.

Additional Resources

PHP Manual: ZipArchive

Files for Download

GitHub Repository: PHP File Management Tools

Read this article and more on fzeba.com.

#31 Apache Server Configuration and the workings of the .htaccess file

Florian Zeba — Fri, 07 Mar 2025 11:00:00 +0000

Apache HTTP Server (commonly called "Apache") is one of the world's most popular web server software solutions, powering approximately 30% of all websites on the internet. This article will explain how Apache works, its basic configuration, and focus particularly on the role and structure of .htaccess files in Apache environments.

What is the Apache HTTP Server?

Apache is an open-source web server application that plays a critical role in delivering web content to users. Developed and maintained by the Apache Software Foundation, it can run on various operating systems including Unix/Linux, Windows, and macOS.

How Apache Works

At its core, Apache functions through a request-processing cycle:

Connection Acceptance: Apache listens for incoming HTTP requests on designated ports (typically port 80 for HTTP or 443 for HTTPS).
Request Processing: When a request arrives, Apache parses the HTTP headers and determines how to handle it based on its configuration.
Content Delivery: The server locates the requested resource (HTML files, images, etc.) and sends it back to the client with appropriate HTTP headers.
Connection Handling: Apache can handle multiple simultaneous connections using different processing models (prefork, worker, or event MPM).

Apache Server Configuration

Apache's primary configuration is controlled through several key files, typically located in /etc/apache2/ or /etc/httpd/ depending on your operating system:

httpd.conf: The main configuration file containing global settings
apache2.conf: Alternative main configuration file name on some distributions
conf.d/: Directory containing modular configuration files
sites-available/: Directory containing configuration for virtual hosts
sites-enabled/: Symbolic links to enabled virtual host configurations

Key Configuration Directives

Apache uses directives to configure its behavior. Some important ones include:

ServerRoot: Base directory for the server
DocumentRoot: Directory where website files are stored
Listen: IP addresses and ports Apache listens on
ServerName: Domain name of the server
VirtualHost: Configuration for hosting multiple websites on a single server
Directory: Configuration for specific directories

Understanding .htaccess Files

The .htaccess (hypertext access) file is a directory-level configuration file that allows for decentralized management of web server configuration. It's particularly useful in shared hosting environments where users don't have access to the main server configuration files.

Purpose of .htaccess Files

.htaccess files can be used for:

URL Rewriting and Redirection: Modifying URLs for better usability or SEO
Access Control: Restricting access to certain pages or directories
Custom Error Pages: Defining specific pages for HTTP errors
MIME Type Handling: Determining how to serve different file types
Performance Settings: Configuring caching and compression

Where to Place .htaccess Files

The location of .htaccess files is crucial for understanding their scope and effect:

Document Root: When placed in the server's document root directory, the .htaccess file affects the entire website.
Subdirectories: When placed in subdirectories, the file's rules apply only to that directory and all its subdirectories.
Cascading Effect: Multiple .htaccess files can exist in a directory hierarchy, with each one applying its rules in addition to (or overriding) the rules from parent directories.

For instance, if you have a website with this structure:

/var/www/html/           # Document root with a .htaccess file
/var/www/html/blog/      # Blog directory with its own .htaccess file
/var/www/html/shop/      # Shop directory with its own .htaccess file

When a user requests /blog/post1.html:

Apache first processes the .htaccess in the document root
Then it processes the .htaccess in the /blog/ directory
Rules in the /blog/.htaccess file can override similar rules in the root .htaccess

This hierarchical processing allows for very granular control over different sections of your website.

How .htaccess Works

When a client requests a resource, Apache checks for .htaccess files in each directory from the document root to the directory containing the requested file. The configurations are applied cumulatively, with more specific (deeper) directory settings overriding earlier ones.

Analyzing Our Example .htaccess File

Let's break down the sample .htaccess file provided:

# .htaccess file

RewriteEngine on

This first line enables Apache's rewrite engine, which is necessary for all the URL manipulation rules that follow.

# Redirect non-www to www for example-domain.com
RewriteCond %{HTTP_HOST} ^example-domain\.com$
RewriteRule (.*) http://www.example-domain.com/$1 [R=301,L]

This section creates a canonical URL structure by redirecting requests from example-domain.com to www.example-domain.com. The [R=301,L] flags indicate that this is a permanent redirect (HTTP 301) and that this is the last rule to be processed if the conditions match.

# Map www.example-domain.com to gambio-example subfolder
RewriteCond %{HTTP_HOST} ^www\.example-domain\.com$
RewriteCond %{REQUEST_URI} !^/gambio-example/
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ /gambio-example/$1 [L]

This more complex section implements URL mapping for content separation. It checks if:

The host is www.example-domain.com
The request URI doesn't already start with /gambio-example/
The requested file doesn't exist (!-f)
The requested directory doesn't exist (!-d)

If all these conditions are met, it internally rewrites the request to the gambio-example subdirectory. This allows for serving content from a subdirectory while presenting it as if it were at the root of the domain.

# Handle root domain case for example-domain.com
RewriteCond %{HTTP_HOST} ^www\.example-domain\.com$
RewriteRule ^$ /gambio-example/index.php [L]

This rule handles requests to the root of www.example-domain.com by directing them to /gambio-example/index.php, ensuring the homepage works correctly.

The file continues with similar rules for another domain (example2.com), showing how multiple websites can be configured within a single .htaccess file:

# Map example2.com to its subfolder (add more domains as needed)
RewriteCond %{HTTP_HOST} ^(www\.)?example2\.com$
RewriteCond %{REQUEST_URI} !^/example2-subfolder/
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ /example2-subfolder/$1 [L]

# Handle root domain case for example2.com
RewriteCond %{HTTP_HOST} ^(www\.)?example2\.com$
RewriteRule ^$ /example2-subfolder/index.php [L]

Note that the example2.com rules handle both the www and non-www versions with a single condition using the (www\.)? pattern, which is a more concise approach than used for the first domain.

# Any other domains will be served from the document root as normal
# No additional rules needed for domains that should go to the root

The final comment indicates that any domains not explicitly handled by the previous rules will be served content from the document root directory as normal.

Effects of .htaccess Placement in This Configuration

In our example file, the location of the .htaccess file has significant implications:

Document Root Placement:

- This .htaccess file should be placed in the document root of the server.
- From this position, it can intercept and process all incoming requests to any domain hosted on this server.
- It can then route these requests to the appropriate subdirectories based on domain names.

Subdirectory Impact:

- If this exact .htaccess were placed in a subdirectory (e.g., `/gambio-example/`), the domain-level rewrites would not work correctly because the rules would only be applied after Apache has already determined which directory to use.
- Domain-level routing decisions must be made at the document root level.

Multiple .htaccess Approach:
- A more modular approach would be to have a simpler .htaccess in the document root handling only the domain routing, and then separate .htaccess files in each subdirectory handling specific rules for each website.
- This would make maintenance easier, especially with multiple websites.

For example:

Document root .htaccess: Handles routing requests to the correct subdirectories
/gambio-example/.htaccess: Contains rules specific to the example-domain.com website
/example2-subfolder/.htaccess: Contains rules specific to the example2.com website

Key Concepts in This .htaccess Configuration

Multi-domain Hosting: This configuration shows how a single Apache server can host multiple websites (example-domain.com and example2.com).
Domain Canonicalization: Enforcing a consistent URL structure (www vs. non-www).
Directory Mapping: Serving content from subdirectories while presenting them as if they were at the root.
Conditional Processing: Using multiple RewriteCond directives to create complex, specific matching patterns.
Flag Usage: The [L] flag stops processing rules when a match is found, and [R=301] creates permanent redirects for SEO benefits.

Best Practices for .htaccess Files

Performance Considerations: .htaccess files are read on every request, so use them judiciously in high-traffic environments.
Security: Protect .htaccess files from being directly accessed by setting appropriate file permissions (typically 644).
Backup: Always back up working .htaccess files before making changes.
Testing: Test modifications thoroughly in a development environment before deploying to production.
Documentation: Comment your .htaccess rules to explain their purpose and function.
Scope Appropriately: Place rules at the appropriate level in the directory hierarchy - only use document root level for rules that need to apply site-wide.

Conclusion

Apache HTTP Server provides a powerful and flexible platform for serving web content. The .htaccess file offers a convenient way to implement specific configurations at the directory level, especially for URL manipulation, access control, and multi-domain hosting.

The example .htaccess file we analyzed demonstrates how to manage multiple websites from a single Apache installation, implementing common patterns like domain canonicalization and subdirectory mapping. Understanding the crucial relationship between .htaccess file placement and its scope of effect is essential for effectively managing and optimizing Apache web servers in various hosting environments.

Read this article and more on fzeba.com.

#30 OETV Tennis Analytics Web App

Florian Zeba — Thu, 06 Mar 2025 17:00:53 +0000

Introduction

The OETV Tennis Analytics project, developed by GitHub user flnzba, represents a sophisticated data pipeline and analytics solution for tennis statistics focused on the Austrian Tennis Federation (OETV) data. The project implements a modern data engineering workflow that extracts, transforms, and visualizes tennis data, providing valuable insights into player performance, rankings, and tournament trends through an interactive dashboard.

This technical article examines the architecture of the project, with a specific focus on the three core components - load.py, transform.py, and app.py - that form the backbone of this analytics solution.

Project Overview

The OETV Tennis Analytics project is structured as a comprehensive data pipeline with three main components:

Data Extraction and Loading (load.py): Responsible for retrieving data from OETV's API and loading it into a SQLite database.
Data Transformation (transform.py): Handles the processing and transformation of raw tennis data into structured, analysis-ready formats.
Web Application Interface (app.py): A Streamlit-powered dashboard that visualizes the processed data, allowing users to interact with and gain insights from tennis statistics.

The project employs Docker for containerization, ensuring consistent deployment across different environments, and includes checkpointing mechanisms to optimize data processing efficiency.

Architecture Deep Dive

Data Flow Architecture

The data flows through the system in the following sequence:

Extraction: Raw data is extracted from OETV APIs and external sources
Loading: Data is stored in a SQLite database (data.db)
Transformation: Raw data is processed, cleaned, and transformed
Visualization: Processed data is presented through an interactive Streamlit dashboard

Core Components Analysis

Let's examine each core component in detail with actual code examples:

1. Data Extraction and Loading (`load.py`)

load.py serves as the data ingestion layer, responsible for extracting tennis data from the OETV API and storing it in a structured format. The script uses the curl_cffi library for making HTTP requests, which helps with browser impersonation to avoid blocking.

Key Functions and Mechanisms

API Integration

The module implements a function to fetch data from OETV's API, handling authentication, rate limiting, and response parsing:

def get_data_batches(client):
    """Generator function that yields batches of player data as they're fetched.

    Parameters:
    - client: Dictionary containing API client configuration

    Yields:
    - Batch of player records (list of dictionaries)
    """
    start = load_checkpoint()
    total_results = 0
    consecutive_errors = 0
    max_consecutive_errors = 3

    print(f"Starting batch fetching from position {start} at {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")

    try:
        # First request to get total results
        retries = 0
        while retries < client['max_retries']:
            try:
                r = requests.get(
                    client['url'],
                    headers=client['headers'],
                    referer=client['referer'],
                    impersonate="chrome",
                    timeout=client['timeout']
                )
                if r.status_code == 200:
                    rankings_data = json.loads(r.text)
                    all_data = list(rankings_data.values())
                    all_data = list(all_data[1].values())
                    total_results = all_data[0]
                    print(f"Total results to fetch: {total_results}")
                    break
                else:
                    print(f"Error status code: {r.status_code}, retrying...")
                    retries += 1
                    time.sleep(5 * retries)  # Increasing backoff
            except Exception as e:
                print(f"Exception during initial request: {e}")
                retries += 1
                time.sleep(5 * retries)

This function acts as a generator that fetches data in batches from the OETV API. It first determines the total number of records available, then systematically retrieves them in chunks of 100.

Robust Error Handling

The module includes robust error handling to manage issues with API requests, rate limiting, and response parsing:

def handle_error(status_code):
    print("Error: ", status_code)
    # Get the base directory from environment or use a default
    DATA_DIR = os.environ.get('DATA_DIR', os.path.dirname(os.path.abspath(__file__)))
    log_dir = os.path.join(DATA_DIR, "logs")
    os.makedirs(log_dir, exist_ok=True)
    with open(os.path.join(log_dir, "error-last-call.txt"), "a") as file:
        file.write(f"Error: {status_code} \n")

Error handling is crucial for maintaining the stability of the pipeline, especially when dealing with external APIs. The function logs errors for later investigation and ensures the process can continue despite temporary failures.

Checkpointing System

To optimize performance and prevent redundant data fetching, load.py implements a checkpointing mechanism. The system tracks the last successful data load, enabling incremental updates:

def get_checkpoint_file():
    # Get the base directory from environment or use a default
    # This allows configuration in Docker environments
    DATA_DIR = os.environ.get('DATA_DIR', os.path.dirname(os.path.abspath(__file__)))
    checkpoint_dir = os.path.join(DATA_DIR, "checkpoints")
    os.makedirs(checkpoint_dir, exist_ok=True)
    return os.path.join(checkpoint_dir, "checkpoint.txt")


def save_checkpoint(start, all_rankings=None):
    with open(get_checkpoint_file(), "w") as f:
        f.write(str(start))

    # Optionally save the current rankings as backup
    if all_rankings:
        # Get the base directory from environment or use a default
        DATA_DIR = os.environ.get('DATA_DIR', os.path.dirname(os.path.abspath(__file__)))
        checkpoint_dir = os.path.join(DATA_DIR, "checkpoints")
        os.makedirs(checkpoint_dir, exist_ok=True)
        temp_path = os.path.join(checkpoint_dir, "rankings_checkpoint.json")
        try:
            with open(temp_path, "w") as file:
                json.dump(all_rankings, file)
        except Exception as e:
            print(f"Error saving rankings checkpoint: {e}")


def load_checkpoint():
    if os.path.exists(get_checkpoint_file()):
        with open(get_checkpoint_file(), "r") as f:
            return int(f.read().strip())
    return 0

The checkpointing mechanism allows the system to resume data collection from where it left off, which is especially useful for large datasets or when handling interruptions.

Dynamic Backoff Strategy

The module implements a sophisticated backoff strategy to handle API rate limits and server load:

# Dynamically adjust delay based on success pattern
if consecutive_errors > 0:
    consecutive_errors = 0
    # Increase delay slightly after recovering from errors
    base_delay = min(base_delay * 1.2, 5.0)
else:
    # Gradually decrease delay on consistent success
    base_delay = max(base_delay * 0.9, 0.5)

# Add some randomness to the delay
time.sleep(base_delay + random.uniform(0.1, 1.0))

This adaptive approach adjusts request timing based on the API's response pattern, reducing delays when the server is responsive and increasing them when encountering errors.

2. Data Transformation (`transform.py`)

transform.py forms the processing layer of the pipeline, converting raw data from the API into a structured SQLite database. This module is responsible for creating the database schema and populating it with the fetched data.

Key Functions and Mechanisms

Database Initialization

The module initializes the SQLite database with the appropriate schema for storing player data:

def init_database():
    """Initialize the database with the players table"""
    conn = sqlite3.connect(db_path)
    c = conn.cursor()

    # Drop table if it exists
    c.execute("DROP TABLE IF EXISTS players")

    # Create table
    c.execute(
        """
        CREATE TABLE IF NOT EXISTS players (
            playerId TEXT,
            licenceNr TEXT,
            natRank INTEGER,
            natRankFed INTEGER,
            firstname TEXT,
            lastname TEXT,
            nationality TEXT,
            clubName TEXT,
            clubNr TEXT,
            fedNickname TEXT,
            fedRank REAL,
            birthYear INTEGER,
            atpPoints INTEGER,
            points INTEGER
        )
        """
    )

    conn.commit()

    return conn, c

This function establishes the database structure, defining the schema for player data with fields for identifiers, rankings, personal information, and performance metrics.

Batch Processing

The module implements a batch processing approach to efficiently handle large amounts of data:

def save_batch(batch, conn, cursor):
    """Save a batch of player data to the database"""
    try:
        # Insert data into the table
        for player in batch:
            cursor.execute(
                """
                INSERT INTO players (playerId, licenceNr, natRank, natRankFed, firstname, lastname, nationality, clubName, clubNr, fedNickname, fedRank, birthYear, atpPoints, points) VALUES
                (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    player["playerId"],
                    player["licenceNr"],
                    player["natRank"],
                    player["natRankFed"],
                    player["firstname"],
                    player["lastname"],
                    player["nationality"],
                    player["clubName"],
                    player["clubNr"],
                    player["fedNickname"],
                    player["fedRank"],
                    player["birthYear"],
                    player["atpPoints"],
                    player["points"],
                ),
            )

        # Commit this batch
        conn.commit()
        return True
    except Exception as e:
        print(f"Error saving batch to database: {e}")
        # Rollback on error
        conn.rollback()
        return False

This function handles the insertion of batches of player data into the database, with transaction management to ensure data integrity.

Continuous Processing Pipeline

The module orchestrates a continuous processing pipeline that transforms data as it's received:

def process_data_continuous():
    """Process data continuously as it's fetched, saving in batches"""
    try:
        print(f"Starting data processing at {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")

        # Initialize database
        conn, cursor = init_database()

        # Set up the API client
        client = setup_api_client()

        # Get batches and process them
        total_processed = 0
        for batch_number, batch in enumerate(get_data_batches(client)):
            # Save this batch to the database
            if save_batch(batch, conn, cursor):
                total_processed += len(batch)
                print(f"Batch {batch_number+1} saved. Total records processed: {total_processed}")
            else:
                print(f"Failed to save batch {batch_number+1}")

        # Get total record count
        cursor.execute("SELECT COUNT(*) FROM players")
        total_records = cursor.fetchone()[0]
        print(f"Total records in database: {total_records}")

        # Close connection
        conn.close()

        print(f"Data processing completed at {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")

    except Exception as e:
        print(f"Fatal error in data processing: {e}")
        if 'conn' in locals():
            conn.close()
        sys.exit(1)

This function serves as the main orchestrator of the transformation process, connecting the data extraction from load.py with the database operations, while providing progress updates and summary statistics.

3. Web Application Interface (`app.py`)

app.py serves as the visualization and interaction layer, implemented as a Streamlit application that presents the processed data in an accessible format through an interactive dashboard.

Key Functions and Mechanisms

Database Connection and Data Loading

The module establishes a connection to the SQLite database and loads the player data:

# Database path - must match the path used in transform.py
# Get the base directory from environment or use a default
# This allows configuration in Docker environments
DATA_DIR = os.environ.get('DATA_DIR', os.path.dirname(os.path.abspath(__file__)))
db_path = os.path.join(DATA_DIR, "data.db")

# Check if the database file exists
if not os.path.exists(db_path):
    st.error(f"Database file not found at {db_path}. Please run transform.py first to create the database.")
    st.stop()

# Load data from SQLite database
conn = sqlite3.connect(db_path)
query = "SELECT * FROM players"
data = pd.read_sql_query(query, conn)
conn.close()

df = pd.DataFrame(data)

# Calculate player age from birth year
current_year = pd.Timestamp.now().year
df['age'] = current_year - df['birthYear']

This section loads the data from the database into a pandas DataFrame, which is then used for visualization and analysis. It also performs some initial data preprocessing, such as calculating the players' ages.

Interactive Filtering

The module implements a comprehensive set of interactive filters in the sidebar to allow users to explore the data based on various dimensions:

# Sidebar Filters
st.sidebar.header("🎾 Tennis Rankings Dashboard")
st.sidebar.markdown("---")

st.sidebar.header("Filter Options")

# Player Search
st.sidebar.subheader("Player Search")
search_query = st.sidebar.text_input("Search by player name")

# Rank Filter
st.sidebar.subheader("Ranking")
# Ensure min and max are different for the inputs
rank_min = float(df["fedRank"].min())
rank_max = float(df["fedRank"].max())
if rank_min == rank_max:
    rank_max = rank_min + 1.0  # Ensure they're different
elif rank_max - rank_min < 0.1:
    rank_max = rank_min + 0.1  # Ensure there's at least 0.1 difference

rank_filter_min = st.sidebar.number_input(
    "Min Rank",
    min_value=rank_min,
    max_value=rank_max,
    value=rank_min,
)
rank_filter_max = st.sidebar.number_input(
    "Max Rank",
    min_value=rank_filter_min,
    max_value=rank_max,
    value=min(rank_filter_min + 9.0, rank_max),  # Default to showing a reasonable range
)
rank_filter = (rank_filter_min, rank_filter_max)

# Apply all filters
df_filtered = df.copy()

# Apply search filter if provided
if search_query:
    df_filtered = df_filtered[
        df_filtered['firstname'].str.contains(search_query, case=False) |
        df_filtered['lastname'].str.contains(search_query, case=False)
    ]

# Apply other filters
df_filtered = df_filtered[
    (df_filtered["fedRank"] >= rank_filter[0]) &
    (df_filtered["fedRank"] <= rank_filter[1]) &
    (df_filtered["birthYear"] >= age_filter[0]) &
    (df_filtered["birthYear"] <= age_filter[1]) &
    (df_filtered["points"] >= points_range[0]) &
    (df_filtered["points"] <= points_range[1])
]

The sidebar includes filters for player name search, ranking range, age/birth year, federation, club, and points, enabling users to focus on specific subsets of the data.

Tabbed Interface for Analysis Categories

The application organizes the analysis into distinct tabs for different categories of visualizations:

# Create tabs for different categories of visualizations
tabs = st.tabs(["Personal Comparison", "Player Rankings", "Federation Analysis", "Age Analysis", "Club Analysis"])

This approach creates a clean, organized interface that groups related visualizations together, improving the user experience.

Personal Comparison Feature

One of the most interesting features is the personal comparison, which allows users to compare their own ranking with the database:

# Tab 1: Personal Comparison
with tabs[0]:
    st.header("Compare Your Ranking")

    col1, col2 = st.columns(2)

    with col1:
        st.subheader("Your Rank Percentile")
        fig, ax = plt.subplots(figsize=(10, 8))

        # Define better colors for pie chart
        colors = ['#4CAF50', '#FF5252']

        # Create pie chart with better styling
        wedges, texts, autotexts = ax.pie(
            [percentile, 100 - percentile],
            labels=None,  # Remove default labels for custom positioning
            autopct='%1.1f%%',
            colors=colors,
            startangle=90,
            shadow=True,
            explode=(0.05, 0),  # Slightly explode the first slice
            wedgeprops={'edgecolor': 'white', 'linewidth': 2}
        )

This feature calculates the user's percentile position within the overall player rankings and displays it visually, providing context for their performance level.

Advanced Data Visualization

The module implements a variety of sophisticated visualization techniques to present the tennis data:

# Federation Performance Analysis
st.subheader("Federation Performance Analysis")
if len(df_filtered) > 0:
    try:
        # Calculate average stats by federation
        fed_stats = df_filtered.groupby('fedNickname').agg({
            'fedRank': 'mean',
            'points': 'mean',
            'atpPoints': 'mean',
            'playerId': 'count'
        }).rename(columns={'playerId': 'player_count'}).reset_index()

        # Only include federations with significant player count
        fed_stats = fed_stats[fed_stats['player_count'] >= 5]

        if not fed_stats.empty and len(fed_stats) > 1:  # Need at least 2 federations for comparison
            # Check for constant values that would break normalization
            has_variation = True
            for col in ['fedRank', 'points', 'atpPoints', 'player_count']:
                if fed_stats[col].nunique() <= 1:
                    has_variation = False
                    st.warning(f"Not enough variation in {col} across federations for proper normalization")

            if has_variation:
                # Normalize the data for the heatmap
                cols_to_norm = ['fedRank', 'points', 'atpPoints', 'player_count']

                # For ranking, lower is better so we invert it
                fed_stats['fedRank_normalized'] = 1 - ((fed_stats['fedRank'] - fed_stats['fedRank'].min()) /
                                                    (fed_stats['fedRank'].max() - fed_stats['fedRank'].min()))

                # For others, higher is better
                for col in cols_to_norm[1:]:
                    # Check for division by zero
                    if fed_stats[col].max() > fed_stats[col].min():
                        fed_stats[f'{col}_normalized'] = (fed_stats[col] - fed_stats[col].min()) / \
                                                    (fed_stats[col].max() - fed_stats[col].min())
                    else:
                        fed_stats[f'{col}_normalized'] = 0.5  # Neutral value if all values are the same

                # Display the normalized data in a heatmap
                fig, ax = plt.subplots(figsize=(12, len(fed_stats)/2 + 3))

                # Data for heatmap
                heatmap_data = fed_stats[['fedNickname', 'fedRank_normalized',
                                        'points_normalized', 'atpPoints_normalized',
                                        'player_count_normalized']]

This example shows the federation performance analysis, which aggregates statistics by tennis federation, normalizes the data, and presents it in a heatmap visualization.

Error Handling and Edge Cases

The application includes extensive error handling to manage edge cases and provide helpful feedback to users:

# Check if we have data
if df.empty:
    st.error("No data found in the database. Please run transform.py to populate the database with player data.")
    st.stop()

# Age vs Ranking Scatter Plot
with col2:
    st.subheader("Age vs. Ranking Correlation")
    if len(df_filtered) > 0:
        try:
            # Filter out any rows with NaN or infinite values
            plot_data = df_filtered.copy()
            plot_data = plot_data[np.isfinite(plot_data['age']) & np.isfinite(plot_data['fedRank']) & np.isfinite(plot_data['points'])]

            if len(plot_data) > 1:  # Need at least 2 points for meaningful plot and trend line
                # ... plotting code ...
            else:
                st.info("Not enough valid data points for age correlation analysis")
        except Exception as e:
            st.error(f"Error creating age correlation plot: {e}")
    else:
        st.info("No data available with current filters")

The code ensures that visualizations are only attempted when there is sufficient data available, and provides informative messages when conditions are not met.

Integration and Workflow

The three core components - load.py, transform.py, and app.py - work together in a cohesive workflow:

Initial Setup:

- The `load.py` script connects to the OETV API and fetches player ranking data
- Checkpoints are created to track the data processing state

Data Processing:

- `transform.py` initializes the SQLite database schema
- It processes the data fetched by `load.py` and stores it in the database
- The process is designed to handle data in batches for efficiency

User Interface:
- app.py creates a Streamlit dashboard that connects to the database
- It provides interactive filters and visualizations for data exploration
- Multiple analysis tabs offer different perspectives on the tennis data

Docker Infrastructure

The project uses Docker for containerization, as evidenced by the Dockerfile in the root directory. This containerization provides several advantages:

Environment Consistency: Ensures that the application runs in the same environment regardless of where it's deployed
Dependency Management: Simplifies handling of Python packages and other dependencies
Deployment Automation: Facilitates deployment to various hosting platforms
Scaling Capability: Enables easy scaling of the application if needed

The implementation uses environment variables to maintain flexibility in configuration:

# Get the base directory from environment or use a default
DATA_DIR = os.environ.get('DATA_DIR', os.path.dirname(os.path.abspath(__file__)))
db_path = os.path.join(DATA_DIR, "data.db")

This approach allows the application to adapt to different deployment environments while maintaining the same codebase.

Advanced Features and Technical Considerations

Robust Error Handling and Recovery

The project implements sophisticated error handling mechanisms to deal with API inconsistencies, network issues, and unexpected data formats:

try:
    # Try alternative parsing: find any list with player data
    for key, value in rankings_data.items():
        if isinstance(value, dict):
            for subkey, subvalue in value.items():
                if isinstance(subvalue, list) and len(subvalue) > 0 and isinstance(subvalue[0], dict) and "playerId" in subvalue[0]:
                    rankings_data = subvalue
                    break
except Exception as parse_error:
    consecutive_errors += 1

    # Save the problematic response for debugging
    # Get the base directory from environment or use a default
    DATA_DIR = os.environ.get('DATA_DIR', os.path.dirname(os.path.abspath(__file__)))
    debug_dir = os.path.join(DATA_DIR, "debug")
    os.makedirs(debug_dir, exist_ok=True)
    error_path = os.path.join(debug_dir, f"error_response_{start}.json")
    with open(error_path, "w") as f:
        f.write(r.text)

This code demonstrates the project's approach to handling unexpected API response formats, saving problematic responses for debugging while allowing the process to continue.

Adaptive Backoff Strategy

The project uses an adaptive backoff strategy to handle rate limiting and server load issues:

# Exponentially increase delay on consecutive errors
retry_delay = base_delay * (2 ** retries)
print(f"Backing off for {retry_delay:.2f} seconds before retry {retries+1}/{client['max_retries']}")
time.sleep(retry_delay)

This exponential backoff approach helps manage API request rates, reducing the risk of being blocked while maintaining data collection progress.

Data Visualization Robustness

The visualization code includes extensive checks to ensure that visualizations are only attempted when the data supports them:

# Check if there's enough variation in the data
if df_nonzero['atpPoints'].nunique() > 1 and df_nonzero['points'].nunique() > 1:
    # ... visualization code ...
else:
    st.info("Not enough variation in points data for correlation analysis")

These checks prevent errors from occurring when trying to create visualizations with insufficient or unsuitable data.

Conclusion

The OETV Tennis Analytics project represents a well-architected data pipeline and analytics solution specifically designed for tennis statistics. Through its three core components - load.py, transform.py, and app.py - it implements a complete ETL (Extract, Transform, Load) workflow with an interactive visualization layer.

The project demonstrates several best practices in data engineering and application development:

Modular Architecture: Clear separation of concerns between data loading, transformation, and presentation
Robust Error Handling: Comprehensive error management and recovery strategies
Adaptive Processing: Dynamic approaches to API interaction and data processing
Interactive Visualization: User-friendly interface with multiple analysis perspectives
Containerization: Deployment-ready packaging with Docker

The OETV Tennis Analytics project showcases how modern data engineering techniques can be applied to sports analytics, creating valuable insights from raw data while maintaining a robust and maintainable codebase.

Links

Read this article and more on fzeba.com.

Transformig a Fintech Investment Platform: From PHP to Java (Spring Boot)

Florian Zeba — Wed, 26 Feb 2025 23:00:00 +0000

Project Overview

Legacy code can be a major challenge for fintech platforms, particularly when dealing with aging PHP systems that are difficult to maintain and scale. To address these issues, we refactored an investment platform from PHP to Java, leveraging Spring Boot and SOAP-based APIs for improved reliability, performance, and maintainability.

In this article, we will walk through the design patterns used, the project structure, and key code examples—all in a way that even those without deep software development experience can understand.

Key Objectives

Why Refactor from PHP to Java?

PHP is widely used for web applications but can present challenges when scaling enterprise-grade financial platforms. The main reasons for refactoring include:

✔️ Improved Maintainability – Java's object-oriented nature makes managing large codebases easier.
✔️ Performance Optimization – Java is better suited for high-performance applications with large-scale transactions.
✔️ Better Security – Java provides strong security features, which are crucial in fintech applications.
✔️ Scalability – Spring Boot enables microservice architecture, allowing for better modularity and scaling.

To facilitate a smooth transition, we designed a new platform structure incorporating design patterns that promote clean, reusable, and scalable code. Also the API's are designed in Java using SOAP protocol.

Full Project Template with SOAP API Implementation (Java + Spring Boot)

This comprehensive manual will guide you through building the new investment platform using Java (Spring Boot) with SOAP API endpoints and the design patterns we discussed.

📂 Project Structure

investment-platform/
├── src/
│   └── main/
│       └── java/
│           └── com/
│               └── investment/
│                   ├── adapter/
│                   │   ├── BankAAdapter.java
│                   │   └── BankBAdapter.java
│                   ├── config/
│                   │   └── SOAPConfig.java
│                   ├── controller/
│                   │   └── InvestmentController.java
│                   ├── facade/
│                   │   └── BankServiceFacade.java
│                   ├── model/
│                   │   └── Client.java
│                   ├── repository/
│                   │   └── ClientRepository.java
│                   ├── service/
│                   │   ├── InvestmentService.java
│                   │   └── MarketDataService.java
│                   ├── soap/
│                   │   ├── BankAPI.java
│                   │   └── SOAPClient.java
│                   ├── strategy/
│                   │   ├── CompoundInterestStrategy.java
│                   │   └── SimpleInterestStrategy.java
│                   └── InvestmentPlatformApplication.java
└── pom.xml

The project structure explained:

The investment-platform project follows a structured layered architecture where each folder serves a specific purpose. This approach separates concerns, making the system more scalable, maintainable, and testable.

Here’s what each folder does and why it exists:

1️⃣ `adapter/` – Bank API Integration (Adapter Pattern)

📌 Purpose: Contains classes that allow the platform to interact with different banks' APIs by converting their responses into a standard format.

📌 Why is it needed?

Different banks have different API structures (some return XML, some JSON).
The Adapter Pattern standardizes these interactions so the main system doesn't need to change when adding a new bank.

📌 Example Files:

BankAAdapter.java – Handles API calls to Bank A.
BankBAdapter.java – Handles API calls to Bank B.

2️⃣ `config/` – Configuration Settings

📌 Purpose: Contains configuration-related files, such as SOAP API settings and database connection properties.

📌 Why is it needed?

Centralizes configuration, keeping it separate from the main business logic.
Allows for easy modifications without affecting core application code.

📌 Example Files:

SOAPConfig.java – Configures the SOAP API client.

3️⃣ `controller/` – REST API Endpoints

📌 Purpose: Defines REST API endpoints that external clients (web apps, mobile apps) interact with.

📌 Why is it needed?

Separates the API layer from the service logic.
Handles HTTP requests and forwards them to the appropriate service.

📌 Example Files:

InvestmentController.java – Handles requests related to investment calculations and bank balances.

4️⃣ `facade/` – Simplifying Complex Bank Interactions (Facade Pattern)

📌 Purpose: Acts as a single point of interaction for multiple banks.

📌 Why is it needed?

If a user needs account balances from multiple banks, instead of calling each bank separately, the Facade Pattern provides a unified method.
Simplifies interactions between different bank APIs.

📌 Example Files:

BankServiceFacade.java – Calls BankAAdapter and BankBAdapter and returns consolidated balances.

5️⃣ `model/` – Data Objects (Entities)

📌 Purpose: Defines Java objects that represent database entities.

📌 Why is it needed?

Keeps the data structure centralized.
Helps in mapping database tables to Java objects.

📌 Example Files:

Client.java – Represents an investor with attributes like id, name, and investmentType.

6️⃣ `repository/` – Database Access (Repository Pattern)

📌 Purpose: Defines database operations using Spring Data JPA.

📌 Why is it needed?

Encapsulates database queries so that other parts of the app don’t need to worry about database logic.
Uses the Repository Pattern for easy database access.

📌 Example Files:

ClientRepository.java – Provides methods to fetch Client data from the database.

7️⃣ `service/` – Business Logic

📌 Purpose: Contains the core logic of the investment platform, such as investment calculations and bank interactions.

📌 Why is it needed?

Separates business logic from controllers (API layer).
Promotes reusability—controllers and other services can call these methods.

📌 Example Files:

InvestmentService.java – Handles investment calculations and retrieves bank balances.
MarketDataService.java – Tracks market changes (for future real-time updates).

8️⃣ `soap/` – SOAP API Clients

📌 Purpose: Handles SOAP-based API communication with external bank services.

📌 Why is it needed?

SOAP APIs require special handling (e.g., XML parsing).
The SOAPClient class ensures efficient API calls using the Singleton Pattern.

📌 Example Files:

SOAPClient.java – A singleton class that manages SOAP API requests.
BankAPI.java – An interface defining methods for interacting with different banks.

9️⃣ `strategy/` – Investment Calculation Strategies (Strategy Pattern)

📌 Purpose: Contains different investment calculation methods, such as compound interest and simple interest.

📌 Why is it needed?

Users may want different investment models.
The Strategy Pattern allows switching between different calculation strategies dynamically.

📌 Example Files:

InvestmentStrategy.java – Defines the contract for all strategies.
CompoundInterestStrategy.java – Implements compound interest calculations.
SimpleInterestStrategy.java – Implements simple interest calculations.

🔟 `InvestmentPlatformApplication.java` – The Main Entry Point

📌 Purpose: This is the main class where the Spring Boot application starts.

📌 Why is it needed?

The @SpringBootApplication annotation automatically configures the app.
It runs the entire platform as a Spring Boot service.

📌 Example File:

InvestmentPlatformApplication.java – Contains the main() method.

By structuring the platform this way, we achieve:

✔ Separation of Concerns – Each folder has a clear responsibility.

✔ Scalability – New features can be added without modifying the entire system.

✔ Maintainability – Developers can quickly understand and modify the project.

Key Takeaways from the Structure

Separation of Concerns – Each feature is isolated in its own folder.
Extensibility – The platform supports multiple banks using an Adapter Pattern.
Scalability – Future services can be added without disrupting existing functionality.

Let's see how the theory would be used in a real-world scenario.

1. Initialize a Spring Boot Project

Create the project using Spring Initializr:

Dependencies: Spring Web, Spring Boot Starter SOAP, Spring Boot Starter Data JPA, Spring Boot Starter Web Services, Lombok, MySQL Driver This setup includes:
Spring Boot Web – To build REST APIs.
Spring Boot Web Services – To handle SOAP-based APIs.
Spring Data JPA – To manage database transactions with MySQL.

pom.xml

<project>
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.investment</groupId>
    <artifactId>investment-platform</artifactId>
    <version>1.0.0</version>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web-services</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

⚙️ 2. Configuration

application.properties

server.port=8080

spring.datasource.url=jdbc:mysql://localhost:3306/investment_db
spring.datasource.username=root
spring.datasource.password=secret

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect

SOAPConfig.java

package com.investment.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.oxm.jaxb.Jaxb2Marshaller;

@Configuration
public class SOAPConfig {

    @Bean
    public Jaxb2Marshaller marshaller() {
        Jaxb2Marshaller marshaller = new Jaxb2Marshaller();
        marshaller.setContextPath("com.investment.soap");
        return marshaller;
    }
}

🟡 3. SOAP Client with Singleton Pattern

SOAPClient.java

package com.investment.soap;

import javax.xml.soap.*;

public class SOAPClient {
    private static SOAPClient instance;
    private SOAPConnection connection;

    private SOAPClient() throws SOAPException {
        SOAPConnectionFactory factory = SOAPConnectionFactory.newInstance();
        connection = factory.createConnection();
    }

    public static SOAPClient getInstance() throws SOAPException {
        if (instance == null) {
            synchronized (SOAPClient.class) {
                if (instance == null) {
                    instance = new SOAPClient();
                }
            }
        }
        return instance;
    }

    public SOAPMessage sendRequest(String endpointUrl, SOAPMessage request) throws SOAPException {
        return connection.call(request, endpointUrl);
    }
}

Why Singleton?

Ensures only one SOAP connection exists, reducing memory usage.
Prevents duplicate API requests, improving efficiency.

🟠 4. Adapter Pattern for Bank APIs

SOAP Interface for Banks

package com.investment.soap;

public interface BankAPI {
    String getAccountBalance(String accountId);
    String transferFunds(String fromAccount, String toAccount, double amount);
}

Bank A Adapter

package com.investment.adapter;

import com.investment.soap.BankAPI;

public class BankAAdapter implements BankAPI {
    @Override
    public String getAccountBalance(String accountId) {
        // Simulate SOAP request to Bank A
        return "<balance>10000.00</balance>";
    }

    @Override
    public String transferFunds(String fromAccount, String toAccount, double amount) {
        return "BankA Transfer Successful";
    }
}

Why Adapter Pattern?

Allows the system to integrate with multiple bank APIs without modifying the core code.
Ensures smooth switching between different banks.

🟡 5. Facade Pattern for Multiple Banks

BankServiceFacade.java

package com.investment.facade;

import com.investment.soap.BankAPI;

public class BankServiceFacade {
    private final BankAPI bankAAdapter;
    private final BankAPI bankBAdapter;

    public BankServiceFacade(BankAPI bankAAdapter, BankAPI bankBAdapter) {
        this.bankAAdapter = bankAAdapter;
        this.bankBAdapter = bankBAdapter;
    }

    public String getConsolidatedBalance(String accountId) {
        String balanceA = bankAAdapter.getAccountBalance(accountId);
        String balanceB = bankBAdapter.getAccountBalance(accountId);
        return "BankA: " + balanceA + ", BankB: " + balanceB;
    }
}

Why Facade Pattern?

Reduces complexity by exposing a single method for fetching balances.
Improves code readability.

🟢 6. Strategy Pattern for Investment Calculations

InvestmentStrategy.java

package com.investment.strategy;

public interface InvestmentStrategy {
    double calculateReturn(double principal, double rate, int years);
}

CompoundInterestStrategy.java

package com.investment.strategy;

public class CompoundInterestStrategy implements InvestmentStrategy {
    @Override
    public double calculateReturn(double principal, double rate, int years) {
        return principal * Math.pow((1 + rate / 100), years);
    }
}

SimpleInterestStrategy.java

package com.investment.strategy;

public class SimpleInterestStrategy implements InvestmentStrategy {
    @Override
    public double calculateReturn(double principal, double rate, int years) {
        return principal + (principal * rate * years / 100);
    }
}

Why Strategy Pattern?

Users can choose between compound interest or simple interest dynamically.
Simplifies adding new investment strategies in the future.

🟤 7. Observer Pattern for Real-Time Market Updates

MarketDataService.java

package com.investment.service;

import java.util.ArrayList;
import java.util.List;

interface Observer {
    void update(String marketUpdate);
}

interface MarketData {
    void addObserver(Observer observer);
    void removeObserver(Observer observer);
    void notifyObservers(String update);
}

public class MarketDataService implements MarketData {
    private final List<Observer> observers = new ArrayList<>();

    @Override
    public void addObserver(Observer observer) {
        observers.add(observer);
    }

    @Override
    public void removeObserver(Observer observer) {
        observers.remove(observer);
    }

    @Override
    public void notifyObservers(String update) {
        for (Observer observer : observers) {
            observer.update(update);
        }
    }
}

🟠 8. Repository Pattern for Database Operations

Client Entity

package com.investment.model;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
@Data
public class Client {
    @Id
    private String id;
    private String name;
    private String investmentType;
}

Client Repository Interface

package com.investment.repository;

import com.investment.model.Client;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ClientRepository extends JpaRepository<Client, String> {
}

🟢 9. Service Layer

InvestmentService.java

package com.investment.service;

import com.investment.facade.BankServiceFacade;
import com.investment.strategy.InvestmentStrategy;
import org.springframework.stereotype.Service;

@Service
public class InvestmentService {
    private final BankServiceFacade bankServiceFacade;
    private InvestmentStrategy strategy;

    public InvestmentService(BankServiceFacade bankServiceFacade) {
        this.bankServiceFacade = bankServiceFacade;
    }

    public void setStrategy(InvestmentStrategy strategy) {
        this.strategy = strategy;
    }

    public double calculateReturn(double principal, double rate, int years) {
        return strategy.calculateReturn(principal, rate, years);
    }

    public String getAccountBalances(String accountId) {
        return bankServiceFacade.getConsolidatedBalance(accountId);
    }
}

🟡 10. REST Controller

InvestmentController.java

package com.investment.controller;

import com.investment.service.InvestmentService;
import com.investment.strategy.CompoundInterestStrategy;
import com.investment.strategy.SimpleInterestStrategy;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/api/investment")
public class InvestmentController {
    private final InvestmentService investmentService;

    public InvestmentController(InvestmentService investmentService) {
        this.investmentService = investmentService;
    }

    @GetMapping("/returns")
    public double calculateReturns(
            @RequestParam String type,
            @RequestParam double principal,
            @RequestParam double rate,
            @RequestParam int years) {

        if ("compound".equalsIgnoreCase(type)) {
            investmentService.setStrategy(new CompoundInterestStrategy());
        } else {
            investmentService.setStrategy(new SimpleInterestStrategy());
        }

        return investmentService.calculateReturn(principal, rate, years);
    }

    @GetMapping("/balances")
    public String getBalances(@RequestParam String accountId) {
        return investmentService.getAccountBalances(accountId);
    }
}

Running the Platform

Step 1: Start MySQL

docker run --name mysql-investment -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=investment_db -p 3306:3306 -d mysql:5.7

Step 2: Build and Run Spring Boot Application

./mvnw clean install
./mvnw spring-boot:run

Step 3: Test Endpoints

Calculate Returns:

http://localhost:8080/api/investment/returns?type=compound&principal=10000&rate=5&years=10
Get Account Balances:

http://localhost:8080/api/investment/balances?accountId=12345

Conclusion

🟡 Scalable Architecture: Layered design with separation of concerns.
🟠 Flexible Patterns: Easy to extend or change logic.
🟢 Reusable Components: Adapters for multiple bank integrations.
🟤 Efficient Design: Singleton for shared resources.
🟡 Real-Time Updates: Observer pattern for market changes.

Read this article and more on fzeba.com.

#28.3 Part 3/3 Basics of Software Architecture and Design Patterns

Florian Zeba — Tue, 25 Feb 2025 23:00:00 +0000

Intro 3

The last part of the design pattern series is all about examples and practical implementations. We will dive into the different types and principles of design patterns and how they can be used in your projects.

Design Patterns in Software Development

1. Creational Patterns (Object Creation Mechanisms)

Singleton: Ensures only one instance of a class is created and provides a global point of access to it.
Factory Method: Creates objects without specifying the exact class to create.
Abstract Factory: Provides an interface for creating families of related or dependent objects without specifying their concrete classes.
Builder: Separates the construction of a complex object from its representation.
Prototype: Creates new objects by copying an existing object, known as the prototype.

2. Structural Patterns (Composition of Classes or Objects)

Adapter (Wrapper): Allows incompatible interfaces to work together.
Bridge: Separates an object’s abstraction from its implementation.
Composite: Composes objects into tree structures to represent part-whole hierarchies.
Decorator: Adds new functionality to an object dynamically.
Facade: Provides a simplified interface to a complex system.
Flyweight: Reduces memory usage by sharing common parts of state between multiple objects.
Proxy: Provides a placeholder for another object to control access to it.

3. Behavioral Patterns (Communication Between Objects)

Chain of Responsibility: Passes requests along a chain of handlers.
Command: Encapsulates a request as an object, allowing for parameterization of requests.
Interpreter: Defines a grammar for interpreting sentences in a language.
Iterator: Provides a way to access elements of a collection sequentially.
Mediator: Reduces coupling between classes by centralizing communication.
Memento: Captures and restores an object’s internal state.
Observer (Publish-Subscribe): Defines a dependency between objects so that when one changes state, all dependents are notified.
State: Allows an object to alter its behavior when its internal state changes.
Strategy: Defines a family of algorithms, encapsulates each one, and makes them interchangeable.
Template Method: Defines the skeleton of an algorithm, deferring steps to subclasses.
Visitor: Adds new operations to a class hierarchy without modifying the classes.

4. Concurrency Patterns (Managing Multi-threaded Applications)

Active Object: Decouples method execution from method invocation.
Balking: Prevents an operation from being executed if the object is in an improper state.
Double-Checked Locking: Reduces overhead when initializing resources in a multithreaded environment.
Guarded Suspension: Manages operations that require preconditions to be met.
Monitor Object: Synchronizes access to an object across multiple threads.
Read-Write Lock: Allows multiple readers or one writer at a time.
Thread Pool: Manages a pool of worker threads to efficiently handle multiple tasks.

5. Architectural Patterns (High-Level Structures of Software Systems)

Layered Architecture (n-tier): Organizes the system into layers with specific responsibilities.
Client-Server: Separates the client and server roles.
Master-Slave: Separates distributed processes into masters and slaves.
Pipe and Filter: Breaks down processes into a sequence of processing stages.
Model-View-Controller (MVC): Separates concerns into Model, View, and Controller.
Model-View-ViewModel (MVVM): Separates logic and UI, common in frameworks like WPF.
Microservices Architecture: Structures an application as a collection of small, independent services.
Event-Driven Architecture: Uses events to trigger communication between decoupled services.
Space-Based Architecture: Reduces the load on databases by using in-memory data grids.
Service-Oriented Architecture (SOA): Builds systems from reusable services.

6. Cloud-Native and Distributed Systems Patterns

Circuit Breaker: Prevents repeated execution of failed requests.
API Gateway: Acts as a single entry point for all microservices.
Service Mesh: Manages service-to-service communication.
Sidecar Pattern: Attaches additional functionality to a service without modifying it.
Saga Pattern: Manages distributed transactions using compensating transactions.
CQRS (Command Query Responsibility Segregation): Separates commands from queries.
Event Sourcing: Stores the state changes as a sequence of events.

7. Enterprise Integration Patterns

Aggregator: Combines multiple messages into one.
Message Broker: Routes messages between services.
Message Queue: Manages the delivery of messages between services.
Content-Based Router: Routes messages based on their content.
Publish-Subscribe Channel: Sends messages to multiple subscribers.

Now let's dive into some practical examples of these design patterns in Python!

1. Creational Patterns

Singleton Pattern

Ensures a class has only one instance and provides a global point of access.

Example in Python:

class Singleton:
    _instance = None

    def __new__(cls):
        if cls._instance is None:
            cls._instance = super().__new__(cls)
        return cls._instance

# Usage
s1 = Singleton()
s2 = Singleton()
print(s1 is s2)  # True

Factory Method

Defines an interface for creating objects but lets subclasses alter the type of objects that will be created.

Example:

from abc import ABC, abstractmethod

class Product(ABC):
    @abstractmethod
    def operation(self):
        pass

class ConcreteProductA(Product):
    def operation(self):
        return "Product A"

class ConcreteProductB(Product):
    def operation(self):
        return "Product B"

class Factory:
    @staticmethod
    def create_product(type_):
        if type_ == "A":
            return ConcreteProductA()
        elif type_ == "B":
            return ConcreteProductB()
        raise ValueError("Unknown product type")

# Usage
product = Factory.create_product("A")
print(product.operation())  # "Product A"

Abstract Factory

Provides an interface for creating families of related or dependent objects.

Example:

class AbstractFactory(ABC):
    @abstractmethod
    def create_product(self):
        pass

class ConcreteFactoryA(AbstractFactory):
    def create_product(self):
        return ConcreteProductA()

class ConcreteFactoryB(AbstractFactory):
    def create_product(self):
        return ConcreteProductB()

# Usage
factory = ConcreteFactoryA()
product = factory.create_product()
print(product.operation())  # "Product A"

Builder Pattern

Separates object construction from its representation.

Example:

class Product:
    def __init__(self):
        self.parts = []

    def add(self, part):
        self.parts.append(part)

    def show(self):
        print(", ".join(self.parts))

class Builder:
    def build_part(self):
        pass

class ConcreteBuilder(Builder):
    def __init__(self):
        self.product = Product()

    def build_part(self):
        self.product.add("Part A")
        self.product.add("Part B")

    def get_result(self):
        return self.product

# Usage
builder = ConcreteBuilder()
builder.build_part()
product = builder.get_result()
product.show()  # "Part A, Part B"

Prototype Pattern

Creates objects by cloning an existing object.

Example:

import copy

class Prototype:
    def clone(self):
        return copy.deepcopy(self)

class ConcretePrototype(Prototype):
    def __init__(self, value):
        self.value = value

# Usage
prototype = ConcretePrototype([1, 2, 3])
clone = prototype.clone()
print(clone.value)  # [1, 2, 3]

2. Structural Patterns

Adapter Pattern

Allows incompatible interfaces to work together.

Example:

class OldSystem:
    def specific_request(self):
        return "Old system output"

class Adapter:
    def __init__(self, old_system):
        self.old_system = old_system

    def request(self):
        return self.old_system.specific_request()

# Usage
adapter = Adapter(OldSystem())
print(adapter.request())  # "Old system output"

Bridge Pattern

Separates abstraction from implementation.

Example:

class Implementation:
    def operation(self):
        pass

class ConcreteImplementationA(Implementation):
    def operation(self):
        return "ConcreteImplementationA"

class Abstraction:
    def __init__(self, implementation):
        self.implementation = implementation

    def operation(self):
        return self.implementation.operation()

# Usage
implementation = ConcreteImplementationA()
abstraction = Abstraction(implementation)
print(abstraction.operation())  # "ConcreteImplementationA"

Decorator Pattern

Dynamically adds behavior to objects.

Example:

class Component:
    def operation(self):
        pass

class ConcreteComponent(Component):
    def operation(self):
        return "ConcreteComponent"

class Decorator(Component):
    def __init__(self, component):
        self.component = component

    def operation(self):
        return f"Decorator({self.component.operation()})"

# Usage
component = ConcreteComponent()
decorated = Decorator(component)
print(decorated.operation())  # "Decorator(ConcreteComponent)"

Facade Pattern

Provides a simplified interface to a complex subsystem.

Example:

class SubsystemA:
    def operation(self):
        return "SubsystemA"

class SubsystemB:
    def operation(self):
        return "SubsystemB"

class Facade:
    def __init__(self):
        self.subsystemA = SubsystemA()
        self.subsystemB = SubsystemB()

    def operation(self):
        return f"{self.subsystemA.operation()} + {self.subsystemB.operation()}"

# Usage
facade = Facade()
print(facade.operation())  # "SubsystemA + SubsystemB"

3. Behavioral Patterns

Observer Pattern

Allows objects to notify others of state changes.

Example:

class Subject:
    def __init__(self):
        self._observers = []

    def attach(self, observer):
        self._observers.append(observer)

    def notify(self, message):
        for observer in self._observers:
            observer.update(message)

class Observer:
    def update(self, message):
        print(f"Observer received: {message}")

# Usage
subject = Subject()
observer = Observer()
subject.attach(observer)
subject.notify("Hello, World!")  # "Observer received: Hello, World!"

Command Pattern

Encapsulates a request as an object.

Example:

class Command:
    def execute(self):
        pass

class ConcreteCommand(Command):
    def __init__(self, receiver):
        self.receiver = receiver

    def execute(self):
        self.receiver.action()

class Receiver:
    def action(self):
        print("Action executed")

# Usage
receiver = Receiver()
command = ConcreteCommand(receiver)
command.execute()  # "Action executed"

State Pattern

Allows an object to change its behavior when its internal state changes.

Example:

class State:
    def handle(self):
        pass

class ConcreteStateA(State):
    def handle(self):
        return "State A"

class ConcreteStateB(State):
    def handle(self):
        return "State B"

class Context:
    def __init__(self, state):
        self.state = state

    def request(self):
        return self.state.handle()

# Usage
context = Context(ConcreteStateA())
print(context.request())  # "State A"
context.state = ConcreteStateB()
print(context.request())  # "State B"

3. Behavioral Patterns (continued)

Chain of Responsibility Pattern

Passes requests along a chain of handlers.

Example:

class Handler:
    def __init__(self, successor=None):
        self.successor = successor

    def handle_request(self, request):
        if self.successor:
            self.successor.handle_request(request)

class ConcreteHandlerA(Handler):
    def handle_request(self, request):
        if request == "A":
            print("Handled by HandlerA")
        else:
            super().handle_request(request)

class ConcreteHandlerB(Handler):
    def handle_request(self, request):
        if request == "B":
            print("Handled by HandlerB")
        else:
            super().handle_request(request)

# Usage
handler_chain = ConcreteHandlerA(ConcreteHandlerB())
handler_chain.handle_request("B")  # "Handled by HandlerB"

Mediator Pattern

Reduces coupling by centralizing communication between objects.

Example:

class Mediator:
    def notify(self, sender, event):
        pass

class ConcreteMediator(Mediator):
    def __init__(self, component1, component2):
        self.component1 = component1
        self.component2 = component2
        self.component1.mediator = self
        self.component2.mediator = self

    def notify(self, sender, event):
        if event == "A":
            self.component2.react("A")
        elif event == "B":
            self.component1.react("B")

class Component:
    def __init__(self):
        self.mediator = None

class Component1(Component):
    def do_a(self):
        print("Component1 does A")
        self.mediator.notify(self, "A")

    def react(self, event):
        print(f"Component1 reacts to {event}")

class Component2(Component):
    def do_b(self):
        print("Component2 does B")
        self.mediator.notify(self, "B")

    def react(self, event):
        print(f"Component2 reacts to {event}")

# Usage
component1 = Component1()
component2 = Component2()
mediator = ConcreteMediator(component1, component2)

component1.do_a()
# Component1 does A
# Component2 reacts to A

component2.do_b()
# Component2 does B
# Component1 reacts to B

Memento Pattern

Captures and restores an object’s state.

Example:

class Memento:
    def __init__(self, state):
        self.state = state

class Originator:
    def __init__(self, state):
        self.state = state

    def create_memento(self):
        return Memento(self.state)

    def restore(self, memento):
        self.state = memento.state

class Caretaker:
    def __init__(self):
        self.mementos = []

    def add_memento(self, memento):
        self.mementos.append(memento)

    def get_memento(self, index):
        return self.mementos[index]

# Usage
originator = Originator("State1")
caretaker = Caretaker()

caretaker.add_memento(originator.create_memento())

originator.state = "State2"
originator.restore(caretaker.get_memento(0))

print(originator.state)  # "State1"

Strategy Pattern

Encapsulates algorithms into interchangeable modules.

Example:

class Strategy:
    def execute(self, a, b):
        pass

class AddStrategy(Strategy):
    def execute(self, a, b):
        return a + b

class SubtractStrategy(Strategy):
    def execute(self, a, b):
        return a - b

class Context:
    def __init__(self, strategy):
        self.strategy = strategy

    def execute_strategy(self, a, b):
        return self.strategy.execute(a, b)

# Usage
context = Context(AddStrategy())
print(context.execute_strategy(5, 3))  # 8

context.strategy = SubtractStrategy()
print(context.execute_strategy(5, 3))  # 2

Template Method Pattern

Defines the skeleton of an algorithm in a base class but lets subclasses override specific steps.

Example:

from abc import ABC, abstractmethod

class AbstractClass(ABC):
    def template_method(self):
        self.step_one()
        self.step_two()
        self.hook()

    @abstractmethod
    def step_one(self):
        pass

    @abstractmethod
    def step_two(self):
        pass

    def hook(self):
        pass

class ConcreteClass(AbstractClass):
    def step_one(self):
        print("Step 1 implemented by ConcreteClass")

    def step_two(self):
        print("Step 2 implemented by ConcreteClass")

    def hook(self):
        print("Optional hook executed")

# Usage
obj = ConcreteClass()
obj.template_method()
# Step 1 implemented by ConcreteClass
# Step 2 implemented by ConcreteClass
# Optional hook executed

Visitor Pattern

Separates operations from the object structure, allowing new operations without modifying objects.

Example:

class Visitor:
    def visit_concrete_element_a(self, element):
        pass

    def visit_concrete_element_b(self, element):
        pass

class ConcreteVisitor(Visitor):
    def visit_concrete_element_a(self, element):
        print(f"Visiting {element}")

    def visit_concrete_element_b(self, element):
        print(f"Visiting {element}")

class Element:
    def accept(self, visitor):
        pass

class ConcreteElementA(Element):
    def accept(self, visitor):
        visitor.visit_concrete_element_a(self)

class ConcreteElementB(Element):
    def accept(self, visitor):
        visitor.visit_concrete_element_b(self)

# Usage
visitor = ConcreteVisitor()
elementA = ConcreteElementA()
elementB = ConcreteElementB()

elementA.accept(visitor)  # Visiting <__main__.ConcreteElementA object>
elementB.accept(visitor)  # Visiting <__main__.ConcreteElementB object>

4. Concurrency Patterns

Thread Pool Pattern

Manages a pool of threads to execute tasks concurrently.

Example:

from concurrent.futures import ThreadPoolExecutor
import time

def worker_function(task_id):
    print(f"Task {task_id} is starting")
    time.sleep(1)
    print(f"Task {task_id} is completed")

# Usage
with ThreadPoolExecutor(max_workers=3) as executor:
    for i in range(5):
        executor.submit(worker_function, i)

Read-Write Lock Pattern

Allows concurrent reads but exclusive writes.

Example:

import threading

class ReadWriteLock:
    def __init__(self):
        self.readers = 0
        self.lock = threading.Lock()
        self.read_lock = threading.Lock()

    def acquire_read(self):
        with self.read_lock:
            self.readers += 1
            if self.readers == 1:
                self.lock.acquire()

    def release_read(self):
        with self.read_lock:
            self.readers -= 1
            if self.readers == 0:
                self.lock.release()

    def acquire_write(self):
        self.lock.acquire()

    def release_write(self):
        self.lock.release()

# Usage
lock = ReadWriteLock()

def reader():
    lock.acquire_read()
    print(f"{threading.current_thread().name} is reading")
    lock.release_read()

def writer():
    lock.acquire_write()
    print(f"{threading.current_thread().name} is writing")
    lock.release_write()

threading.Thread(target=reader).start()
threading.Thread(target=writer).start()

Double-Checked Locking Pattern

Ensures that only one instance of a resource is created, with minimal synchronization overhead.

Example:

import threading

class Singleton:
    _instance = None
    _lock = threading.Lock()

    @classmethod
    def get_instance(cls):
        if cls._instance is None:
            with cls._lock:
                if cls._instance is None:
                    cls._instance = cls()
        return cls._instance

# Usage
def test_singleton():
    singleton = Singleton.get_instance()
    print(f"Instance: {id(singleton)}")

thread1 = threading.Thread(target=test_singleton)
thread2 = threading.Thread(target=test_singleton)

thread1.start()
thread2.start()

thread1.join()
thread2.join()

5. Architectural Patterns

Layered Architecture Pattern

Separates concerns into different layers (e.g., Presentation, Business Logic, Data).

Example:

# Data Layer
class DataLayer:
    def get_data(self):
        return "Data from database"

# Business Logic Layer
class BusinessLogicLayer:
    def __init__(self, data_layer):
        self.data_layer = data_layer

    def process_data(self):
        data = self.data_layer.get_data()
        return data.upper()

# Presentation Layer
class PresentationLayer:
    def __init__(self, business_logic_layer):
        self.business_logic_layer = business_logic_layer

    def display(self):
        result = self.business_logic_layer.process_data()
        print(f"Presentation: {result}")

# Usage
data_layer = DataLayer()
business_layer = BusinessLogicLayer(data_layer)
presentation_layer = PresentationLayer(business_layer)

presentation_layer.display()
# Presentation: DATA FROM DATABASE

Model-View-Controller (MVC) Pattern

Separates application logic (Model), user interface (View), and input handling (Controller).

Example:

class Model:
    def __init__(self):
        self.data = "Hello MVC"

class View:
    def show(self, data):
        print(f"View displaying: {data}")

class Controller:
    def __init__(self, model, view):
        self.model = model
        self.view = view

    def update_view(self):
        data = self.model.data
        self.view.show(data)

# Usage
model = Model()
view = View()
controller = Controller(model, view)

controller.update_view()
# View displaying: Hello MVC

Microservices Pattern

Separates functionality into small, independently deployable services.

Example:

from flask import Flask

app = Flask(__name__)

@app.route('/service1')
def service1():
    return "Service 1: User Management"

@app.route('/service2')
def service2():
    return "Service 2: Order Processing"

if __name__ == '__main__':
    app.run(port=5000)

6. Cloud-Native and Distributed Systems Patterns

Circuit Breaker Pattern

Prevents a system from trying to execute an operation that is likely to fail.

Example:

import time
import random

class CircuitBreaker:
    def __init__(self, failure_threshold=3, recovery_timeout=5):
        self.failure_threshold = failure_threshold
        self.recovery_timeout = recovery_timeout
        self.failures = 0
        self.last_failure_time = None
        self.state = "CLOSED"

    def call(self, func, *args, **kwargs):
        if self.state == "OPEN":
            if time.time() - self.last_failure_time >= self.recovery_timeout:
                self.state = "HALF-OPEN"
            else:
                raise Exception("Circuit is open, call blocked")

        try:
            result = func(*args, **kwargs)
            self.failures = 0
            if self.state == "HALF-OPEN":
                self.state = "CLOSED"
            return result
        except Exception as e:
            self.failures += 1
            if self.failures >= self.failure_threshold:
                self.state = "OPEN"
                self.last_failure_time = time.time()
            raise Exception("Request failed, circuit breaker activated")

# Usage
def unstable_service():
    if random.random() < 0.7:
        raise Exception("Service failed")
    return "Success"

breaker = CircuitBreaker()

for i in range(10):
    try:
        print(breaker.call(unstable_service))
    except Exception as e:
        print(e)
    time.sleep(1)

API Gateway Pattern

Acts as a single entry point for a set of microservices.

Example:

from flask import Flask, jsonify, request
import requests

app = Flask(__name__)

MICROSERVICE_1_URL = "http://localhost:5001"
MICROSERVICE_2_URL = "http://localhost:5002"

@app.route('/user/<user_id>')
def get_user_info(user_id):
    response = requests.get(f"{MICROSERVICE_1_URL}/users/{user_id}")
    return response.json()

@app.route('/orders/<user_id>')
def get_user_orders(user_id):
    response = requests.get(f"{MICROSERVICE_2_URL}/orders/{user_id}")
    return response.json()

# Run with: python api_gateway.py
if __name__ == '__main__':
    app.run(port=5000)

Service Mesh Pattern

Manages service-to-service communication within a microservices architecture.

Example (Istio/Kubernetes Conceptual Example):

# istio-gateway.yaml
apiVersion: networking.istio.io/v1alpha3
kind: Gateway
metadata:
  name: service-mesh-gateway
spec:
  selector:
    istio: ingressgateway
  servers:
    - port:
        number: 80
        name: http
        protocol: HTTP
      hosts:
        - '*'

Sidecar Pattern

Attaches additional functionality to a service without altering it (e.g., logging, monitoring).

Example (Conceptual Docker Compose):

version: '3'
services:
  app:
    image: my-app
    ports:
      - '5000:5000'
  sidecar:
    image: logging-sidecar
    volumes:
      - /var/log/app:/app/logs

Saga Pattern

Manages distributed transactions using compensating actions.

Example (Compensating Transactions):

class Saga:
    def __init__(self):
        self.transactions = []
        self.compensations = []

    def add_step(self, transaction, compensation):
        self.transactions.append(transaction)
        self.compensations.append(compensation)

    def execute(self):
        for i, transaction in enumerate(self.transactions):
            try:
                transaction()
            except Exception:
                self.compensate(i)
                break

    def compensate(self, index):
        for compensation in reversed(self.compensations[:index]):
            compensation()

# Usage
def reserve_inventory():
    print("Inventory reserved")

def cancel_inventory():
    print("Inventory reservation cancelled")

def charge_payment():
    print("Payment charged")
    raise Exception("Payment failed")

def refund_payment():
    print("Payment refunded")

saga = Saga()
saga.add_step(reserve_inventory, cancel_inventory)
saga.add_step(charge_payment, refund_payment)
saga.execute()

# Output:
# Inventory reserved
# Payment charged
# Payment refunded
# Inventory reservation cancelled

CQRS (Command Query Responsibility Segregation) Pattern

Separates read and write operations for better scalability.

Example:

class CommandHandler:
    def __init__(self):
        self.data_store = {}

    def update(self, user_id, user_data):
        self.data_store[user_id] = user_data
        print(f"Updated user {user_id}: {user_data}")

class QueryHandler:
    def __init__(self, command_handler):
        self.data_store = command_handler.data_store

    def get_user(self, user_id):
        return self.data_store.get(user_id, "User not found")

# Usage
command_handler = CommandHandler()
query_handler = QueryHandler(command_handler)

command_handler.update(1, {"name": "Alice"})
print(query_handler.get_user(1))  # {"name": "Alice"}

Event Sourcing Pattern

Stores state changes as a sequence of events.

Example:

class EventStore:
    def __init__(self):
        self.events = []

    def add_event(self, event):
        self.events.append(event)

    def get_events(self):
        return self.events

class Account:
    def __init__(self, event_store):
        self.event_store = event_store
        self.balance = 0

    def deposit(self, amount):
        self.event_store.add_event(f"Deposited {amount}")
        self.balance += amount

    def withdraw(self, amount):
        if self.balance >= amount:
            self.event_store.add_event(f"Withdrew {amount}")
            self.balance -= amount
        else:
            print("Insufficient funds")

    def replay_events(self):
        for event in self.event_store.get_events():
            print(event)

# Usage
event_store = EventStore()
account = Account(event_store)

account.deposit(100)
account.withdraw(50)
account.replay_events()

# Output:
# Deposited 100
# Withdrew 50

7. Enterprise Integration Patterns (EIP)

Aggregator Pattern

Collects messages and combines them into a single message.

Example:

class Aggregator:
    def __init__(self):
        self.messages = []

    def add_message(self, message):
        self.messages.append(message)

    def aggregate(self):
        return " | ".join(self.messages)

# Usage
aggregator = Aggregator()
aggregator.add_message("Message 1")
aggregator.add_message("Message 2")
print(aggregator.aggregate())  # "Message 1 | Message 2"

Message Broker Pattern

Routes messages between services.

Example (Using RabbitMQ):

import pika

# Publisher
connection = pika.BlockingConnection(pika.ConnectionParameters('localhost'))
channel = connection.channel()
channel.queue_declare(queue='hello')

channel.basic_publish(exchange='', routing_key='hello', body='Hello RabbitMQ!')
print("Sent 'Hello RabbitMQ!'")
connection.close()

# Consumer
def callback(ch, method, properties, body):
    print(f"Received {body}")

connection = pika.BlockingConnection(pika.ConnectionParameters('localhost'))
channel = connection.channel()
channel.queue_declare(queue='hello')

channel.basic_consume(queue='hello', on_message_callback=callback, auto_ack=True)
print('Waiting for messages...')
channel.start_consuming()

Content-Based Router Pattern

Routes messages based on content.

Example:

class ContentBasedRouter:
    def route(self, message):
        if "order" in message:
            self.handle_order(message)
        elif "payment" in message:
            self.handle_payment(message)
        else:
            self.handle_default(message)

    def handle_order(self, message):
        print(f"Order Handler: {message}")

    def handle_payment(self, message):
        print(f"Payment Handler: {message}")

    def handle_default(self, message):
        print(f"Default Handler: {message}")

# Usage
router = ContentBasedRouter()
router.route("This is an order request")
router.route("This is a payment notification")

# Output:
# Order Handler: This is an order request
# Payment Handler: This is a payment notification

Publish-Subscribe Channel Pattern

Broadcasts messages to multiple subscribers.

Example:

from collections import defaultdict

class PubSub:
    def __init__(self):
        self.subscribers = defaultdict(list)

    def subscribe(self, event_type, handler):
        self.subscribers[event_type].append(handler)

    def publish(self, event_type, message):
        for handler in self.subscribers[event_type]:
            handler(message)

# Usage
def log_event(message):
    print(f"Logging: {message}")

def alert_event(message):
    print(f"Alert: {message}")

pubsub = PubSub()
pubsub.subscribe("error", log_event)
pubsub.subscribe("error", alert_event)

pubsub.publish("error", "Disk space low")

# Output:
# Logging: Disk space low
# Alert: Disk space low

Conclusion

You now have a comprehensive guide to software architecture design patterns, complete with:

Creational Patterns: Singleton, Factory, Builder, Prototype
Structural Patterns: Adapter, Bridge, Composite, Decorator, Facade
Behavioral Patterns: Observer, Command, Strategy, State, Visitor
Concurrency Patterns: Thread Pool, Read-Write Lock, Circuit Breaker
Cloud-Native Patterns: API Gateway, Saga, CQRS, Event Sourcing
Enterprise Integration Patterns (EIP): Aggregator, Message Broker, Content-Based Router, Pub-Sub

Read this article and more on fzeba.com.

#28.2 Part 2/3 Basics of Software Architecture and Design Patterns

Florian Zeba — Mon, 24 Feb 2025 23:00:00 +0000

Intro 2

Last time we spoke about all the benefits and reasons why design patterns are important. Today we will dive into the different types and principles of design patterns and how they can be used in your projects.

Aspects of Software Architecture

1. Modularity

Modularity is the practice of dividing a system into smaller, independent modules, each responsible for a specific functionality. These modules can be developed, tested, and maintained independently.

Enhances maintainability by making it easier to locate and fix issues.
Increases reusability, allowing modules to be used in different applications.
Facilitates parallel development, enabling different teams to work on separate modules.

Microservices architecture exemplifies modularity, where each microservice represents an independent module that handles a distinct business function, such as authentication, payment processing, or inventory management.

2. Separation of Concerns (SoC)

Separation of Concerns (SoC) is a design principle that ensures different parts of a system handle distinct functionalities without unnecessary dependencies.

Simplifies development and debugging by isolating functionalities.
Reduces code complexity and enhances scalability.
Enables teams to work on different aspects of a system independently.

The Model-View-Controller (MVC) pattern demonstrates SoC by dividing a software application into:

Model: Handles data and business logic.
View: Manages user interface and presentation.
Controller: Processes user input and updates the model or view accordingly.

3. Single Responsibility Principle (SRP)

SRP states that each class, module, or function should have only one reason to change by being responsible for a single task or functionality.

Reduces system complexity.
Improves code maintainability and flexibility.
Prevents unintended side effects when modifying code.

A UserAuthenticationService class should only handle user authentication, while a DatabaseService should manage database operations. This ensures each service has a single responsibility.

4. Scalability

Scalability is the ability of a system to handle increasing loads efficiently, either by adding more resources (horizontal scaling) or upgrading existing resources (vertical scaling).

Ensures system performance remains stable as demand increases.
Reduces downtime and improves user experience.
Supports business growth without major architectural changes.

A load balancer distributes requests among multiple servers to achieve horizontal scaling, preventing any single server from becoming a bottleneck.

5. Performance Optimization

Performance optimization involves designing a system to maximize efficiency while minimizing resource consumption.

Enhances user experience by reducing latency.
Improves system efficiency and reduces operational costs.
Ensures smooth handling of high traffic loads.
Implementing caching mechanisms (e.g., Redis, Memcached).
Using efficient algorithms and data structures.
Optimizing database queries with indexing and proper schema design.

Using a Content Delivery Network (CDN) to cache and serve static assets closer to users significantly improves page load times.

6. High Availability & Fault Tolerance

High availability ensures a system remains operational despite failures, while fault tolerance allows the system to continue functioning with minimal disruption.

Prevents revenue loss and maintains customer trust.
Ensures business continuity.
Reduces downtime and operational risks.
Redundant servers and database replication.
Automated failover mechanisms.
Distributed systems with load balancing.

Cloud-based architectures with auto-healing instances detect failures and automatically replace malfunctioning components.

7. Security

Security in software architecture protects against unauthorized access, data breaches, and vulnerabilities.

Ensures data integrity and confidentiality.
Builds user trust and compliance with regulations (e.g., GDPR, HIPAA).
Prevents financial and reputational damage.
Implement authentication & authorization mechanisms (OAuth, JWT).
Use encryption (TLS for communication, AES for data storage).
Follow secure coding principles (e.g., input validation, avoiding SQL injection).

Role-Based Access Control (RBAC) ensures that only authorized users can access certain resources.

8. Loose Coupling & High Cohesion

Loose coupling minimizes dependencies between components, while high cohesion ensures each module focuses on a well-defined purpose.

Enhances flexibility and maintainability.
Simplifies debugging and testing.
Encourages modular and reusable components.

Message queues (e.g., RabbitMQ, Kafka) enable asynchronous communication between microservices, reducing direct dependencies.

9. Maintainability & Extensibility

Maintainability ensures ease of modification and debugging, while extensibility allows adding new features without disrupting existing functionality.

Reduces technical debt and long-term costs.
Enables continuous system evolution.
Improves developer productivity.
Writing modular and well-documented code.
Adopting design patterns like Factory, Singleton, and Dependency Injection.

A plug-in architecture allows developers to extend system functionality without modifying the core codebase.

10. Consistency & Data Integrity

Ensuring data remains accurate and consistent across different parts of a system.

Prevents data corruption and anomalies.
Maintains system reliability and trustworthiness.
Strong Consistency: Immediate data synchronization (ACID transactions in SQL databases).
Eventual Consistency: Slight delay in data propagation (NoSQL databases like DynamoDB).

Using database transactions ensures an order is not processed unless payment is successfully received.

11. Observability & Monitoring

Observability enables tracking system performance and identifying issues.

Helps in proactive troubleshooting.
Improves system reliability and uptime.
Logging (ELK Stack, Grafana).
Monitoring (Prometheus, Datadog).

Structured logging allows quick debugging by providing meaningful error messages.

12. API-First Design

Designing APIs before developing system components to ensure seamless integrations.

Facilitates third-party integrations.
Ensures consistency across microservices.
Use RESTful or GraphQL APIs.
Implement API versioning.

Designing an API contract using OpenAPI before implementing backend logic.

13. Event-Driven Architecture

Building systems that respond to real-time events instead of synchronous calls.

Enhances responsiveness.
Supports scalable, loosely coupled components.

Apache Kafka enables real-time processing of user interactions in applications.

14. Dependency Management

Efficiently managing libraries and external dependencies.

Prevents software bloat and vulnerabilities.
Ensures compatibility and security.
Use dependency injection.
Keep libraries updated.

Using Maven or npm for dependency management.

15. Cost Efficiency

Optimizing resources to balance performance and expenses.

Reduces operational costs.
Ensures sustainability.

AWS Lambda allows serverless computing, reducing infrastructure costs.

Conclusion

Software architecture encompasses various aspects, from modularity and scalability to security and performance optimization. By following architectural principles and design patterns, developers can build robust, maintainable, and scalable systems that meet business requirements and user expectations.

In the next part, we will explore the different types of design patterns and how they can be applied to solve common software design challenges.

Read this article and more on fzeba.com.

#28.1 Part 1/3 Basics of Software Architecture and Design Patterns

Florian Zeba — Sun, 23 Feb 2025 23:00:00 +0000

Why these patterns matter?

Modern software development faces constant challenges. As our applications grow bigger and more complex, we need smart ways to keep our code clean and manageable. Design patterns help us solve these challenges.

Think of design patterns as battle-tested blueprints for solving common programming problems. They're like recipes that have been refined by countless developers over many years. When you use these patterns, you're not just writing code – you're applying solutions that are proven to work.

These patterns help us write better software by:

Making our code easier to maintain
Helping teams work together more effectively
Making it simpler to add new features
Keeping our applications scalable

By learning and using design patterns, you're not reinventing the wheel – you're building on the experience of the entire development community.

Why Are Design Patterns Important? (The Details)

Code Reusability: Design patterns promote modular and reusable code, reducing redundancy and improving efficiency. By using patterns, developers can avoid reinventing the wheel and apply pre-tested solutions to their software projects.
Scalability and Maintainability: Well-structured applications are easier to maintain and extend. Design patterns ensure that software is scalable, allowing new features to be integrated without major refactoring.
Improved Collaboration: Using common patterns allows teams to communicate effectively. Instead of explaining complex logic, developers can refer to a known pattern, making collaboration across teams easier.
Code Readability and Structure: By following design patterns, developers ensure clearer, more structured code, making it easier for others (or even their future selves) to understand and modify.
To Solve Recurring Problems: Many software design challenges are common across different projects. Design patterns provide structured solutions that have been tested and refined over time.
To Reduce Development Time: Using design patterns accelerates development by eliminating trial-and-error approaches. Developers can focus on building new features rather than solving architectural issues from scratch.
To Improve Code Consistency: Patterns enforce standardization in coding practices, making software more predictable and reducing the risk of inconsistent implementations.

When Should Design Patterns Be Used?

Design patterns do help a lot building the basis of a projectstructure. BUT in some cases it is just not necessary to use them. For example: Using the finest materials on a ferrarri (trademark: ferrarri) makes sense. Using the finest materials on a family van not so much. So when should you use design patterns?

🔹 Use Design Patterns When:

The project is large and complex.
The same problem reoccurs across multiple projects.
The codebase needs to be maintainable and extendable.
Development involves multiple teams and requires standardization.
There is a need for scalability without massive code rewrites.

❌ Avoid Overusing Design Patterns When:

The project is small and simple.
The pattern introduces unnecessary complexity.
It is used just for the sake of using it, without solving a real problem.

How to Start Implementing Design Patterns?

1️⃣ Learn the Basic Categories

Design patterns are divided into three main categories:

Creational Patterns – Managing object creation (e.g., Singleton, Factory Method, Builder).
Structural Patterns – Organizing code structure (e.g., Adapter, Composite, Facade).
Behavioral Patterns – Managing communication between objects (e.g., Observer, Strategy, Command).

2️⃣ Start with Commonly Used Patterns

Begin with patterns that are widely used and easy to understand:

Singleton Pattern – Ensures a class has only one instance.
Factory Method – Provides a way to create objects without specifying the exact class.
Observer Pattern – Implements a subscription-based model.
Strategy Pattern – Enables swapping different algorithms at runtime.

3️⃣ Apply Patterns in Small Projects

Instead of diving into a large-scale implementation, start by using patterns in small personal projects or refactoring existing code.

4️⃣ Use Design Patterns in Real-World Scenarios

Implement Factory Pattern when dealing with different types of database connections.
Use Observer Pattern for handling event-driven applications.
Apply Facade Pattern to simplify interaction with third-party APIs.

5️⃣ Read and Practice

Read "Design Patterns: Elements of Reusable Object-Oriented Software" (by the Gang of Four).
Explore open-source projects and observe how patterns are used.
Practice implementing patterns in different programming languages.

Conclusion

Design patterns are essential tools for modern software development. By understanding and applying these patterns, developers can build scalable, maintainable, and efficient applications. Whether you're working on a personal project or a large enterprise application, design patterns provide a solid foundation for success.

In the next part, we will dive deeper into the different types and principles of design patterns and how they can be used in your projects. Stay tuned!

Read this article and more on fzeba.com.

Forem: Florian Zeba

#37 Running a Docker Container in a Docker Container

0. High-Level Introduction (Why Run Docker in Docker?)

1. Technical Introduction

1.1 What is Docker-in-Docker?

1.2 Common Use Cases

1.3 Key Approaches & Terminology (DinD vs. DooD)

2. Prerequisites

3. Method 1: Mounting the Host's Docker Socket (DooD)

3.1 Concept

3.2 Pros & Cons

3.3 Implementation Steps & docker exec Access

3.4 Code Example (Including docker exec usage)

3.5 Security Considerations (DooD)

4. Method 2: Running a Dedicated Docker Daemon Inside (True DinD)

4.1 Concept

4.2 Pros & Cons

4.3 Implementation Steps & docker exec Access

4.4 Code Example (Including docker exec usage)

4.4.1 Security Considerations (True DinD)

4.5 Advanced Example: Controlling Docker via Python/Jupyter (using DooD)

4.5.1 Implementation Steps

4.5.2 Code Example

5. Security Best Practices (General)

6. Troubleshooting Common Issues

7. Alternatives

8. Conclusion

9. TL;DR

#36 Connecting Alteryx to Snowflake: A Comprehensive Guide

Introduction

Methods to Connect Alteryx to Snowflake

1. Connecting via Snowflake ODBC Driver

Overview

Steps to Configure

Pros & Cons

2. Using Alteryx’s In-Database Tools (Snowflake Connector)

Overview

Steps to Configure

Pros & Cons

3. Using Alteryx’s Snowflake Bulk Loader

Overview

Steps to Configure

Pros & Cons

4. Using Python or R Scripts in Alteryx

Overview

Example Python Script

Pros & Cons

Best Practices for Alteryx-Snowflake Integration

Conclusion

#35 Python & Alteryx Integration: Unlocking Advanced Analytics

Introduction

1. Why Use Python in Alteryx?

2. Setting Up Python in Alteryx

Prerequisites

Configuration

3. Key Python Libraries for Data Analysis

4. Common Use Cases

A. Advanced Data Wrangling

B. Machine Learning Integration

C. Custom Visualizations

D. Text & NLP Processing

5. Best Practices & Limitations

Best Practices

Limitations

Conclusion

#34 50 Advanced SQL Queries Every Developer Should Know

1. Window Functions (Analytical Queries)

1.1. ROW_NUMBER() – Assign a Unique Row Number

1.2. RANK() – Rank with Gaps for Ties

1.3. DENSE_RANK() – Rank Without Gaps

1.4. NTILE() – Divide Rows into Buckets

1.5. LEAD() – Access Next Row’s Value

1.6. LAG() – Access Previous Row’s Value

1.7. FIRST_VALUE() – Get First Value in a Window

1.8. LAST_VALUE() – Get Last Value in a Window

1.9. Running Total with SUM() OVER

1.10. Moving Average

2. Common Table Expressions (CTEs) and Recursive Queries

2.1. Basic CTE

2.2. Recursive CTE (Hierarchical Data)

3.3 Implementation Steps & `docker exec` Access

3.4 Code Example (Including `docker exec` usage)

4.3 Implementation Steps & `docker exec` Access

4.4 Code Example (Including `docker exec` usage)