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.
This skill helps create and manage Ecto migrations safely with proper patterns and rollback support.
mix ecto.gen.migration add_email_to_users
Creates: priv/repo/migrations/TIMESTAMP_add_email_to_users.exs
create_table_name - Creating new tableadd_field_to_table - Adding columnremove_field_from_table - Removing columnadd_index_to_table_on_field - Adding indexmodify_field_in_table - Changing column typeadd_constraint_to_table - Adding constraintdefmodule MyApp.Repo.Migrations.AddEmailToUsers do
use Ecto.Migration
def change do
alter table(:users) do
add :email, :string
end
end
end
def change do
alter table(:users) do
add :active, :boolean, default: true, null: false
end
end
def change do
alter table(:users) do
add :email, :string
end
create unique_index(:users, [:email])
end
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
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
def change do
alter table(:products) do
modify :price, :decimal, precision: 10, scale: 2
end
end
def change do
rename table(:users), :username, to: :name
end
def change do
create index(:posts, [:user_id, :published_at])
end
def change do
create unique_index(:users, [:email])
create unique_index(:users, [:organization_id, :email]) # Composite unique
end
def change do
create constraint(:products, :price_must_be_positive, check: "price > 0")
end
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: "[email protected]"
end
# Fill existing NULLs
execute(
"UPDATE users SET email = '[email protected]' 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
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
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
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
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
# 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
# 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
# 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
# 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
change)up/down)# 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
add :user_id, references(:users)
create index(:posts, [:user_id]) # Always add this!
# 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
# Good
add :price, :decimal, precision: 10, scale: 2
# Bad (database decides precision)
add :price, :decimal
# Email should be unique and not null
add :email, :string, null: false
create unique_index(:users, [:email])
# After creating migration
mix ecto.migrate
mix ecto.rollback
mix ecto.migrate
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:
change vs up/downdown functionLock 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
Timeout on large tables:
@disable_ddl_transaction trueReferences to application code:
@disable_ddl_transaction true
def change do
create index(:posts, [:user_id], concurrently: true)
end
def change do
if function_exported?(MyApp.Repo, :__adapter__, 0) do
# Migration code
end
end
create table(:users) do
add :name, :string
timestamps() # Adds inserted_at and updated_at
end