Skip to main content

Data Scripts: Run Data Operations as Code

Data Scripts run data operations as code. Use HCL to define backfills, GDPR purges, re-encryption jobs, and one-off reports, and Atlas runs it with built-in transactions, SQL guards, and output masking. Because the script lives in code, it is versioned, reviewed, and tested in CI before it ever touches production data.

If you use Terraform, Data Scripts are Actions for your database: the Day 2 work that comes after the schema is in place, invoked on demand rather than recorded in your migration history.

Atlas Pro Feature

Data Scripts are available to Atlas Pro users that purchased Atlas Pipelines. To use this feature, run:

atlas login

Quick Start

Write a transactional mutation that runs only when the data is in the state you expect, and roll the whole unit back when a guard fails.

Script Types​

Atlas provides three runnable script types: exec for transactional mutations, query for reads and reports, and loop for batched or repeated work.

Each type uses the script "<type>" "<name>" wrapper and runs with its own verb. The examples below cover the three types, plus two capabilities they build on: masking the columns a script prints and calling external services from a batch.

A transactional mutation. The statements run in one transaction, guarded by condition, assert, and check:

scripts.hcl
script "exec" "cancel_pending" {
  exec {
    sql = "UPDATE orders SET status = 'canceled' WHERE status = 'pending'"
  }
}
atlas script exec --url "$URL" --file "file://scripts.hcl" --run '^cancel_pending$'

Transactional Mutations

Run a set of writes as one transactional unit with script exec: condition, assert, and check guards, with automatic rollback.

Queries and Reports

Run SELECTs with script query, serialize results as CSV or a table, and feed one query's rows into the next.

Batched Loops

Iterate a large table in transactional batches with script loop, with pacing, staged ramp-up, and back-pressure.

Masking Sensitive Output

Redact result columns before they leave Atlas with REDACT, PARTIAL, HASH, and REPLACE masks, applied inline or as reusable named masks.

Service Operations using HTTP

Call a service's API from a batch with the http command, to cascade a data operation to blob storage, caches, and search indexes.

Running on Kubernetes

Run scripts inside your cluster with the arigaio/atlas image, on a schedule as a CronJob or on demand as a Job.

Testing Data Scripts

Assert script logic and the privileges it runs under, with output and error checks and the as block for reduced roles.

When to Use Scripts​

Use a Data Script for ad-hoc or planned data work that needs a transaction, a pre-flight guard, or a controlled loop:

  • Data migrations and backfills: populate a new column, re-key rows, re-encrypt a field.
  • GDPR and privacy purges: delete or anonymize a user's data in bounded batches, and cascade to the services beyond the database, blob storage, caches, and search indexes, with the http command.
  • Reports: print a result set as CSV or a table, with sensitive columns masked before they leave Atlas.
  • Monitors and probes: run an assertion-only script on a schedule that fails when an invariant no longer holds, or a scheduled monitor that alerts when it does.

Use a migration instead when the change belongs to the schema itself, or when the data change must ship with it. A migration is a versioned file in the migration directory: it is applied once per database, in order, and Atlas records that it ran. A script has none of that bookkeeping. It runs whenever you invoke atlas script, as many times as you invoke it, so the script is responsible for its own safety, which is what condition guards and batch bounds are for. For lookup and reference rows that must match a desired state in every environment, use the declarative data block instead, as described in Seed Data as Code.

Running Scripts​

A script file is a flat list of top-level blocks: the runnable script wrappers above, plus the variable, locals, and mask declarations they reference. Every kind also accepts a description, which documents what the script does. Each script kind has its own verb, and each verb only runs scripts of its own kind:

atlas script exec  --url "$URL" --file "file://scripts.hcl" --run '^archive_user$'
atlas script query --url "$URL" --file "file://scripts.hcl" --run '^vip_count$'
atlas script loop  --url "$URL" --file "file://scripts.hcl" --run '^gdpr_purge_inactive$'

--url is the runtime database the script runs against, and --file is the script source: a single file, a directory, or a pushed atlas:// archive. Omit it to use the selected env's script.src.

Add -q / --quiet to print only the script's product, or --format to render the run report through a Go template, for example --format '{{ json . }}'. On a multi-target env, each target gets its own rendering (see Running on every tenant). Each kind page opens with worked examples: exec, query, and loop.

--run takes a regular expression matched against the script name, and it is worth being precise with it:

  • The pattern is not anchored, so --run purge also selects purge_v2. Anchor it ('^purge$') to run exactly one.
  • Every match runs. A failure in one script does not stop the others: the run continues and reports the errors together at the end, so scripts that already committed stay committed.
  • A pattern that matches nothing prints No scripts found to run and exits 0. In CI, assert on the output rather than the exit code, or a typo will pass silently.
  • Omit --run and every script of the verb's kind in the source runs.

