# Ecto Migration Helper

> 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. Use when this capability is needed.

- Skill: `tomevault-io/ecto-migration-helper` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add tomevault-io/ecto-migration-helper`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/ecto-migration-helper/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: tomevault-io (https://skillmd.com/u/tomevault-io)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/tomevault-io/ecto-migration-helper

---


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

---
> Converted and distributed by [TomeVault](https://tomevault.io/claim/mkreyman) — claim your Tome and manage your conversions.
<!-- tomevault:4.0:skill_md:2026-04-11 -->