Declarative schema migrations
With Atlas, users do not need to plan database schema changes themselves. Instead of figuring out the correct SQL statements to get their database to the desired state, Atlas supports a kind of workflow that we call declarative schema migration. With declarative schema migrations the user provides a connection string to the target database and the desired schema, while Atlas does the planning.
Read more about declarative workflows
Flags​
The schema apply
command generates a migration plan and applies it to the database to bring it to the desired state.
A desired state can be specified using a database URL, an HCL or SQL schema, or a migration directory.
--url
(-u
accepted as well) - the URL of the database to be inspected.--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.
Auto-approval​
Before executing the migration against the target database, Atlas will print the SQL
statements that it is going to run and prompt the user for approval. Users that wish
to automatically approve may run the schema apply
command with the --auto-approve
flag.
Dry-runs​
In order to skip the execution of the SQL queries against the target database,
users may provide the --dry-run
flag. When invoked with this flag, Atlas will
connect to the target database, inspect its current state, calculate the diff
between the provided desired schema and print out a series of SQL statements to
reconcile any gaps between the inspected and desired schemas.
Dev-database​
When storing schema definitions, many database engines perform some form of normalization. That is, despite us providing a specific definition of some aspect of the schema, the database will store it in another, equivalent form. This means in certain situations it may appear to Atlas as if some diff exists between the desired and inspected schemas, whereas in reality there is none.
To overcome these situations, users may use the --dev-url
flag to provide
Atlas with a connection string to a Dev-Database.
This database is used to normalize the schema prior to planning migrations and
for simulating changes to ensure their applicability before execution.
Examples​
HCL schema​
The following example demonstrates how to use Atlas DDL (HCL) as the desired state and update the database schema to match it:
- MySQL
- MariaDB
- PostgreSQL
- SQLite
atlas schema apply \
--url "mysql://root:pass@localhost:3306/example" \
--to "file://schema.hcl" \
--dev-url "docker://mysql/8/example"
atlas schema apply \
--url "maria://root:pass@:3306/example" \
--to "file://schema.hcl" \
--dev-url "docker://maria/latest/example"
atlas schema apply \
--url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable" \
--to "file://schema.hcl" \
--dev-url "docker://postgres/15"
atlas schema apply \
--url "sqlite://file.db" \
--to "file://schema.hcl" \
--dev-url "sqlite://file?mode=memory"
SQL schema​
The following example demonstrates how to use an SQL schema file as the desired state and update the database schema to match it:
- MySQL
- MariaDB
- PostgreSQL
- SQLite
atlas schema apply \
--url "mysql://root:pass@localhost:3306/example" \
--to "file://schema.sql" \
--dev-url "docker://mysql/8/example"
atlas schema apply \
--url "maria://root:pass@:3306/example" \
--to "file://schema.sql" \
--dev-url "docker://maria/latest/example"
atlas schema apply \
--url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable" \
--to "file://schema.sql" \
--dev-url "docker://postgres/15"
atlas schema apply \
--url "sqlite://file.db" \
--to "file://schema.sql" \
--dev-url "sqlite://file?mode=memory"
Atlas loads the desired state by executing the SQL files onto the provided dev database, compares it against the database current state by inspecting its schema and writes a migration plan for moving from the current state to the desired state.
Migration directory​
The following example demonstrates how to use the migration directory as the desired state and update the database schema to match it. The URL for the migration directory can contain two optional query parameters:
format
- migration directory format: atlas (default), golang-migrate, goose, dbmate, flyway, liquibase.version
- until which version of migration files to read. By default, all migrations are read.
- MySQL
- MariaDB
- PostgreSQL
- SQLite
atlas schema apply \
--url "mysql://root:pass@localhost:3306/example" \
--to "file://migrations" \
--dev-url "docker://mysql/8/example"
atlas schema apply \
--url "maria://root:pass@:3306/example" \
--to "file://migrations" \
--dev-url "docker://maria/latest/example"
atlas schema apply \
--url "postgres://postgres:pass@localhost:5432/database?search_path=public&sslmode=disable" \
--to "file://migrations" \
--dev-url "docker://postgres/15"
atlas schema apply \
--url "sqlite://file.db" \
--to "file://migrations" \
--dev-url "sqlite://file?mode=memory"
Multi-Tenant environments​
The Atlas configuration language provides built-in support for executing declarative workflows in multi-tenant
environments. Using the for_each
meta-argument, users can define a single env
block that is expanded to N instances,
one for each tenant:
- atlas.hcl
- schema.hcl
env "prod" {
for_each = toset(var.tenants)
url = urlsetpath(var.url, each.value)
src = "schema.hcl"
format {
schema {
apply = format(
"{{ json . | json_merge %q }}",
jsonencode({
Tenant : each.value
})
)
}
}
// Inject custom variables to the schema.hcl defined below.
tenant = each.value
}
variable "tenant" {
type = string
description = "The schema we operate on"
}
schema "tenant" {
name = var.tenant
}
table "users" {
schema = schema.tenant
// ...
}
Read more about how to define declarative workflows using project files in multi-tenant environments.