Skip to main content

Testing Schemas

The atlas schema test command allows you to write tests for your database schema using familiar software testing paradigms.

Atlas Pro Feature

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

atlas login

Introduction​

Atlas schema 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​

  • --url (-u) - a list of URLs to the tested schema: can be a database URL, an HCL, SQL or ORM schema, or a migration directory.
  • --dev-url - a URL to the Dev-Database to run the tests on.
  • --run (optional) - run only tests matching the given regexp.

Examples​

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

The test "schema" block​

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

  • skip (bool) - indicates whether the test case should be executed or skipped (can be computed). Defaults to false.
  • parallel (bool) - indicates whether this test case can be executed in parallel with all other cases defined this way. Test cases that are stateless should be set as parallel tests to speed up test time.

Before running a test case, Atlas creates the desired schema on the dev-database, making the environment ready for testing, and cleans the schema after the test is done, regardless of its result.

Example​

test "schema" "postal" {
  parallel = true
  # The "exec" command is explained below.
  exec {
    sql = "select '12345'::us_postal_code"
  }
  # The "catch" command is explained below.
  catch {
    sql = "select 'hello'::us_postal_code"
  }
}

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:

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.
schema.test.hcl
test "schema" "postal" {
  # Expected exec to pass.
  exec {
    sql = <<SQL
CREATE TABLE t (a INT, b TEXT);
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.
schema.test.hcl
test "schema" "postal" {
  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.
schema.test.hcl
test "schema" "postal" {
  assert {
    sql = "SELECT json_valid('{}')"
  }
  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.
schema.test.hcl
test "schema" "seed" {
  exec {
    sql = "SELECT seed()"
  }
  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.

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:

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

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

Table Driven Tests​

Test blocks support the for_each meta-argument, which accepts a map or a set of values and is used to generate a test case for each item in the set or map. See the example below:

schema.test.hcl
test "schema" "seed" {
  for_each = [
    {input: "hello", expected: "HELLO"},
    {input: "world", expected: "WORLD"},
  ]
  log {
    message = "Testing ${each.value.input} -> ${each.value.expected}"
  }
  exec {
    sql    = "SELECT upper('${each.value.input}')"
    output = each.value.expected
  }
}
atlas schema test --env dev
Test Output
-- PASS: seed/1 (901µs)
    schema.test.hcl:6: Testing hello -> HELLO
-- PASS: seed/2 (89µs)
    schema.test.hcl:6: Testing world -> WORLD
PASS