ecto-migration-helper

name	ecto-migration-helper
description	Create, manage, and safely run Ecto database migrations with proper rollback handling and best practices. Use when working with database schema changes, adding columns, or modifying constraints.
allowed-tools	Bash, Read, Edit, Write

Ecto Migration Helper

This skill helps create and manage Ecto migrations safely with proper patterns and rollback support.

When to Use

• Creating new migrations
• Modifying existing tables
• Adding/removing indexes
• Changing constraints
• Data migrations
• Rolling back migrations

Creating Migrations

Generate Empty Migration

mix ecto.gen.migration add_email_to_users

Creates: priv/repo/migrations/TIMESTAMP_add_email_to_users.exs

Migration Naming Conventions

• create_table_name - Creating new table
• add_field_to_table - Adding column
• remove_field_from_table - Removing column
• add_index_to_table_on_field - Adding index
• modify_field_in_table - Changing column type
• add_constraint_to_table - Adding constraint

Common Migration Patterns

Adding a Column

defmodule MyApp.Repo.Migrations.AddEmailToUsers do
  use Ecto.Migration

  def change do
    alter table(:users) do
      add :email, :string
    end
  end
end

Adding Column with Default

def change do
  alter table(:users) do
    add :active, :boolean, default: true, null: false
  end
end

Adding Column with Index

def change do
  alter table(:users) do
    add :email, :string
  end

  create unique_index(:users, [:email])
end

Adding Foreign Key

def change do
  alter table(:posts) do
    add :user_id, references(:users, on_delete: :delete_all), null: false
  end

  create index(:posts, [:user_id])
end

Removing a Column

def change do
  alter table(:users) do
    remove :old_field
  end
end

WARNING: Removing columns is irreversible with change. Use up/down:

def up do
  alter table(:users) do
    remove :old_field
  end
end

def down do
  alter table(:users) do
    add :old_field, :string
  end
end

Modifying Column Type

def change do
  alter table(:products) do
    modify :price, :decimal, precision: 10, scale: 2
  end
end

Renaming Column

def change do
  rename table(:users), :username, to: :name
end

Adding Composite Index

def change do
  create index(:posts, [:user_id, :published_at])
end

Adding Unique Constraint

def change do
  create unique_index(:users, [:email])
  create unique_index(:users, [:organization_id, :email])  # Composite unique
end

Adding Check Constraint

def change do
  create constraint(:products, :price_must_be_positive, check: "price > 0")
end

Safe Migration Patterns

Making Columns NOT NULL

WRONG (will fail if existing NULLs):

def change do
  alter table(:users) do
    modify :email, :string, null: false  # FAILS!
  end
end

RIGHT (two-step approach):

# Migration 1: Add default, fill NULLs
def change do
  # Set default for new rows
  alter table(:users) do
    modify :email, :string, default: "[e-mail]"
  end

  # Fill existing NULLs
  execute(
    "UPDATE users SET email = '[e-mail]' WHERE email IS NULL",
    ""  # No rollback needed
  )
end

# Migration 2: Add NOT NULL constraint
def change do
  alter table(:users) do
    modify :email, :string, null: false
  end
end

Removing Columns Safely

Step 1: Deploy code that doesn't use the column Step 2: Run migration to remove column (after deployment)

# Deploy this migration AFTER code no longer references the field
def up do
  alter table(:users) do
    remove :old_field
  end
end

def down do
  alter table(:users) do
    add :old_field, :string  # Specify type for rollback
  end
end

Large Data Migrations

Use batching to avoid locking:

def up do
  execute """
  UPDATE users
  SET status = 'active'
  WHERE status IS NULL
  AND id IN (SELECT id FROM users WHERE status IS NULL LIMIT 1000)
  """

  # Repeat in batches or use recursive function
end

Data Migrations

Backfilling Data

defmodule MyApp.Repo.Migrations.BackfillUserDefaults do
  use Ecto.Migration
  import Ecto.Query
  alias MyApp.Repo
  alias MyApp.Accounts.User

  def up do
    # Use application code in migrations carefully
    User
    |> where([u], is_nil(u.status))
    |> Repo.update_all(set: [status: "active"])
  end

  def down do
    # Usually no rollback for data migrations
    :ok
  end
