Skip to main content

Transactional Mutations: script exec

A script "exec" runs a set of writes with SQL guardrails: controlled mutations that run only when the data is in the expected state, such as archiving a user, backfilling a column, or moving dormant rows to cold storage.

Unlike an ad-hoc SQL command, the mutation is defined as code. It can be versioned, reviewed, reused, and tested consistently across environments. Tests can run it, assert expected failures, and verify the resulting database state. They can also run the script under a reduced role or login to verify that the intended operations succeed while out-of-scope operations are denied.

Each script is a small program whose body is a sequence of blocks that run in textual order against the connected database. On databases and operations that support transactions, the statements run in one transaction by default, with SQL guards checking preconditions along the way. If a guard fails, the run aborts and every write is rolled back, so a half-applied mutation never survives.

Examples​

Common mutations as code. Each tab is a complete script, and each Example execution block shows an end-to-end run on SQLite.

This copies dormant accounts to an archive table and deletes them, as one transaction, guarded so it does the whole job or nothing:

archive.hcl
script "exec" "archive_dormant" {
  # Gate: stop cleanly if there is nothing to archive.
  condition "have_dormant" {
    sql = "SELECT count(*) > 0 FROM accounts WHERE last_seen < '2020-01-01'"
  }

  # Read the dormant rows once and bind them for the writes below.
  query "dormant" {
    sql = "SELECT id FROM accounts WHERE last_seen < '2020-01-01' ORDER BY id"
    rows {
      id = int
    }
  }

  # Copy to the archive, then delete, each keyed off the same captured ids, and
  # each asserting it touched exactly the rows the read found.
  exec "snapshot" {
    sql = <<-SQL
      INSERT INTO archive (id, email)
      SELECT id, email FROM accounts
      WHERE id IN (SELECT value FROM json_each(?))
    SQL
    args        = [jsonencode(query.dormant.rows[*].id)]
    expect_rows = length(query.dormant.rows)
  }
  exec "purge" {
    sql = <<-SQL
      DELETE FROM accounts
      WHERE id IN (SELECT value FROM json_each(?))
    SQL
    args        = [jsonencode(query.dormant.rows[*].id)]
    expect_rows = length(query.dormant.rows)
  }

  # Verify the invariant before committing.
  assert "archived_all" {
    sql  = "SELECT count(*) = ? FROM archive"
    args = [length(query.dormant.rows)]
  }

  output {
    message = "archived ${length(query.dormant.rows)} dormant accounts"
  }
}
Example execution

The example is self-contained on SQLite. Set up a database with sqlite3 archive.db < setup.sql:

