Up and Down Methods — Complete Guide

In this tutorial, you will learn about Up and Down Methods. We cover key concepts, practical examples, and best practices to help you master this topic.

Learn up and down Migration methods: write upgrade functions for schema changes, implement downgrade for safe rollback, handle data transformations, and use reversible operations for cleaner migrations.

What You Learn

You will learn how to write effective upgrade and downgrade methods for database migrations, handle schema changes and data transformations, implement reversible operations, and avoid common pitfalls in migration logic.

Why It Matters

Badly written up methods cause data loss. Badly written down methods make rollback impossible. Understanding up/down patterns ensures your migrations are safe, reversible, and maintainable over years of schema evolution.

Real-World Use

DodaTech's migration review Process caught a migration that would have dropped a column with production data. The migration used a simple DROP COLUMN without a backup. The reviewer required a data backup step before the drop and a restore step in the down method.

Basic Up/Down Pattern

# Alembic up/down
def upgrade():
    op.add_column('users', sa.Column('phone', sa.String(20)))

def downgrade():
    op.drop_column('users', 'phone')

// Knex up/down
exports.up = function(knex) {
    return knex.schema.table('users', (table) => {
        table.string('phone', 20);
    });
};

exports.down = function(knex) {
    return knex.schema.table('users', (table) => {
        table.dropColumn('phone');
    });
};

Expected output: The upgrade function adds a column. The downgrade function removes it. Every operation in upgrade has a reverse operation in downgrade. The migration is fully reversible.

Adding Columns with Options

def upgrade():
    op.add_column('users', sa.Column(
        'phone',
        sa.String(20),
        nullable=True,          # Allow NULL for existing rows
        server_default=None,     # No default value
        comment='Primary phone number',
    ))
    op.add_column('users', sa.Column(
        'is_active',
        sa.Boolean(),
        nullable=False,
        server_default=sa.text('true'),  # Default for existing rows
    ))

def downgrade():
    op.drop_column('users', 'is_active')
    op.drop_column('users', 'phone')

Expected output: Adding a NOT NULL column requires a server_default for existing rows. Adding a nullable column is simpler. Downgrade drops columns in reverse order of addition.

Creating Tables

def upgrade():
    op.create_table(
        'orders',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('total', sa.Numeric(10, 2), nullable=False),
        sa.Column('status', sa.String(20), server_default='pending'),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
        sa.Column('updated_at', sa.DateTime(), onupdate=sa.func.now()),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], name='fk_orders_users'),
        sa.Index('idx_orders_user', 'user_id'),
        sa.Index('idx_orders_status', 'status'),
    )

def downgrade():
    op.drop_table('orders')

Expected output: Create table migration defines columns, constraints, foreign keys, and indexes. The downgrade drops the table. Foreign keys are created inline with the table.

Adding Foreign Keys

def upgrade():
    # Add the column first
    op.add_column('orders', sa.Column('user_id', sa.Integer(), nullable=True))

    # Add foreign key constraint
    op.create_foreign_key(
        'fk_orders_users',          # Constraint name
        'orders',                   # Source table
        'users',                    # Target table
        ['user_id'],                # Source columns
        ['id'],                     # Target columns
        ondelete='CASCADE',
    )

def downgrade():
    op.drop_constraint('fk_orders_users', 'orders', type_='foreignkey')
    op.drop_column('orders', 'user_id')

Expected output: Adding a foreign key requires the column to exist first. The constraint is created separately. Downgrade drops the constraint before the column.

Data Migrations in Up/Down

def upgrade():
    # Add the column
    op.add_column('users', sa.Column('full_name', sa.String(100)))

    # Backfill data (separate transaction recommended)
    op.execute("""
        UPDATE users
        SET full_name = COALESCE(first_name || ' ' || last_name, 'Unknown')
        WHERE full_name IS NULL
    """)

    # Make NOT NULL after backfill
    op.alter_column('users', 'full_name', nullable=False)

def downgrade():
    op.drop_column('users', 'full_name')

