Comment by pounderstanding

> This implies somebody with admin rights makes alterations in ad-hoc way without first doing it in test env.

Not necessarily. With a large team/org using the same database schema, it can just mean multiple people were trying to make changes to an environment around the same time, e.g. the migrations were applied in a different order in staging vs prod.

Some migration tools provide extra checks for strict ordering, but many do not. There's often no guarantee that the migration file naming scheme ordering, Git commit ordering, and actual DB apply ordering line up -- that's 3 different possible sources of truth, or more since the DB state varies by environment (dev/stage/prod etc).

Late-night hot-fixes (to solve an emergency outage) can be another source of inconsistencies / drift.

> If they continue with adhoc stuff, then it means auto auto-generated migrations will be different in test vs prod

That depends on the declarative tool and whether it fully syncs the schema each time, or just generates migrations which are frozen into a plan which is executed as-is in all environments. Not that full-sync is bad, but yes in that case it will generate different things in each env. Although the end result is that it will solve the drift, and give you the same end state in all environments. And that's likely what you want to happen: after running the tool, the database state will match the desired state which was expressed by the CREATE statements in your schema repo.

That said, the declarative tooling should have sufficient safety checks to ensure it doesn't do anything destructive in prod without very loudly telling you and requiring manual confirmation. That way, you won't be harmed when trying to synchronize an environment that had unexpected out-of-band changes.