ecto-migrator

name: ecto-migrator

description: "Generate Ecto migrations from natural language or schema descriptions. Handles tables, columns, indexes, constraints, references, enums, and partitioning. Supports reversible migrations, data migrations, and multi-tenant patterns. Use when creating or modifying database schemas, adding indexes, altering tables, creating enums, or performing data migrations in an Elixir project."

Ecto Migrator

Generating Migrations

From Natural Language

Parse the user's description and generate a migration file. Common patterns:

| User Says | Migration Action |

|-----------|-----------------|

| "Create users table with email and name" | create table(:users) with columns |

| "Add phone to users" | alter table(:users), add :phone |

| "Make email unique on users" | create unique_index(:users, [:email]) |

| "Add tenant_id to all tables" | Multiple alter table with index |

| "Rename status to state on orders" | rename table(:orders), :status, to: :state |

| "Remove the legacy_id column from users" | alter table(:users), remove :legacy_id |

| "Add a check constraint on orders amount > 0" | create constraint(:orders, ...) |

File Naming

mix ecto.gen.migration <name>

# Generates: priv/repo/migrations/YYYYMMDDHHMMSS_<name>.exs

Name conventions: create_<table>, add_<column>_to_<table>, create_<table>_<column>_index, alter_<table>_add_<columns>.

Migration Template

defmodule MyApp.Repo.Migrations.CreateUsers do

  use Ecto.Migration



  def change do

    create table(:users, primary_key: false) do

      add :id, :binary_id, primary_key: true

      add :email, :string, null: false

      add :name, :string, null: false

      add :role, :string, null: false, default: "member"

      add :metadata, :map, default: %{}

      add :tenant_id, :binary_id, null: false



      add :team_id, references(:teams, type: :binary_id, on_delete: :delete_all)



      timestamps(type: :utc_datetime_usec)

    end



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

    create index(:users, [:tenant_id])

    create index(:users, [:team_id])

  end

end

Column Types

See references/column-types.md for complete type mapping and guidance.

Key decisions:

• IDs: Use :binary_id (UUID) — set primary_key: false on table, add :id manually.

• Money: Use :integer (cents) or :decimal — never :float.

• Timestamps: Always timestamps(type: :utc_datetime_usec).

• Enums: Use :string with app-level Ecto.Enum — avoid Postgres enums (hard to migrate).

• JSON: Use :map (maps to jsonb).

• Arrays: Use {:array, :string} etc.

Index Strategies

See references/index-patterns.md for detailed index guidance.

When to Add Indexes

Always index:

• Foreign keys (_id columns)

• tenant_id (first column in composite indexes)

• Columns used in WHERE clauses

• Columns used in ORDER BY

• Unique constraints

Index Types

# Standard B-tree

create index(:users, [:tenant_id])



# Unique

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



# Partial (conditional)

create index(:orders, [:status], where: "status != 'completed'", name: :orders_active_status_idx)



# GIN for JSONB

create index(:events, [:metadata], using: :gin)



# GIN for array columns

create index(:posts, [:tags], using: :gin)



# Composite

create index(:orders, [:tenant_id, :status, :inserted_at])



# Concurrent (no table lock — use in separate migration)

@disable_ddl_transaction true

@disable_migration_lock true



def change do

  create index(:users, [:email], concurrently: true)

end

Constraints

# Check constraint

create constraint(:orders, :amount_must_be_positive, check: "amount > 0")



# Exclusion constraint (requires btree_gist extension)

execute "CREATE EXTENSION IF NOT EXISTS btree_gist", ""

create constraint(:reservations, :no_overlapping_bookings,

  exclude: ~s|gist (room_id WITH =, tstzrange(starts_at, ends_at) WITH &&)|

)



# Unique constraint (same as unique_index for most purposes)

create unique_index(:accounts, [:slug])

References (Foreign Keys)

add :user_id, references(:users, type: :binary_id, on_delete: :delete_all), null: false

add :team_id, references(:teams, type: :binary_id, on_delete: :nilify_all)

