Database Migration Guide

How to create and manage database migrations in the Definable backend

This guide outlines the process for creating and managing database migrations in the Definable backend using Alembic.

Overview

Definable uses Alembic to manage database migrations. Alembic is an SQL migration tool for SQLAlchemy that provides:

• Version control for database schemas
• Ability to upgrade and downgrade between versions
• Auto-generation of migrations based on model changes
• A flexible environment for migration script execution

The database migration system follows a numbered sequence approach, with each migration file prefixed with a sequential number (e.g., 001_create_base_tables.py, 002_create_models_and_agents.py).

Migration Workflow Visualization

Setup

The Alembic configuration is in the project root directory:

• alembic.ini: Contains database connection information and Alembic settings
• alembic/: Directory containing migration scripts and environment configuration:
  • env.py: The Alembic environment setup
  • script.py.mako: Template for generating migration files
  • versions/: Directory containing migration scripts

Project Structure for Migrations

Creating a New Migration

Automatic Migration Generation

Alembic can automatically detect changes between your SQLAlchemy models and the current database schema to generate migrations:

1. Make changes to your SQLAlchemy models in the src/models/ directory
2. Ensure the new model is imported in the models __init__.py file
3. Run the following command to generate a migration:

# Generate migration with auto-detected changes
alembic revision --autogenerate -m "Description of changes"

This will create a new file in the alembic/versions/ directory with a format like <revision_id>_description_of_changes.py.

Automatic Migration Process

Manual Migration Creation

For more complex changes or to insert seed data, create a manual migration:

# Create a blank migration file
alembic revision -m "Description of changes"

Then edit the generated file to include your migration logic in the upgrade() and downgrade() functions.

Migration File Format

Each migration file follows this structure:

"""Description of changes

Revision ID: <revision_id>
Revises: <previous_revision_id>
Create Date: <timestamp>
"""

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa

# revision identifiers
revision: str = "<revision_id>"
down_revision: Union[str, None] = "<previous_revision_id>"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

def upgrade() -> None:
    # Migration code that brings the database one step forward
    # Example: op.create_table(), op.add_column(), etc.
    pass

def downgrade() -> None:
    # Migration code that brings the database one step backward
    # Example: op.drop_table(), op.drop_column(), etc.
    pass

Migration Dependencies

Naming Convention

Following the project’s convention, you should:

1. Prefix your migration file with the next sequential number
2. Add a short descriptive name
3. For example, if the latest migration is 010_create_upload_files.py, name yours 011_your_feature_name.py

To rename the file after generation:

mv alembic/versions/<revision_id>_description_of_changes.py alembic/versions/<revision_id>_011_your_feature_name.py

Running Migrations

Upgrading the Database

To apply all pending migrations:

alembic upgrade head

To upgrade to a specific revision:

alembic upgrade <revision_id>

To upgrade a relative number of steps:

alembic upgrade +1  # Upgrade one step

Downgrading the Database

To downgrade to a previous revision:

alembic downgrade <revision_id>

To downgrade a relative number of steps:

alembic downgrade -1  # Downgrade one step

To downgrade to the base (before any migrations):

alembic downgrade base

Migration State Flow

Checking Migration Status

To see the current database revision:

alembic current

To see migration history:

alembic history

To see which migrations need to be applied:

alembic history -i

Migration Examples

Creating a Table

