Skip to main content

Batched Loops: script loop and loop policy

A script "loop" runs a do body repeatedly, once per row page of an optional source, with each iteration wrapped in its own transaction by default. Atlas manages the loop mechanics, the cursor state, iteration counter, range position, and transaction boundary, while you write the SQL statements themselves.

Use it for data migrations that are too large to run as a single statement: purging inactive users and their dependent rows, backfilling a new column across millions of records, or re-encrypting and anonymizing PII in bounded chunks.

Examples​

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

This deletes inactive users and their posts in keyset batches, ramps the batch size up, records an audit row per batch, and verifies the result with post-loop asserts:

purge.hcl
script "loop" "purge_inactive" {
  # Pre-loop safety gate: run once, before the iterator opens.
  condition "have_inactive" {
    sql = "SELECT count(*) > 0 FROM users WHERE active = 0"
  }

  # The source: page the inactive users by id. `init` reads the first page,
  # `next` seeks past the last id of the previous one.
  iterator "keyset" {
    cursor {
      id = int
    }
    batch {
      id = int
    }
    init {
      sql = "SELECT id FROM users WHERE active = 0 ORDER BY id LIMIT ${ramp.size}"
    }
    next {
      sql  = "SELECT id FROM users WHERE active = 0 AND id > ? ORDER BY id LIMIT ${ramp.size}"
      args = [cursor.id]
    }
  }

  # The body: runs once per page, in its own transaction, over the ids the
  # iterator just read. Dependents first, then the users themselves.
  do {
    exec {
      sql  = "DELETE FROM posts WHERE user_id IN (SELECT value FROM json_each(?))"
      args = [jsonencode(iterator.keyset.batch[*].id)]
    }
    exec {
      sql  = "DELETE FROM users WHERE id IN (SELECT value FROM json_each(?))"
      args = [jsonencode(iterator.keyset.batch[*].id)]
    }
    exec {
      sql  = "INSERT INTO audit (batch, deleted) VALUES (?, ?)"
      args = [self.index, length(iterator.keyset.batch)]
    }
    log {
      message = "batch ${self.index}: purged ${length(iterator.keyset.batch)} users"
    }
  }

  # Post-loop verification: runs once after the loop ends.
  assert "all_gone" {
    sql = "SELECT count(*) = 0 FROM users WHERE active = 0"
  }
  assert "no_orphan_posts" {
    sql = "SELECT count(*) = 0 FROM posts WHERE user_id NOT IN (SELECT id FROM users)"
  }

  policy {
    ramp {
      stage {
        size       = 2
        iterations = 2
      }
      stage { size = 5 }
    }
  }
}
Example execution

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

setup.sql
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, active INTEGER NOT NULL);
CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL);
CREATE TABLE audit (batch INTEGER, deleted INTEGER);

-- 12 users (ids 1..12); every 4th is active, so 9 are inactive. Two posts each.
INSERT INTO users (id, name, active)
  WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 12)
  SELECT n, 'user' || n, (n % 4 = 0) FROM s;
INSERT INTO posts (id, user_id)
  SELECT u.id * 10 + p.k, u.id FROM users u, (SELECT 1 AS k UNION SELECT 2) p;

Run the script:

atlas script loop --url "sqlite://purge.db" --file "file://purge.hcl" --run purge_inactive

Atlas streams a per-step report as the loop runs, grouping each iteration under its own header. Each iteration opens its own transaction, the default PER_ITERATION mode, so a failure rolls back one batch. The ramp pages the 9 inactive users as 2, 2, 5: the first stage runs size = 2 for two iterations, then the final stage moves to size = 5. The three active users and their posts survive:

sqlite3 purge.db "SELECT count(*) FROM users; SELECT count(*) FROM posts; SELECT batch, deleted FROM audit ORDER BY batch"
3
6
0|2
1|2
2|5

The pre-loop condition stops a run that has nothing to purge, and the two post-loop asserts run once after the loop ends.

Loop Structure​

A script "loop" body has three groups, and the parser enforces this top-level order:

  1. Pre-loop: zero or more condition blocks. They run once, before the iterator opens. A falsy condition stops the run cleanly (no transaction, no rollback); use it to stop a run that has nothing to do. A NULL result is an error that fails the run, as on the exec page.
  2. The loop: exactly one do { } (required, non-empty) and at most one iterator "<mode>" { } (optional; omit it for a source-less loop).
  3. Post-loop: zero or more assert / check blocks. They run once after the loop ends, whether the iterator exhausted, an in-do break fired, or schedule.limit / timeout was reached, outside any transaction; a failure aborts the run. A run stopped by a pre-loop condition never reaches them.

