Migration Analyzers
The database is often the most critical component in software architectures. Being a stateful component, it cannot be easily rebuilt, scaled-out or fixed by a restart. Outages that involve damage to data or simply unavailability of the database are notoriously hard to manage and recover from, often taking long hours of careful work by a team's most senior engineers.
As most outages happen directly as a result of a change to a system, Atlas provides users with means to verify the
safety of planned changes before they happen. The sqlcheck
package provides interfaces for analyzing the contents of SQL files to generate insights on the safety of many kinds of
changes to database schemas. With this package developers may define an Analyzer
that can be used to diagnose the impact
of SQL statements on the target database.
Using these interfaces, Atlas provides different Analyzer
implementations that are useful for determining the
safety of migration scripts.
Analyzers
Below are the Analyzer
implementations currently supported by Atlas.
Non-Linear Changes
Non-additive changes, often referred to as non-linear changes, are changes to the migration directory that are not added in a sequential order. This is a bit like the linear history in version control systems, where migration files are commits and the migration directory is the repository. Let's explain with three examples why ensuring the linearity of the migration directory is important:
- When a developer introduces a new migration file without having the latest state of the directory, there is a risk of generating an incorrect file that might conflict with the actual schema and cause a failure during deployment.
- When a developer merges a feature branch to the main branch with a new migration file that is not positioned at the end of the directory, there is a risk that Atlas will skip this migration file, as there might be some database that already contains a higher version of the migration directory. This scenario can cause unexpected and surprising behavior during deployments.
- Having non-linear history can make it challenging to roll back (or revert changes) to a specific version of the migration directory, as the state of the database might be different than the state of the migration directory. Applying the migration directory in consistent order promises deterministic behavior.
Luckily, Atlas detects non-linear and non-additive changes made to a migration directory. To enable this behavior in your project, integrate Atlas into your GitHub Actions or GitLab CI pipelines, and Atlas will automatically detect and report non-linear changes during the CI run.
By default, non-linear changes are reported but not cause migration linting to fail. Users can change this by
configuring the non_linear
changes detector in the atlas.hcl
file:
lint {
non_linear {
error = true
}
}
Destructive Changes
Destructive changes are changes to a database schema that result in loss of data. For instance, consider a statement such as:
ALTER TABLE `users` DROP COLUMN `email_address`;
This statement is considered destructive because whatever data is stored in the email_address
column
will be deleted from disk, with no way to recover it. There are definitely situations where this type
of change is desired, but they are relatively rare. Using the destructive
(GoDoc)
Analyzer, teams can detect this type of change and design workflows that prevent it from happening accidentally.
Running migration linting locally on in CI fails with exit code 1 in case destructive changes are detected. However,
users can disable this by configuring the destructive
analyzer in the atlas.hcl
file:
lint {
destructive {
error = false
}
}
Enforce Destructive Change Checks Atlas Pro
In some teams, destructive changes are considered high-risk and should never be skipped, regardless of whether a developer
adds an -- atlas:nolint
directive. In these cases, the force
option can be set on the destructive
analyzer. This guarantees
the check always runs and reports diagnostics, even if explicitly excluded.
lint {
destructive {
force = true
}
}
Data-dependent Changes
Data-dependent changes are changes to a database schema that may succeed or fail, depending on the data that is stored in the database. For instance, consider a statement such as:
ALTER TABLE `example`.`orders` ADD UNIQUE INDEX `idx_name` (`name`);
This statement is considered data-dependent because if the orders
table
contains duplicate values on the name column we will not be able to add a uniqueness
constraint. Consider we added two records with the name atlas
to the table:
mysql> create table orders ( name varchar(100) );
Query OK, 0 rows affected (0.11 sec)
mysql> insert into orders (name) values ("atlas");
Query OK, 1 row affected (0.06 sec)
mysql> insert into orders (name) values ("atlas");
Query OK, 1 row affected (0.01 sec)
Attempting to add a uniqueness constraint on the name
column, will fail:
mysql> ALTER TABLE `example`.`orders` ADD UNIQUE INDEX `idx_name` (`name`);
This type of change is tricky because a developer trying to simulate it locally
might succeed in performing it only to be surprised that their migration script
fails in production, breaking a deployment sequence or causing other unexpected
behavior. Using the data_depend
(GoDoc)
Analyzer, teams can detect this risk early and account for it in pre-deployment checks to a database.
By default, data-dependent changes are reported but not cause migration linting to fail. Users can change this by
configuring the data_depend
analyzer in the atlas.hcl
file:
lint {
data_depend {
error = true
}
}
Backward Incompatible Changes
Backward-incompatible changes, also known as breaking changes, are schema changes that have the potential to break the contract with applications that rely on
the old schema. For instance, renaming a column from email_address
to email
can cause errors during deployment
(migration) phase if applications running the previous version of the schema reference the old column name in their
queries.
By default, detected breaking changes are reported but do not cause migration linting to fail. Users can change this by
configuring the incompatible
analyzer in the atlas.hcl
file:
lint {
incompatible {
error = true
}
}
Naming Conventions Policy
In database schema design, maintaining consistency and readability through naming conventions is a widely common practice. Atlas provides an analyzer that can help enforce naming conventions on a variety of schema resources, including tables, columns, and indexes.
Users can enable this by configuring the naming
analyzer in their
atlas.hcl
file:
- Global policy
- Resource-specific policy
lint {
naming {
match = "^[a-z]+$"
message = "must be lowercase"
}
}
lint {
naming {
match = "^[a-z]+$"
message = "must be lowercase"
index {
match = "^[a-z]+_idx$"
message = "must be lowercase and end with _idx"
}
// schema, table, column, foreign_key and check are also supported.
}
}
By default, detected naming violations are reported but do not cause migration linting to fail. Users can change this by
configuring the naming
analyzer in the atlas.hcl
file:
lint {
naming {
error = true
match = "^[a-z]+$"
message = "must be lowercase"
}
}
Concurrent Index Policy (PostgreSQL) Atlas Pro
Schema changes like CREATE INDEX
or DROP INDEX
can cause the database to lock the table against write operations.
Luckily, PostgreSQL provides the
CONCURRENTLY
option that
may be more resource-intensive, but allows normal database operations to continue while the index is built or dropped.
Atlas provides an analyzer that identifies non-concurrent index creation or deletion for tables not created within the same file, and recommends executing them concurrently.
Additionally, since indexes cannot be created or deleted concurrently within a transaction, Atlas ensures the
atlas:txmode none
directive exists in the file header to prevent this file from running in a transaction. This check
can be disabled along with the other ones as follows:
lint {
concurrent_index {
check_create = false // `true` by default.
check_drop = false // `true` by default.
check_txmode = false // `true` by default.
}
}
By default, detected concurrent index violations are reported but do not cause migration linting to fail. Users can
change this by configuring the concurrent_index
analyzer in the
atlas.hcl
file:
lint {
concurrent_index {
error = true
}
}
Checks
The checks below are built into Atlas; for additional guardrails, see Atlas custom schema linting rules or ask
for guidance.Check | Short Description |
---|---|
BC | Backward incompatible changes |
BC101 | Renaming a table |
BC102 | Renaming a column |
CD | Constraint deletion changes Atlas Pro |
CD101 | Foreign-key constraint was dropped |
CD102 | Check constraint was dropped |
CD103 | Primary-key constraint was dropped |
DS | Destructive changes |
DS101 | Schema was dropped |
DS102 | Table was dropped |
DS103 | Non-virtual column was dropped |
MF | Data-dependent changes (changes that might fail) |
MF101 | Add unique index to existing column |
MF102 | Modifying non-unique index to unique |
MF103 | Adding a non-nullable column to an existing table |
MF104 | Modifying a nullable column to non-nullable |
PG | PostgreSQL specific checks |
PG110 | Creating table with non-optimal data alignment |
PG1 | Concurrent Indexes Atlas Pro |
PG101 | Missing the CONCURRENTLY in index creation |
PG102 | Missing the CONCURRENTLY in index deletion |
PG103 | Missing atlas:txmode none directive in file header |
PG104 | PRIMARY KEY constraint creation that acquires an ACCESS EXCLUSIVE lock on the table |
PG105 | UNIQUE constraint creation that acquires an ACCESS EXCLUSIVE lock on the table |
PG3 | PostgreSQL-specific blocking table changes Atlas Pro |
PG301 | Column type change that requires table and indexes rewrite |
PG302 | Adding a column with a volatile DEFAULT value requires a rewrite of the table |
PG303 | Modifying a nullable column to non-nullable requires a full table scan |
PG304 | Adding a PRIMARY KEY on nullable columns implicitly set them to NOT NULL requires a full table scan |
PG305 | Adding a CHECK constraint that requires a full table scan |
PG306 | Adding a FOREIGN KEY constraint that requires a full table scan and blocks write operations |
MY | MySQL and MariaDB specific checks |
MY101 | Adding a non-nullable column without a DEFAULT value to an existing table |
MY102 | Adding a column with an inline REFERENCES clause has no actual effect |
MY110 | Removing enum values from a column requires a table copy |
MY111 | Reordering enum values of a colum requires a table copy |
MY112 | Inserting new enum values not at the end requires a table copy |
MY113 | Exceeding 256 enum values changes storage size and requires a table copy |
MY120 | Removing set values from a column requires a table copy |
MY121 | Reordering set values of a colum requires a table copy |
MY122 | Inserting new set values not at the end requires a table copy |
MY123 | Exceeding 8, 16, 24, 32 or 64 set values changes the storage size and requires a table copy |
NM | Naming Conventions |
NM101 | Schema name violates the naming convention |
NM102 | Table name violates the naming convention |
NM103 | Column name violates the naming convention |
NM104 | Index name violates the naming convention |
NM105 | Foreign-key constraint name violates the naming convention |
NM106 | Check constraint name violates the naming convention |
LT | SQLite specific checks |
LT101 | Modifying a nullable column to non-nullable without a DEFAULT value |