Skip to main content

Testing Data Scripts: the script command

Data Scripts are tested with the same testing framework as schemas and migrations. Because a script is code rather than an ad-hoc SQL session, it is tested like code: versioned and reviewed alongside your schema, and verified in CI against a real database before it runs in production.

A script command inside a test "schema", test "migrate", test "plan", or test "script" block loads a script file, runs the scripts matching run, and asserts on the result, along two axes:

  • Logic: the script produces the right result, asserted with output, match, error, or a follow-up read.
  • Privileges: the script runs with the right access, by running it under a reduced role or login to verify that the operations it was granted succeed and the ones it was not are rejected.

Examples​

Every tab tests these scripts:

scripts.hcl
script "query" "pending_count" {
  query "count" {
    sql    = "SELECT count(*) FROM orders WHERE status = 'pending'"
    format = CSV
  }
}

script "exec" "cancel_pending" {
  exec {
    sql = "UPDATE orders SET status = 'canceled' WHERE status = 'pending'"
  }
}

script "query" "whoami" {
  query "who" {
    sql    = "SELECT current_user"
    format = CSV
  }
}

The script runs against the desired schema. This seeds the table, runs the mutation, and verifies the effect by reading it back:

scripts.test.hcl
test "schema" "cancel_pending" {
  exec {
    sql = "INSERT INTO orders (id, status) VALUES (1, 'pending'), (2, 'pending'), (3, 'shipped')"
  }
  script "query" {
    file   = "scripts.hcl"
    run    = "^pending_count$"
    output = "2"
  }
  script "exec" {
    file = "scripts.hcl"
    run  = "^cancel_pending$"
  }
  script "query" {
    file   = "scripts.hcl"
    run    = "^pending_count$"
    output = "0"
  }
}
Example execution

Run the case against the environment's dev database:

atlas schema test --env dev --run cancel_pending

Two of the three seeded orders are pending, so the first pending_count prints 2; after cancel_pending runs, the second prints 0:

Test Output
-- PASS: cancel_pending (2ms)
PASS

Choosing the test Block​

The four blocks differ in the state the script runs against:

  • test "schema" runs the script against the current schema version. The tables exist but hold no data, so seed them with exec commands to reproduce the data the script expects, then run the script and assert on the result.
  • test "migrate" runs the script at a point in migration history. Migrate to the version you want to test against, seed the data, run the script, and assert. This is how you verify that a script written today still works against the schema version it will run on in production, or that a script shipped alongside a migration behaves correctly once that migration is applied.
  • test "plan" runs the script as part of a plan test, so a script that accompanies a planned change is exercised in the same case that asserts the plan. Run these with atlas schema plan test.
  • test "script" starts from a fresh state with no schema in place. Create the tables and constraints the script depends on with exec, which keeps the case self-contained and pins exactly which parts of the schema the script relies on.

Running the Tests​

Declare the test files on the environment, then run them with the verb that matches the test block:

atlas.hcl
env "dev" {
  src = "file://schema.sql"
  dev = "docker://postgres/15/dev"
  test {
    schema {
      src = ["scripts.test.hcl"]
    }
  }
}
atlas schema test --env dev

Add --run <regexp> to any of them to run a single case.

The script Command​

script "exec", script "query", or script "loop" runs the scripts of that kind whose name matches run:

  • file (string): the script file to load.
  • run (optional): a regexp that selects which scripts of the command's kind run. Anchor it (^archive_all$) to match one script exactly. A run that matches nothing fails the test with no exec script matches "..." in scripts.hcl. Omit it to run every script of that kind in the file, in file order.
  • vars (optional map): values bound to the scripts' variable blocks.
  • output (optional): the expected result, compared exactly after whitespace normalization.
  • match (optional): a regexp matched against the result, when an exact output is too strict.
  • error (optional): expects the run to fail, and is a regexp matched against the failure message. Use it for an operation the principal is not granted, or for a failing in-script assert, check, or expect_rows.
  • as (optional block): runs the scripts under another principal. See Testing privileges.