end

Complex Data Migration (Separate Module)

defmodule MyApp.Repo.Migrations.MigrateUserData do
  use Ecto.Migration

  def up do
    MyApp.ReleaseTasks.migrate_user_data()
  end

  def down do
    :ok
  end
end

# In lib/my_app/release_tasks.ex
defmodule MyApp.ReleaseTasks do
  def migrate_user_data do
    # Complex logic here
  end
end

Running Migrations

Development

# Run all pending migrations
mix ecto.migrate

# Run to specific version
mix ecto.migrate --to 20250101120000

# Rollback last migration
mix ecto.rollback

# Rollback last 3 migrations
mix ecto.rollback --step 3

# Rollback to specific version
mix ecto.rollback --to 20250101120000

Test Environment

# Create test database
MIX_ENV=test mix ecto.create

# Run migrations in test
MIX_ENV=test mix ecto.migrate

# Reset test database (drop, create, migrate)
MIX_ENV=test mix ecto.reset

Production

# Run on production (typically via release task)
bin/my_app eval "MyApp.ReleaseTasks.migrate()"

# Or if mix is available
MIX_ENV=prod mix ecto.migrate

Migration Status

# Check migration status
mix ecto.migrations

# Output shows:
# Status    Migration ID    Migration Name
# --------------------------------------------------
# up        20250101120000  create_users
# up        20250101130000  add_email_to_users
# down      20250101140000  add_profile_to_users

Reversible vs Non-Reversible

Reversible (use change)

• Adding columns
• Creating tables
• Adding indexes
• Adding references

Non-Reversible (use up/down)

• Removing columns (data loss)
• execute() with SQL
• Data transformations
• Dropping tables

Best Practices

1. One Logical Change Per Migration

# Good: Focused migration
mix ecto.gen.migration add_email_to_users

# Bad: Multiple unrelated changes
mix ecto.gen.migration update_users_and_posts_and_comments

2. Always Add Indexes for Foreign Keys

add :user_id, references(:users)
create index(:posts, [:user_id])  # Always add this!

3. Specify on_delete for Foreign Keys

# Be explicit about cascade behavior
add :user_id, references(:users, on_delete: :delete_all)  # Cascade
add :user_id, references(:users, on_delete: :nilify_all)  # Set NULL
add :user_id, references(:users, on_delete: :restrict)    # Prevent delete
add :user_id, references(:users, on_delete: :nothing)     # No action

4. Use Precision for Decimals

# Good
add :price, :decimal, precision: 10, scale: 2

# Bad (database decides precision)
add :price, :decimal

5. Make Constraints Explicit

# Email should be unique and not null
add :email, :string, null: false
create unique_index(:users, [:email])

6. Test Rollbacks Locally

# After creating migration
mix ecto.migrate
mix ecto.rollback
mix ecto.migrate

Troubleshooting

Migration Fails

Column already exists:

# Check current schema
mix ecto.migrations

# Drop and recreate if in development
mix ecto.drop && mix ecto.create && mix ecto.migrate

Can't rollback:

• Check if migration uses change vs up/down
• Review the migration for non-reversible operations
• May need to write custom down function

Lock timeout:

# Add timeout to migration
@disable_ddl_transaction true  # For operations that can't run in transaction
@disable_migration_lock true   # For long-running migrations

def change do
  # Migration code
end

Data Migration Issues

Timeout on large tables:

• Use batching
• Consider running outside of migration (Rails-style rake task)
• Use @disable_ddl_transaction true

References to application code:

• Be careful with schema changes
• Application code might change, migration won't
• Consider using raw SQL for data migrations

Advanced Patterns

Concurrent Index Creation (PostgreSQL)

@disable_ddl_transaction true

def change do
  create index(:posts, [:user_id], concurrently: true)
end

Conditional Migrations

def change do
  if function_exported?(MyApp.Repo, :__adapter__, 0) do
    # Migration code
  end
end

Timestamps Helper

create table(:users) do
  add :name, :string
  timestamps()  # Adds inserted_at and updated_at
end