Skip to main content

Testing Migrations

The atlas migrate test command allows you to write tests for your schema migrations. This feature enables you to test logic in your migrations, commonly data migrations, in a concise and straightforward way.

The command is similar to atlas schema test but is focused on testing schema migrations. If you are using the declarative migration workflow, refer to the atlas schema plan test documentation.

Atlas Pro Feature

Migrations testing works only for Atlas Pro users, free and paid. Use the following command to use this feature:

atlas login

Introduction​

Atlas migration testing is inspired by the popular databases in the way they test their public (and private) APIs. The structure is defined in HCL files (suffixed with a .test.hcl), but the underlying testing logic is written in plain SQL. The following document describes the different structure options, flags, and capabilities supported by the testing framework.

Flags​

  • --dir the URL to the migration directory, by default it is file://migrations.
  • --dev-url - a URL to the Dev-Database to run the tests on.
  • --run (optional) - run only tests matching the given regexp.

Examples​

atlas migrate test --dev-url "docker://postgres/15/dev" .

The test "migrate" block​

The test "migrate" "<name>" block describes a test case. The second label defines the test case name, and the following arguments are supported:

  • skip (bool) - indicates whether the test case should be executed or skipped (can be computed). Defaults to false.

Every test case starts with the zero state of the migration directory, and calls to the migrate command blocks migrate it to the specified version. At the end of the execution, Atlas cleans up the dev-database and prepares it for the next test case, regardless of the test result.

Example​

test "migrate" "20240613061102" {
  # Migrate to version 20240613061046.
  migrate {
    to = "20240613061046"
  }
  # Insert some test data.
  exec {
    sql = "insert into users (name) values ('Ada Lovelace')"
  }
  # Migrate to version 20240613061102.
  migrate {
    to = "20240613061102"
  }
  # Verify the correctness of the data migration.
  exec {
    sql = "select first_name,last_name from users"
    output = "Ada, Lovelace"
  }
}
info
  • Unlike schema test cases, migration tests cannot be run in parallel.
  • The skip argument or the --run flag can be used to test only migrations that are relevant to the current development. For example, testing only the latest migration.

A test case is composed of zero or more commands that are executed in order, and it is aborted if any of the commands fail. The supported commands are:

migrate command​

The migrate commands migrates the dev-database used by the test case to the desired version.

The strategy for testing a specific version is to execute migrate to the version before it, insert test data, migrate to the tested version to ensure it passes, and then run assertions to verify its correctness.

migrate.test.hcl
test "migrate" "20240613061102" {
  # Migrate to one version before the tested version.
  migrate {
    to = "20240613061046"
  }
  # Insert some test data.
  exec {
    sql = "insert into users (name) values ('Ada Lovelace')"
  }
  # Migrate to the tested version.
  migrate {
    to = "20240613061102"
  }
  # Verify the correctness of the data migration.
  exec {
    sql = "select first_name,last_name from users"
    output = "Ada, Lovelace"
  }
}

exec command​

The exec command expects an SQL statement to pass. If output or match is defined, the output of the SQL statement is compared to the expected value.

  • sql (string) - the SQL statement to execute.
  • format (optional) - the format of the output (default: csv). Can be table or csv.
  • output (optional) - the expected output of the SQL statement.
  • match (optional) - a regular expression to match the output of the SQL statement.
migrate.test.hcl
test "migrate" "20240613061046" {
  migrate {
    to = "20240613061046"
  }
  # Expected exec to pass.
  exec {
    sql = <<SQL
INSERT INTO t VALUES (1, 'one');
INSERT INTO t VALUES (2, 'two');
SQL
  }
  # Expected exec to pass and output
  # be equal to the expected table.
  exec {
    sql = "SELECT a, b FROM t;"
    format = table
    output = <<TAB
 a |  b
---+-----
 1 | one
 2 | two
TAB
  }
}

catch command​

The catch command expects an SQL statement to fail. If error is defined, the error message is compared to the expected value.

  • sql (string) - the SQL statement to execute.
  • error (optional) - matches the error message.
migrate.test.hcl
test "migrate" "20240613061046" {
  migrate {
    to = "20240613061046"
  }
  catch {
    sql   = "SELECT 1+"
    error = "incomplete input"
  }
}

assert command​

The assert command expects an SQL statement to pass and the output to be a single row (+column) with a true value.

  • sql (string) - the SQL statement to execute.
  • error_message (optional) - the error message to display if the assertion fails.
migrate.test.hcl
test "migrate" "20240613061046" {
  migrate {
    to = "20240613061046"
  }
  assert {
    sql = "SELECT json_valid('{')"
    error_message = "expects a valid JSON"
  }
}

log command​

The log command logs a message to the test output. It can be useful to report the progress of the test case.

  • message (string) - the message to log.
migrate.test.hcl
test "migrate" "20240613061046" {
  migrate {
    to = "20240613061046"
  }
  log {
    message = "Seeded the database"
  }
}

external command​

The external command runs an external program and expects it to pass. If output or match is defined, the output (stdout) of the program is compared to the expected value.

  • program ([]string) - The first element of the string is the program to run. The remaining elements are optional command line arguments.
  • working_dir (optional) - The working directory to run the program from. Defaults to the current working directory.
  • output (optional) - The expected output of the program.
  • match (optional) - A regular expression to match the output of the program.

The example below demonstrates how to run Go tests within an Atlas test case in order to seed the database before testing a migration:

migrations/tests/migrate.test.hcl
variable "working_dir" {
  type = string
}

test "migrate" "20240908145916" {
  # Migrate the database to one version before the one we are testing.
  migrate {
    to = "20240908145846"
  }
  # Seed our database using an external program.
  external {
    program = [
      "go", "test",
      "-run", "Test_20240908145916_Seed",
      "--dev-url", testing.dev_url,
    ]
    working_dir = var.working_dir
  }
  # Migrate to the tested version, and ensure it passes.
  migrate {
    to = "20240908145916"
  }
}
Test case variables

The testing object is available in the test case scope and contains the following attributes:

  • name - the name of the test case.
  • dev_url - the URL to the dev-database used by the test case.

Input Variables​

Test files can be parameterized using variables, and their values can be set through the atlas.hcl config file. For example, given this test file:

migrate.test.hcl
variable "seed_file" {
  type = string
}

test "migrate" "seed" {
  migrate {
    to = "20240613061046"
  }
  exec {
    sql = "SELECT seed('${var.seed_file}')"
  }
}

Test config can be defined on the env block (or globally) and executed using the --env flag:

atlas.hcl
env "dev" {
  src = "<schema to test>"
  dev = "<docker-based dev-url>"

  # Test configuration for local development.
  test {
    migrate {
      src = ["migrate.test.hcl"]
      vars = {
        seed_file = "filename.sql"
        variable2 = var.name
        variable3 = data.external.value
      }
    }
  }
}
atlas migrate test --env dev
Input Variables

Input variables can be defined statically per environment, injected from the CLI using the --var flag, or computed from a data source.