output, match, and error are mutually exclusive. output and match compare everything the script prints: the result sections of a query script and the output lines of any kind, including a query script's own. Only a loop's log messages, which are diagnostics, are excluded.

vars binds values to the script's variable blocks, so one script covers several cases:

scripts.hcl
variable "min_id" {
  type = number
}

script "query" "by_id" {
  query "q" {
    sql    = "SELECT id FROM users WHERE id >= ? ORDER BY id"
    args   = [var.min_id]
    format = CSV
  }
}
scripts.test.hcl
script "query" {
  file   = "scripts.hcl"
  run    = "^by_id$"
  vars   = { min_id = 2 }
  output = "2"
}

Asserting Logic​

A script that reports what it did through an output block is asserted directly. For example, this archive script prints one line, and the test compares it:

scripts.hcl
script "exec" "archive_dormant" {
  # ...
  output {
    message = "archived ${length(query.dormant.rows)} dormant accounts"
  }
}
scripts.test.hcl
script "exec" {
  file   = "scripts.hcl"
  run    = "^archive_dormant$"
  output = "archived 3 dormant accounts"
}

To assert a change the script does not print, read the effect back with a script "query", as the test "schema" example does. For example, this runs a purge loop and checks how many rows survive:

scripts.test.hcl
test "schema" "purge" {
  exec {
    sql = "INSERT INTO users (id, active) VALUES (1, true), (2, false), (3, false), (4, false)"
  }
  script "loop" {
    file = "scripts.hcl"
    run  = "^purge_inactive$"
  }
  script "query" {
    file   = "scripts.hcl"
    run    = "^remaining$"
    output = "1"
  }
}

The test runs on the dev database: Atlas prepares the state described by the test block, runs the case, and cleans up after it, regardless of the result. A failing guard inside the script (assert, check, expect_rows) fails the script command, and with it the test case, unless the command declares an error that matches it.

Testing Privileges​

The as block runs the matched scripts under another principal. There are two options:

  • as { role = "..." } switches the session to the role.
  • as { user = "...", password = "..." } connects separately as that login.

Exactly one of role or user must be set. password is valid only together with user, and may be omitted for a login that needs none. On SQL Server, role names a database user, typically one created WITHOUT LOGIN in the test's setup.

The test runs the real grants against a real database, so a passing case is a guarantee about the deployment identity: the script does the work it was granted, and the operations it was not granted are rejected. Point as at the role the deployment uses, and because roles and users are managed as code in the same schema, the role under test and the role in production come from one definition.

A role defined in the tested schema is already there when the case runs, so pass its name straight to as. Otherwise, create a temporary principal in the setup exec and drop it in cleanup, which is enough to validate the privilege constraints the script runs under.

The available form, the setup that creates the principal, and the error the engine reports differ per engine. For example, each case below asserts the identity the scripts run under, a granted read, and a rejected out-of-scope write:

scripts.test.hcl
test "schema" "reporter_scope" {
  # A least-privileged role: may read orders, not write them.
  exec {
    sql = <<-SQL
      CREATE ROLE reporter;
      GRANT SELECT ON orders TO reporter;
      INSERT INTO orders (id, status) VALUES (1, 'pending'), (2, 'shipped');
    SQL
  }
  # A bare DROP ROLE fails while grants exist.
  cleanup {
    sql = "DROP OWNED BY reporter; DROP ROLE IF EXISTS reporter;"
  }
  script "query" {
    file   = "scripts.hcl"
    run    = "^whoami$"
    as { role = "reporter" }
    output = "reporter"
  }
  script "query" {
    file   = "scripts.hcl"
    run    = "^pending_count$"
    as { role = "reporter" }
    output = "1"
  }
  script "exec" {
    file  = "scripts.hcl"
    run   = "^cancel_pending$"
    as { role = "reporter" }
    error = "permission denied"
  }
}

A table outside public needs one more grant, since only public grants USAGE to everyone by default: GRANT USAGE ON SCHEMA <name> TO reporter;