The optional policy { } block sits alongside these groups and allows controlling the transaction boundary, the pacing and back-pressure, and the staged ramp-up of the batch size. See The policy block.

The iterator Block​

The iterator block supplies the pages the do body runs over. A loop has at most one, and its label selects the mode: keyset pages through a table with a cursor, and range walks a numeric interval. Omit the block and the do body repeats. The one-iterator rule is enforced per mode, so declare a single iterator: a second block with a different mode parses and is then ignored.

The keyset iterator​

keyset pages through a table with a cursor: you write the paginated SELECT, and Atlas threads the cursor value from the last page into the next one. It has four sub-blocks:

  • cursor { <col> = <type> }: the seek state carried across iterations, bound inside next.args as cursor.<col>.
  • batch { <col> = <type> }: optional. The page exposed to do. Defaults to the cursor columns.
  • init { sql, args }: runs on iteration 1, before any cursor exists.
  • next { sql, args }: runs on iterations 2+, with cursor bound from the last row of the previous page.

cursor and batch columns are matched against the result set by name, so alias the column in the SELECT when it is an expression. A mismatch aborts the run with column "x" not found in query result.

For example, this pages inactive users by id, 500 at a time:

iterator "keyset" {
  cursor {
    id = int
  }
  init {
    sql = "SELECT id FROM users WHERE active = 0 ORDER BY id LIMIT 500"
  }
  next {
    sql  = "SELECT id FROM users WHERE active = 0 AND id > ? ORDER BY id LIMIT 500"
    args = [cursor.id]
  }
}

The cursor and batch references resolve by lexical scope, so the form depends on where you write them:

  • Inside the iterator, in next.args, write the bare cursor.<col>.
  • Inside do, write iterator.keyset.batch[*].<col> for the page as a list. The body runs once per page, not once per row, so write set-based SQL over the batch. iterator.keyset.cursor.<col> is the last row of the page, not the current row.

Always bind values through args = [...], using your engine's placeholder marker, and never string-interpolate row data. The only ${...} interpolation allowed is for trusted structural scalars that cannot be bound parameters, such as LIMIT ${ramp.size}.

Cursor and batch types

The column blocks accept four bare type identifiers: int, number, string, and bool. Typing them lets the editor validate field access statically and complete iterator.keyset.batch[*].<col>.

For a table whose id repeats per bucket, a single-column seek skips or double-processes rows at bucket boundaries. Declare both columns in cursor and seek on the tuple, on SQLite WHERE (bucket, id) > (?, ?) with args = [cursor.bucket, cursor.id].

The range iterator​

range walks from..to in step-sized chunks. step defaults to 1, and the last chunk clamps to to, so walking 1..10 in step-4 chunks yields [1,4], [5,8], [9,10]. Inside do, the chunk is exposed as iterator.range.from, iterator.range.to, and iterator.range.step.

For example, this backfills ids 1..1000 in chunks of 100:

script "loop" "tier_backfill" {
  iterator "range" {
    from = 1
    to   = 1000
    step = 100
  }
  do {
    exec {
      sql  = "UPDATE users SET tier = 'standard' WHERE id BETWEEN ? AND ? AND tier IS NULL"
      args = [iterator.range.from, iterator.range.to]
    }
  }
}

Source-less loops​

Omit the iterator block and the do body repeats. Without a source there is no natural exhaustion, so the bound is checked at parse time: a source-less loop must set policy.schedule.limit or policy.schedule.timeout, or contain a break, otherwise the run fails with ``a loop without an iterator must be bounded by schedule.limit/timeout or a break```. self.indexis still available as the 0-based iteration counter. For example, thedrain` loop below deletes queue rows two at a time until the queue is empty:

script "loop" "drain" {
  do {
    break { sql = "SELECT NOT EXISTS (SELECT 1 FROM queue)" }
    exec  { sql = "DELETE FROM queue WHERE id IN (SELECT id FROM queue ORDER BY id LIMIT 2)" }
  }
}

There is no iterator "until" mode; to loop until a condition holds, write a source-less loop with a break, as drain does above.

The do Block​

The do block runs its commands in textual order. Flow control inside the body is break and continue; condition is valid only pre-loop, at the script level. The supported commands are:

