Choose between change/0 and explicit up/down migrations for irreversible Ecto schema changes

Answer the question first

When a migration contains any step that cannot be auto‑reversed—raw SQL, data backfills, or custom transformations—use an explicit up/0 and down/0 pair. change/0 is safe only for pure, reversible schema changes that Ecto can invert automatically.

Why explicit up/0 / down/0 gives clearer rollback semantics

• The down/0 block is written by you, so you can design it to undo every step, even when the inverse is not a simple Ecto command.
• It can be made idempotent and tested independently, which is essential when a migration fails partway and you need to retry.
• When the database adapter does not support transactional DDL (MySQL, MariaDB), a failed migration can leave partial schema objects that only a custom down/0 can clean up.

How adapter transactionality changes the safety of each style

1. PostgreSQL – DDL is transactional. A change/0 that fails will roll back the entire migration, leaving the database in the pre‑migration state. However, if you do execute/1 with raw SQL that performs non‑transactional data changes, the rollback will still raise, and you must rely on down/0 to revert.
2. MySQL / MariaDB – Most DDL statements are non‑transactional. A change/0 that fails can leave a partially created table or column. Even if you write a down/0, the DDL may have already been committed, so you must manually drop the artifact before retrying.
3. SQLite – Supports transactional DDL in recent versions, but raw SQL can still cause partial changes. Treat it like PostgreSQL for safety.

When to prefer explicit up/0 / down/0

• Any irreversible data transformation (e.g., data migration, encryption, de‑duplication).
• Raw SQL that cannot be expressed as a reversible Ecto command.
• Deployments to adapters without transactional DDL or where @disable_ddl_transaction is used.
• When you want the rollback to be idempotent and safe to run multiple times.

Practical steps for the current project

1. Identify all migrations that contain raw SQL or data changes.
2. For each, replace change/0 with a pair of up/0 and down/0 functions.
3. Test mix ecto.rollback on a fresh clone of the database to confirm the down/0 path restores the schema and data to the previous state.
4. For PostgreSQL migrations, you can optionally keep change/0 for simple reversible schema changes; for MySQL, avoid change/0 unless you are certain the DDL is transactional.

Missing diagnostic detail

Do you use @disable_ddl_transaction or any adapter‑specific pragma that disables DDL transactions in your migrations? That detail can influence whether a change/0 will truly roll back atomically.