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.--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:
- Skip Destructive
- Concurrent Indexes
variable "destructive" {
type = bool
default = false
}
env "local" {
diff {
skip {
drop_schema = !var.destructive
drop_table = !var.destructive
}
}
}
The usage is as follows:
atlas schema diff --env "local" --var "destructive=true"
env "local" {
diff {
// By default, indexes are not created or dropped concurrently.
concurrent_index {
create = true
drop = true
}
}
}
Examples
Compare databases
- MySQL
- MariaDB
- PostgreSQL
- SQLite
atlas schema diff \
--from "mysql://user:pass@localhost:3306" \
--to "mysql://user:pass@remote:3306"
atlas schema diff \
--from "maria://user:pass@localhost:3306" \
--to "maria://user:pass@remote:3306"
atlas schema diff \
--from "postgres://postgres:pass@localhost:5432/database?sslmode=disable" \
--to "postgres://postgres:pass@remote:5432/database"
atlas schema diff \
--from "sqlite://file1.db" \
--to "sqlite://file2.db"
Compare database schemas
- MySQL
- MariaDB
- PostgreSQL
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 two MariaDB schemas/databases
named example
:
atlas schema diff \
--from "maria://user:pass@localhost:3306/example" \
--to "maria://user:pass@remote:3306/example"
Compare two PostgreSQL schemas named public
under the
example
database:
atlas schema diff \
--from "postgres://postgres:pass@localhost:5432/example?search_path=public&sslmode=disable" \
--to "postgres://postgres:pass@remote:5432/example?search_path=public"
Compare HCL schemas
- MySQL
- MariaDB
- PostgreSQL
- SQLite
atlas schema diff \
--from "file://schema1.hcl" \
--to "file://schema2.hcl" \
--dev-url "docker://mysql"
atlas schema diff \
--from "file://schema1.hcl" \
--to "file://schema2.hcl" \
--dev-url "docker://maria"
atlas schema diff \
--from "file://schema1.hcl" \
--to "file://schema2.hcl" \
--dev-url "docker://postgres"
atlas schema diff \
--from "file://schema1.hcl" \
--to "file://schema2.hcl" \
--dev-url "sqlite://file?mode=memory"
Compare SQL schemas
- MySQL
- MariaDB
- PostgreSQL
- SQLite
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"
atlas schema diff \
--from "file://schema1.sql" \
--to "file://schema2.sql" \
--dev-url "docker://maria/latest/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://maria"
atlas schema diff \
--from "file://schema1.sql" \
--to "file://schema2.sql" \
--dev-url "docker://postgres"
atlas schema diff \
--from "file://schema1.sql" \
--to "file://schema2.sql" \
--dev-url "sqlite://file?mode=memory"
Compare migration directories
- MySQL
- MariaDB
- PostgreSQL
- SQLite
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"
atlas schema diff \
--from "file://migrations1" \
--to "file://migrations2" \
--dev-url "docker://maria/latest/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://maria"
atlas schema diff \
--from "file://migrations1" \
--to "file://migrations2" \
--dev-url "docker://postgres"
atlas schema diff \
--from "file://migrations1" \
--to "file://migrations2" \
--dev-url "sqlite://file?mode=memory"
Compare SQL to HCL
- MySQL
- MariaDB
- PostgreSQL
- SQLite
atlas schema diff \
--from "file://schema.hcl" \
--to "file://schema.sql" \
--dev-url "docker://mysql/8/test"
atlas schema diff \
--from "file://schema.hcl" \
--to "file://schema.sql" \
--dev-url "docker://maria/latest/test"
atlas schema diff \
--from "file://schema.hcl" \
--to "file://schema.sql" \
--dev-url "docker://postgres"
atlas schema diff \
--from "file://schema.hcl" \
--to "file://schema.sql" \
--dev-url "sqlite://file?mode=memory"
Compare a migration directory to a database
- MySQL
- MariaDB
- PostgreSQL
- SQLite
atlas schema diff \
--from "file://migrations" \
--to "mysql://root:pass@:3306/example" \
--dev-url "docker://mysql/8/example"
atlas schema diff \
--from "file://migrations" \
--to "maria://root:pass@:3306/example" \
--dev-url "docker://maria/latest/example"
atlas schema diff \
--from "file://migrations" \
--to "postgres://postgres:pass@localhost:5435/test?sslmode=disable" \
--dev-url "docker://postgres"
atlas schema diff \
--from "file://migrations" \
--to "file://schema.sql" \
--dev-url "sqlite://file?mode=memory"
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.
- MySQL
- PostgreSQL
- SQLite
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.hcl_schema.gorm.url
sequelize = data.hcl_schema.sequelize.url
}
atlas schema diff \
--env "drift" \
--from "env://gorm" \
--to "env://sequelize"
data "external_schema" "gorm" {
program = [
"go", "run", "-mod=mod",
"ariga.io/atlas-provider-gorm",
"load",
"--path", "./path/to/models",
"--dialect", "postgres",
]
}
data "external_schema" "sequelize" {
program = [
"npx",
"@ariga/atlas-provider-sequelize",
"load",
"--path", "./path/to/models",
"--dialect", "postgres",
]
}
env "drift" {
dev = "docker://postgres/15/dev?search_path=public"
# Variables defined and available with env:// prefix.
gorm = data.hcl_schema.gorm.url
sequelize = data.hcl_schema.sequelize.url
}
atlas schema diff \
--env "drift" \
--from "env://gorm" \
--to "env://sequelize"
data "external_schema" "gorm" {
program = [
"go", "run", "-mod=mod",
"ariga.io/atlas-provider-gorm",
"load",
"--path", "./path/to/models",
"--dialect", "sqlite",
]
}
data "external_schema" "sequelize" {
program = [
"npx",
"@ariga/atlas-provider-sequelize",
"load",
"--path", "./path/to/models",
"--dialect", "sqlite",
]
}
env "drift" {
dev = "sqlite://dev?mode=memory"
# Variables defined and available with env:// prefix.
gorm = data.hcl_schema.gorm.url
sequelize = data.hcl_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 . " " }}'