Sharing scripts through the registry​

atlas script push uploads a script source to the Atlas Registry, and the commands then read it back by name:

atlas script push --env prod my-scripts
atlas script exec --url "$URL" --file "atlas://my-scripts" --run '^archive_user$'

A pushed archive accepts any .hcl file name, unlike a local directory. Point env.script.repo.name at the repository to push without naming it on the command line.

Directories load *.script.hcl

A --file pointing at a directory loads only files matching *.script.hcl, non-recursively, and fails with no *.script.hcl files found in directory when there are none. A directory holding a mix of *.script.hcl and plain *.hcl files silently skips the plain ones. Single files and repeated --file flags accept any name, as do atlas:// archives.

Running on every tenant​

When the selected env is defined with for_each, such as a database or a schema per tenant, atlas script exec, query, and loop run on every target in turn, in for_each order (sets and maps iterate in sorted order). Several env blocks sharing one name form a multi-target env as well. Each target runs with its own settings:

  • the database is the target's url;
  • the script source is the target's script.src, unless --file is given;
  • each script variable takes the target's own env attribute, such as tenant = each.value (see Variables).
atlas.hcl
locals {
  tenants = ["tenant_1", "tenant_2"]
}

env "prod" {
  for_each = toset(local.tenants)
  url      = "sqlite://${each.value}.db"
  tenant   = each.value
  script {
    src = "file://scripts"
  }
}

Each target prints its own report, one after another, with no header naming the target. Put the tenant in the script's output, in an output or log message built from a variable, to tell them apart:

scripts/tenants.script.hcl
variable "tenant" {
  type = string
}

script "query" "user_counts" {
  query "counts" {
    sql = "SELECT count(*) AS total, count(CASE WHEN plan = 'pro' THEN 1 END) AS pro FROM users"
    rows {
      total = int
      pro   = int
    }
  }
  output {
    message = "${var.tenant}: ${query.counts.rows[0].total} users, ${query.counts.rows[0].pro} on pro"
  }
}

script "exec" "backfill_email_norm" {
  condition "has_pending" {
    sql = "SELECT count(*) > 0 FROM users WHERE email_norm IS NULL"
  }
  query "pending" {
    sql = "SELECT count(*) AS n FROM users WHERE email_norm IS NULL"
    rows {
      n = int
    }
  }
  exec "fill" {
    sql         = "UPDATE users SET email_norm = lower(trim(email)) WHERE email_norm IS NULL"
    expect_rows = query.pending.rows[0].n
  }
  output {
    message = "${var.tenant}: backfilled ${query.pending.rows[0].n} rows"
  }
}
atlas script query --env prod --run '^user_counts$' --quiet
tenant_1: 4 users, 2 on pro
tenant_2: 3 users, 1 on pro
Example execution

The example is self-contained on SQLite. Create one database per tenant with sqlite3 tenant_1.db < setup_tenant_1.sql and sqlite3 tenant_2.db < setup_tenant_2.sql:

setup_tenant_1.sql
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, email_norm TEXT, plan TEXT, active INTEGER NOT NULL);
INSERT INTO users VALUES
  (1, ' Ada@Example.com ', NULL, 'pro', 1),
  (2, 'bob@example.com', 'bob@example.com', 'free', 0),
  (3, 'CARA@example.com ', NULL, 'pro', 0),
  (4, 'dan@example.com', 'dan@example.com', 'free', 0);
setup_tenant_2.sql
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, email_norm TEXT, plan TEXT, active INTEGER NOT NULL);
INSERT INTO users VALUES
  (1, 'Eve@Example.com', NULL, 'free', 1),
  (2, ' FRANK@example.com', NULL, 'free', 0),
  (3, 'gina@example.com', 'gina@example.com', 'pro', 1);

Run the backfill on both tenants:

atlas script exec --env prod --run '^backfill_email_norm$' --quiet
tenant_1: backfilled 2 rows
tenant_2: backfilled 2 rows

Verify the result on each tenant:

sqlite3 tenant_1.db "SELECT id, email_norm FROM users ORDER BY id"
sqlite3 tenant_2.db "SELECT id, email_norm FROM users ORDER BY id"
1|ada@example.com
2|bob@example.com
3|cara@example.com
4|dan@example.com
1|eve@example.com
2|frank@example.com
3|gina@example.com

Running the backfill again prints nothing: has_pending is false on both tenants, so each script stops cleanly.

