Testing Migration Plans

The atlas schema plan test command allows you to write tests for your declarative schema migrations, which are essentially plan files. This feature enables you to test the logic of 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 plan files. If you are using the versioned migration workflow, refer to the atlas migrate 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 way popular databases (such as PostgreSQL) 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

--dev-url - a URL to the Dev-Database to run the tests on.
--run (optional) - run only tests matching the given regexp.

Examples

Test Directories
Test Files

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

atlas schema plan test --env local plan.test.hcl

The `test "plan"` block

The test "plan" "<name>" block describes a test case for a plan file. 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 zero state of the schema, but expect at least two blocks: schema and apply. The schema block brings the database to the state defined by the provided schema, and the apply block executes the plan file on it. 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.

Drift Detection Check

A declarative migration plan includes two attributes, From and To, that describe the schema transition it defines. Each attribute is a fingerprint of the schema state it represents: From reflects the current state of the schema before applying the plan, and To the desired state after applying the plan. Therefore, the From attribute in the plan must match the schema state defined in the schema block. Otherwise, the test case will fail.

Example

test "plan" "20240613061102" {
  # Initial state is schema.v1.sql.
  schema {
    url = "file://snapshots/schema.v1.sql"
  }
  # Insert some test data.
  exec {
    sql = <<-SQL
    INSERT INTO "users" ("name") VALUES
      ('Ariel Mashraki'),
      ('Rotem Tamir'),
      ('Giau Tran Minh'),
      ('Dat')
    SQL
  }
  # Apply the tested plan.
  apply {
    url = "file://plans/20240613061102.plan.hcl"
  }
  # Verify the correctness of the data migration.
  exec {
    sql    = "SELECT first_name, last_name FROM users ORDER BY id"
    format = table
    output = <<-TAB
first_name | last_name
------------+-----------
 Ariel      | Mashraki
 Rotem      | Tamir
 Giau       | Tran Minh
 Dat        |
    TAB
  }
}

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:

`schema` command

The strategy for testing a specific migration plan is to set the schema block to the state defined by the From attribute (before applying the plan). Then, insert test data, apply the tested migration plan to ensure it passes, and then run assertions to verify its correctness.

Unlike versioned-migration, the different versions of the schema are stored in VCS (version control system, such as Git) rather than the migration directory. Hence, in order to use the schema at a specific version/state, you should provide a URL that points to this schema state.

Currently, there are three ways to access the schema state in its previous versions:

Atlas Registry: Read the schema state from the registry at a specific version or tag using the atlas:// scheme. e.g., atlas://<NAME>?tag=<TAG>.
Local Copy: Create a versions/ or snapshots/ directory to store schema dumps of different tested versions and access them using the file:// scheme. To dump the schema state into a file, refer to the schema inspect command.
Data Source: Read the schema state from an external service using the external_schema data source.

From Atlas Registry
From External Source

plan.test.hcl
test "plan" "20240613061102" {
  # Initial state is schema.v1.sql.
  schema {
    url = "atlas://repo-name?tag=v1"
  }
  # Insert some test data.
  exec {
    sql = <<-SQL
    INSERT INTO "users" ("name") VALUES
      ('Ariel Mashraki'),
      ('Rotem Tamir'),
      ('Giau Tran Minh'),
      ('Dat')
    SQL
  }
  # Apply the tested plan.
  apply {
    url = "file://plans/20240613061102.plan.hcl"
  }
  # Verify the correctness of the data migration.
  exec {
    sql    = "SELECT first_name, last_name FROM users ORDER BY id"
    format = table
    output = <<-TAB
first_name | last_name
------------+-----------
 Ariel      | Mashraki
 Rotem      | Tamir
 Giau       | Tran Minh
 Dat        |
    TAB
  }
}

Read the state using the external_schema data source and inject to the tests the URL to it as a variable:

atlas.hcl
data "external_schema" "github-v1" {
  program = [
    "bash", "-c",
    "gh api repos/OWNER/REPO/contents/FILE?ref=TAG --jq '.content' | base64 -d",
    # For example: repos/octocat/hello-world/contents/schema.sql?ref=v1
  ]
}

