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 within one transaction to ensure it completes the job entirely or not at all:

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 (default) wraps the whole body in one transaction
  • mode = NONE runs the statements without a transaction

on_error decides what happens to the transaction on failure:

  • on_error = ROLLBACK (default) discards the transaction's work when the script fails
  • on_error = COMMIT keeps the results of the transation

Omitting the tx block defaults to mode = AUTO and on_error = ROLLBACK.

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

Enum values may be bare or quoted (AUTO or "AUTO"), and a typo fails at parse time.

note

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:

  1. The body completes and the transaction commits,
  2. An unmet condition or a fired break stops the script and commits the work done so far, or
  3. A failing assert or check aborts the run and rolls it back.

An exec body requires 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 a guard on the script, so it may only appear at the beginning of the body, before every other block. Define several to form a set of preconditions that must all hold for the run to start. To stop in the middle of a body instead, once a read or a write has already run, use a break.

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 break Block​

A break stops the run when its condition is true. It takes either sql (with args) evaluated in the database, or expr, a boolean expression evaluated in-process over the script scope. The two are mutually exclusive, and one of them is required, with args belonging to sql alone. The name label is optional and appears in the report.

A condition guards the whole script and may only appear at the beginning of the body. A break runs in its written position, so it can stop on what a read or a write before it produced.

settle.hcl
script "exec" "settle_pending" {
  query "pending" {
    sql = "SELECT id FROM payments WHERE status = 'pending' ORDER BY id LIMIT 500"
    rows {
      id = int
    }
  }

  # Nothing to settle: stop before writing anything.
  break "nothing_pending" {
    expr = length(query.pending.rows) == 0
  }

  # Back-pressure: stop rather than add load.
  break "disk_pressure" {
    sql = "SELECT pg_database_size(current_database()) > 5e11"
  }

  exec "settle" {
    sql         = "UPDATE payments SET status = 'settled' WHERE id IN (SELECT value FROM json_each(?))"
    args        = [jsonencode(query.pending.rows[*].id)]
    expect_rows = length(query.pending.rows)
  }
}

To stop one iteration of a batched run instead of the whole script, use the break command in a script "loop".

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. A passing check lets the script proceed; a failing one aborts it.

Use output for an exact match or match for a regular expression. The two are mutually exclusive. output is compared after trimming leading and trailing newlines only, so surrounding spaces are significant (e.g. 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 http Block​

An http block calls an external endpoint for the side effects a mutation leaves outside the database: blob storage, caches, search indexes, and downstream services. url, headers, and body interpolate the script scope, so the request can carry rows a preceding query block captured, referenced as query.<name>.rows[*].<col>. method is decoded once at parse time, defaults to POST, and does not interpolate.

expect_status asserts the reply. Any other status fails the step and aborts the run. Since the block runs without a transaction, per the caution below, the writes that already ran stay committed, so what survives a failed call depends on where it sits among the script's blocks.

Always set expect_status

expect_status is optional and has no default. Without it Atlas never inspects the status, so a 4xx or 5xx counts as a successful step and the blocks after it still run. In the call-then-write pattern below, that deletes the rows pointing at blobs that were never removed. Set expect_status, or assert the decoded body with a check.

An HTTP 200 can still carry an application-level failure. Declare the response shape with response = object({ ... }) to expose its fields as http.<name>.<field>, then assert over them with a nested check { }. Extra keys are dropped, and declared fields missing from the response decode as null. The fields are part of the script scope, so a later exec or assert can bind them through args as well. The name label is optional, as it is for exec, but a block that declares a response or a check needs one, since both address it as http.<name>.

No rollback for HTTP

The http block is allowed only under tx { mode = NONE }. A request has already left the process, so there is nothing to roll back, and the default mode = AUTO rejects it. Statements then autocommit, which is the goal. Call the service first and record the outcome after, so a failed call leaves the database in a state a rerun can recover from.

The script below deletes a user's blobs in storage first, and drops the rows that point at them only after the service confirms:

erase_blobs.hcl
variable "user_id" {
  type = number
}

variable "storage_endpoint" {
  type = string
}

script "exec" "erase_blobs" {
  # No transaction: a fired request cannot be rolled back.
  tx {
    mode = NONE
  }

  # Read the keys this run deletes. The http body below references them.
  query "blobs" {
    sql  = "SELECT key FROM user_blobs WHERE user_id = ?"
    args = [var.user_id]
    rows {
      key = string
    }
  }

  # Delete them in storage, and verify the service agrees it did.
  http "purge" {
    url           = "${var.storage_endpoint}/delete"
    method        = POST
    headers       = { Content-Type = "application/json" }
    body          = jsonencode({ keys = query.blobs.rows[*].key })
    expect_status = 200
    response      = object({ ok = bool })
    check {
      condition     = http.purge.ok == true
      error_message = "storage service reported not-ok"
    }
  }

  # Drop the rows only after the blobs are gone.
  exec "drop_rows" {
    sql         = "DELETE FROM user_blobs WHERE user_id = ?"
    args        = [var.user_id]
    expect_rows = length(query.blobs.rows)
  }
}

Reporting the result​

A block can also report the run rather than drive it to Slack, a webhook, or an incident tool. body interpolates the same scope an output line does, so this closes the archive_dormant script with the count it archived:

http "report" {
  url    = "https://slack.com/api/chat.postMessage"
  method = POST
  headers = {
    "Content-type"  = "application/json"
    "Authorization" = "Bearer ${var.slack_token}"
  }
  body = jsonencode({
    channel = "ABCDEFGHIJK"
    text    = "archived ${length(query.dormant.rows)} dormant accounts"
  })
}

Placed after the writes it reports, the block is reached only once every guard before it passed. A failing assert aborts the run and no message is sent. Slack answers 200 and reports a refusal in the body, so verify delivery of the report itself with response = object({ ok = bool }) and a check.

The block mirrors the data "http" block. Wrap it in a retry { } block to retry a transient failure with exponential backoff (attempts, min_delay_ms, max_delay_ms), and configure TLS with ca_cert_pem, client_cert_pem, client_key_pem, and insecure. ca_cert_pem and insecure are mutually exclusive, and client_cert_pem and client_key_pem must be set together. request_timeout_ms is not TLS configuration. It bounds the whole request, on plain http:// too.

The body requirement is unchanged. An http block is not one of the blocks that make an exec body do something, so pair it with an exec, assert, or check, as the script above does. To make the same call once per batch of a large mutation, use the http command in a script "loop".

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.