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
}
}
Relax Non-Linear Errors on Edits
Users who configured the non_linear change detector to fail migration linting (error = true) can relax or ignore
these errors when editing existing migration files by setting the on_edit option to WARN or IGNORE.
WARN: file edits that would trigger a non-linear error are downgraded to warnings.IGNORE: file edits are ignored and do not produce any diagnostic.
lint {
non_linear {
error = true
on_edit = WARN // IGNORE | ERROR
}
}
This is useful when developers need to update the latest migration files right after they land on the main branch and want to keep CI pipelines unblocked while non-linear checks still apply to older files.
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
}
}
Allowlist Drop Rules Atlas Pro
The destructive analyzer can be configured to allow specific destructive changes, such as dropping a table or column
whose name matches a defined pattern. This allows teams following a deprecation workflow, like renaming objects with
a drop_ prefix before deletion, to continue using the analyzer while avoiding false positives from intentional drops.
lint {
destructive {
// Allow dropping tables or columns
// that their name start with "drop_".
allow_table {
match = "drop_.+"
}
allow_column {
match = "drop_.+"
}
}
}
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`);
ERROR 1062 (23000): Duplicate entry 'atlas' for key 'orders.idx_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
}
}
Ownership Policy Atlas Pro
GitHub CODEOWNERS enforces who can modify files, but cannot enforce who can modify schema objects. A developer
with access to the inventory/ schema folder could alter the schema files to include changes to
public.users, a core table owned by another team, causing a migration that crosses schema boundaries.
The ownership policy validates that migration changes only affect resources the author is authorized to modify.
It resolves the current user from the GITHUB_ACTOR environment variable and checks membership against the
configured rules.
lint {
ownership "github" {
allow "core-tables" {
match = "public.*[type=table]"
teams = ["backend"]
users = ["a8m"]
}
allow "inventory" {
match = "inventory.*"
teams = ["data-eng"]
}
deny "contractors" {
match = "*"
users = ["contractor-x"]
}
}
}
Rules are evaluated in order. The first matching rule determines access. deny rules take precedence over
allow rules for the same resource. Changes that don't match any rule are denied by default. Set
default = ALLOW on the ownership block to invert this behavior.
When you specify teams, Atlas uses the GITHUB_TOKEN environment variable to call the GitHub Teams API and
check membership. Make sure the token has the correct permissions. See GitHub's
fine-grained personal access tokens
documentation for details.
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
}
}
Nested Transactions Atlas Pro
By default, Atlas wraps each migration file in its own transaction (see transaction configuration for more details). If a file includes a statement that starts a transaction (for example, BEGIN or START TRANSACTION), the database will return an error (or fail later when using a connection-pooler like PgBouncer) such as cannot start a transaction within a transaction (SQLite) or pq: unexpected transaction status idle (PostgreSQL).
The nestedtx analyzer flags such statements. You can fix them by either removing the manual transaction control or adding -- atlas:txmode none to the file header to indicate that this migration file should not be wrapped in a transaction by Atlas. When Atlas detects this issue, it automatically adds a code suggestion with this directive to the pull request.
By default, violations are reported as warnings. You can make them fail the lint step by configuring the analyzer in your atlas.hcl file:
lint {
nestedtx {
error = true
}
}
Statement Rules Atlas Pro
The statement analyzer lets you define regular expression rules to permit or reject specific SQL statements in your migration files.
It processes each statement sequentially against an ordered list of rules. The supported rule types are allow and deny:
allow: When a statement matches anallowrule, the analyzer lets it pass.deny: When a statement matches adenyrule, the analyzer rejects it and reports a diagnostic.- If no rules match, the statement is allowed by default.
The statement analyzer reports violations as warnings by default. You can make them fail the linting step by
setting error = true in the statement block.
To ensure the analyzer cannot be skipped with the -- atlas:nolint directive, set force = true.
- Block DELETE/TRUNCATE
- Block DELETE without WHERE
lint {
statement {
error = true
deny "delete-truncate" {
match = "(?i)(DELETE|TRUNCATE)"
}
}
}
lint {
statement {
allow "delete-with-where" {
match = "(?i)DELETE .+ WHERE"
}
deny "delete-truncate" {
match = "(?i)DELETE"
message = "DELETE statements must have a WHERE clause"
}
}
}
SQL Injection
The SQL Injection analyzer examines SQL statements in migration files and declarative schemas to detect constructs that could allow untrusted input to be executed as part of an SQL command. It identifies unsafe dynamic SQL patterns, such as string concatenation or variable interpolation, that may bypass proper escaping or identifier quoting.
As more schema code and migrations are edited automatically, including by AI-assisted tools, developers may introduce such patterns unintentionally. This analyzer provides a deterministic way to surface these risks early in the development process, helping teams maintain secure, reviewable, and policy-compliant database changes.
This analyzer performs static analysis on parsed statements and reports potential vulnerabilities based on the following checks:
- Use of
EXECorEXECUTEstatements that concatenate strings or interpolate variables into SQL text. - Construction of dynamic SQL using string concatenation or format functions without proper sanitization or identifier quoting.
-- Unsafe: building a query through concatenation
EXECUTE 'ALTER TABLE ' || tbl_name || ' DROP COLUMN ' || col;
-- Safe: using format() with identifier sanitization
EXECUTE format('ALTER TABLE %I DROP COLUMN %I', tbl_name, col);
Static analysis cannot always determine whether a value is derived from a trusted source at runtime. As a result, the analyzer takes a conservative approach and may report false positives, particularly in complex and well-audited dynamic SQL code.
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 |
| SA | Static Analysis |
| SA101 | Possible SQL injection vulnerabilities detected |
| 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 |
| PG307 | Changing table logging mode (LOGGED/UNLOGGED) requires a full table rewrite |
| PG308 | Adding a trigger to an existing table acquires a SHARE ROW EXCLUSIVE lock, blocking writes |
| PG309 | Adding a STORED generated column requires a full table rewrite |
| PG310 | Adding an identity column requires a full table rewrite |
| PG311 | Changing a table's access method requires a full table and index rewrite |
| TX | Transaction Safety Atlas Pro |
| TX101 | Migration file contains multiple statements that cannot run in a single transaction. |
| TX201 | Nested transaction detected |
| 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 |
| MY1 | MySQL-specific blocking table changes Atlas Pro |
| MY130 | Changing a column type requires a table copy |
| MY131 | Adding a foreign key blocks DML |
| MY132 | Adding a primary key requires a table rebuild |
| MY133 | Dropping a primary key without adding a new one requires a table copy and blocks DML |
| MY134 | Adding a FULLTEXT index blocks DML |
| MY135 | Adding a SPATIAL index blocks DML |
| MY136 | Changing the table character set requires a table rebuild |
| OW | Ownership Policy Atlas Pro |
| OW101 | User not authorized to modify resource |
| OW102 | User explicitly denied access to resource |
| 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 |
DS101
Destructive change that is reported when a database schema was dropped. For example:
DROP SCHEMA test;
DS102
A destructive change is reported when a table is dropped, posing a risk of data loss. For example:
DROP TABLE users;
The suggested solution is to back up the data before dropping the table, or add a pre-migration check to ensure that the table is empty before dropping it:
+ -- atlas:txtar
+
+ -- checks/destructive.sql --
+ SELECT NOT EXISTS (SELECT 1 FROM users) AS users_empty;
+
+ -- migration.sql --
DROP TABLE users;
DS103
A destructive change is reported when a non-virtual column is dropped, posing a risk of data loss. For example:
ALTER TABLE t DROP COLUMN c;
The suggest solution to resolve these errors is to add a pre-migration check that ensures the column is empty before dropping it:
+ -- atlas:txtar
+
+ -- checks/destructive.sql --
+ SELECT NOT EXISTS (SELECT 1 FROM t WHERE c IS NOT NULL) AS t_c_empty;
+
+ -- migration.sql --
ALTER TABLE t DROP COLUMN c;
MF101
Adding a unique index to a table might fail in case one of the indexed columns contain duplicate entries. For example:
CREATE UNIQUE INDEX i ON t(c);
MF102
Modifying a non-unique index to be unique might fail in case one of the indexed columns contain duplicate entries.
Since index modification is done with DROP and CREATE, this check will be reported only when analyzing changes
programmatically or when working with the declarative workflow.
MF103
Adding a non-nullable column to a table might fail in case the table is not empty. For example:
ALTER TABLE t ADD COLUMN c int NOT NULL;
MF104
Modifying nullable column to non-nullable might fail in case it contains NULL values. For example:
ALTER TABLE t MODIFY COLUMN c int NOT NULL;
The solution, in this case, is to backfill NULL values with a default value:
UPDATE t SET c = 0 WHERE c IS NULL;
ALTER TABLE t MODIFY COLUMN c int NOT NULL;
Atlas can add this safeguard automatically. Enable the not_null diff policy
with check = true, and Atlas generates a pre-migration check that stops the
deployment if the column still contains NULLs, so no MF104 diagnostic is reported.
With check on, Atlas adds an -- atlas:assert MF104 assertion to the version's checks.sql. This is
supported on PostgreSQL, MySQL, and SQL Server; the query shape depends on the dialect:
- PostgreSQL
- MySQL
- SQL Server
-- atlas:assert MF104
SELECT NOT EXISTS (SELECT 1 FROM "users" WHERE "name" IS NULL) AS "not_null";
-- atlas:assert MF104
SELECT NOT EXISTS (SELECT 1 FROM `users` WHERE `name` IS NULL) AS `not_null`;
-- atlas:assert MF104
SELECT CASE WHEN NOT EXISTS (SELECT 1 FROM [users] WHERE [name] IS NULL) THEN 1 ELSE 0 END AS [not_null];
SQL Server wraps the check in CASE WHEN ... THEN 1 ELSE 0 END because T-SQL does not allow EXISTS
directly in a SELECT list.
See the Safe NOT NULL Migrations guide for the full workflow, including the
PostgreSQL-only lock_safe option that also removes the table lock.
BC101
Renaming a table is a backward-incompatible change that can cause errors during deployment (migration) if applications running the previous version of the schema refer to the old name in their statements. For example:
ALTER TABLE `users` RENAME TO `Users`;
Unlike other checks, there is no single correct way to resolve this one. Here are some possible solutions:
-
It's likely that this change was introduced when you renamed one of the entities in your ORM, and the linter helped you catch the potential problem this could cause. In such cases, most ORM frameworks allow you to rename the entity while still pointing to its previous table name. e.g., here is how you can do it in Ent and in GORM.
-
If renaming is desired but the previous version of the application uses the old table name, a temporary
VIEWcan be created to mimic the previous schema version in the deployment phase. However, the downside of this solution is that mutations using the old table name might fail (e.g., if the VIEW is not Updatable/Insertable). Yet, if Atlas detects a consecutive statement with aCREATE VIEW <old_name>, it will ignore this check.
ALTER TABLE `users` RENAME TO `Users`;
CREATE VIEW `users` AS SELECT * FROM `Users`;
- If renaming the table is desired and no clients depend on it yet, or if it is acceptable to return errors during migration phase when traffic is minimal, you can configure Atlas to ignore this check with the following directive:
-- atlas:nolint BC101
ALTER TABLE `users` RENAME TO `Users`;
BC102
Renaming a column is a backward-incompatible change that can cause errors during deployment (migration) if applications running the previous version of the schema refer to the old column name in their statements. For example:
ALTER TABLE `users` RENAME COLUMN `user_name` TO `name`;
Unlike other checks, there is no single correct way to resolve this one. Here are some possible solutions:
-
It's likely that this change was introduced when you renamed a field in one of the entities in your ORM, and the linter helped you catch the potential problem this could cause. In such cases, most ORM frameworks allow you to rename a field while still pointing to its previous column name. e.g., in Ent you can configure it using the
StorageKeyoption, and in GORM you can set it by adding thecolumnstruct tag. -
If renaming is desired but the previous version of the application uses the old column name, a temporary
VIRTUALgenerated column can be created to mimic the previous schema version in the deployment phase. However, the downside of this solution is that mutations using the old column name will fail. Yet, if Atlas detects a consecutive command with such a column, it will ignore this check.
-- For MySQL or MariaDB:
ALTER TABLE `posts` RENAME COLUMN `id` TO `uid`, ADD COLUMN `id` int AS (`uid`);
-- SQLite:
ALTER TABLE `posts` RENAME COLUMN `id` TO `uid`;
ALTER TABLE `posts` ADD COLUMN `id` int AS (`uid`);
- If renaming the column is desired and no clients depend on it yet, or if it is acceptable to return errors during the migration phase when traffic is minimal, you can configure Atlas to ignore this check with the following directive:
-- atlas:nolint BC102
ALTER TABLE `posts` RENAME COLUMN `id` TO `uid`;
CD101
Constraint deletion is reported when a foreign-key constraint was dropped. For example:
ALTER TABLE pets DROP CONSTRAINT owner_id;
CD102
Constraint deletion is reported when a check constraint was dropped. For example:
ALTER TABLE orders DROP CONSTRAINT price_is_positive;
CD103
Constraint deletion is reported when a primary-key constraint was dropped. For example:
ALTER TABLE orders DROP CONSTRAINT orders_pkey
MY101
Adding a non-nullable column to a table without a DEFAULT value implicitly sets existing rows with the column
zero (default) value. For example:
ALTER TABLE t ADD COLUMN c int NOT NULL;
-- Append column `c` to all existing rows with the value 0.
MY102
Adding a column with an inline REFERENCES clause has no actual effect. Users should define a separate FOREIGN KEY
specification instead. For example:
-CREATE TABLE pets (owner_id int REFERENCES users(id));
+CREATE TABLE pets (owner_id int, FOREIGN KEY (owner_id) REFERENCES users(id));
MY110
Removing enum values from a column changes the column type and requires a table copy. During this process, the table is locked for write operations. In addition, operation may fail if the column contains values that are not in the new enum definition.
MY111
Reordering enum values of a colum requires a table copy. During this process, the table is locked for write operations.
Note that since the order of the enum values defines how the table is sorted when using ORDER BY on the column,
controlling the ordering behavior can be achieved using the DESC clause or expressions as follows:
-- Instead of reversing the enum values.
SELECT * FROM t ORDER BY enum_column DESC;
-- Instead of reordering the enum values.
SELECT * FROM t ORDER BY CASE enum_column WHEN 'a' THEN 1 WHEN 'b' THEN 2 ELSE 3 END;
MY112
Inserting new enum values not at the end requires table copy. During this process, the table is locked for write operations.
If possible, it is recommended to add new enum values at the end of the enum definition.
MY113
Exceeding 256 enum values changes storage size and requires a table copy. During this process, the table is locked for write operations.
MY120
Removing set values from a column requires a table copy. During this process, the table is locked for write operations. In addition, operation may fail if the column contains values that are not in the new set definition.
MY121
Reordering set values of a colum requires a table copy. During this process, the table is locked for write operations.
MY122
Inserting new set values not at the end requires table copy. During this process, the table is locked for write operations.
If possible, it is recommended to add new set values at the end of the set definition.
MY123
Adding set values to a column changes its storage size and requires a table copy, if the new amount changes the number of bytes needed for the bitmap. The storage size of a set column is 1, 2, 3, 4 or 8 bytes, depending on the number of values: One byte can store up to 8 values, two bytes can store up to 16 etc.
During this process, the table is locked for write operations.
MY130 Atlas Pro
Changing a column type only supports ALGORITHM=COPY
in MySQL, meaning the operation creates a full temporary copy of the table with the new schema, copies all rows into it,
and then swaps it with the original. During this process, the table is locked for write operations (concurrent DML is
not permitted), which can cause downtime. In addition, because both the original and the temporary table must exist on
disk simultaneously, this can consume significant disk space - potentially leading to out-of-space errors when altering
large tables or multiple tables at once. For example:
ALTER TABLE t MODIFY COLUMN c text;
Common type changes that trigger a table copy include changing between incompatible types (e.g., varchar to text,
int to bigint, varchar(50) to varchar(100) in certain cases).
MY131 Atlas Pro
Adding a foreign key with foreign_key_checks
enabled requires a table copy and blocks concurrent DML. During this process, the table is locked for write operations,
which can cause downtime on busy tables. For example:
ALTER TABLE t ADD FOREIGN KEY (c) REFERENCES t2 (id);
MY132 Atlas Pro
Adding a primary key requires a table rebuild in MySQL. During this process, the data is reorganized in-place which can be a slow operation on large tables. For example:
ALTER TABLE t ADD PRIMARY KEY (id);
MY133 Atlas Pro
Dropping a primary key without adding a new one only supports ALGORITHM=COPY
in MySQL, meaning the operation creates a full temporary copy of the table and blocks concurrent DML. During this process,
the table is locked for write operations, which can cause downtime on busy tables. For example:
ALTER TABLE t DROP PRIMARY KEY;
If you drop and re-add a primary key in the same ALTER TABLE statement, only the MY132 diagnostic
is reported, as the table rebuild for the new primary key subsumes the drop.
MY134 Atlas Pro
Adding a FULLTEXT index blocks concurrent
DML in MySQL. During this process, the table is locked for write operations, which can cause downtime on busy tables.
For example:
ALTER TABLE t ADD FULLTEXT INDEX (c);
MY135 Atlas Pro
Adding a SPATIAL index blocks concurrent
DML in MySQL. During this process, the table is locked for write operations, which can cause downtime on busy tables.
For example:
ALTER TABLE t ADD SPATIAL INDEX (c);
MY136 Atlas Pro
Changing the table's character set requires a table rebuild in MySQL. During this process, the data is reorganized in-place which can be a slow operation on large tables. For example:
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4;
LT101
Modifying a nullable column to non-nullable without setting a DEFAULT might fail in case it contains NULL values.
The solution is one of the following:
1. Set a DEFAULT value on the modified column:
-- create "new_users" table
CREATE TABLE `new_users` (`a` int NOT NULL DEFAULT 1);
-- copy rows from old table "users" to new temporary table "new_users"
INSERT INTO `new_users` (`a`) SELECT IFNULL(`a`, 1) FROM `users`;
-- drop "users" table after copying rows
DROP TABLE `users`;
-- rename temporary table "new_users" to "users"
ALTER TABLE `new_users` RENAME TO `users`;
2. Backfill NULL values with a default value:
-- backfill previous rows
UPDATE `users` SET `a` = 1 WHERE `a` IS NULL;
-- disable the enforcement of foreign-keys constraints
PRAGMA foreign_keys = off;
-- create "new_users" table
CREATE TABLE `new_users` (`a` int NOT NULL);
-- copy rows from old table "users" to new temporary table "new_users"
INSERT INTO `new_users` (`a`) SELECT `a` FROM `users`;
-- drop "users" table after copying rows
DROP TABLE `users`;
-- rename temporary table "new_users" to "users"
ALTER TABLE `new_users` RENAME TO `users`;
-- enable back the enforcement of foreign-keys constraints
PRAGMA foreign_keys = on;
TX101
This check is reported when a migration file contains multiple statements that cannot safely run within a single transaction. If execution stops midway, the database may end up in an intermediate state between the previous and target versions.
CREATE TABLE t1 (id int);
ALTER TABLE t2 ADD COLUMN c int;
You can resolve this check by splitting non-transactional statements into separate migration versions (files),
or by adding the -- atlas:txmode none directive at the top to tell Atlas not to wrap the file in a transaction.
TX201
This check fires when a migration file opens its own transaction even though Atlas already wraps the file. For example:
BEGIN;
CREATE TABLE accounts (...);
COMMIT;
To avoid nested transactions:
- Remove the explicit
BEGIN/COMMITstatements and rely on Atlas to wrap the file. - Or add
-- atlas:txmode nonedirective at the top of the file to tell Atlas not to wrap the file in a transaction.
OW101
The user who authored the migration is not authorized to modify the reported schema resource. No allow rule
matched the user for the affected object. To resolve, add the user or their team to an allow block in the
matching ownership rule.
OW102
The user who authored the migration is explicitly denied access to the reported schema resource. A deny block
in a matching ownership rule lists the user. deny takes precedence over allow within the same rule.
NM101
A schema has been given a name that violates the naming convention.
NM102
A table has been given a name that violates the naming convention.
NM103
A column has been given a name that violates the naming convention.
NM104
An index has been given a name that violates the naming convention.
NM105
A foreign-key constraint has been given a name that violates the naming convention.
NM106
A check constraint has been given a name that violates the naming convention.
SA101
Possible SQL injection vulnerabilities have been detected in the provided SQL. These issues may allow user-controlled input to be executed as part of an SQL statement, potentially leading to unauthorized data access, data modification, or execution of unintended operations.
PG110
Creating a table with optimal data alignment may help minimize the amount of required disk space. For example consider the next Postgres table on a 64-bit system:
CREATE TABLE accounts (
id bigint PRIMARY KEY,
premium boolean,
balance integer,
age smallint
);
Each tuple in the table takes 24 bytes of successive memory without the header.
the id attribute takes 8 bytes, the premium takes 1 byte and 3 bytes of padding, the balance takes 4 bytes and the age takes 2 bytes,
and lastly 6 bytes of padding allocated for the end of the row.
In total 9 bytes of padding are allocated for each row.
Compared to same table with different ordering which only takes 16 bytes in memory with 1 byte of padding:
CREATE TABLE accounts (
id bigint PRIMARY KEY,
balance integer,
age smallint,
premium boolean
);
PG101 Atlas Pro
Creating an index non-concurrently acquires a SHARE lock on the table blocking writes but allowing reads during the
operation.
PG102 Atlas Pro
Dropping an index non-concurrently acquires an ACCESS EXCLUSIVE lock on the table blocking both reads and writes
during the operation.
PG103 Atlas Pro
Indexes cannot be created or deleted concurrently within a transaction. Add the atlas:txmode none directive to the
header to prevent this file from running in a transaction.
PG104 Atlas Pro
Adding a PRIMARY KEY constraint (with its index) acquires an ACCESS EXCLUSIVE lock on the table, blocking all access
during the operation. The solution is to add as follows:
- Create the
UNIQUE INDEXconcurrently:
CREATE UNIQUE INDEX CONCURRENTLY users_pkey ON users(id);
-
Validate the index creation and ensure it is in
VALIDstate. Otherwise, the constraint creation will fail with the following error:ERROR: index "users_pkey" is not a validNote, this step can be automated using a pre-migration check on the migration file below.
-
After validating the index state, add the
PRIMARY KEYconstraint using the created index:
ALTER TABLE users ADD PRIMARY KEY USING INDEX users_pkey;
PG105 Atlas Pro
Adding a UNIQUE constraint (with its index) acquires an ACCESS EXCLUSIVE lock on the table, blocking all access
during the operation. The solution is to add as follows:
- Create the
UNIQUE INDEXconcurrently:
CREATE UNIQUE INDEX CONCURRENTLY users_id_key ON users(id);
- Validate the index creation and ensure it is in
VALIDstate. Otherwise, the constraint creation will fail with the following error:
ERROR: index "users_id_key" is not a valid
Note, this step can be automated using a pre-migration check on the migration file below.
- After validating the index state, add the
UNIQUEconstraint using the created index:
ALTER TABLE users ADD UNIQUE USING INDEX users_id_key;
PG301 Atlas Pro
A change to the column type that requires adjusting the actual stored data and triggers a rewrite of the table and potentially
indexes. During this operation, an ACCESS EXCLUSIVE
lock is acquired on the table, preventing any level of access, including SELECT.
PG302 Atlas Pro
Adding a column with a volatile DEFAULT value requires a rewrite of the table. During this operation, an
ACCESS EXCLUSIVE lock is acquired on the
table, preventing any level of access, including SELECT.
PG303 Atlas Pro
Modifying a column from NULL to NOT NULL requires a full table scan. During this operation, an
ACCESS EXCLUSIVE lock is acquired on the
table, preventing any level of access, including SELECT.
If the table has a CHECK constraint that ensures NULL cannot exist, such as CHECK (c > 10 AND c IS NOT NULL),
the table scan is skipped, and therefore this check is not reported.
Atlas can plan NOT NULL changes to avoid this scan automatically. Enable the
not_null diff policy with lock_safe = true, and Atlas adds the
constraint NOT VALID and validates it under a SHARE UPDATE EXCLUSIVE lock (which does not block reads or
writes), so no PG303 diagnostic is reported. See the
Safe NOT NULL Migrations guide.
PG304 Atlas Pro
Adding a PRIMARY KEY on a nullable columns implicitly sets them to NOT NULL, resulting a full table scan unless
there is a CHECK constraint that ensures NULL cannot exist.
PG305 Atlas Pro
Adding a CHECK constraint without the NOT VALID clause requires scanning the table to verify that all rows satisfy
the constraint. During this operation, an ACCESS EXCLUSIVE
lock is acquired on the table, preventing any level of access, including SELECT.
PG306 Atlas Pro
Adding a FOREIGN KEY constraint without the NOT VALID clause requires a full table scan to verify that all rows
satisfy the constraint. During this process, a SHARE ROW EXCLUSIVE lock is acquired on both tables blocking write
operations.
To avoid this, the constraint can be added with NOT VALID, which skips the initial table scan and prevents long-running
locks. The existing rows can then be validated separately using VALIDATE CONSTRAINT, which only acquires a SHARE UPDATE EXCLUSIVE
lock on the modified table and a ROW SHARE lock on the referenced table, minimizing disruptions.
If validation fails, the constraint stays in the NOT VALID state, blocking new violations while allowing time to fix
existing data. If the migration runs within a transaction, the constraint will be dropped at the end of the transaction.
ALTER TABLE orders ADD CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
-- Validate the constraint.
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_users;
PG307 Atlas Pro
Changing a table's logging mode between LOGGED and UNLOGGED requires a full table rewrite. During this operation, an
ACCESS EXCLUSIVE lock is acquired on the
table, preventing any level of access, including SELECT.
In PostgreSQL, LOGGED tables (the default) write data to the Write-Ahead Log (WAL), providing durability and crash
recovery. UNLOGGED tables skip WAL writes for better performance but lose data on a crash or unclean shutdown.
-- Changing from LOGGED to UNLOGGED
ALTER TABLE users SET UNLOGGED;
-- Changing from UNLOGGED to LOGGED
ALTER TABLE temp_data SET LOGGED;
Both operations trigger a complete table rewrite, which can be time-consuming for large tables and will block all access during the process.
PG308 Atlas Pro
Adding a trigger to an existing table acquires a
SHARE ROW EXCLUSIVE lock on the table,
blocking INSERT, UPDATE, and DELETE operations until the CREATE TRIGGER command completes.
-- This acquires a SHARE ROW EXCLUSIVE lock on the "users" table.
CREATE TRIGGER notify_update AFTER UPDATE ON users FOR EACH ROW EXECUTE FUNCTION notify_change();
Note: This diagnostic is only reported when the trigger is added to an existing table. Triggers on newly created tables or on views are not flagged.
PG309 Atlas Pro
Adding a STORED generated column to an existing table requires a full table rewrite because PostgreSQL must compute
and materialize the generated value for every existing row.
-- This triggers a full table rewrite.
ALTER TABLE users ADD COLUMN full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;
Note: VIRTUAL generated columns (PostgreSQL 18+) are not flagged, as they are computed on read and do not require a
table rewrite.
PG310 Atlas Pro
Adding an identity column to an existing table requires a full table rewrite because PostgreSQL must generate and store a value for every existing row.
-- This triggers a full table rewrite.
ALTER TABLE users ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY;
PG311 Atlas Pro
Changing a table's access method requires a full table and index rewrite. During this operation, an
ACCESS EXCLUSIVE lock is acquired on
the table, preventing any level of access, including SELECT.
-- This triggers a full table and index rewrite.
ALTER TABLE users SET ACCESS METHOD columnar;
Check-Level Policy Atlas Pro
Atlas Pro users can customize the behavior of individual checks using the check block. Two options are available:
error and skip. Setting a check to error will cause migration linting to fail if the check is violated,
while the skip option ignores the check and does not report it.
lint {
check "PG301" {
error = true
}
check "PG304" {
skip = true
}
}
To set an environment-specific policy, embed the lint block inside an env block:
env "ci" {
lint {
destructive {
error = false
}
# Only table drops can cause CI to fail. The rest are warnings only.
check "DS102" {
error = true
}
}
}