Pre-migration checks

Atlas supports the concept of pre-migration checks, where each migration version can include a list of assertions (predicates) that must evaluate to true before the migration is applied. For example, before dropping a table, we aim to ensure that no data is deleted and the table must be empty, or we check for the absence of duplicate values before adding a unique index to a table.

Atlas Pro Feature

Pre-migration checks are available only to Atlas Pro users. To use this feature, run:

atlas login

atlas:txtar directive

Atlas supports a text-based file archive to describe "migration plans". Unlike regular migration files, which mainly contain a list of DDL statements (with optional directives), Atlas txtar files (currently) support two file types: migration files and pre-execution check files.

Each file entry in the archive starts with a marker line formatted as -- FILENAME -- and is followed by one or more lines containing the file content (e.g., SQL statements, assertions, etc.). The file ends at the next marker line or when the archive ends. For example:

One check file

The code below presents the most standard example of pre-migration checks. The default checks file is named checks.sql, and the migration.sql file contains the actual DDLs to be executed on the database in case the assertions are passed.

20240201131900_drop_users.sql

-- atlas:txtar

-- checks.sql --
-- The assertion below must be evaluated to true. Hence, table users must be empty.
SELECT NOT EXISTS(SELECT * FROM users);

-- migration.sql --
-- The statement below will be executed only if the assertion above is evaluated to true.
DROP TABLE users;

Multiple check files

The example below presents a case where an archive file includes two check files:

• checks/users.sql - The default policy for check files dictates that all assertions must be evaluated to true.
• checks/roles.sql - This check file utilizes the atlas:assert oneof directive, indicating that at least one of the assertions must be evaluated to true.

20240201131900_drop_users.sql

-- atlas:txtar

-- checks/users.sql --
-- All assertions below must be evaluated to true. The tables mentioned below must be empty.
SELECT NOT EXISTS(SELECT * FROM internal_users);
SELECT NOT EXISTS(SELECT * FROM external_users);

-- checks/roles.sql --
-- atlas:assert oneof

-- At least one of the assertions below must be evaluated to true. Hence, we drop
-- the "roles" table only if it is empty, or there are no associations to it.
SELECT NOT EXISTS(SELECT * FROM roles);
SELECT NOT EXISTS(SELECT * FROM user_roles);

-- migration.sql --
-- The statements below will be executed only if the check files above are passed.
DROP TABLE internal_users;
DROP TABLE external_users;
DROP TABLE roles;

File directives

The atlas:txtar file directive is located at the top of the migration file and should not be associated with any statement. Therefore, double new lines (\n\n) are used to separate file directives from its content.

Examples

Executing the migration examples defined above will result in the following output:

Pre-execution checks passed

If the pre-execution checks pass, the migration will be applied, and Atlas will report the results.

atlas migrate --dir atlas://app --env prod

Output

Migrating to version 20240201131900 from 20240201131800 (1 migrations in total):

  -- checks before migrating version 20240201131900
    -> SELECT NOT EXISTS(SELECT * FROM users);
  -- ok (624.004µs)

  -- migrating version 20240201131900
    -> DROP TABLE users;
  -- ok (5.412737ms)

  -------------------------
  -- 22.138088ms
  -- 1 migration
  -- 1 check
  -- 1 sql statement

Pre-execution checks failed

If the pre-execution checks fail, the migration will not be applied, and Atlas will exit with an error.

atlas migrate --dir atlas://app --env prod

Output

Migrating to version 20240201131900 from 20240201131800 (1 migrations in total):

  -- checks before migrating version 20240201131900
    -> SELECT NOT EXISTS(SELECT * FROM internal_users);
    -> SELECT NOT EXISTS(SELECT * FROM external_users);
  -- ok (1.322842ms)

  -- checks before migrating version 20240201131900
    -> SELECT NOT EXISTS(SELECT * FROM roles);
    -> SELECT NOT EXISTS(SELECT * FROM user_roles);
    2 of 2 assertions failed: check assertion "SELECT NOT EXISTS(SELECT * FROM user_roles);" returned false

  -------------------------
  -- 19.396779ms
  -- 1 migration with errors
  -- 2 checks ok, 2 failures
Error: 2 of 2 assertions failed: check assertion "SELECT NOT EXISTS(SELECT * FROM user_roles);" returned false