Skip to content

Alembic Migrations

Referenced Files in This Document

Table of Contents

  1. Introduction

  2. Project Structure

  3. Core Components

  4. Architecture Overview

  5. Detailed Component Analysis

  6. Dependency Analysis

  7. Performance Considerations

  8. Troubleshooting Guide

  9. Conclusion

  10. Appendices

Introduction

This document explains how Nexo uses Alembic for database migrations, including the end-to-end workflow to create, review, test, and apply schema changes across environments. It covers versioning, backward compatibility strategies, rollback procedures, dependency management, conflict resolution, and best practices for writing migration scripts (data transformations, constraint modifications, index management). Examples reference existing migrations such as the initial schema setup and decision type additions.

Project Structure

Alembic configuration and migration files are located under alembic/. The key elements include:

  • alembic.ini: top-level Alembic configuration

  • alembic/env.py: runtime environment setup and connection handling

  • alembic/script.py.mako: template used when generating new migration scripts

  • alembic/versions/: directory containing individual migration scripts

graph TB

A["alembic.ini"] --> B["alembic/env.py"]

B --> C["alembic/versions/<migration>.py"]

C --> D["apps/shared/models/*.py"]

B --> E["apps/shared/db.py"]

Diagram sources

Section sources

Core Components

  • alembic.ini: defines metadata location, script locations, and default URL patterns.

  • env.py: loads configuration, sets up SQLAlchemy engine/connection, and provides the target_metadata for autogenerate.

  • script.py.mako: customizes generated migration file headers and boilerplate.

  • versions/*: each file represents a single revision with upgrade() and downgrade() functions.

Key responsibilities:

  • Connection lifecycle and transaction boundaries

  • Autogeneration of SQL from model changes

  • Version tracking via alembic_version table

  • Environment-specific overrides via config variables

Section sources

Architecture Overview

The migration pipeline connects Alembic to the application’s SQLAlchemy models and database through env.py. When running commands, Alembic reads alembic.ini, initializes the environment, and executes the requested operation against the configured database.

sequenceDiagram

participant Dev as "Developer"

participant CLI as "Alembic CLI"

participant Env as "alembic/env.py"

participant DB as "Database"

participant Models as "apps/shared/models"

Dev->>CLI : "alembic revision -m <msg>"

CLI->>Env : load_config()

Env-->>CLI : Engine + target_metadata

CLI->>Models : inspect models (for autogenerate)

CLI-->>Dev : generate <hash>_msg.py

Dev->>CLI : "alembic upgrade head"

CLI->>Env : connect()

Env->>DB : begin transaction

CLI->>DB : execute upgrade steps

DB-->>CLI : success

CLI-->>Dev : applied revision

Diagram sources

Detailed Component Analysis

Migration Workflow

End-to-end process for creating and applying migrations:

flowchart TD

Start(["Start"]) --> DefineModel["Update SQLAlchemy models"]

DefineModel --> Generate["Generate migration<br/>alembic revision -m '...'"]

Generate --> Review["Review generated script<br/>upgrade()/downgrade()"]

Review --> TestLocal["Test locally<br/>alembic downgrade base && alembic upgrade head"]

TestLocal --> Commit["Commit migration and models"]

Commit --> Deploy["Deploy to staging/prod"]

Deploy --> Apply["Apply on target DB<br/>alembic upgrade head"]

Apply --> Verify["Verify schema and data"]

Verify --> End(["Done"])

[No sources needed since this diagram shows conceptual workflow, not actual code structure]

Creating New Migrations

  • Update models in apps/shared/models.

  • Generate a migration: alembic revision -m "short descriptive message".

  • If using autogenerate, ensure env.py imports all models so Alembic can detect changes.

  • Inspect the generated file in alembic/versions/_message.py and adjust if necessary.

Best practices:

  • Keep messages concise and meaningful.

  • Prefer multiple small migrations over one large change.

  • Ensure both upgrade() and downgrade() are implemented.

Section sources

Reviewing Generated SQL

  • Use alembic current to check the active revision.

  • Use alembic history to visualize the chain of revisions.

  • Use alembic upgrade --sql head to preview SQL without executing it.

  • Validate that indexes, constraints, and data operations match expectations.

Section sources

Testing Migration Scripts

Recommended local testing strategy:

  • Downgrade to base: alembic downgrade base

  • Upgrade to head: alembic upgrade head

  • Repeat to confirm idempotency and correctness

  • For destructive changes, test on a copy of production-like data

Section sources

Applying Changes Across Environments

  • Configure per-environment URLs or use environment variables to override the database URL.

  • In CI/CD, run alembic upgrade head before starting the service.

  • Pin the expected revision in deployment manifests to avoid drift.

Section sources

Versioning System and Dependencies

  • Each migration has a unique revision ID; dependencies are declared explicitly when needed.

  • Use alembic heads and alembic branches to manage divergent histories.

  • Avoid branching unless you must support parallel development paths; merge by rewriting history carefully.

Section sources

Backward Compatibility Strategies

When modifying schemas:

  • Add columns with defaults or nullable to avoid breaking existing app code.

  • Introduce new enum values or fields gradually.

  • Split changes into additive then cleanup migrations.

  • Provide safe downgrade() implementations that reverse changes without data loss.

Example references:

  • Initial schema creation establishes baseline tables and relationships.

  • Decision type addition demonstrates adding an enum value safely.

Section sources

Rollback Procedures

  • To revert the last migration: alembic downgrade -1

  • To revert to a specific revision: alembic downgrade

  • Always test downgrades on non-production databases first.

Section sources

Conflict Resolution Strategies

  • If two developers branch off the same parent, resolve by merging their migrations into a common descendant or by rebasing.

  • Use alembic merge to combine branches when appropriate.

  • After resolving, verify with alembic upgrade head and alembic downgrade base.

Section sources

Best Practices for Writing Migration Scripts

Data transformations:

  • Batch updates to avoid long-running transactions.

  • Use explicit transactions and commit points where supported.

  • Preserve data integrity with careful ordering of operations.

Constraint modifications:

  • Drop/recreate constraints only when necessary.

  • Prefer additive changes (add column, backfill, then drop old column) to minimize downtime.

Index management:

  • Create indexes concurrently where possible to reduce lock time.

  • Remove unused indexes in separate migrations after validation.

Environment-specific configurations:

  • Use env.py to read environment variables for DB URL and other settings.

  • Keep secrets out of alembic.ini; prefer runtime injection.

Section sources

Example: Initial Schema Setup

  • Establishes core tables and relationships required by the application.

  • Serves as the base revision for subsequent changes.

Section sources

Example: Decision Type Additions

  • Adds or extends decision-related types to support evolving business logic.

  • Demonstrates safe extension of enums or related structures.

Section sources

Dependency Analysis

High-level dependencies between Alembic components and application modules:

graph LR

AlembicINI["alembic.ini"] --> EnvPy["alembic/env.py"]

EnvPy --> DBModule["apps/shared/db.py"]

EnvPy --> Models["apps/shared/models/*.py"]

Versions["alembic/versions/*.py"] --> EnvPy

Versions --> Models

Diagram sources

Section sources

Performance Considerations

  • Large data migrations should be chunked to avoid long locks and memory pressure.

  • Use concurrent index creation where supported to reduce downtime.

  • Avoid unnecessary ALTER TABLE operations; batch schema changes when possible.

  • Monitor slow queries during backfills and consider temporary indexes to speed them up.

[No sources needed since this section provides general guidance]

Troubleshooting Guide

Common issues and resolutions:

  • Missing models in autogenerate: ensure env.py imports all relevant models so target_metadata is complete.

  • Stale revision state: verify alembic current and compare with repository HEAD; fix with alembic stamp if necessary (use cautiously).

  • Conflicting branches: use alembic merge to reconcile divergent histories.

  • Transaction errors: split large operations into smaller steps and add explicit commits where supported.

Operational checks:

  • alembic current: show the active revision

  • alembic history: list revisions and links

  • alembic upgrade --sql head: preview SQL

  • alembic downgrade base && alembic upgrade head: full cycle test

Section sources

Conclusion

Alembic provides a robust, versioned approach to managing Nexo’s database schema. By following the workflows, best practices, and examples outlined here—especially around backward compatibility, testing, and environment-specific configuration—you can evolve the schema safely across development, staging, and production.

[No sources needed since this section summarizes without analyzing specific files]

Appendices

Quick Reference Commands

  • Generate migration: alembic revision -m "message"

  • Preview SQL: alembic upgrade --sql head

  • Apply latest: alembic upgrade head

  • Revert last: alembic downgrade -1

  • Revert to revision: alembic downgrade

  • Check status: alembic current

  • List history: alembic history

[No sources needed since this section provides general guidance]