The scripts are evaluated separately for each target, with that target's values, and nothing carries over from one target to the next. Across targets:

  • A target whose run fails stops the command. The targets after it do not run, and the command exits 1. Within one target, every script matching --run still runs and the errors are reported together.
  • --var and --var-file are shared by all targets.
  • --format renders one report per target, separated by a newline, so --format '{{ json . }}' prints one JSON document per line. The report has no target field, so identify the tenant from the script's output.
  • An atlas:// source is pulled from the registry once per command and reused for every target, so all targets run the same version even if a new one is pushed mid-run. Each target reports its own run to Atlas Cloud.
  • atlas script push, describe, and test take a single-target env and fail with multiple envs found for "<name>" on a multi-target one.
Don't pass --url with a multi-target env

--url on the command line overrides the url of every target. All targets then run against that one database, each with its own variables, so the output is labeled with the wrong tenants.

Variables​

Declare typed inputs with variable and reference them as var.<name>. Each variable binds from the first source that sets it:

  1. the selected env's extra attributes; on a multi-target env, the target's own attributes;
  2. --var name=value;
  3. --var-file;
  4. the variable's default.

An env attribute that evaluates to null counts as unset, so the next source applies. A for_each env can use this to set a value for some targets only.

--var-file takes the path to an HCL or JSON file of values, or - to read JSON from stdin. JSON keeps its types, so map and object variables can be set from it:

echo '{"plan": "pro"}' | atlas script query --env prod --run '^plan_users$' --var-file -

On a multi-target env, --var and --var-file are shared by all targets, and stdin is read once. A target that sets the variable as an env attribute keeps its own value. For example, with a plan variable added to the tenant scripts:

scripts/tenants.script.hcl
variable "plan" {
  type    = string
  default = "free"
}

script "query" "plan_users" {
  query "count" {
    sql  = "SELECT count(*) AS users FROM users WHERE plan = ?"
    args = [var.plan]
    rows {
      users = int
    }
  }
  output {
    message = "${var.tenant}, ${var.plan} users: ${query.count.rows[0].users}"
  }
}

The prod env sets no plan, so --var plan=pro reaches both tenants:

atlas script query --env prod --run '^plan_users$' --quiet --var plan=pro
tenant_1, pro users: 2
tenant_2, pro users: 1

An env that sets plan per tenant wins over --var:

atlas.hcl
env "tenants" {
  for_each = {
    tenant_1 = { plan = "pro" }
    tenant_2 = { plan = "free" }
  }
  url    = "sqlite://${each.key}.db"
  tenant = each.key
  plan   = each.value.plan
  script {
    src = "file://scripts"
  }
}
atlas script query --env tenants --run '^plan_users$' --quiet --var plan=free
tenant_1, pro users: 2
tenant_2, free users: 2

SQL placeholders​

Atlas never parses or rewrites your SQL: you write it in your database's own dialect, and placeholders are driver-native (? on MySQL, SQLite, and ClickHouse; $1 on PostgreSQL; @p1 on SQL Server; :1 on Oracle). Arguments bind scalars only.

To pass a list, jsonencode(...) it and expand it back into rows in SQL. For example, binding args = [jsonencode(ids)] and expanding it in the IN clause:

EngineExpand in SQL
SQLiteIN (SELECT value FROM json_each(?))
PostgreSQLIN (SELECT value::int FROM json_array_elements_text($1::json))
MySQLIN (SELECT v FROM JSON_TABLE(?, '$[*]' COLUMNS (v INT PATH '$')) t)
ClickHouseIN (SELECT arrayJoin(JSONExtract(?, 'Array(Int64)')))
SQL ServerIN (SELECT CAST(value AS INT) FROM OPENJSON(@p1))
OracleIN (SELECT jt.v FROM JSON_TABLE(:1, '$[*]' COLUMNS (v NUMBER PATH '$')) jt)

The examples throughout these pages use SQLite's ? placeholder and json_each(?) expansion.

Engine support for JSON-to-rows

JSON_TABLE requires MySQL 8.0.4 or later, or MariaDB 10.6 or later. On older MySQL, and on engines without table functions, pass the values as individual placeholders or join against a temporary table instead.

The streaming report​

A run streams a per-step report as it goes: condition guards, execs, checks, and the transaction's commit or rollback, followed by a closing summary. Durations are reported for exec statements, loop iterations, and http calls. Add -q / --quiet to drop the report and print only the script's product. Running the all_users query from above:

atlas script query --url "$URL" --file "file://scripts.hcl" --run '^all_users$'
Executing script "all_users" (scripts.hcl:1):

  -- query "all" (scripts.hcl:2)

  1,ada@example.com
  2,grace@example.com

  -------------------------
  -- 58µs
  -- 1 query