Migrations

Changing a TableBase class does not change its table in Postgres. A migration records the SQL needed to bring the database schema up to date. Iceaxe can generate that migration by comparing your models with the database.

To run migrations from the command line, wrap Iceaxe's migration functions with your CLI library. This example uses Click. Start with the project name from pyproject.toml and a database connection helper. The credentials below are examples; load yours from environment variables.

# cli.py
import asyncio
import asyncpg
from click import command, option

from iceaxe import DBConnection
from iceaxe.migrations.cli import handle_generate, handle_apply, handle_rollback

from myproject import models # noqa: F401

PROJECT = "myproject"

async def get_connection():
    return DBConnection(
        await asyncpg.connect(
            host="localhost",
            port=5432,
            user="db_user",
            password="yoursecretpassword",
            database="your_db",
        )
    )

Add commands to generate a migration, apply pending migrations, and roll back the latest one:

@command()
@option("--message", help="A message to attach to the migration")
def generate(message: str | None):
    async def _inner():
        await handle_generate(PROJECT, await get_connection(), message=message)
    asyncio.run(_inner())

@command()
def apply():
    async def _inner():
        await handle_apply(PROJECT, await get_connection())
    asyncio.run(_inner())

@command()
def rollback():
    async def _inner():
        await handle_rollback(PROJECT, await get_connection())
    asyncio.run(_inner())

Register those commands in pyproject.toml so you can call them with uv run:

[project.scripts]
migrate-generate = "myproject.cli:generate"
migrate-apply = "myproject.cli:apply"
migrate-rollback = "myproject.cli:rollback"

Automatic local schema sync

For local development, DBConnection.magic_migrate() combines generation and application into a single call:

await conn.magic_migrate("myproject")

# Limit the sync to specific tables
await conn.magic_migrate("myproject", models=[Employee, Office])

# Add a message to the generated revision
await conn.magic_migrate("myproject", message="Add office capacity column")

magic_migrate() will:

  • introspect the current database schema
  • generate a new migration file only when the schema actually changed
  • apply any pending migrations, including the one it just created

Use this for local branches and prototypes. Review and commit the generated migration files before applying them to production.

Ignoring externally managed tables

If your database contains tables that Iceaxe should leave alone, you can exclude them during migration generation:

await handle_generate(
    PROJECT,
    await get_connection(),
    ignore_tables=["legacy_audit_log", "spatial_ref_sys"],
)

Ignored tables are removed from both the in-memory model comparison and the live database introspection step. This is useful when your application shares a database with extensions, third-party tools, or manually managed tables.

Generating migrations

Suppose the database already has an employee table with an auto-incrementing ID and a name:

Column NameData TypeConstraints
idintPRIMARY KEY
namevarchar

To add an age for each employee, update the TableBase definition:

from iceaxe import TableBase, Field

class Employee(TableBase):
    id: int | None = Field(primary_key=True, default=None)
    name: str
    age: int  # new field

Running migrate-generate will:

  • Use the database connection to introspect the current schema
  • Compare it with your model definitions
  • Generate a migration file that will add the age column to the employee table
$ uv run migrate-generate

Generating migration to current schema
New migration added: rev_1729278706.py

It places the migration file in your myproject/migrations directory:

from iceaxe.migrations.migrator import Migrator
from iceaxe.migrations.migration import MigrationRevisionBase
from iceaxe.schemas.actions import ColumnType

class MigrationRevision(MigrationRevisionBase):
    """
    Migration auto-generated on 2024-10-18T12:11:46.941324.

    Context: None

    """
    up_revision: str = "1729278608"
    down_revision: str | None = None

    async def up(self, migrator: Migrator):
        await migrator.actor.add_column(table_name="employee", column_name="age", explicit_data_type=ColumnType.INTEGER, explicit_data_is_list=False, custom_data_type=None)
        await migrator.actor.add_not_null(table_name="employee", column_name="age")

    async def down(self, migrator: Migrator):
        await migrator.actor.drop_column(table_name="employee", column_name="age")

up applies the schema change; down reverses it. Here, rolling back drops the age column and its data.

Review the generated migration before applying it. Existing employees need an age before the new column can become NOT NULL, so add your data update between creating the column and adding the constraint:

async def up(self, migrator: Migrator):
    await migrator.actor.add_column(table_name="employee", column_name="age", explicit_data_type=ColumnType.INTEGER, explicit_data_is_list=False, custom_data_type=None)

    # Insert known ages for employees
    await migrator.db_connection.exec(
        ...
    )

    await migrator.actor.add_not_null(table_name="employee", column_name="age")

Column Type Changes and Autocast

Changing a column's type generates a modify_column_type migration with autocast=True. Iceaxe adds a SQL cast so Postgres can convert existing values to the new type.

Automatic Type Conversion

For example, change a price stored as text to an integer:

# Before
class Product(TableBase):
    id: int = Field(primary_key=True)
    price: str  # Originally stored as text

# After  
class Product(TableBase):
    id: int = Field(primary_key=True)
    price: int  # Now we want it as an integer

