# sqlalchemy/alembic architecture

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

- Interactive diagram: https://gitdiagram.com/sqlalchemy/alembic
- Repository: https://github.com/sqlalchemy/alembic

## Overview

##### Project type and purpose
- Project type: A Python-based library and CLI tool for database schema migrations.
- Purpose: Provide a framework for generating, organizing, and applying incremental schema changes (migrations) to relational databases, with support for autogeneration, transactional DDL, multiple database dialects, and script-based migrations.

##### File structure
- Top-level scripts/configs:
– setup.py, pyproject.toml, setup.cfg, MANIFEST.in: packaging metadata
– alembic/__main__.py and command.py: CLI entrypoints
– alembic/config.py, alembic/context.py: configuration and runtime context loaders
- Core modules under alembic/:
– autogenerate/ (api.py, compare.py, render.py, rewriter.py): schema inspection & diff generation
– ddl/ (impl.py, base.py, dialect-specific modules): constructs to emit DDL per dialect
– operations/ (ops.py, batch.py, base.py): high-level migration operations (add_column, rename_table, etc.)
– script/ (base.py, revision.py): migration file management, revision graph
– runtime/ (environment.py, migration.py): migration execution engine
– util/ (messaging.py, exc.py, langhelpers.py, etc.): shared utilities
- Templates directory: defines skeletons for new projects and migrations (generic, async, multidb, pyproject)
- tests/: complete test suite exercising all subsystems
- docs/: static and generated documentation

##### Additional insights from README
- Emphasizes CLI-driven workflow layered on a programmatic API
- Highlights “autogenerate” feature that compares current DB state (via SQLAlchemy) to models
- Non-linear revision graph model with UUIDs for branching/merging

##### Main components
a. CLI Layer
– alembic command (entrypoint)
– Dispatch to alembic.command module
b. Configuration & Environment Loader
– alembic.config loads alembic.ini or pyproject.toml settings
– alembic.context builds the MigrationContext (connects to target database via SQLAlchemy Engine)
c. Migration Script Management
– alembic.script.Service and Revision classes handle the revision graph
– Template renderer picks one of the templates under alembic/templates
d. Core API Subsystems
– Operations API: high-level functions (add_column, create_table) → maps to Operation objects
– DDL API: low-level DDL constructs and dialect implementations that emit SQL for each database type
– Autogenerate API: uses SQLAlchemy inspector → compare.py produces a list of Operation objects → render.py turns them into Python migration directives
e. Runtime Execution Engine
– MigrationContext applies Operation objects within transactional DDL (where supported)
– EnvironmentContext orchestrates pre- and post- hooks, offline vs. online mode
f. External Dependencies
– SQLAlchemy for schema inspection, connection management, and SQL compilation
– Target relational databases (PostgreSQL, MySQL, SQLite, Oracle, SQL Server)
g. Testing Harness & Documentation
– tests/ exercise all subsystems via pytest + fixtures
– docs/ build static site and API reference

##### Relationships and interactions
- CLI Layer invokes Configuration Loader → passes config to EnvironmentContext
- EnvironmentContext either runs in “offline” mode (SQL script output) or “online” (direct DB connection)
- Script Service locates revisions and templates → user generates new revision file via autogenerate or blank template
- Autogenerate subsystem queries current DB metadata and model definitions → outputs Operation objects
- Operations are passed to the runtime engine which uses DDL API to emit SQL statements
- Dialect implementations tailor SQL per database
- All major actions occur within transactions when supported
- Tests mock or spin up databases to validate each subsystem