add :parent_id, references(:categories, type: :binary_id, on_delete: :nothing)

| on_delete | Use When |

|-------------|----------|

| :delete_all | Child can't exist without parent (memberships, line items) |

| :nilify_all | Child should survive parent deletion (optional association) |

| :nothing | Handle in application code (default) |

| :restrict | Prevent parent deletion if children exist |

Multi-Tenant Patterns

Every Table Gets tenant_id

def change do

  create table(:items, primary_key: false) do

    add :id, :binary_id, primary_key: true

    add :name, :string, null: false

    add :tenant_id, :binary_id, null: false

    timestamps(type: :utc_datetime_usec)

  end



  # Always composite index with tenant_id first

  create index(:items, [:tenant_id])

  create unique_index(:items, [:tenant_id, :name])

end

Adding tenant_id to Existing Tables

def change do

  alter table(:items) do

    add :tenant_id, :binary_id

  end



  # Backfill in a separate data migration, then:

  # alter table(:items) do

  #   modify :tenant_id, :binary_id, null: false

  # end

end

Data Migrations

Rule: Never mix schema changes and data changes in the same migration.

Safe Data Migration Pattern

defmodule MyApp.Repo.Migrations.BackfillUserRoles do

  use Ecto.Migration



  # Don't use schema modules — they may change after this migration runs

  def up do

    execute """

    UPDATE users SET role = 'member' WHERE role IS NULL

    """

  end



  def down do

    # Data migrations may not be reversible

    :ok

  end

end

Batched Data Migration (large tables)

def up do

  execute """

  UPDATE users SET role = 'member'

  WHERE id IN (

    SELECT id FROM users WHERE role IS NULL LIMIT 10000

  )

  """



  # For very large tables, use a Task or Oban job instead

end

Reversible vs Irreversible

Reversible (use change)

These are auto-reversible:

• create table ↔ drop table

• add column ↔ remove column

• create index ↔ drop index

• rename ↔ rename

Irreversible (use up/down)

Must define both directions:

• modify column type — Ecto can't infer the old type

• execute raw SQL

• Data backfills

• Dropping columns with data

def up do

  alter table(:users) do

    modify :email, :citext, from: :string  # from: helps reversibility

  end

end



def down do

  alter table(:users) do

    modify :email, :string, from: :citext

  end

end

Using modify with from:

Phoenix 1.7+ supports from: for reversible modify:

def change do

  alter table(:users) do

    modify :email, :citext, null: false, from: {:string, null: true}

  end

end

PostgreSQL Extensions

def change do

  execute "CREATE EXTENSION IF NOT EXISTS citext", "DROP EXTENSION IF EXISTS citext"

  execute "CREATE EXTENSION IF NOT EXISTS pgcrypto", "DROP EXTENSION IF EXISTS pgcrypto"

  execute "CREATE EXTENSION IF NOT EXISTS pg_trgm", "DROP EXTENSION IF EXISTS pg_trgm"

end

Enum Types (PostgreSQL native — use sparingly)

Prefer Ecto.Enum with :string columns. If you must use Postgres enums:

def up do

  execute "CREATE TYPE order_status AS ENUM ('pending', 'confirmed', 'shipped', 'delivered')"



  alter table(:orders) do

    add :status, :order_status, null: false, default: "pending"

  end

end



def down do

  alter table(:orders) do

    remove :status

  end



  execute "DROP TYPE order_status"

end

Warning: Adding values to Postgres enums requires ALTER TYPE ... ADD VALUE which cannot run inside a transaction. Prefer :string + Ecto.Enum.

Checklist

• Primary key: primary_key: false + add :id, :binary_id, primary_key: true

• null: false on required columns

• timestamps(type: :utc_datetime_usec)

• Foreign keys with appropriate on_delete

• Index on every foreign key column

• tenant_id indexed (composite with lookup fields)

• Unique constraints where needed

• Concurrent indexes in separate migration with @disable_ddl_transaction true

• Data migrations in separate files from schema migrations