# dimitri/pgloader architecture

> Architecture diagram of the GitHub repository dimitri/pgloader, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-04-03). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/dimitri/pgloader
- Repository: https://github.com/dimitri/pgloader

## Overview

##### Project Type and Purpose
- pgloader is an open‐source data loading tool for PostgreSQL. It is used to efficiently migrate and copy data from a variety of sources (like MySQL, SQLite, CSV files, and others) into PostgreSQL, using the COPY command.
- The README emphasizes features such as transaction behavior during load, data reformatting (e.g., transforming “0000-00-00” dates into NULL), and robust error handling (continued loading with rejected data files).

##### Key Components
- Command Line Interface (CLI) and Entry Point:
– Represented mainly by “src/main.lisp” along with related parser modules in “src/parsers”. This component handles input arguments, command-line options, and configuration.
- Data Extraction / Source Modules:
– A collection of specialized modules under “src/sources/…” (for MySQL, SQLite, CSV, DBF, fixed, ixf, mssql, pgsql, sqlite, etc.) handle connecting to different data sources. Each source adapter is responsible for reading the raw data and applying any casting or conversion rules.
- Data Transformation and Mapping:
– Transformation components are spread across modules in “src/utils”, “src/params”, and other transformation-specific files (like cast rules defined in several subdirectories). This part takes the extracted data, applies necessary format conversions (for date formats, type casting, etc.), and gets it ready for PostgreSQL.
- PostgreSQL Interface:
– Modules in “src/pgsql” and “src/pg-copy” manage database connections, schema creation/migration, and the actual data load using PostgreSQL’s COPY command. Highlight that this component ensures transactional integrity and handles error logging.
- Error Handling and Logging:
– Although not isolated in one folder, the design incorporates error reporting (storing rejected data) and debugging/logging mechanisms (as hinted by the various utility files and options in the README). This forms a cross-cutting concern in pgloader.
- Build, Packaging, and Deployment:
– The top-level files (Dockerfile, Makefile, debian/, Vagrantfile, etc.) and the “build” directory indicate how the project is packaged and deployed. These can be shown as peripheral components that integrate with CI/CD processes.
- Documentation and Tests:
– The “docs” and “test” directories (including regress) should be shown as supporting components, reflecting the quality assurance and documentation aspects.

##### Relationships and Data Flow
– This flows into the CLI component (src/main.lisp) that parses command line arguments.
- The CLI then routes the request to the specific data source module based on the input (for example, the “src/sources/mysql” or “src/sources/sqlite” directories).
- The transformed data is then routed to the PostgreSQL interface modules (src/pgsql and src/pg-copy), which perform the bulk-data load using the COPY command.
- Throughout the process, error handling/logging modules monitor the flow and capture any rejected records.
- Finally, the build/packaging and testing components, although not in the main data flow, can be indicated on the side to show aspects of deployment and quality assurance.

##### Architectural Patterns and Design Principles
- Modular Design: The project separates responsibilities by using specific modules for each data source and for closely related tasks (parsing, transformation, DB connectivity).
- Error Isolation: Emphasize that pgloader continues processing good data even when encountering errors.
- Extensibility: The arrangement of directories like “src/sources” and “src/parsers” indicates an architecture that is designed to be extended with new source types and transformation rules.
- Integration with Build/Deployment Tools: The presence of Dockerfiles, Debian packaging, Travis CI, and Vagrantfiles highlight attention to deployment, testing, and continuous integration.

## Diagram (Mermaid)

