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.

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.

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 . }}'. 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.

Variables​

Declare typed inputs with variable and reference them as var.<name>. Values bind from the --var flag or the selected env's extra attributes, with the env attribute winning when both set the same key and the default flowing in when neither does, exactly as in the schema commands.

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