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.

Login to Atlas

By default, running atlas schema diff diffs only schemas, tables, and their associated indexes and constraints such as foreign keys and checks.

Views, materialized views, functions, procedures, triggers, sequences, domains, extensions, and additional features are available to Atlas Pro users. To include these resources in the schema diffing, use the following command:

atlas login

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.
  • --format (optional) - Go template to use to format the output.
  • --web (-w accepted as well)- visualize the schema diff as an ERD on Atlas Cloud. See an example here.

Diff Policy​

Atlas allows configuring the schema diffing policy in project configuration to fine-tune or modify suggested changes before they are printed:

To instruct Atlas to create and drop indexes concurrently, set the concurrent_index option in the diff block of the environment configuration. Note that such migrations are tagged with atlas:txmode none to ensure they do not run within a transaction.

atlas.hcl
env "local" {
  diff {
    // By default, indexes are not created or dropped concurrently.
    concurrent_index {
      create = true
      drop   = true
    }
  }
}

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"

Compare external schemas​

The schema diff command can also be used to compare external schemas defined in data sources, such as ORM schemas, with a database, HCL or SQL schemas, or even with other ORM schemas.

data "external_schema" "gorm" {
  program = [
    "go", "run", "-mod=mod",
    "ariga.io/atlas-provider-gorm",
    "load",
    "--path", "./path/to/models",
    "--dialect", "mysql",
  ]
}

data "external_schema" "sequelize" {
  program = [
    "npx",
    "@ariga/atlas-provider-sequelize",
    "load",
    "--path", "./path/to/models",
    "--dialect", "mysql",
  ]
}

env "drift" {
  dev = "docker://mysql/8/dev"
  # Variables defined and available with env:// prefix.
  gorm       = data.external_schema.gorm.url
  sequelize  = data.external_schema.sequelize.url
}
atlas schema diff \
  --env "drift" \
  --from "env://gorm" \
  --to "env://sequelize"

Indented SQL​

The schema diff command outputs a list of SQL statements without indentation by default. If you would like to view the SQL statements with indentation, use the --format flag. For example:

# Indent SQL statements with 2 spaces.
atlas schema diff \
  --from "mysql://user:pass@localhost:3306/example" \
  --to "mysql://user:pass@remote:3306/example" \
  --format '{{ sql . "  " }}'

Reference​

CLI Command Reference