Expected output: Data migration adds a column, backfills data for existing rows, then sets NOT NULL. The downgrade drops the column. Backfill SQL handles NULL values from the initial add.

Renaming Columns

def upgrade():
    # Rename column (preserves data)
    op.alter_column('users', 'name', new_column_name='username')

def downgrade():
    op.alter_column('users', 'username', new_column_name='name')

// Knex rename
exports.up = function(knex) {
    return knex.schema.table('users', (table) => {
        table.renameColumn('name', 'username');
    });
};

exports.down = function(knex) {
    return knex.schema.table('users', (table) => {
        table.renameColumn('username', 'name');
    });
};

Expected output: Renaming a column preserves existing data without copying. The reverse operation renames back. Verify that no application code references the old column name before deploying.

Complex Down Migration

def upgrade():
    # Create new table
    op.create_table('orders_new',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('amount', sa.Numeric(10, 2), nullable=False),
    )

    # Copy data
    op.execute("INSERT INTO orders_new (id, user_id, amount) SELECT id, user_id, total FROM orders")

    # Drop old table
    op.drop_table('orders')

    # Rename new table
    op.rename_table('orders_new', 'orders')

def downgrade():
    # Reverse: create old structure, copy data back, drop new table
    op.create_table('orders_old',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('user_id', sa.Integer(), nullable=False),
        sa.Column('total', sa.Numeric(10, 2), nullable=False),
        sa.Column('status', sa.String(20)),
    )

    op.execute("INSERT INTO orders_old (id, user_id, total, status) SELECT id, user_id, amount, 'migrated' FROM orders")

    op.drop_table('orders')
    op.rename_table('orders_old', 'orders')

Expected output: Complex migrations create new structures, copy data, rename, and drop old structures. The downgrade reverses each step exactly. Data is preserved in both directions.

Common Mistakes

1. Missing Down Migration

Skipping the down migration makes rollback impossible. Always write the down migration. Test it on a copy of production data. The down migration should restore the exact previous schema.

2. Irreversible Operations

Dropping a column loses data. Renaming a table breaks references. Some operations are not fully reversible. Document data loss risks. Back up data before irreversible migrations.

3. NOT NULL Column Without Default

Adding a NOT NULL column to a table with existing rows causes the migration to fail. Existing rows have NULL for the new column. Add as nullable first, backfill data, then set NOT NULL.

4. Broken Down Migration

A down migration that fails leaves the database in an inconsistent state. Test both up and down. Verify the down migration produces the exact previous schema. Include data restoration if needed.

5. Forgetting to Remove Indexes

Indexes should be dropped in the down migration if they were created in the up migration. Orphaned indexes waste space and slow writes. Each operation in up must have a corresponding reverse in down.

Practice Questions

1. Why add a column as nullable before setting NOT NULL?

Existing rows have NULL for the new column. Setting NOT NULL immediately causes the migration to fail. Add as nullable, backfill data, then alter to NOT NULL.

2. What is the purpose of the downgrade function?

The downgrade reverts the schema changes made by the upgrade function. It enables rollback to the previous schema version. Every operation in upgrade must have a reverse operation in downgrade.

3. How do you handle data migration in up/down methods?

Add schema changes first, then run data transformation SQL. The down migration should reverse data transformations. For complex data changes, use separate data migration files.

4. What makes a migration irreversible?

Dropping columns or tables that contain data. Renaming without tracking old names. Operations that transform data in ways that cannot be reversed (hashing, encryption with new keys).

Challenge

Write a complete migration that: renames the total column to amount in the orders table, creates an orders_audit table, copies existing order data to the audit table, adds an index on amount, and has a fully reversible down migration that restores all data.

FAQ

Mini Project: Up/Down Migration Patterns

Create a set of example migrations demonstrating: add column (nullable and NOT NULL), create table with constraints, add foreign key, data migration with backfill, rename column, add/drop index, create/drop view, and a complex migration with data transformation. Each must have complete up and down functions.

What's Next

Now that you understand up/down methods, learn about Alembic for Python for managing migrations in Python applications.