commandpurpose
exec ["<name>"] { sql, args, expect_rows }a mutation
query "<name>" { sql, args, rows { <col> = <type> } }a read; feeds later commands
assert ["<name>"] { sql, args, error_message }fail the run if falsy/NULL
check ["<name>"] { sql, args, format, output | match, error_message }compare read output
break ["<name>"] { sql, args }stop the whole loop when true
continue ["<name>"] { sql, args }skip the rest of this iteration when true
log { message }diagnostics (suppressed under --quiet)
output { message }script output (kept under --quiet)
sleep { duration }pause, for example "500ms", "5s"
http ["<name>"] { url, method, body, expect_status }call a service, see The http command
tx { }group commands into one transaction, see The tx command
on_error = ABORT | CONTINUEthe loop's failure policy (attribute, see below)

http and tx require policy.tx.mode = MANUAL. Names in brackets are optional; only a query must be named, since later commands address it as query.<name>.rows. An http also needs a name once it declares a response or a check. Unlike in an exec script, a do-body exec's expect_rows is evaluated eagerly, so it cannot reference a value produced later in the same iteration.

The query command​

A query reads a page into query.<name>.rows. Later commands reference it 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.

A bound query emits nothing, so a purge can read a page, break when it comes back empty, and delete the same rows[*].id.

break and continue​

Both commands run a boolean SELECT with bind args and commit the work done so far. break stops the whole loop, and continue skips the rest of the current iteration. Each runs in its written position, so a break after an exec lets that exec commit before the loop ends.

log and output​

Both commands interpolate scope through ${...} and differ only under -q / --quiet: log is diagnostics and is suppressed along with the streaming report, while output is the script's product and is printed either way.

The tx command​

A tx { } command makes part of an iteration atomic while the rest autocommits. Use it when some commands must persist regardless of the batch outcome and others must not. For example, this settle loop audits the whole page outside any transaction, then validates and deletes it inside the command, so a failed validation leaves the rows intact while their audit rows survive:

do {
  on_error = CONTINUE
  exec {                                       # audit: outside any tx, always persists
    sql  = "INSERT INTO audit (id) SELECT value FROM json_each(?)"
    args = [jsonencode(iterator.keyset.batch[*].id)]
  }
  tx {                                         # atomic group: rolls back on a failed assert
    assert "valid" {
      sql  = "SELECT count(*) = 0 FROM items WHERE id IN (SELECT value FROM json_each(?)) AND ok = 0"
      args = [jsonencode(iterator.keyset.batch[*].id)]
    }
    exec {
      sql  = "DELETE FROM items WHERE id IN (SELECT value FROM json_each(?))"
      args = [jsonencode(iterator.keyset.batch[*].id)]
    }
  }
}

policy {
  tx {
    mode = MANUAL
  }
}
Nested transactions

A tx { } command is a parse error unless policy.tx.mode = MANUAL; under the default PER_ITERATION it would nest inside the per-iteration transaction. An empty command is also rejected.

A tx { } accepts only exec, assert, and check commands. Anything else, including a nested query, http, log, or sleep, is a parse error.

The http command​

An http command sends the current batch to a service's API, for side effects outside the database such as blob storage, caches, and downstream services. url, headers, and body interpolate the iteration scope, so the body carries this batch's ids: reference a preceding query command as query.<name>.rows[*].<col>, or a keyset page as iterator.keyset.batch[*].<col>. method is decoded once at parse time and does not interpolate.

expect_status asserts the reply: a status outside it fails the command and, per on_error, the loop.

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 commands after it still run. In the deliver-then-mark pattern below, that marks a batch done that was never delivered. 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 check { }. Extra keys are dropped, and declared fields missing from the response decode as null. For example:

do {
  // Read the page this iteration delivers. The http body below
  // references its result as query.batch.rows[*].id.
  query "batch" {
    sql = "SELECT id FROM outbox WHERE delivered = 0 ORDER BY id LIMIT 100"
    rows {
      id = int
    }
  }
  // Nothing left to deliver: stop the loop.
  break {
    sql  = "SELECT ? = 0"
    args = [length(query.batch.rows)]
  }
  http "hook" {
    url           = "${var.endpoint}/deliver"
    method        = POST
    headers       = { Content-Type = "application/json" }
    body          = jsonencode({ ids = query.batch.rows[*].id })
    expect_status = 200
    response      = object({ ok = bool })
    check {
      condition     = http.hook.ok == true
      error_message = "delivery service reported not-ok"
    }
  }
  // Mark the page delivered only after the call succeeds.
  exec {
    sql  = "UPDATE outbox SET delivered = 1 WHERE id IN (SELECT value FROM json_each(?))"
    args = [jsonencode(query.batch.rows[*].id)]
  }
}

policy {
  tx {
    mode = MANUAL
  }
}

The command 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. Two constraints apply: 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.

No rollback for HTTP

