Ecto + PostgreSQL: Should Ecto expose a helper for automatic schema drift detection after restore?

Direct answer

Yes – Ecto could provide an optional helper that compares a restored PostgreSQL schema with the code‑defined schema and reports any drift before the application starts. Making it opt‑in avoids unnecessary overhead for users who do not need the check.

Confirmed facts

• Ecto currently has no built‑in mechanism to compare the restored database schema with the schema modules; developers must write custom scripts or inspect the migrations table manually.
• A pg_dump restore that predates recent migrations can leave the migrations table out of sync, causing runtime errors or data inconsistencies.
• Ecto 3.x exposes a Migration module for introspection, while Ecto 2.x relies only on the migrations table.

Likely explanation of how a helper could work

A drift‑detection helper would:

1. Load the application’s Repo and collect all schema modules (MyApp.Accounts.User, etc.).
2. Query PostgreSQL’s information_schema for tables, columns, data types, nullability, and default values.
3. Diff the information‑schema results against the fields defined in each schema module (ignoring associations and virtual fields).
4. Report mismatches as warnings (e.g., missing column, type change, extra column).

Integration steps

1. Add a new Mix task, e.g., mix ecto.detect_drift, that performs the steps above and exits with a non‑zero status if any drift is found.
2. Optionally extend mix ecto.migrate with a flag like --check-drift that runs the detect task before executing migrations.
3. For Ecto 2.x compatibility, fall back to inspecting the migrations table when the Migration module is unavailable; for 3.x use the module to list applied migrations and improve accuracy.
4. In CI pipelines, run mix ecto.detect_drift against a restored staging database to catch drift early.

Missing diagnostic detail that could change the recommendation

Whether the helper should halt application startup on drift (hard failure) or merely log a warning. If a hard failure is required, the helper would need to be invoked early in the supervision tree; otherwise a simple Mix task suffices.