Rollbacks — Complete Guide

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

Learn database Migration rollbacks: strategies for reverting schema changes safely in production. Understand down migrations, automated rollback scripts, rollback testing, and handling partial failures during rollback operations.

What You Learn

You will learn how rollbacks work in database migrations, how to write effective down migrations, how to automate rollback procedures in deployment pipelines, how to test rollbacks before production, and how to handle complex rollback scenarios like partial failures and irreversible migrations.

Why It Matters

Every migration can fail. A wrong column type, a missing default value, or a constraint violation can break the application. Rollbacks revert the schema to a known good state. Without rollbacks, a failed migration causes extended downtime while engineers manually reconstruct the previous schema.

Real-World Use

DodaTech's deployment pipeline includes automated rollback. When a migration to add a NOT NULL column failed because existing NULL values were missed, the pipeline detected the failure, triggered an automatic rollback, and reverted the schema within 30 seconds. The application continued running with the previous schema.

How Rollbacks Work

graph LR
    V1[Schema v1] -->|Up Migration| V2[Schema v2]
    V2 -->|Down Migration| V1
    V2 -->|Up Migration| V3[Schema v3]
    V3 -->|Down Migration| V2

Each migration has an up function (apply) and a down function (revert). The down function reverses exactly what the up function did. Running downgrade moves the schema back one step at a time.

The Down Migration

# Migration with up and down
"""Add phone column to users table."""

def upgrade():
    op.add_column('users', sa.Column('phone', sa.String(20), nullable=True))

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

-- Flyway-style down migration
-- V002__add_phone_to_users.sql

-- Up
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Down
ALTER TABLE users DROP COLUMN phone;

Expected output: The up migration adds the column. The down migration removes it. The down function is the exact inverse of the up function. Running downgrade reverts the schema to the state before the migration was applied.

Automated Rollback in CI/CD

# .gitlab-ci.yml with automated rollback
stages:
  - migrate
  - deploy
  - verify
  - rollback  # Automated rollback stage

migrate:
  stage: migrate
  script:
    - alembic upgrade head
  environment: production

deploy:
  stage: deploy
  script:
    - kubectl apply -f deployment.yaml
  environment: production
  needs: ["migrate"]

verify:
  stage: verify
  script:
    - python scripts/health_check.py
  environment: production
  needs: ["deploy"]

rollback:
  stage: rollback
  script:
    - python scripts/rollback.py
  environment: production
  when: on_failure  # Triggered when verify fails
  needs: ["deploy"]

Expected output: CI/CD pipeline includes an automated rollback stage. If verification fails after deployment, the rollback stage runs automatically. The application is reverted to the previous schema without manual intervention.

Rollback Script

# scripts/rollback.py
"""Automated rollback script for production."""
import subprocess
import sys
import time
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def rollback(steps=1):
    logger.info(f"Starting rollback of {steps} migration(s)...")

    try:
        # Step 1: Notify team
        notify_team(f"Rollback initiated: reverting {steps} migration(s)")

        # Step 2: Run downgrade
        result = subprocess.run(
            ['alembic', 'downgrade', f'-{steps}'],
            capture_output=True, text=True, check=True,
            timeout=300  # 5 minute timeout
        )
        logger.info(f"Downgrade output: {result.stdout}")

        # Step 3: Verify the rollback
        result = subprocess.run(
            ['alembic', 'current'],
            capture_output=True, text=True, check=True
        )
        current_version = result.stdout.strip()
        logger.info(f"Current schema version: {current_version}")

        # Step 4: Health check
        time.sleep(10)  # Wait for connections to stabilize
        health = subprocess.run(
            ['python', 'scripts/health_check.py'],
            capture_output=True, text=True
        )

        if health.returncode == 0:
            logger.info("Health checks passed after rollback")
            notify_team("Rollback completed successfully")
            return True
        else:
            logger.error(f"Health check failed: {health.stderr}")
            notify_team(f"Rollback completed but health check failed: {health.stderr}")
            return False

    except subprocess.TimeoutExpired:
        logger.error("Rollback timed out after 5 minutes")
        notify_team("Rollback timed out - manual intervention required")
        return False
    except subprocess.CalledProcessError as e:
        logger.error(f"Rollback failed: {e.stderr}")
        notify_team(f"Rollback failed: {e.stderr}")
        return False

def notify_team(message):
    """Send notification to team chat."""
    subprocess.run([
        'curl', '-X', 'POST',
        os.environ.get('SLACK_WEBHOOK_URL'),
        '-H', 'Content-Type: application/json',
        '-d', f'{{"text": "Database Rollback: {message}"}}'
    ], capture_output=True)

if __name__ == '__main__':
    steps = int(sys.argv[1]) if len(sys.argv) > 1 else 1
    success = rollback(steps)
    sys.exit(0 if success else 1)

Expected output: Rollback script downgrades the schema, verifies the current version, runs health checks, and notifies the team. It handles timeouts and errors gracefully. The script is triggered by the CI/CD pipeline on failure.

Testing Rollbacks

# Test rollback in CI pipeline
#!/bin/bash
set -euo pipefail

echo "=== Testing Migration Rollback ==="

# Step 1: Apply all migrations
echo "Applying all migrations..."
alembic upgrade head
echo "Schema at current version"

# Step 2: Record schema state
echo "Recording schema state..."
psql $DATABASE_URL -c "\dt" > schema_before_rollback.txt

