atlas migrate diff fails with 'connected database is not clean' error

Why does atlas migrate diff fail with a "connected database is not clean" error after running atlas migrate apply?

Answer

The same database cannot not be used for both migrate apply and migrate diff. The dev-database (specified with --dev-url) for migrate diff is meant to be a temporary (and empty) database running locally. It is used by Atlas to parse, validate, and analyze SQL definitions and is cleaned up after the process.

On the other hand, the database used with migrate apply should be the one you intend to migrate — the same database used by the application.

To simplify this process, Atlas can spin up an ephemeral local Docker container using the special Docker driver and clean it up afterward. For example, to use a docker-based PostgreSQL 15 database for migrate diff, you can run:

# For a single database schema, use the "public" schema as the search path.
--dev-url "docker://postgres/15/dev?search_path=public"

# For multiple database schemas:
--dev-url "docker://postgres/15/dev"

For more details, refer to the Atlas dev-database documentation.