Iceaxe will generate a migration like this:

async def up(self, migrator: Migrator):
    await migrator.actor.modify_column_type(
        table_name="product", 
        column_name="price", 
        explicit_data_type=ColumnType.INTEGER,
        autocast=True  # Automatically added
    )

autocast=True adds a USING clause to the type change. Supported conversions include:

  • String to numeric types ("123" → 123)
  • String to boolean ("true" → True)
  • String to date/time types ("2023-01-01" → date(2023, 1, 1))
  • String to UUID, JSON, and other specialized types
  • Scalar to array types (42 → [42])
  • Custom enum conversions

Customizing Autocast Behavior

Edit the generated migration when your data needs a custom conversion. autocast=False omits the USING clause, so Postgres will accept only type changes it can perform without an explicit cast:

async def up(self, migrator: Migrator):
    # Disable autocast for manual control
    await migrator.actor.modify_column_type(
        table_name="product", 
        column_name="price", 
        explicit_data_type=ColumnType.INTEGER,
        autocast=False
    )
    
    # Or add custom data transformation logic
    await migrator.db_connection.conn.execute("""
        UPDATE product 
        SET price = CASE 
            WHEN price ~ '^[0-9]+$' THEN price::integer
            ELSE 0
        END
    """)

Supported Type Conversions

The autocast feature supports most common PostgreSQL type conversions:

From TypeTo TypeExample
VARCHAR/TEXTINTEGER/BIGINT"123" → 123
VARCHAR/TEXTBOOLEAN"true" → true
VARCHAR/TEXTDATE/TIMESTAMP"2023-01-01" → 2023-01-01
VARCHAR/TEXTUUID"550e8400-..." → UUID
VARCHAR/TEXTJSON/JSONB'{"key":"value"}' → JSON
VARCHAR/TEXTCustom Enums"ACTIVE" → enum value
Scalar TypesArray Types42 → [42]
Compatible numeric typesDirect conversion123 → 123.0

Migration Safety

Automatic casting does not guarantee that every stored value can be converted. Before a production type change:

  1. Review generated migrations before applying them to production
  2. Test migrations on a copy of your production data first
  3. Back up your database before applying type-changing migrations
  4. Consider data validation - autocast will fail if data cannot be converted

If you have data that cannot be automatically converted (e.g., non-numeric strings when converting to integers), the migration will fail and you'll need to clean up the data first or provide custom conversion logic.

Applying migrations

After reviewing the migration, run migrate-apply. It applies all pending revisions in your migrations folder, in order:

$ uv run migrate-apply

🚀 Applied 1729278608 in 0.02s

Undoing migrations

Run migrate-rollback to execute the most recent migration's down method. Each call rolls back one revision:

$ poetry run migrate-rollback

🪃 Rolled back migration to None in 0.01s

Advanced Migration Patterns

On a busy database, a schema change can block application queries while it waits for or holds a lock. Plan the migration around the locks it needs and how long its data updates will take.

When to Disable Transactions

Iceaxe wraps each migration in a transaction by default. If a step fails, Postgres rolls back the transaction's changes. Set use_transaction = False only when you need to run operations separately:

Busy tables: A schema change can hold locks until the transaction ends. Separating operations can shorten that time, though each operation still needs its own locks. An ACCESS EXCLUSIVE lock waiting behind a long query can also block later queries in the lock queue.

Concurrent index creation: PostgreSQL does not allow CREATE INDEX CONCURRENTLY inside a transaction block.

Long data updates: Committing batches separately can avoid holding one transaction open for the entire update.

Set the flag on the migration class:

class MigrationRevision(MigrationRevisionBase):
    up_revision: str = "1729278608"
    down_revision: str | None = None
    
    use_transaction: bool = False  # No transaction wrapper
    
    async def up(self, migrator: Migrator):
        # This migration runs without a transaction
        await migrator.actor.create_index_concurrently(
            table_name="employee", 
            index_name="idx_employee_email",
            columns=["email"]
        )

Warning: Without the transaction wrapper, completed steps remain applied if a later step fails. Plan how to resume or undo those partial changes before running the migration.

The NOT NULL Constraint Problem

Adding a required field to a large users table needs two operations: filling existing rows and checking that none are NULL. The check can block traffic.

Don't do this on a hot table:

async def up(self, migrator: Migrator):
    # This will lock your entire table during validation!
    await migrator.actor.add_column(table_name="users", column_name="department", explicit_data_type=ColumnType.TEXT)
    await migrator.actor.add_not_null(table_name="users", column_name="department")

Unless an existing validated constraint proves the column has no NULL values, adding NOT NULL requires a table scan under an ACCESS EXCLUSIVE lock. That lock blocks reads and writes while Postgres checks the rows.

Do this instead:

class MigrationRevision(MigrationRevisionBase):
    use_transaction: bool = False  # We'll handle this manually
    
    async def up(self, migrator: Migrator):
        # Step 1: Add the column as nullable (fast)
        await migrator.actor.add_column(
            table_name="users", 
            column_name="department", 
            explicit_data_type=ColumnType.TEXT
        )
        
        # Step 2: Add a CHECK constraint without validation (very fast)
        await migrator.raw_sql("""
            ALTER TABLE users 
            ADD CONSTRAINT users_department_not_null 
            CHECK (department IS NOT NULL) NOT VALID
        """)
        
        # Step 3: Backfill existing data (can be done in batches)
        await migrator.raw_sql("""
            UPDATE users 
            SET department = 'Engineering' 
            WHERE department IS NULL
        """)
        
        # Step 4: Validate the constraint (scans table but allows reads/writes)
        await migrator.raw_sql("""
            ALTER TABLE users VALIDATE CONSTRAINT users_department_not_null
        """)
        
        # Step 5 (optional): Convert to a real NOT NULL constraint 
        # This step is only supported in PostgreSQL 12+
        await migrator.raw_sql("""
            ALTER TABLE users ALTER COLUMN department SET NOT NULL
        """)
        
        # Step 6: Clean up the check constraint
        await migrator.raw_sql("""
            ALTER TABLE users DROP CONSTRAINT users_department_not_null
        """)

The steps separate the long validation from the exclusive lock:

  • Step 1 adds a nullable column without rewriting existing rows
  • Step 2 takes an ACCESS EXCLUSIVE lock but skips scanning existing rows
  • Step 3 can be done in batches if needed, and you control the data being inserted
  • Step 4 scans the whole table but only needs a SHARE UPDATE EXCLUSIVE lock, so normal operations continue
  • Steps 5-6 are optional cleanup if you want a "real" NOT NULL constraint

Making Migrations Idempotent

If a migration runs without a transaction, a retry may encounter changes already applied by the first attempt. Make each step check or tolerate that state so rerunning it does not repeat the work.

Always check if changes already exist:

async def up(self, migrator: Migrator):
    # Check if column already exists before adding it
    result = await migrator.db_connection.conn.fetch("""
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name = 'users' AND column_name = 'department'
    """)
    
    if not result:
        await migrator.actor.add_column(
            table_name="users", 
            column_name="department", 
            explicit_data_type=ColumnType.TEXT
        )

Use IF NOT EXISTS when possible:

async def up(self, migrator: Migrator):
    # PostgreSQL has built-in idempotency for many operations
    await migrator.raw_sql("""
        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email 
        ON users (email)
    """)
    
    await migrator.raw_sql("""
        ALTER TABLE users 
        ADD CONSTRAINT IF NOT EXISTS users_email_unique 
        UNIQUE (email)
    """)

Handle data migrations carefully:

async def up(self, migrator: Migrator):
    # For data migrations, use UPDATE WHERE conditions
    # that won't affect already-migrated rows
    await migrator.raw_sql("""
        UPDATE users 
        SET status = 'active' 
        WHERE status IS NULL  -- Only update unmigrated rows
    """)
    
    # Or use INSERT ... ON CONFLICT for new data
    await migrator.raw_sql("""
        INSERT INTO user_preferences (user_id, theme) 
        SELECT id, 'dark' FROM users 
        WHERE id NOT IN (SELECT user_id FROM user_preferences)
    """)

Migration Deployment Strategies

For high-traffic applications, consider splitting complex migrations across multiple deployments:

Deploy 1: Add new columns, populate them

async def up(self, migrator: Migrator):
    await migrator.actor.add_column(table_name="orders", column_name="payment_method", explicit_data_type=ColumnType.TEXT)
    # Populate from existing data
    await migrator.raw_sql("UPDATE orders SET payment_method = 'credit_card' WHERE payment_method IS NULL")

Deploy 2: Add constraints once data is clean

async def up(self, migrator: Migrator):
    # Data is already populated, safe to add constraint
    await migrator.actor.add_not_null(table_name="orders", column_name="payment_method")

Deploy 3: Remove old columns after application is updated

async def up(self, migrator: Migrator):
    # Application code no longer references old_payment_field
    await migrator.actor.drop_column(table_name="orders", column_name="old_payment_field")

This "expand-migrate-contract" sequence lets old and new application versions coexist during deployment. Remove the old column only after every running version has stopped using it.

Monitoring Migration Performance

For a long data update, report progress between batches so you can see whether the migration is advancing:

async def up(self, migrator: Migrator):
    # Log progress for long operations
    await migrator.raw_sql("SELECT 'Starting user migration...'")
    
    start_time = time.time()
    
    # Batch large updates to avoid holding locks too long
    batch_size = 1000
    offset = 0
    
    while True:
        result = await migrator.db_connection.conn.execute("""
            UPDATE users 
            SET last_login = COALESCE(last_login, created_at)
            WHERE id >= $1 AND id < $2 AND last_login IS NULL
        """, offset, offset + batch_size)
        
        if result == "UPDATE 0":
            break
            
        offset += batch_size
        elapsed = time.time() - start_time
        print(f"Processed {offset} users in {elapsed:.2f}s")

You can also query PostgreSQL's pg_stat_progress_create_index view to monitor index creation progress, or pg_locks to see what locks your migration is holding.