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

bash

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

elixir

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

elixir

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

Adding Column with Index

elixir

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

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

Adding Foreign Key

elixir

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

elixir

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

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

elixir

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

elixir

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

Renaming Column

elixir

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

Adding Composite Index

elixir

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

Adding Unique Constraint

elixir

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

Adding Check Constraint

elixir

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):

elixir

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

RIGHT (two-step approach):

elixir

# Migration 1: Add default, fill NULLs
def change do
  # Set default for new rows
  alter table(:users) do
    modify :email, :string, default: "unknown@example.com"
  end

  # Fill existing NULLs
  execute(
    "UPDATE users SET email = 'unknown@example.com' 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)

elixir

# 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:

elixir

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

elixir

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)

elixir

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

bash

# 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

bash

# 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

bash

# 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

bash

# 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

bash

# 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

elixir

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

3. Specify on_delete for Foreign Keys

elixir

# 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

elixir

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

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

5. Make Constraints Explicit

elixir

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

6. Test Rollbacks Locally

bash

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

Troubleshooting

Migration Fails

Column already exists:

bash

# 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:

elixir

# 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)

elixir

@disable_ddl_transaction true

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

Conditional Migrations

elixir

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

Timestamps Helper

elixir

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

Ecto Migration Helper

When to Use

Creating Migrations

Generate Empty Migration

Migration Naming Conventions

Common Migration Patterns

Adding a Column

Adding Column with Default

Adding Column with Index

Adding Foreign Key

Removing a Column

Modifying Column Type

Renaming Column

Adding Composite Index

Adding Unique Constraint

Adding Check Constraint

Safe Migration Patterns

Making Columns NOT NULL

Removing Columns Safely

Large Data Migrations

Data Migrations

Backfilling Data

Complex Data Migration (Separate Module)

Running Migrations

Development

Test Environment

Production

Migration Status

Reversible vs Non-Reversible

Reversible (use change)

Non-Reversible (use up/down)

Best Practices

1. One Logical Change Per Migration

2. Always Add Indexes for Foreign Keys

3. Specify on_delete for Foreign Keys

4. Use Precision for Decimals

5. Make Constraints Explicit

6. Test Rollbacks Locally

Troubleshooting

Migration Fails

Data Migration Issues

Advanced Patterns

Concurrent Index Creation (PostgreSQL)

Conditional Migrations

Timestamps Helper

Reversible (use `change`)

Non-Reversible (use `up`/`down`)