# kellyjadams/run-sql-in-python architecture

> Architecture diagram of the GitHub repository kellyjadams/run-sql-in-python, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-05-18). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/kellyjadams/run-sql-in-python
- Repository: https://github.com/kellyjadams/run-sql-in-python

## Overview

##### Project type and purpose
- This is a small Python utility project (an open-source tool) whose sole purpose is to demonstrate how to connect to two different database services: PostgreSQL and Google BigQuery.
- The README confirms it’s not a full-stack web application but a set of scripts to establish database connections, with a blog post for deeper context.

##### File structure
- Top-level files only:
- README.md – project overview and instructions
- postgresql.py – code for connecting to PostgreSQL, using a secret manager for credentials
- bigquery.py – code for connecting to BigQuery
- No “src” folder or additional submodules; the scripts live at the root.
- No build scripts, no Dockerfile, no CI configuration – minimal deployment footprint.

##### Extract insights from the README
- The README highlights two distinct database-connection flows.
- It mentions a “secret manager” for the PostgreSQL password, implying an external secrets service.
- BigQuery script likely uses GCP’s client libraries and authentication (service account or ADC).

##### Main components
a. Python runtime (where the scripts execute)
b. postgresql.py module
c. bigquery.py module
d. Secret Manager service (for PostgreSQL credentials)
e. PostgreSQL database server (could be self-hosted or cloud)
f. Google BigQuery service (GCP API endpoint)
g. (Optional) Local developer workstation or CI environment that runs the scripts

##### Relationships & interactions
- The Python runtime loads one of the two modules (postgresql.py or bigquery.py).
- postgresql.py queries Secret Manager to obtain DB credentials, then opens a TCP connection to PostgreSQL.
- bigquery.py uses GCP’s authentication mechanism to call the BigQuery API.
- Both modules execute SQL commands over those connections and return results to the caller.

##### Architectural patterns & design principles
- Single-responsibility scripts: each file handles only one type of database connection.
- Externalize secrets: separation between code and credentials via the secret manager.
- Dependency on managed services (PostgreSQL instance and BigQuery API) rather than in-app persistence.

##### Elements
- Clear labels:
- “Python Runtime / Client”
- “postgresql.py”
- “bigquery.py”
- “Secret Manager”
- “PostgreSQL Server”
- “Google BigQuery API”
- Credential fetch → Secret Manager
- DB connection request → PostgreSQL Server
- API call → BigQuery service
- Another for secret management
- Another for external services (databases, cloud APIs)

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Environment
    Dev["Developer / CI Environment"]:::env
    Dev -->|runs scripts| PyRuntime["Python Runtime / Client"]:::env

    %% Python Runtime and Scripts
    subgraph "Python Environment"
        PyRuntime
        PGScript["postgresql.py"]:::local
        BQScript["bigquery.py"]:::local
    end
    PyRuntime --> PGScript
    PyRuntime --> BQScript

    %% Documentation
    README["README.md"]:::local
    Dev -->|references| README

    %% Secret Manager and Databases
    Secret["Secret Manager"]:::secret
    Postgres["PostgreSQL Server"]:::external
    BigQuery["Google BigQuery API"]:::external

    PGScript -->|fetch secret| Secret
    Secret -->|returns credentials| PGScript
    PGScript -->|establish connection / execute SQL| Postgres

    BQScript -->|authenticate & call API| BigQuery

    %% Click Events
    click PGScript "https://github.com/kellyjadams/run-sql-in-python/blob/main/postgresql.py"
    click BQScript "https://github.com/kellyjadams/run-sql-in-python/blob/main/bigquery.py"
    click README "https://github.com/kellyjadams/run-sql-in-python/blob/main/README.md"

    %% Styles
    classDef local fill:#f9f,stroke:#333,stroke-width:2px
    classDef secret fill:#ff9,stroke:#333,stroke-width:2px
    classDef external fill:#9f9,stroke:#333,stroke-width:2px
    classDef env fill:#ccf,stroke:#333,stroke-width:2px
```