# Step 3: Rollback one step
echo "Rolling back one migration..."
alembic downgrade -1
echo "Rollback completed"

# Step 4: Verify schema state
echo "Verifying schema after rollback..."
psql $DATABASE_URL -c "\dt" > schema_after_rollback.txt

# Step 5: Re-apply the migration
echo "Re-applying migration..."
alembic upgrade head
echo "Migration re-applied"

# Step 6: Compare schema states
echo "Comparing schema states..."
diff schema_before_rollback.txt schema_after_rollback.txt
if [ $? -eq 0 ]; then
    echo "PASS: Schema restored to previous state"
else
    echo "FAIL: Schema does not match expected state"
    exit 1
fi

echo "=== Rollback Test Passed ==="

Expected output: Rollback test applies all migrations, records schema state, downgrades one step, verifies the schema, re-applies, and compares. The schema before and after the rollback cycle should match exactly.

Handling Irreversible Migrations

# migrations/20260628_merge_names.py
"""Merge first_name and last_name into full_name (irreversible)."""

def upgrade():
    op.add_column('users', sa.Column('full_name', sa.String(200)))
    op.execute("""
        UPDATE users SET full_name = CONCAT(first_name, ' ', last_name)
    """)
    op.drop_column('first_name')
    op.drop_column('last_name')

def downgrade():
    # WARNING: Irreversible! Original first_name and last_name are lost.
    # The only option is to restore from backup.
    raise NotImplementedError(
        "This migration is irreversible. "
        "First_name and last_name columns have been dropped. "
        "To revert, restore the database from backup taken before this migration."
    )

Expected output: Irreversible migrations raise NotImplementedError in the down function. The error message explains why reversal is impossible and what to do instead. These migrations require a database backup before execution.

Partial Rollback Scenarios

// Handling partial rollback
async function rollbackWithCheckpoint(steps) {
    const applied = await getAppliedMigrations();

    for (let i = 0; i < steps; i++) {
        const migration = applied[applied.length - 1 - i];
        console.log(`Rolling back: ${migration.name}`);

        try {
            await runDownMigration(migration);
            console.log(`Rolled back: ${migration.name}`);
        } catch (err) {
            console.error(`Failed to rollback ${migration.name}:`, err);
            console.log('Migration state is now:');
            console.log(`  Rolled back: ${i} migration(s)`);
            console.log(`  Failed at: ${migration.name}`);
            console.log('Manual investigation required');
            throw err;
        }
    }
}

async function getAppliedMigrations() {
    const { rows } = await db.query(
        'SELECT version, name FROM _migrations ORDER BY applied_at DESC'
    );
    return rows;
}

Expected output: Partial rollback handles failures during the rollback Process itself. Each migration is rolled back individually. If one fails, the process stops and reports which migrations were reverted and which failed.

Common Mistakes

1. Not Writing Down Migrations

Migrations without down functions make rollback impossible. Always write down functions. They should be the exact inverse of the up function. Test down functions in CI.

2. Assuming Rollback Will Always Work

Rollbacks can fail: down functions have bugs, database constraints prevent reversal, or data changes cannot be undone. Always test rollbacks. Have a backup plan (database restore) for when rollbacks fail.

3. Rolling Back Multiple Migrations at Once

Rolling back many migrations in one command multiplies the risk. Each migration's down function runs sequentially. If one fails, the rollback is stuck partway. Rollback one migration at a time when possible.

4. No Rollback Testing in CI

Untested down migrations may have bugs or become incompatible with later schema states. Test downgrade in CI for every migration. Verify the schema returns to the exact previous state.

5. Manual Rollback Under Pressure

Running rollback commands manually during an incident leads to mistakes. Automate rollback in the CI/CD pipeline. Manual rollback should be a last resort with documented procedures.

6. Forgetting to Backup Before Rollback

Rollbacks can fail or cause data loss. Always take a database backup before running rollbacks in production. A backup provides a safety net if the rollback itself fails.

Practice Questions

1. What is the purpose of a down migration?

The down migration reverts the changes made by the up migration. It is the exact inverse operation. Running downgrade moves the schema back to the state before the migration was applied.

2. How should you handle irreversible migrations?

Document irreversibility in the migration file. Raise NotImplementedError in the down function. Take a database backup before running in production. Get team approval for irreversible changes.

3. Why should rollbacks be automated in CI/CD?

Automated rollbacks respond faster than humans during incidents. The rollback script is tested and reliable. Manual rollback under pressure is error-prone. Automation reduces downtime.

4. How do you test that a rollback works correctly?

Apply all migrations, record schema state, downgrade one step, verify schema matches expected previous state, re-apply migration, and compare final state to initial state. The schema should be identical after the up-down-up cycle.

Challenge

Build a rollback automation system that: detects migration failures by monitoring health checks, triggers automated rollback on failure, rolls back one migration at a time with verification, notifies the team via Slack with status details, supports manual approval gate before rollback, and generates a rollback report for post-mortem analysis.

FAQ

Mini Project: Rollback Framework

Build a rollback framework that: creates a backup before any production rollback, rolls back migrations one at a time with verification, runs health checks after each step, notifies the team on completion or failure, supports rollback approval gates, generates a rollback report with timestamps and status, and includes a dry-run mode for preview.

What's Next

Now that you understand rollbacks, learn about Squashing Migrations for reducing migration count and improving fresh setup performance.