```mermaid
flowchart TD

    %% Core Flow
    User["User Command / CLI Interface"]:::cli

    subgraph "CLI & Command Parser"
        CLI1["Entry Point"]:::cli
        CLI2["Parser Modules"]:::cli
        CLI1 --> CLI2
    end

    User --> CLI1

    subgraph "Data Sources"
        DS1["MySQL Source"]:::datasource
        DS2["SQLite Source"]:::datasource
        DS3["CSV Source"]:::datasource
        DS4["DB3 Source"]:::datasource
        DS5["Fixed Source"]:::datasource
        DS6["IXF Source"]:::datasource
        DS7["MSSQL Source"]:::datasource
        DS8["PGSQL Source"]:::datasource
        DS9["Syslog Source"]:::datasource
        %% Vertically stack the data source adapters
        DS1 --> DS2
        DS2 --> DS3
        DS3 --> DS4
        DS4 --> DS5
        DS5 --> DS6
        DS6 --> DS7
        DS7 --> DS8
        DS8 --> DS9
    end

    %% Aggregator for Data Extraction Output
    DS_Aggregate["Extracted Data"]:::datasource
    DS9 --> DS_Aggregate
    CLI2  --> DS_Aggregate

    subgraph "Transformation Engine"
        TE1["Transforms Module"]:::transformation
        TE2["Parameters Configuration"]:::transformation
        TE1 --> TE2
    end

    TE_Aggregate["Transformed Data"]:::transformation
    TE2 --> TE_Aggregate
    DS_Aggregate --> TE1

    subgraph "PostgreSQL Loader"
        PG1["DB Connection & Schema"]:::pgloader
        PG2["COPY Executor"]:::pgloader
        PG3["COPY Format"]:::pgloader
        PG1 --> PG2
        PG2 --> PG3
    end

    PG_Aggregate["Loaded Data"]:::pgloader
    PG3 --> PG_Aggregate
    TE_Aggregate --> PG1

    %% Error Handling (cross-cutting)
    EH["Error Handling & Logging"]:::error
    %% Dashed lines to indicate cross-cutting support
    TE_Aggregate ---|dashed| EH
    PG_Aggregate ---|dashed| EH

    %% Peripheral: Build, Packaging & Deployment
    subgraph "Build & Packaging"
        B1["Dockerfile"]:::build
        B2["Makefile"]:::build
        B3["debian"]:::build
        B4["build"]:::build
        B5["Vagrantfile"]:::build
        B1 --> B2
        B2 --> B3
        B3 --> B4
        B4 --> B5
    end

    %% Peripheral: Testing & Documentation
    subgraph "Testing & Documentation"
        TD1["Documentation"]:::documentation
        TD2["Tests"]:::documentation
        TD1 --> TD2
    end

    %% Click Events for CLI & Entry Point
    click CLI1 "https://github.com/dimitri/pgloader/blob/master/src/main.lisp"
    click CLI2 "https://github.com/dimitri/pgloader/blob/master/src/parsers/command-parser.lisp"

    %% Click Events for Data Sources
    click DS1 "https://github.com/dimitri/pgloader/tree/master/src/sources/mysql"
    click DS2 "https://github.com/dimitri/pgloader/tree/master/src/sources/sqlite"
    click DS3 "https://github.com/dimitri/pgloader/tree/master/src/sources/csv"
    click DS4 "https://github.com/dimitri/pgloader/tree/master/src/sources/db3"
    click DS5 "https://github.com/dimitri/pgloader/tree/master/src/sources/fixed"
    click DS6 "https://github.com/dimitri/pgloader/tree/master/src/sources/ixf"
    click DS7 "https://github.com/dimitri/pgloader/tree/master/src/sources/mssql"
    click DS8 "https://github.com/dimitri/pgloader/tree/master/src/sources/pgsql"
    click DS9 "https://github.com/dimitri/pgloader/blob/master/src/sources/syslog.lisp"

    %% Click Events for Transformation Engine
    click TE1 "https://github.com/dimitri/pgloader/blob/master/src/utils/transforms.lisp"
    click TE2 "https://github.com/dimitri/pgloader/blob/master/src/params.lisp"

    %% Click Events for PostgreSQL Loader
    click PG1 "https://github.com/dimitri/pgloader/blob/master/src/pgsql/connection.lisp"
    click PG2 "https://github.com/dimitri/pgloader/blob/master/src/pg-copy/copy-rows-in-batch.lisp"
    click PG3 "https://github.com/dimitri/pgloader/blob/master/src/pg-copy/copy-format.lisp"

    %% Click Event for Error Handling
    click EH "https://github.com/dimitri/pgloader/blob/master/src/utils/logs.lisp"

    %% Click Events for Build & Packaging
    click B1 "https://github.com/dimitri/pgloader/tree/master/Dockerfile"
    click B2 "https://github.com/dimitri/pgloader/tree/master/Makefile"
    click B3 "https://github.com/dimitri/pgloader/tree/master/debian/"
    click B4 "https://github.com/dimitri/pgloader/tree/master/build/"
    click B5 "https://github.com/dimitri/pgloader/tree/master/Vagrantfile"

    %% Click Events for Testing & Documentation
    click TD1 "https://github.com/dimitri/pgloader/tree/master/docs/"
    click TD2 "https://github.com/dimitri/pgloader/tree/master/test/"

    %% Styles
    classDef cli fill:#cce5ff,stroke:#004085,stroke-width:2px;
    classDef datasource fill:#d4edda,stroke:#155724,stroke-width:2px;
    classDef transformation fill:#fff3cd,stroke:#856404,stroke-width:2px;
    classDef pgloader fill:#f8d7da,stroke:#721c24,stroke-width:2px;
    classDef error fill:#d6d8d9,stroke:#1b1e21,stroke-width:2px;
    classDef build fill:#e2e3e5,stroke:#41464b,stroke-width:2px;
    classDef documentation fill:#fefefe,stroke:#818182,stroke-width:2px;
```