env "dev" {
  test {
    schema {
      src = ["plan.test.hcl"]
      vars = {
        schema-v1 = data.external_schema.github-v1.url
      }
    }
  }
}

Then, use the variable in the test case:

plan.test.hcl
variable "schema-v1" {
  type = string
}

test "plan" "20240613061102" {
  # Initial state is schema.v1.sql.
  schema {
    url = var.schema-v1
  }
  # Insert some test data.
  exec {
    # ...
  }
  # Apply the tested plan.
  apply {
    url = "file://plans/20240613061102.plan.hcl"
  }
  # Verify the correctness of the data migration.
  exec {
    # ...
  }
}

Testing Remote Plans

The examples in this document uses the file:// scheme to show how to test local migration plans while developers work on them. However, users that are not interested in storing these files in VCS, can push them to the registry and use their URL in the apply block instead.

`apply` command

The apply command expects a plan file to apply. Note, the From attribute in the plan must match the schema state defined in the schema block.

url (string) - The URL to the plan file to apply.

`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.

plan.test.hcl
test "plan" "20240613061102" {
  schema {
    url = "atlas://repo-name?tag=v1"
  }
  # Expected exec to pass.
  exec {
    sql = <<SQL
INSERT INTO t VALUES (1, 'one');
INSERT INTO t VALUES (2, 'two');
SQL
  }
  apply {
    url = "file://plans/20240613061102.plan.hcl"
  }
  # 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.

plan.test.hcl
test "plan" "20240613061102" {
  # ...
  apply {
    url = "file://plans/20240613061102.plan.hcl"
  }
  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.

plan.test.hcl
test "plan" "20240613061102" {
  # ...
  apply {
    url = "file://plans/20240613061102.plan.hcl"
  }
  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.

plan.test.hcl
test "plan" "20240613061102" {
  schema {
    url = "atlas://repo-name?tag=v0"
  }
  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:

users.test.hcl
migrate_test.go
atlas.hcl

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

test "plan" "20240613061102" {
  # Initial state is schema.v1.sql.
  schema {
    url = "file://snapshots/schema.v1.sql"
  }
  # Seed our database using an external program.
  external {
    program = [
      "go", "test",
      "-run", "Test_20240613061102_Seed",
      "--dev-url", testing.dev_url,
    ]
    working_dir = var.working_dir
  }
  # Apply the tested plan and ensure is passes.
  apply {
    url = "file://plans/20240613061102.plan.hcl"
  }
}

tests/users_test.go
package tests

import (
	"database/sql"
	"flag"
	"os"
	"testing"

	_ "github.com/lib/pq"
	"github.com/stretchr/testify/require"
)

func TestMain(m *testing.M) {
	flag.Parse()
	os.Exit(m.Run())
}

func Test_20240613061102_Seed(t *testing.T) {
	require.NotEmpty(t, t, *dev)
	db, err := sql.Open("postgres", *dev)
	require.NoError(t, err)
	defer db.Close()
    // ...
}

atlas.hcl
env "dev" {
  dev = "docker://postgres/15/dev?search_path=public"
  test {
    schema {
      src = ["tests"]
      vars = {
        working_dir = "tests"
      }
    }
  }
}

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:

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

test "plan" "v1-v2" {
  schema {
    url = "file://snapshots/schema.v1.sql"
  }
  exec {
    sql = file(var.seed_file)
  }
  apply {
    url = "file://plans/v1-v2.plan.hcl"
  }
}

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 {
    schema {
      src = ["plan.test.hcl"]
      vars = {
        seed_file = "filename.sql"
        variable2 = var.name
        variable3 = data.external.value
      }
    }
  }
}

atlas schema plan 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.

Introduction​

Flags​

Examples​

The test "plan" block​

Example​

schema command​

apply command​

exec command​

catch command​

assert command​

log command​

external command​

Input Variables​

Introduction

Flags

Examples

The `test "plan"` block

Example

`schema` command

`apply` command

`exec` command

`catch` command

`assert` command

`log` command

`external` command

Input Variables