Skip to main content

Comparing Schemas

It is sometimes useful to be able to calculate the diff between two schemas. For instance, as you are developing you may want to calculate how to move from an existing database to some other state that you are interested in. Alternatively, you may be diagnosing some issue and want to verify there is no difference between a local copy of a schema and a remote one.

To accommodate these types of use-cases, Atlas offers the schema diff that accepts two schema states: --from and --to, calculates the differences between them, and generates a plan of SQL statements that can be used to migrate the "from" schema to the state defined by the "to" schema. A state can be specified using a database URL, an HCL or SQL schema, or a migration directory.

Flags

  • --from - a list of URLs to the current state: can be a database URL, an HCL or SQL schema, or a migration directory.
  • --to - a list of URLs to the desired state: can be a database URL, an HCL or SQL schema, or a migration directory.
  • --dev-url - a URL to the Dev-Database.
  • --schema (optional, may be supplied multiple times) - schemas to inspect within the target database.
  • --exclude (optional, may be supplied multiple times) - filter out resources matching the given glob pattern.

Examples

Compare databases

atlas schema diff \
--from "mysql://user:pass@localhost:3306" \
--to "mysql://user:pass@remote:3306"

Compare database schemas

Compare two MySQL schemas/databases named example:

atlas schema diff \
--from "mysql://user:pass@localhost:3306/example" \
--to "mysql://user:pass@remote:3306/example"

Compare HCL schemas

atlas schema diff \
--from "file://schema1.hcl" \
--to "file://schema2.hcl" \
--dev-url "docker://mysql"

Compare SQL schemas

atlas schema diff \
--from "file://schema1.sql" \
--to "file://schema2.sql" \
--dev-url "docker://mysql/8/test"

If the DDL statements only include qualified tables (e.g., schema.table), you can omit the database name from the --dev-url:

atlas schema diff \
--from "file://schema1.sql" \
--to "file://schema2.sql" \
--dev-url "docker://mysql"

Compare migration directories

atlas schema diff \
--from "file://migrations1" \
--to "file://migrations2" \
--dev-url "docker://mysql/8/test"

If the DDL statements only include qualified tables (e.g., schema.table), you can omit the database name from the --dev-url:

atlas schema diff \
--from "file://migrations1" \
--to "file://migrations2" \
--dev-url "docker://mysql"

Compare SQL to HCL

atlas schema diff \
--from "file://schema.hcl" \
--to "file://schema.sql" \
--dev-url "docker://mysql/8/test"

Compare a migration directory to a database

atlas schema diff \
--from "file://migrations" \
--to "mysql://root:pass@:3306/example" \
--dev-url "docker://mysql/8/example"

Reference

CLI Command Reference