setup.sql
CREATE TABLE accounts (id INTEGER PRIMARY KEY, email TEXT, last_seen TEXT);
CREATE TABLE archive  (id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO accounts VALUES
  (1, 'ada@example.com',  '2024-06-01'),
  (2, 'bob@example.com',  '2019-03-01'),
  (3, 'cara@example.com', '2018-11-01'),
  (4, 'dan@example.com',  '2024-01-15'),
  (5, 'eve@example.com',  '2017-07-20');

Run the script:

atlas script exec --url "sqlite://archive.db" --file "file://archive.hcl" --run archive_dormant
Executing script "archive_dormant" (archive.hcl:1):

  -- tx open
  -- condition "have_dormant" ok (archive.hcl:3)
  -- exec "snapshot" (archive.hcl:17)
    -> INSERT INTO archive (id, email)
       SELECT id, email FROM accounts
       WHERE id IN (SELECT value FROM json_each(?))
  -- ok (400.584µs) | 3 rows affected

  -- exec "purge" (archive.hcl:26)
    -> DELETE FROM accounts
       WHERE id IN (SELECT value FROM json_each(?))
  -- ok (48.791µs) | 3 rows affected

  -- assert "archived_all" ok (archive.hcl:36)
  -- output (archive.hcl:41): archived 3 dormant accounts
  -- tx commit

  -------------------------
  -- 815.708µs
  -- 2 statements
  -- 1 assertion passed

Reading the report:

  • tx open: the whole body runs in one transaction, the default AUTO mode.
  • condition "have_dormant" ok: the gate passed. Had no account been dormant it would stop here and commit nothing.
  • The bound query "dormant" runs silently (a rows query prints nothing) and captures the dormant ids for the writes, which is why it has no line of its own.
  • exec "snapshot" and exec "purge" each report 3 rows affected, and each expect_rows = length(query.dormant.rows) asserts they touched exactly the 3 captured rows. A mismatch would abort and roll back.
  • assert "archived_all" ok, then the output line, then tx commit.
  • The closing summary counts exec statements only: conditions, asserts, and bound queries are not statements.

The two recent accounts remain, and the three dormant ones now live in archive. Had any guard failed, the default on_error = ROLLBACK would have left both tables untouched.

The tx Block​

The optional tx block sets the transaction policy. mode = AUTO (the default) wraps the whole body in one transaction; mode = NONE runs the statements without a transaction. on_error = ROLLBACK (the default) discards the transaction's work when the script fails; on_error = COMMIT keeps it.

script "exec" "archive_user" {
  tx {
    mode     = AUTO      # AUTO, NONE
    on_error = ROLLBACK  # ROLLBACK, COMMIT
  }
  # ...
}

Omitting the tx block is the same as setting mode = AUTO and on_error = ROLLBACK. Enum values may be bare or quoted (AUTO or "AUTO"), and a typo fails at parse time.

note

on_error decides what happens to the transaction on failure, not whether the script fails. A failing assert always aborts the run; on_error only controls whether its writes are rolled back or committed.

Execution Order​

The blocks in an exec body run in the order they are written, inside one transaction. A run ends in one of three ways: the body completes and the transaction commits, an unmet condition stops the script and commits the work done so far, or a failing assert or check aborts the run and rolls it back.

An exec body must do something: it needs at least one exec, assert, or check. A body of only a bound query and an output is rejected at parse time with exec kind requires at least one 'assert', 'check', or 'exec' block. Use a script "query" for a read that only prints.

Because blocks run in written order, a guard checks the state left by the writes before it:

script "exec" "stepping" {
  assert "n_is_0"  { sql = "SELECT n = 0 FROM steps" }
  exec             { sql = "UPDATE steps SET n = n + 1" }
  assert "n_is_1"  { sql = "SELECT n = 1 FROM steps" }
  assert "still_1" { sql = "SELECT n = 1 FROM steps" }
  exec             { sql = "UPDATE steps SET n = n + 10" }
}

Starting from n = 0, this script leaves n = 11. Each assert checks the value produced by the exec before it.

The condition Block​

A condition block determines whether the script should continue. Its sql must return one row with a single boolean (or 1/0) column. A truthy value lets the script continue, and a falsy value (boolean false or 0) stops it gracefully at that point. No failure is reported, and the work done so far is committed. Use it to stop a run that has nothing to do. For example, the script below inserts a row only when the items table is not empty; against an empty table the condition is false and the exec never runs:

script "exec" "x" {
  condition "needs_rows" {
    sql = "SELECT count(*) > 0 FROM items"
  }
  exec { sql = "INSERT INTO items (qty) VALUES (1)" }
}

A condition is not required to appear before the writes. A condition placed after an exec runs in its written position and sees that write's effect. If it stops the script there, the prior write is committed; a graceful stop is not a rollback. This is specific to the exec kind: in a script "loop", condition is valid only before the loop.

NULL results

Only a non-NULL falsy value (boolean false or 0) stops gracefully. A NULL result is an error that fails the run, just as it does for assert. Under the default on_error = ROLLBACK a failing condition rolls back any prior work, the opposite of a graceful stop. Write the condition's sql so it always returns a concrete boolean, for example by wrapping a nullable expression in COALESCE(..., 0).

The assert Block​

An assert block verifies that an invariant holds and fails the run if it does not. Its sql returns one row with one boolean column, like condition, but a falsy result fails the whole run instead of stopping it gracefully. A NULL result also fails the run. Set error_message to control the message returned to the user.

script "exec" "archive" {
  assert "is_active" {
    sql           = "SELECT status = 'active' FROM users WHERE id = 1"
    error_message = "user must be active"
  }
  exec {
    sql = "UPDATE users SET status='archived' WHERE id = 1"
  }
}

If the user is not active the assert fails with user must be active, the run aborts, and the exec is never reached.

A body of only assertions is valid, which makes an exec script a smoke test or a post-deploy verifier: it writes nothing, and the run fails when an invariant no longer holds. For example, this checks that no order references a missing user:

verify.hcl
script "exec" "no_orphan_orders" {
  assert "orders_have_users" {
    sql           = "SELECT count(*) = 0 FROM orders WHERE user_id NOT IN (SELECT id FROM users)"
    error_message = "orders reference missing users"
  }
}

The check Block​

A check block compares query output to an expected value. It runs sql, serializes the result per format (CSV by default, or TABLE), and compares the text to the expectation. Use output for an exact match or match for a regular expression. The two are mutually exclusive. A passing check lets the script proceed; a failing one aborts it.

output is compared after trimming leading and trailing newlines only, so surrounding spaces are significant: output = " 2 " fails against 2. Under format = TABLE, per-line padding is ignored. A check with neither output nor match asserts only that the query returned a row, and fails with query returned no rows otherwise.

script "exec" "value_checks" {
  check "exact_count" {
    sql    = "SELECT count(*) FROM users"
    output = "2"
  }
  check "name_regex" {
    sql   = "SELECT name FROM users WHERE id = 1"
    match = "^ada$"
  }
  exec { sql = "INSERT INTO marker (ok) VALUES (1)" }
}

The exec Block​

An exec block runs a mutation statement or batch. The name label is optional and appears in the report. args binds values into the statement's placeholders in order, using the driver-native marker, ? on MySQL for example. The optional expect_rows asserts the number of rows the statement affects, and a mismatch aborts the script.

For example, the script below binds the id and email into the statement's placeholders:

script "exec" "seed" {
  exec {
    sql  = "INSERT INTO users (id, email) VALUES (?, ?)"
    args = [1, "ada@example.com"]
  }
}

Rollback Semantics​

Because the body runs inside one transaction, the point at which a guard fails determines what survives. A guard that fails before any exec aborts the run before anything is written: with an inactive user, the is_active assert above fails and the row is untouched. A guard that fails after an exec rolls that write back under the default on_error = ROLLBACK. For example, the assert below fails after the UPDATE, so the transaction rolls back and the row reverts:

script "exec" "archive" {
  exec {
    sql = "UPDATE users SET status='archived' WHERE id = 1"
  }
  assert "wrong" {
    sql = "SELECT status = 'deleted' FROM users WHERE id = 1"
  }
}
Destructive writes

An exec script mutates data. Guard destructive writes with condition/assert/check and keep on_error = ROLLBACK (the default) so a failed run leaves no partial change. mode = NONE and on_error = COMMIT remove that safety net. Use them only when the writes should persist across a failure.

The query Block​

A query block reads a result once and makes it available to the blocks after it. It emits no output; the rows block names the result columns and binds them into scope for later steps. In an exec script, rows is required, since the script prints no result sections.

Use it when a write must affect exactly the rows a read captured. For example, the script below captures the inactive user ids and deletes exactly those rows:

script "exec" "purge_inactive" {
  query "inactive" {
    sql = "SELECT id FROM users WHERE active = 0"
    rows {
      id = int
    }
  }
  exec {
    sql         = "DELETE FROM users WHERE id IN (SELECT value FROM json_each(?))"
    args        = [jsonencode(query.inactive.rows[*].id)]
    expect_rows = length(query.inactive.rows)
  }
}

Later steps reference the bound result in three forms:

  • query.<name>.rows[*].<col> - the column as a list
  • query.<name>.rows[N].<col> - one row's column
  • length(query.<name>.rows) - the row count

The list form supports Terraform-style comprehensions: [for r in query.inactive.rows : r.id] equals rows[*].id and can also filter or transform the rows. To bind a list into a placeholder, encode it with jsonencode(...) and unpack it in SQL, as the example above does.

The output Block​

An output { message } block emits a line of script output, interpolating scope values. Use it to report what the mutation did. For example, the block below from the archive_dormant script prints archived 3 dormant accounts:

output {
  message = "archived ${length(query.dormant.rows)} dormant accounts"
}

Unlike a loop's log, which is diagnostics and is hidden under -q / --quiet along with the streaming report, an output line is the script's product and is printed either way.

Testing​

Exec scripts are tested with the Atlas testing framework: a test case seeds data, runs the scripts matching run with the script "exec" command, and asserts on the rows they leave behind or the error they fail with. An as block runs the same script under a reduced role or login to test privileges. See Testing Data Scripts for the full reference.