Skip to main content

Error: 'connected database is not clean'

Question​

Why does atlas migrate diff fail with the following error after running atlas migrate apply?

Error: sql/migrate: connected database is not clean: found table "atlas_schema_revisions" in schema <schema_name>. baseline version or allow-dirty is required

Answer​

This error occurs when the wrong database is used as a dev database (specified with --dev-url) when running migrate diff. The dev database is meant to be a temporary (and empty), locally-run database. It is used by Atlas to parse, validate, and analyze SQL definitions and is cleaned up after the process.

To simplify this process, Atlas can spin up an ephemeral local Docker container using the Docker driver, apply the statements from the migration directory to it (if there is one), determine the migrations needed to get to the desired schema, and clean up the dev database 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"

This is different from the database URL (specified with --url) used when running schema inspect or migrate apply, which is the URL to the database you intend to migrate — the same database used by the application:

# For a single database schema, use the "public" schema as the search path.
--url "postgres://user:pass@localhost:5432/db_name?sslmode_disable&search_path=public"

# For multiple database schemas:
--url "postgres://user:pass@localhost:5432/db_name?sslmode_disable"

If the database URL is used as the dev database because your actual database could have data in it (the database is "not clean") that can lead to inaccurate results during the diffing process, so Atlas stops it and prints the "not clean" error.

For more details on the two different database URLS, refer to the Atlas dev-database documentation and Atlas URL documentation.