def upgrade() -> None:
    op.create_table(
        "your_table_name",
        sa.Column("id", postgresql.UUID(), server_default=sa.text("gen_random_uuid()"), nullable=False),
        sa.Column("name", sa.String(255), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("organization_id", postgresql.UUID(), nullable=False),
        sa.Column("is_active", sa.Boolean(), server_default="true", nullable=False),
        sa.Column("created_at", sa.TIMESTAMP(), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
        sa.Column("updated_at", sa.TIMESTAMP(), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["organization_id"], ["organizations.id"], ondelete="CASCADE"),
    )
    
    # Create index on columns
    op.create_index("ix_your_table_name_organization_id", "your_table_name", ["organization_id"])
    
    # Create update trigger for updated_at column
    op.execute(f"""
        CREATE TRIGGER update_your_table_name_updated_at
            BEFORE UPDATE ON your_table_name
            FOR EACH ROW
            EXECUTE PROCEDURE update_updated_at_column();
    """)

def downgrade() -> None:
    # Remove the trigger first
    op.execute("DROP TRIGGER IF EXISTS update_your_table_name_updated_at ON your_table_name")
    
    # Then drop the table
    op.drop_table("your_table_name")

Example Table Schema

Adding a Column

def upgrade() -> None:
    op.add_column("your_table_name", sa.Column("new_column", sa.String(100), nullable=True))

def downgrade() -> None:
    op.drop_column("your_table_name", "new_column")

Column Addition Visualization

Modifying a Column

def upgrade() -> None:
    op.alter_column("your_table_name", "existing_column", 
                   existing_type=sa.String(50),
                   type_=sa.String(100),
                   nullable=False)

def downgrade() -> None:
    op.alter_column("your_table_name", "existing_column", 
                   existing_type=sa.String(100),
                   type_=sa.String(50),
                   nullable=True)

Adding a Foreign Key

def upgrade() -> None:
    op.add_column("your_table_name", sa.Column("parent_id", postgresql.UUID(), nullable=True))
    op.create_foreign_key(
        "fk_your_table_name_parent",
        "your_table_name", "parent_table", 
        ["parent_id"], ["id"],
        ondelete="CASCADE"
    )

def downgrade() -> None:
    op.drop_constraint("fk_your_table_name_parent", "your_table_name", type_="foreignkey")
    op.drop_column("your_table_name", "parent_id")

Relationship Visualization

Inserting Seed Data

def upgrade() -> None:
    # Generate a UUID for new record
    new_id = str(uuid4())
    
    op.execute(f"""
        INSERT INTO your_table_name (id, name, description, organization_id, created_at, updated_at)
        VALUES (
            '{new_id}',
            'Default Item',
            'This is a default item created by migration',
            'your-org-id',
            CURRENT_TIMESTAMP,
            CURRENT_TIMESTAMP
        )
    """)

def downgrade() -> None:
    op.execute("""
        DELETE FROM your_table_name
        WHERE name = 'Default Item'
    """)

Best Practices

General Guidelines

1. One migration per change: Create separate migrations for distinct changes to make them easier to understand and maintain
2. Test migrations: Test both upgrade and downgrade paths before applying to production
3. Be concise but descriptive: Use clear naming conventions that indicate what each migration does
4. Comment your code: Add comments to complex migration logic to explain the purpose

Database Changes

1. Non-destructive changes: Prefer adding over dropping where possible
2. Nullable columns: When adding new columns to existing tables, make them nullable or provide a default value
3. Data migrations: Separate schema changes from data migrations when possible
4. Indexes: Add appropriate indexes for foreign keys and frequently queried columns
5. Updated_at triggers: Add triggers for updated_at columns as demonstrated in the example migrations

Migration Decision Flow

SQL Execution

1. Use SQLAlchemy operations: Prefer Alembic’s operation helpers (op.*) over raw SQL when possible
2. Transaction safety: Ensure your migrations are transaction-safe
3. Idempotence: Make migrations idempotent where possible to avoid errors on reapplication
4. Performance: Be mindful of migration performance on large tables; consider batch operations

Version Control

1. Commit migrations: Always commit migration files to version control
2. Do not edit applied migrations: Create new migrations rather than modifying existing ones that have been applied

Troubleshooting

Common Issues

1. Autogeneration misses changes: Alembic can’t detect all types of changes. Always review auto-generated migrations.
2. Migration conflicts: If multiple developers create migrations simultaneously, conflicts may occur. Coordinate migrations or adjust revision chains as needed.
3. Database connection issues: Verify your database connection settings in alembic.ini.
4. SQLAlchemy model not detected: Ensure your model is imported in the models __init__.py file.

Troubleshooting Flow

Fixing a Failed Migration

If a migration fails midway:

1. Fix the issue in the migration script
2. Run alembic upgrade head again, or specifically target the failed migration

If you need to revert a partial migration:

1. Manually fix the database state if necessary
2. Mark the current revision: alembic stamp <last_successful_revision>
3. Then try upgrading again

Migration Workflow Summary

1. Make changes to SQLAlchemy models
2. Generate a migration with alembic revision --autogenerate -m "description"
3. Review and edit the generated migration file if necessary
4. Rename the file to follow the sequential numbering convention
5. Test the migration in a development environment
6. Apply the migration with alembic upgrade head
7. Commit the migration file to version control

Complete Migration Lifecycle

By following this guide and examining existing migrations, you’ll be able to create and manage database migrations effectively in the Definable backend.