The http command is allowed only under policy.tx.mode = MANUAL. A request has already left the process, so there is nothing to roll back, and the default PER_ITERATION rejects it at parse time. Deliver first and mark rows done after, so a failed call leaves them pending and redelivered on the next run.

The on_error attribute​

on_error decides whether the loop proceeds after an iteration's body fails:

  • ABORT (default): stop the loop and surface the error.
  • CONTINUE: skip the failed iteration and move on.

It is independent of the transaction outcome, which the tx structure decides. Under the default PER_ITERATION mode a failing batch rolls back either way, and CONTINUE only means the loop moves on instead of aborting.

The policy Block​

policy is optional and holds three independent sub-blocks: tx sets the transaction boundary, schedule paces the loop, and ramp grows the batch size.

policy {
  tx { mode = PER_ITERATION }  # or MANUAL
  schedule {
    every      = "5m"
    limit      = 100000
    timeout    = "6h"
    pause_when = "SELECT replica_lag() > 2"
  }
  ramp {
    stage {
      size       = 5
      iterations = 2
    }
    stage { size = 500000 }
  }
}

The tx block​

tx.mode decides how much of each do iteration is atomic.

modeBehavior
PER_ITERATIONDefault. Wraps the whole do body of one iteration in a single transaction. A failure rolls back the entire batch.
MANUALNo automatic wrap. Commands autocommit, unless you group them in a tx command.

Enum values may be bare or quoted (mode = MANUAL or "MANUAL"); a typo fails at parse. Choose MANUAL when part of an iteration must be atomic and the rest must persist regardless of the outcome, and group the atomic part with the tx command.

Destructive loops

A single loop can cascade DELETEs across several tables, and Atlas never parses or synthesizes that SQL. Keep the loop batched (a LIMITed page per iteration) and transactional (the default PER_ITERATION mode), so a failure rolls back one batch rather than corrupting the table. Verify the result with post-loop assert/check blocks, as the examples above do.

The schedule block​

schedule bounds and paces the loop:

AttributeMeaning
everyFixed delay between iterations (for example "5m"). Omit for back-to-back.
limitMaximum number of iterations before the loop stops.
timeoutWall-clock bound, checked between iterations (for example "6h").
pause_whenA boolean SQL probe. While it returns true the loop pauses and re-probes, at the every interval or once a second if every is unset.

pause_when applies back-pressure to the loop. You write the full predicate, so any signal works: replica lag, active connections, lock waits, or queue depth. It pauses the loop without failing it.

Neither limit nor timeout interrupts work already in flight: both are evaluated between iterations, so a long single iteration runs to completion past the deadline. Keep each batch small enough that the bound is meaningful.

every also gives a source-less loop a fixed cadence, turning it into a monitor or drain that runs do on a timer until a break fires or limit or timeout is reached.

The ramp block​

A ramp grows the batch size in stages: the first iterations run at a small size before later stages advance to the full one. It is an ordered list of stage blocks. Each non-final stage runs at its size for a hold, either an iteration count (iterations) or a duration (hold), before advancing to the next. The final stage needs only a size, the steady state; it may still set a pause, and an iterations or hold on it is accepted but ignored.

The current stage's size is exposed to the iterator SQL as ${ramp.size}. It is interpolated rather than bound through args, since not every engine accepts a bound row limit. Without a ramp, ${ramp.size} is undefined and the run fails with There is no variable named "ramp", so an iterator that interpolates it must declare one.

For example, this pages five rows at a time for the first five iterations, then twenty, with the iterator reading the current size from ${ramp.size}:

iterator "keyset" {
  cursor {
    id = int
  }
  init {
    sql = "SELECT id FROM users WHERE active = 0 ORDER BY id LIMIT ${ramp.size}"
  }
  next {
    sql  = "SELECT id FROM users WHERE active = 0 AND id > ? ORDER BY id LIMIT ${ramp.size}"
    args = [cursor.id]
  }
}

policy {
  ramp {
    stage {
      size       = 5
      iterations = 5      # five small batches first
    }
    stage { size = 20 }   # then steady state
  }
}

A stage may hold for a wall-clock duration (hold) instead of an iteration count, and may set a pause between its iterations to slow the cadence within a stage. Ramp validation rejects a ramp with no stage, a size below 1, a non-final stage with neither iterations nor hold, a stage that sets both, and an unparsable hold.

Testing​

Loop scripts are tested with the Atlas testing framework: a test case seeds data, runs the loop end to end with the script "loop" command, and asserts on the rows it leaves behind and what it prints. An as block runs the same loop under a reduced role or login to test privileges. See Testing Data Scripts for the full reference.