##### Architectural patterns and design principles
- Modular layered architecture: CLI → Config → Script management → Core API → Runtime → Dialect
- Dependency-inversion: high-level Operations API built atop lower-level DDL, with clean interfaces
- Template-based code generation for flexibility and extensibility
- Plugin-style revision graph for branching/merging migrations
- Separation of online vs. offline execution modes

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% CLI Layer
    subgraph "CLI Layer"
        CLI_MAIN["alembic/__main__.py"]:::cli
        CLI_CMD["alembic/command.py"]:::cli
    end
    click CLI_MAIN "https://github.com/sqlalchemy/alembic/blob/main/alembic/__main__.py"
    click CLI_CMD "https://github.com/sqlalchemy/alembic/blob/main/alembic/command.py"

    %% Configuration & Environment Loader
    subgraph "Configuration & Environment"
        CONFIG["alembic/config.py"]:::config
        CONTEXT["alembic/context.py"]:::config
    end
    click CONFIG "https://github.com/sqlalchemy/alembic/blob/main/alembic/config.py"
    click CONTEXT "https://github.com/sqlalchemy/alembic/blob/main/alembic/context.py"

    %% Script Management
    subgraph "Migration Script Management"
        SCRIPT_BASE["alembic/script/base.py"]:::script
        SCRIPT_REV["alembic/script/revision.py"]:::script
        SCRIPT_HOOK["alembic/script/write_hooks.py"]:::script
        TEMPLATES["alembic/templates/"]:::script
    end
    click SCRIPT_BASE "https://github.com/sqlalchemy/alembic/blob/main/alembic/script/base.py"
    click SCRIPT_REV "https://github.com/sqlalchemy/alembic/blob/main/alembic/script/revision.py"
    click SCRIPT_HOOK "https://github.com/sqlalchemy/alembic/blob/main/alembic/script/write_hooks.py"
    click TEMPLATES "https://github.com/sqlalchemy/alembic/tree/main/alembic/templates/"

    %% Autogenerate API
    subgraph "Autogenerate API"
        AG_API["alembic/autogenerate/api.py"]:::core
        AG_COMPARE["alembic/autogenerate/compare.py"]:::core
        AG_RENDER["alembic/autogenerate/render.py"]:::core
        AG_REWRITER["alembic/autogenerate/rewriter.py"]:::core
    end
    click AG_API "https://github.com/sqlalchemy/alembic/blob/main/alembic/autogenerate/api.py"
    click AG_COMPARE "https://github.com/sqlalchemy/alembic/blob/main/alembic/autogenerate/compare.py"
    click AG_RENDER "https://github.com/sqlalchemy/alembic/blob/main/alembic/autogenerate/render.py"
    click AG_REWRITER "https://github.com/sqlalchemy/alembic/blob/main/alembic/autogenerate/rewriter.py"

    %% Operations API
    subgraph "Operations API"
        OPS_OPS["alembic/operations/ops.py"]:::core
        OPS_BATCH["alembic/operations/batch.py"]:::core
        OPS_BASE["alembic/operations/base.py"]:::core
        OPS_SCHEMA["alembic/operations/schemaobj.py"]:::core
        OPS_TOIMPL["alembic/operations/toimpl.py"]:::core
    end
    click OPS_OPS "https://github.com/sqlalchemy/alembic/blob/main/alembic/operations/ops.py"
    click OPS_BATCH "https://github.com/sqlalchemy/alembic/blob/main/alembic/operations/batch.py"
    click OPS_BASE "https://github.com/sqlalchemy/alembic/blob/main/alembic/operations/base.py"
    click OPS_SCHEMA "https://github.com/sqlalchemy/alembic/blob/main/alembic/operations/schemaobj.py"
    click OPS_TOIMPL "https://github.com/sqlalchemy/alembic/blob/main/alembic/operations/toimpl.py"

    %% DDL API & Dialects
    subgraph "DDL API & Dialects"
        DDL_BASE["alembic/ddl/base.py"]:::dialect
        DDL_IMPL["alembic/ddl/impl.py"]:::dialect
        DDL_AUTOGEN["alembic/ddl/_autogen.py"]:::dialect
        DDL_PG["alembic/ddl/postgresql.py"]:::dialect
        DDL_MY["alembic/ddl/mysql.py"]:::dialect
        DDL_SQLITE["alembic/ddl/sqlite.py"]:::dialect
        DDL_ORA["alembic/ddl/oracle.py"]:::dialect
        DDL_MSSQL["alembic/ddl/mssql.py"]:::dialect
    end
    click DDL_BASE "https://github.com/sqlalchemy/alembic/blob/main/alembic/ddl/base.py"
    click DDL_IMPL "https://github.com/sqlalchemy/alembic/blob/main/alembic/ddl/impl.py"
    click DDL_AUTOGEN "https://github.com/sqlalchemy/alembic/blob/main/alembic/ddl/_autogen.py"
    click DDL_PG "https://github.com/sqlalchemy/alembic/blob/main/alembic/ddl/postgresql.py"
    click DDL_MY "https://github.com/sqlalchemy/alembic/blob/main/alembic/ddl/mysql.py"
    click DDL_SQLITE "https://github.com/sqlalchemy/alembic/blob/main/alembic/ddl/sqlite.py"
    click DDL_ORA "https://github.com/sqlalchemy/alembic/blob/main/alembic/ddl/oracle.py"
    click DDL_MSSQL "https://github.com/sqlalchemy/alembic/blob/main/alembic/ddl/mssql.py"

    %% Runtime Execution Engine
    subgraph "Runtime Execution Engine"
        RUNTIME_ENV["alembic/runtime/environment.py"]:::runtime
        RUNTIME_MIG["alembic/runtime/migration.py"]:::runtime
    end
    click RUNTIME_ENV "https://github.com/sqlalchemy/alembic/blob/main/alembic/runtime/environment.py"
    click RUNTIME_MIG "https://github.com/sqlalchemy/alembic/blob/main/alembic/runtime/migration.py"

    %% Utility Modules
    subgraph "Utility Modules"
        UTIL_MSG["alembic/util/messaging.py"]:::util
        UTIL_EXC["alembic/util/exc.py"]:::util
        UTIL_LANG["alembic/util/langhelpers.py"]:::util
        UTIL_SQLA["alembic/util/sqla_compat.py"]:::util
        UTIL_EDITOR["alembic/util/editor.py"]:::util
        UTIL_PYFILES["alembic/util/pyfiles.py"]:::util
    end
    click UTIL_MSG "https://github.com/sqlalchemy/alembic/blob/main/alembic/util/messaging.py"
    click UTIL_EXC "https://github.com/sqlalchemy/alembic/blob/main/alembic/util/exc.py"
    click UTIL_LANG "https://github.com/sqlalchemy/alembic/blob/main/alembic/util/langhelpers.py"
    click UTIL_SQLA "https://github.com/sqlalchemy/alembic/blob/main/alembic/util/sqla_compat.py"
    click UTIL_EDITOR "https://github.com/sqlalchemy/alembic/blob/main/alembic/util/editor.py"
    click UTIL_PYFILES "https://github.com/sqlalchemy/alembic/blob/main/alembic/util/pyfiles.py"

    %% Testing Harness
    subgraph "Testing Harness"
        TEST_SUITE["tests/"]:::test
        TEST_ALEMBIC["alembic/testing/"]:::test
    end
    click TEST_SUITE "https://github.com/sqlalchemy/alembic/tree/main/tests/"
    click TEST_ALEMBIC "https://github.com/sqlalchemy/alembic/tree/main/alembic/testing/"

    %% Documentation
    subgraph "Documentation"
        DOC_API["docs/build/api/"]:::doc
        DOC_GUIDES["docs/build/"]:::doc
    end
    click DOC_API "https://github.com/sqlalchemy/alembic/tree/main/docs/build/api/"
    click DOC_GUIDES "https://github.com/sqlalchemy/alembic/tree/main/docs/build/"

    %% External Dependencies
    subgraph "External Dependencies"
        SQLA["SQLAlchemy"]:::external
        DB["Target RDBMS"]:::external
    end

    %% Connections
    CLI_MAIN -->|"dispatch"| CLI_CMD
    CLI_CMD -->|"loads settings"| CONFIG
    CONFIG -->|"builds"| CONTEXT
    CONTEXT -->|"invokes"| RUNTIME_ENV
    RUNTIME_ENV -->|"executes steps"| RUNTIME_MIG
    CLI_CMD -->|"script service"| SCRIPT_BASE
    SCRIPT_BASE --> SCRIPT_REV
    SCRIPT_BASE --> SCRIPT_HOOK
    SCRIPT_BASE --> TEMPLATES
    CLI_CMD -->|"autogenerate"| AG_API
    AG_API --> AG_COMPARE
    AG_COMPARE --> AG_RENDER
    AG_RENDER --> AG_REWRITER
    RUNTIME_MIG --> OPS_OPS
    OPS_OPS --> OPS_BATCH
    OPS_OPS --> OPS_BASE
    OPS_OPS --> OPS_SCHEMA
    OPS_OPS --> OPS_TOIMPL
    OPS_TOIMPL --> DDL_BASE
    DDL_BASE --> DDL_IMPL
    DDL_IMPL --> DDL_PG
    DDL_IMPL --> DDL_MY
    DDL_IMPL --> DDL_SQLITE
    DDL_IMPL --> DDL_ORA
    DDL_IMPL --> DDL_MSSQL
    DDL_IMPL -->|"SQL compilation"| SQLA
    SQLA --> DB
    UTIL_MSG --> CLI_CMD
    UTIL_EXC --> CONFIG
    UTIL_LANG --> AG_REWRITER
    UTIL_SQLA --> AG_COMPARE
    UTIL_EDITOR --> SCRIPT_HOOK
    UTIL_PYFILES --> SCRIPT_BASE
    TEST_SUITE -->|"validates"| CLI_CMD
    TEST_SUITE -->|"validates"| AG_API
    TEST_SUITE -->|"validates"| DDL_PG
    TEST_SUITE -->|"validates"| DDL_MY
    TEST_SUITE -->|"validates"| DDL_SQLITE
    TEST_SUITE -->|"validates"| DDL_ORA
    TEST_SUITE -->|"validates"| DDL_MSSQL
    TEST_ALEMBIC --> TEST_SUITE
    DOC_GUIDES -->|"includes"| DOC_API

    %% Styles
    classDef cli fill:#ccf,stroke:#333,stroke-width:1px;
    classDef config fill:#cfc,stroke:#333,stroke-width:1px;
    classDef script fill:#fcf,stroke:#333,stroke-width:1px;
    classDef core fill:#cff,stroke:#333,stroke-width:1px;
    classDef runtime fill:#fc9,stroke:#333,stroke-width:1px;
    classDef dialect fill:#f96,stroke:#333,stroke-width:1px;
    classDef util fill:#ccc,stroke:#333,stroke-width:1px;
    classDef test fill:#efe,stroke:#333,stroke-width:1px;
    classDef doc fill:#eef,stroke:#333,stroke-width:1px;
    classDef external fill:#eee,stroke:#333,stroke-width:1px;
```
